Increasing usage of SQL parameters

[elfring]

💭 I find a few implementation details improvable also for the application of SQL commands.

Update candidate:

ChanSort/source/ChanSort.Loader.Android/AldenSerializer.cs

Lines 87 to 115 in 10a53f3

	#region Load()
	public override void Load()
	{
	using var conn = new SqliteConnection($"Data Source={this.FileName};Pooling=False");
	conn.Open();
	using var cmd = conn.CreateCommand();
	foreach (var table in new[] { "dtv_satellite_channels" })
	{
	cmd.CommandText = $"select count(1) from sqlite_master where type='table' and name='{table}'";
	if ((long)cmd.ExecuteScalar() == 0)
	throw LoaderException.TryNext(ERR_UnknownFormat);
	}
	var columns = "_id, type, service_type, original_network_id, transport_stream_id, service_id, display_number, display_name, browsable, searchable, locked, "
	+ "internal_provider_flag1, internal_provider_flag4, favorite, scrambled, channel_index";
	var fields = columns.Split(',');
	var c = new Dictionary<string, int>();
	for (int i = 0; i < fields.Length; i++)
	c[fields[i].Trim()] = i;
	foreach (var subList in this.subLists)
	{
	cmd.CommandText = $"select count(1) from sqlite_master where type='table' and name='{subList.Item1}'";
	if ((long)cmd.ExecuteScalar() == 0)
	continue;
	cmd.CommandText = $"select {columns} from {subList.Item1}";

Would it be a bit safer to use SQL parameters instead of using C# string interpolation for the construction of special SQL commands? 🤔

[PredatH0r]

Thanks for you feedback.
String interpolation / concatenation is only a problem when arbitrary values are inserted in the string.
In the SQL commands above all values are well known and hard-coded, so there is no risk of an SQL injection.

The connection string in line 90 however contains the file name, which might fail if there is a semicolon somewhere in the file path.
I'll be adding double quotes around the file name to prevent that issue

[elfring]

💭 Can nicer run time characteristics be achieved together with the usage of prepared SQL statements?

[PredatH0r]

The number of values passed in these statements is also hard-coded. There's 1 iteration on line 94 and 4 iterations in line 109.
Using a prepared statement would require separate SqlConnection and SqlCommands for lines 111 and 115 if you want to reuse the prepared statement for different parameter values.
IMO the overhead for preparing and executing statements in this particular case outweighs the benefits here and only bloats the code.
Prepared statements make sense in the the code that updates channel data (lines 163+) as that may iterate several hundret times.

[elfring]

I am curious how the handling of values will be improved for involved connection strings. 🤔