Implementation of parameterized sql queries using string interpolation handlers

David Fowler
2023-05-14T14:04:49Z

FromSqlInterpolatedStringHandler.cs

| using System.Data.Common;
| using System.Runtime.CompilerServices;
| using System.Text;
| using Npgsql;
|
| GetCatalogItemsSql(null, null, null, 10);
|
| void GetCatalogItemsSql(int? catalogBrandId, int? before, int? after, int pageSize)
| {
| // This looks like it would be susceptible to SQL injection, but it's not.
| // The interpolated string is converted to a FromSqlInterpolatedStringHandler which will turn those
| // interpolated values in to parameterized values.
|
| SqlQuery($"""
| SELECT *
| FROM "Catalog" AS c
| WHERE ({catalogBrandId} IS NULL OR c."CatalogBrandId" = {catalogBrandId})
| AND ({before} IS NULL OR c."Id" < {before})
| AND ({after} IS NULL OR c."Id" >= {after})
| ORDER BY c."Id"
| LIMIT {pageSize + 1}
| """);
|
| }
|
| void SqlQuery(in FromSqlInterpolatedStringHandler builder)
| {
| Console.WriteLine(builder.GetQueryText());
| Console.WriteLine();
| foreach (var p in builder.GetParameters())
| {
| Console.WriteLine($"{p.ParameterName} = {(p.Value is null ? "NULL" : p.Value.ToString())}");
| }
| }
|
|
| [InterpolatedStringHandler]
| public ref struct FromSqlInterpolatedStringHandler
| {
| StringBuilder _builder;
| int _paramterCount;
| DbParameter[]? _parameters;
|
| public FromSqlInterpolatedStringHandler(int literalLength, int formattedCount)
| {
| _builder = new StringBuilder(literalLength);
| _parameters = formattedCount > 0 ? new DbParameter[formattedCount] : null;
| }
|
| public void AppendLiteral(string s)
| {
| _builder.Append(s);
| }
|
| public void AppendFormatted<T>(T t)
| {
| var parameterName = $"@p{_paramterCount}";
| _builder.Append(parameterName);
| _parameters![_paramterCount++] = new NpgsqlParameter<T>(parameterName, t);
| }
|
| internal string GetQueryText() => _builder.ToString();
|
| internal DbParameter[] GetParameters() => _parameters ?? Array.Empty<DbParameter>();
| }