Skip to content

Instantly share code, notes, and snippets.

@davidfowl
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.
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>();
}
@gtroja
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