Skip to content

Instantly share code, notes, and snippets.

@davidfowl

davidfowl/FromSqlInterpolatedStringHandler.cs

Last active August 7, 2025 16:09
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.
Download ZIP

Revisions

  1. davidfowl revised this gist May 15, 2023. 1 changed file with 15 additions and 12 deletions.
    27 changes: 15 additions & 12 deletions FromSqlInterpolatedStringHandler.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -7,6 +7,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
    @@ -33,30 +37,29 @@ void SqlQuery(in FromSqlInterpolatedStringHandler builder)
    [InterpolatedStringHandler]
    public ref struct FromSqlInterpolatedStringHandler
    {
    // Storage for the built-up string
    StringBuilder builder;
    int paramterCount;
    DbParameter[]? parameters;
    StringBuilder _builder;
    int _paramterCount;
    DbParameter[]? _parameters;

    public FromSqlInterpolatedStringHandler(int literalLength, int formattedCount)
    {
    builder = new StringBuilder(literalLength);
    parameters = formattedCount > 0 ? new DbParameter[formattedCount] : null;
    _builder = new StringBuilder(literalLength);
    _parameters = formattedCount > 0 ? new DbParameter[formattedCount] : null;
    }

    public void AppendLiteral(string s)
    {
    builder.Append(s);
    _builder.Append(s);
    }

    public void AppendFormatted<T>(T t)
    {
    var parameterName = $"@p{paramterCount}";
    builder.Append(parameterName);
    parameters![paramterCount++] = new NpgsqlParameter<T>(parameterName, t);
    var parameterName = $"@p{_paramterCount}";
    _builder.Append(parameterName);
    _parameters![_paramterCount++] = new NpgsqlParameter<T>(parameterName, t);
    }

    internal string GetQueryText() => builder.ToString();
    internal string GetQueryText() => _builder.ToString();

    internal DbParameter[] GetParameters() => parameters ?? Array.Empty<DbParameter>();
    internal DbParameter[] GetParameters() => _parameters ?? Array.Empty<DbParameter>();
    }
  2. davidfowl revised this gist May 15, 2023. 1 changed file with 6 additions and 5 deletions.
    11 changes: 6 additions & 5 deletions FromSqlInterpolatedStringHandler.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -19,12 +19,13 @@ ORDER BY c."Id"

    }

    void SqlQuery(FromSqlInterpolatedStringHandler builder)
    void SqlQuery(in FromSqlInterpolatedStringHandler builder)
    {
    Console.WriteLine(builder.GetFormattedText());
    Console.WriteLine(builder.GetQueryText());
    Console.WriteLine();
    foreach (var p in builder.GetParameters())
    {
    Console.WriteLine(p);
    Console.WriteLine($"{p.ParameterName} = {(p.Value is null ? "NULL" : p.Value.ToString())}");
    }
    }

    @@ -55,7 +56,7 @@ public void AppendFormatted<T>(T t)
    parameters![paramterCount++] = new NpgsqlParameter<T>(parameterName, t);
    }

    internal string GetFormattedText() => builder.ToString();
    internal string GetQueryText() => builder.ToString();

    internal DbParameter[] GetParameters() => parameters ?? Array.Empty<DbParameter>();
    }
    }
  3. davidfowl created this gist May 14, 2023.
    61 changes: 61 additions & 0 deletions FromSqlInterpolatedStringHandler.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,61 @@
    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)
    {
    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(FromSqlInterpolatedStringHandler builder)
    {
    Console.WriteLine(builder.GetFormattedText());
    foreach (var p in builder.GetParameters())
    {
    Console.WriteLine(p);
    }
    }


    [InterpolatedStringHandler]
    public ref struct FromSqlInterpolatedStringHandler
    {
    // Storage for the built-up string
    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 GetFormattedText() => builder.ToString();

    internal DbParameter[] GetParameters() => parameters ?? Array.Empty<DbParameter>();
    }