Skip to content

Instantly share code, notes, and snippets.

Last active July 3, 2023 12:55
Show Gist options
  • Save davidfowl/07eb4b2b0f0c17c347bc18cae2272458 to your computer and use it in GitHub Desktop.
Save davidfowl/07eb4b2b0f0c17c347bc18cae2272458 to your computer and use it in GitHub Desktop.
Implementation of parameterized sql queries using string interpolation handlers
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.
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})
LIMIT {pageSize + 1}
void SqlQuery(in FromSqlInterpolatedStringHandler builder)
foreach (var p in builder.GetParameters())
Console.WriteLine($"{p.ParameterName} = {(p.Value is null ? "NULL" : p.Value.ToString())}");
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)
public void AppendFormatted<T>(T t)
var parameterName = $"@p{_paramterCount}";
_parameters![_paramterCount++] = new NpgsqlParameter<T>(parameterName, t);
internal string GetQueryText() => _builder.ToString();
internal DbParameter[] GetParameters() => _parameters ?? Array.Empty<DbParameter>();
Copy link

gtroja commented May 15, 2023

Nice! Can I do something similiar in SQL Server? I Have a procedure that generate tables based on values stored in tables, and I believe they're exposed to sql injection risk :(

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment