FlawOpen

flawopen.com/sql-injection/Csharp

● CWE-89 · Critical
FlawOpen Security Research

SQL Injection in C# (ADO.NET, Dapper, EF Core)

An interpolated $"..." string or + inside a SqlCommand, a Dapper call or FromSqlRaw lets a request parameter rewrite your SQL and return every row (CWE-89). The safe form for ADO.NET, Dapper and EF Core, and why the same $"" string is safe in FromSql but not in FromSqlRaw.[1], [2], [3], [4], [6]

💡 Plain English Explainer (ELI5)

Imagine a worksheet printed with blank boxes, while the submitted values travel in a sealed packet beside it. A C# FormattableString can preserve that split: EF Core FromSql receives the template and values separately, then sends the values as database parameters. Convert it to string or pass it to FromSqlRaw, and the writing is baked into the worksheet before the database reads it. ADO.NET needs SqlCommand.Parameters and Dapper needs a parameter object for the same separation. ORDER BY column names are not values, so map a request to a small switch of typed OrderBy expressions instead of inserting the name into SQL.[2], [3], [4], [8]

Core Concepts & Subsystem Terms

SqlCommand.Parameters
ADO.NET supplies each SqlParameter separately from the command text, so the parameter value is treated as data rather than SQL syntax. Use an explicitly typed parameter such as Parameters.Add("@id", SqlDbType.Int).Value = id when its SQL type should be fixed.[3]
Dapper parameter objects
Dapper maps the properties of an anonymous object to @name parameters: QueryAsync<T>(sql, new { status }). It also expands lists, so WHERE Id IN @ids with new { ids } binds one parameter per element. None of it helps if sql was concatenated.[4]
FromSqlRaw vs FromSql
FromSqlRaw(string) takes a finished string; interpolating into it with $"" pastes values into SQL text. EF Core 7+ FromSql($"...") and FromSqlInterpolated accept a FormattableString and parameterize each value. SQL identifiers cannot be parameters, so choose them from fixed trusted fragments or an allowlist.[2]
FormattableString
When a $"" literal is passed to a parameter of type FormattableString, the compiler keeps the format and the arguments separate instead of producing a string. That is what lets EF Core parameterize it — and why assigning it to a string variable first silently loses the protection.[8], [2]
Dynamic ORDER BY in LINQ
Column names cannot be parameters. In EF Core, map the requested key to a strongly typed OrderBy(u => u.Email) with a switch expression, so only compiled expressions ever reach the SQL generator.[2]

Framework mappings

Step-by-Step Attack Flow

Step 1

Request value reaches the repository

An ASP.NET Core action takes [FromQuery] string id and passes it to GetEmailAsync(). String binding does not parse the ID as a number; without explicit validation, a non-numeric string can reach the repository.[7]

Step 2

Interpolation builds the command text

$"... WHERE Id = {userId}" is a plain string here, because SqlCommand takes a string. With the benign test value 0 OR 1=1, the command reads WHERE Id = 0 OR 1=1.[1], [3], [6]

Step 3

SQL Server compiles the attacker's text

The command has no parameters, so SQL Server parses OR 1=1 as part of the WHERE clause.[1], [2]

Step 4

Every row comes back

The condition matches every row. Through FromSqlRaw the result is the same: EF Core composes further LINQ on top of the injected SQL.[2]

Source Code: Flaw vs. Secure Implementation

UNPATCHED FLAW
// UserRepository.cs: each query below builds SQL text from request data
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

public class UserRepository(SqlConnection connection, AppDbContext db)
{
    // ADO.NET: SqlCommand takes a string, so $"" is plain concatenation here
    public async Task<string?> GetEmailAsync(string userId)
    {
        using var cmd = new SqlCommand($"SELECT Email FROM Users WHERE Id = {userId}", connection);
        return (string?)await cmd.ExecuteScalarAsync();
    }

    // Dapper: concatenation, and no parameter object
    public Task<IEnumerable<int>> FindOrderIdsAsync(string status) =>
        connection.QueryAsync<int>("SELECT Id FROM Orders WHERE Status = '" + status + "'");

    // EF Core: FromSqlRaw receives a finished string
    public Task<List<User>> FindByEmailAsync(string email) =>
        db.Users.FromSqlRaw($"SELECT * FROM Users WHERE Email = '{email}'").ToListAsync();

    // ORDER BY column taken straight from the request
    public Task<List<User>> ListAsync(string sortColumn) =>
        db.Users.FromSqlRaw($"SELECT * FROM Users ORDER BY {sortColumn}").ToListAsync();
}
HARDENED SECURE PATCH
// UserRepository.cs: values are parameters, sorting uses typed expressions
using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

public class UserRepository(SqlConnection connection, AppDbContext db)
{
    // ADO.NET: typed SqlParameter sent separately from the command text
    public async Task<string?> GetEmailAsync(int userId)
    {
        using var cmd = new SqlCommand("SELECT Email FROM Users WHERE Id = @id", connection);
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = userId;
        return (string?)await cmd.ExecuteScalarAsync();
    }

    // Dapper: anonymous object properties become @parameters
    public Task<IEnumerable<int>> FindOrderIdsAsync(string status) =>
        connection.QueryAsync<int>("SELECT Id FROM Orders WHERE Status = @status", new { status });

    // EF Core 7+: FromSql takes a FormattableString and parameterizes {email}
    public Task<List<User>> FindByEmailAsync(string email) =>
        db.Users.FromSql($"SELECT * FROM Users WHERE Email = {email}").ToListAsync();

    // allowlisted keys map to compiled OrderBy expressions
    public Task<List<User>> ListAsync(string sortKey) => sortKey switch
    {
        "email" => db.Users.OrderBy(u => u.Email).ToListAsync(),
        "created" => db.Users.OrderBy(u => u.CreatedAt).ToListAsync(),
        _ => db.Users.OrderBy(u => u.Id).ToListAsync(),
    };
}

Engineering & System Hardening Checklist

Sources