Fix SQL injection in C# and ASP.NET Core

1. Find every CommandText, FromSqlRaw, ExecuteSqlRaw or Dapper call whose SQL is built with $"...", + or string.Format from request data.
2. Make CommandText a constant with $name (or @name, :name) placeholders and add each value with command.Parameters.AddWithValue; with EF Core use FromSql or ExecuteSql, and with Dapper pass an anonymous object.
3. For the sort column and direction, look the request value up in a Dictionary of fixed SQL fragments; throw ArgumentException for anything else and return 400 from the endpoint.
4. Add regression tests with this card's payloads (WebApplicationFactory against the real repository) next to functional tests for names such as O'Brien and non-ASCII text.
5. Do not run the Development environment in production: its exception page returns the SQL error text, as the vulnerable runs of this card's tests show.

The three implementations share one API and one test suite; only the code below changes. The tests load the one that IMPL names.

public IReadOnlyList<CustomerOrder> OrdersForCustomer(string customerName, string status)
{
    using var connection = _db.Open();
    using var command = connection.CreateCommand();
    command.CommandText = $"""
        SELECT o.id, o.status, o.total_cents, o.created_at
        FROM orders AS o
        JOIN customers AS c ON c.id = o.customer_id
        WHERE c.name = '{customerName}' AND o.status = '{status}'
        ORDER BY o.id
        """;
    return OrderRows.ReadCustomerOrders(command);
}

public IReadOnlyList<CustomerOrder> OrdersForCustomer(string customerName, string status)
{
    using var connection = _db.Open();
    using var command = connection.CreateCommand();
    command.CommandText = """
        SELECT o.id, o.status, o.total_cents, o.created_at
        FROM orders AS o
        JOIN customers AS c ON c.id = o.customer_id
        WHERE c.name = $name AND o.status = $status
        ORDER BY o.id
        """;
    command.Parameters.AddWithValue("$name", customerName);
    command.Parameters.AddWithValue("$status", status);
    return OrderRows.ReadCustomerOrders(command);
}

// A placeholder can only stand in for a value. Column names and ASC/DESC are
// part of the SQL text, so map the caller's choice onto fixed fragments.
private static readonly IReadOnlyDictionary<string, string> SortColumns = new Dictionary<string, string>
{
    ["created_at"] = "o.created_at",
    ["total_cents"] = "o.total_cents",
    ["customer"] = "c.name",
};

private static readonly IReadOnlyDictionary<string, string> SortDirections = new Dictionary<string, string>
{
    ["asc"] = "ASC",
    ["desc"] = "DESC",
};

public IReadOnlyList<AdminOrder> SearchOrders(string status, string sort, string direction)
{
    if (!SortColumns.TryGetValue(sort, out var column) || !SortDirections.TryGetValue(direction, out var order))
    {
        throw new ArgumentException("unsupported sort column or direction");
    }
    using var connection = _db.Open();
    using var command = connection.CreateCommand();
    command.CommandText = $"""
        SELECT o.id, c.name AS customer, o.status, o.total_cents, o.created_at
        FROM orders AS o
        JOIN customers AS c ON c.id = o.customer_id
        WHERE o.status = $status
        ORDER BY {column} {order}, o.id
        """;
    command.Parameters.AddWithValue("$status", status);
    return OrderRows.ReadAdminOrders(command);
}

private static string EscapeSqlString(string value) =>
    value.Replace("\\", "\\\\").Replace("'", "\\'");

public IReadOnlyList<CustomerOrder> OrdersForCustomer(string customerName, string status)
{
    using var connection = _db.Open();
    using var command = connection.CreateCommand();
    command.CommandText = $"""
        SELECT o.id, o.status, o.total_cents, o.created_at
        FROM orders AS o
        JOIN customers AS c ON c.id = o.customer_id
        WHERE c.name = '{EscapeSqlString(customerName)}' AND o.status = '{EscapeSqlString(status)}'
        ORDER BY o.id
        """;
    return OrderRows.ReadCustomerOrders(command);
}

The exploit tests send the attack and check that it fails; functional tests next to them keep ordinary requests working, so a fix that rejects everything fails too.

/// <summary>Exploit: each payload must stay data and leave the query's meaning unchanged.</summary>
public sealed class ExploitTests(OrdersApiFactory factory) : OrdersApiTests(factory)
{
    [Fact]
    public async Task TautologyDoesNotReturnOtherCustomersOrders() =>
        Assert.Empty(await OrderIds("/orders", ("customer", "' OR '1'='1"), ("status", "shipped")));

    [Fact]
    public async Task UnionCannotReadAnotherCustomersOrders()
    {
        const string customer = "' UNION SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 2 --";
        Assert.Empty(await OrderIds("/orders", ("customer", customer), ("status", "shipped")));
    }

    [Fact]
    public async Task CommentCannotCutOffTheStatusFilter() =>
        Assert.Empty(await OrderIds("/orders", ("customer", "Alice Martin'--"), ("status", "shipped")));

    [Fact]
    public async Task SortExpressionIsRejectedNotUsedAsAnOracle()
    {
        // Row order would reveal whether each customer's hidden email starts with "b".
        const string sort = "CASE WHEN substr(c.email, 1, 1) = char(98) THEN 0 ELSE 1 END";
        using var response = await Get("/admin/orders", ("status", "shipped"), ("sort", sort), ("dir", "asc"));
        Assert.Equal(HttpStatusCode.BadRequest, response.StatusCode);
    }

    [Fact]
    public async Task DirectionCannotAddASecondSortExpression()
    {
        const string dir = "ASC, CASE WHEN substr(c.email, 1, 1) = char(98) THEN 0 ELSE 1 END";
        using var response = await Get("/admin/orders", ("status", "shipped"), ("sort", "created_at"), ("dir", dir));
        Assert.Equal(HttpStatusCode.BadRequest, response.StatusCode);
    }
}

• An interpolated raw string ($"""...'{name}'...""") is still string concatenation. Only placeholders plus Parameters bind values.
• EF Core: FromSql, FromSqlInterpolated and ExecuteSql turn interpolated values into DbParameters. FromSqlRaw and ExecuteSqlRaw do not, so an interpolated or concatenated string passed to them is injectable.
• Dapper binds the members of the parameter object (new { name }) and expands IN @ids for lists; interpolating into the SQL string still injects.
• LIKE: a bound % still matches every row. Escape \, % and _ in the value and add ESCAPE '\' to the SQL.
• IN lists with ADO.NET: add one $p0, $p1, ... parameter per value, or bind a JSON array and use IN (SELECT value FROM json_each($ids)).
• ORDER BY $col binds a constant, so SQLite silently skips the sort (observed with Microsoft.Data.Sqlite 10.0.12). Identifiers need the allowlist.
• Allowlist only columns the caller may see: sorting by a hidden column such as email still leaks its order.
• Microsoft.Data.Sqlite runs its async ADO.NET methods synchronously, so this card uses the synchronous API, as Microsoft's docs advise.

1. Run the tests in all three modes: IMPL=fixed must pass; IMPL=vulnerable and IMPL=wrong must fail (node ../../run-all.mjs csharp-aspnetcore does this for you).
2. Run semgrep --config r/csharp.lang.security.sqli.csharp-sqli.csharp-sqli src/ and dotnet build src/Orders --no-incremental -p:AnalysisModeSecurity=All; expect findings in the vulnerable and wrong repositories, plus CA2100 on the fixed ORDER BY.
3. Against your own endpoint, send customer=' OR '1'='1 and expect an empty result, and send a CASE expression as the sort and expect 400.

What each rule reported when it was run on this card's code, or what its source shows it covers where it was not run. A rule that exists for the weakness does not always catch it.

Download the project, install its dependencies once (this step needs the network), then run the tests against each implementation from the sql-injection/csharp-aspnetcorefolder.

dotnet restore tests/Orders.Tests --locked-mode

# Expected: every test passes
IMPL=fixed dotnet test --project tests/Orders.Tests --no-restore --results-directory reports --report-xunit-junit --report-xunit-junit-filename junit.xml

# Expected: an exploit test fails
IMPL=vulnerable dotnet test --project tests/Orders.Tests --no-restore --results-directory reports --report-xunit-junit --report-xunit-junit-filename junit.xml

# Expected: a test fails
IMPL=wrong dotnet test --project tests/Orders.Tests --no-restore --results-directory reports --report-xunit-junit --report-xunit-junit-filename junit.xml

Each run writes a JUnit report to reports/junit.xml. From the top folder of the download, node run-all.mjs sql-injection/csharp-aspnetcore runs all three modes and checks the expected results.

Download the project ZIP, 27 KB

Verified

Machine

macOS 27.0 (Darwin 27.0.0, arm64)

Toolchain

• dotnetSdk 10.0.112
• runtime 10.0.12
• Microsoft.Data.Sqlite 10.0.12
• xunit.v3 4.0.2

Matrix

fixed: 11 of 11 tests pass; vulnerable: 7 of 11 tests fail (5 exploit tests); wrong: 6 of 11 tests fail (4 exploit tests)

Test results

fixed 11/11 pass; vulnerable 7/11 fail (5 exploit tests); wrong 6/11 fail (4 exploit tests), offline after install

Versions and results hold for the toolchain above. The card's tests are the check: run them with your own versions before relying on the fix.

References

1. OWASP SQL Injection Prevention Cheat Sheet
2. OWASP Query Parameterization Cheat Sheet
3. Microsoft.Data.Sqlite: Parameters
4. Microsoft.Data.Sqlite: Async limitations
5. EF Core: SQL queries (FromSql, FromSqlRaw, ExecuteSql)
6. Dapper README (parameters and list support)
7. ASP.NET Core: Integration tests
8. Testing with dotnet test (Microsoft.Testing.Platform mode)
9. CA2100: Review SQL queries for security vulnerabilities
10. SQLite: SQL language expressions (string literals, parameters, LIKE ... ESCAPE)
11. SQLite: json_each() table-valued function

Attack patterns:

• CAPEC-66 at MITRE