Parameters and SQL Injection with Dapper: The Rules of a Safe Query

Version note: The code in this post targets Dapper 2.1 and .NET 10.

Guess First

Picture a search box: the user types a book title and we look it up in the database. The most "practical" way seems to be putting the incoming value into the SQL with string interpolation:

string title = "The Hitchhiker's Guide to the Galaxy";

var found = connection.Query<Book>(
    $"SELECT id, title, price FROM books WHERE title = '{title}'");

Console.WriteLine(found.Count());

The book is in the database and the title is exactly the same. Do you see 1, 0, or an exception? Hint: the title contains an innocent-looking character.

The Bookstore Database

The setup is the same as in the first post: a .NET 10 console project, Dapper 2.1.89, Microsoft.Data.Sqlite 10.0.14, a Book class with Id/Title/Price properties and BookstoreDb.cs, which rebuilds the database on every run:

using Dapper;
using Microsoft.Data.Sqlite;

public static class BookstoreDb
{
    public const string ConnectionString = "Data Source=bookstore.db";

    public static void Reset()
    {
        using var connection = new SqliteConnection(ConnectionString);
        connection.Execute("""
            DROP TABLE IF EXISTS order_items;
            DROP TABLE IF EXISTS orders;
            DROP TABLE IF EXISTS books;
            DROP TABLE IF EXISTS authors;

            CREATE TABLE authors (
                id      INTEGER PRIMARY KEY,
                name    TEXT NOT NULL,
                country TEXT
            );
            CREATE TABLE books (
                id             INTEGER PRIMARY KEY,
                author_id      INTEGER NOT NULL REFERENCES authors(id),
                title          TEXT NOT NULL,
                price          REAL NOT NULL,
                published_year INTEGER,
                stock          INTEGER NOT NULL DEFAULT 0
            );
            CREATE TABLE orders (
                id             INTEGER PRIMARY KEY,
                customer_email TEXT NOT NULL,
                created_at     TEXT NOT NULL
            );
            CREATE TABLE order_items (
                id         INTEGER PRIMARY KEY,
                order_id   INTEGER NOT NULL REFERENCES orders(id),
                book_id    INTEGER NOT NULL REFERENCES books(id),
                quantity   INTEGER NOT NULL,
                unit_price REAL NOT NULL
            );

            INSERT INTO authors (id, name, country) VALUES
                (1, 'Ursula K. Le Guin', 'USA'),
                (2, 'Frank Herbert', 'USA'),
                (3, 'Stanislaw Lem', 'Poland'),
                (4, 'Douglas Adams', 'UK'),
                (5, 'Ted Chiang', 'USA');

            INSERT INTO books (id, author_id, title, price, published_year, stock) VALUES
                (1, 1, 'A Wizard of Earthsea', 12.50, 1968, 8),
                (2, 1, 'The Left Hand of Darkness', 14.00, 1969, 3),
                (3, 1, 'The Dispossessed', 15.25, 1974, 0),
                (4, 2, 'Dune', 18.90, 1965, 12),
                (5, 3, 'Solaris', 11.40, 1961, 5),
                (6, 4, 'The Hitchhiker''s Guide to the Galaxy', 9.99, 1979, 20),
                (7, 5, 'Exhalation', 13.20, NULL, 4);
            """);
    }
}

The snippets below run on a single connection opened after BookstoreDb.Reset();. When running the examples that throw, I wrapped each one in a try/catch; in the outputs you see the type and message of the exception.

Why Is String Concatenation Off-Limits?

The answer to the guess:

Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'near "s": syntax error'.

The apostrophe in the title closed the SQL string early. The text sent to the database became broken SQL that ends with WHERE title = 'The Hitchhiker' followed by a meaningless s Guide to the Galaxy' fragment. This looks like an innocent bug, but the same door is open to someone with bad intentions. What happens if the user types this into the search box instead of a book title?

string input = "x' OR 1=1 --";

var leaked = connection.Query<Book>(
    $"SELECT id, title, price FROM books WHERE title = '{input}'");

Console.WriteLine(leaked.Count());

7

The whole table came back. The resulting SQL is WHERE title = 'x' OR 1=1 --'; OR 1=1 is true for every row, and -- turns the rest of the line into a comment, neutralizing the trailing apostrophe. This is SQL injection. Only the book list leaked here, but with the same technique data can be read from other tables, records can be deleted and a login check can be bypassed.

The root of the problem is this: when you put the value inside the SQL text, the database cannot tell what is a command and what is data. As an analogy, it is the difference between telling a clerk "look up the name on this form in the list" and asking them to read the text on the form aloud as instructions. In the second case, whoever fills in the form chooses what you do.

Parameters with an Anonymous Object

The fix is to send the value separately from the SQL. In the SQL you put a name (@Title) where the value goes, and you give the value to Dapper as an object:

var safe = connection.Query<Book>(
    "SELECT id, title, price FROM books WHERE title = @Title",
    new { Title = title });

var stillSafe = connection.Query<Book>(
    "SELECT id, title, price FROM books WHERE title = @Title",
    new { Title = input });

Console.WriteLine($"{safe.Count()} | {stillSafe.Count()}");

1 | 0

The title with the apostrophe was found without trouble, and the attack text returned nothing, because the database simply looked for a book named x' OR 1=1 --. Dapper finds the properties of the anonymous object that appear in the SQL text and adds a DbParameter to the command for each of them; properties you do not use in the SQL never make it into the command. The SQL text and the values travel to the database separately; whatever the value contains, it is never interpreted as SQL. Manual "cleaning" such as adding escape characters or doubling quotes becomes unnecessary.

There is a side benefit too: since the SQL text stays the same on every call, both Dapper's own cache and the database's query plan cache can reuse it. With string concatenation, every different value means a new SQL text. Besides an anonymous object you can also pass a Dictionary<string, object>, but in most cases the anonymous object is the most readable.

Parameter Names: SQLite Is Strict

What happens if you write the name in the SQL differently from the property? Let only the letter case differ:

var wrongCase = connection.Query<Book>(
    "SELECT id, title, price FROM books WHERE title = @title",
    new { Title = title });

System.InvalidOperationException: Must add values for the following parameters: @title

It is not Dapper that gives this message but Microsoft.Data.Sqlite. Dapper saw @title in the SQL, matched it to the Title property regardless of case and added the parameter under the name Title; in SQLite, however, parameter names are case-sensitive, and @title and Title could not meet. Set yourself a single rule: write the name in the SQL exactly like the property name.

As for prefixes, since 2.1 Dapper recognizes the $ prefix in addition to @. In this setup WHERE title = $Title works too, and the SQLite provider also accepts :Title. I will use @ throughout the series, because it is the common denominator across SQL Server, PostgreSQL (Npgsql) and SQLite.

DynamicParameters: Building Parameters Step by Step

An anonymous object is ideal when the shape is known at compile time. On a search screen that adds filters based on what the user picks, though, you need to add parameters conditionally. That is what DynamicParameters is for:

string? country = "USA";
decimal? maxPrice = 14m;

var sql = "SELECT b.title FROM books b JOIN authors a ON a.id = b.author_id WHERE 1 = 1";
var parameters = new DynamicParameters();

// Only fixed fragments go into the SQL; the values travel as parameters
if (country is not null)
{
    sql += " AND a.country = @Country";
    parameters.Add("Country", country);
}
if (maxPrice is not null)
{
    sql += " AND b.price < @MaxPrice";
    parameters.Add("MaxPrice", maxPrice, DbType.Decimal);
}

var titles = connection.Query<string>(sql + " ORDER BY b.title", parameters);
Console.WriteLine(string.Join(", ", titles));

A Wizard of Earthsea, Exhalation

There is string concatenation here too, but with a big difference: everything added to the SQL is a fixed fragment written in the code. Values coming from the user never enter the SQL text; they always go through parameters.Add. With the optional parameters of Add you can also specify the DbType, the direction (ParameterDirection), the size and the precision; we will use output parameters in the stored procedure post. Do not forget to add the System.Data namespace.

DynamicParameters also lets you start from an anonymous object and add to it:

var merged = new DynamicParameters(new { AuthorId = 1 });
merged.AddDynamicParams(new { MinStock = 1 });

var inStock = connection.Query<string>(
    "SELECT title FROM books WHERE author_id = @AuthorId AND stock >= @MinStock",
    merged);

Console.WriteLine($"{string.Join(", ", merged.ParameterNames)} -> {string.Join(", ", inStock)}");

AuthorId, MinStock -> A Wizard of Earthsea, The Left Hand of Darkness

As dynamic filters grow, keeping this code tidy gets harder; in post 14 we will do the same job more cleanly with Dapper.SqlBuilder.

List Expansion with IN @Ids

Searching by several ids is a classic need. In Dapper you pass a collection as the parameter and you do not write parentheses after IN:

int[] ids = [1, 4, 6];

var picked = connection.Query<string>(
    "SELECT title FROM books WHERE id IN @Ids ORDER BY id",
    new { Ids = ids });

Console.WriteLine(string.Join(", ", picked));

A Wizard of Earthsea, Dune, The Hitchhiker's Guide to the Galaxy

When Dapper sees @Ids, it expands it into as many parameters as there are elements in the collection; according to the README example the SQL becomes in (@Ids1, @Ids2, @Ids3) and each value goes as a separate parameter. The mistake is not forgetting the parentheses but writing them. If you write IN (@Ids) out of SQL habit, you get double parentheses and SQLite takes it for a row value:

var withParens = connection.Query<string>(
    "SELECT title FROM books WHERE id IN (@Ids)",
    new { Ids = ids });

Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'row value misused'.

An empty list is safe as well. For an empty collection Dapper replaces IN @Ids with (SELECT @Ids WHERE 1 = 0), which matches no rows, and the query returns zero rows without throwing:

var nothing = connection.Query<string>(
    "SELECT title FROM books WHERE id IN @Ids",
    new { Ids = Array.Empty<int>() });

Console.WriteLine(nothing.Count());

0

This expansion has a side effect: the SQL text changes as the number of elements in the list changes. On SQL Server that means a separate query plan per list length; we will come back to this in post 13, among the SQL Server specifics.

DbString: ANSI or Unicode?

In .NET a string parameter is sent as Unicode by default. On some databases that is an important distinction. Dapper offers the DbString type so you can control it:

var duneId = connection.QuerySingle<int>(
    "SELECT id FROM books WHERE title = @Title",
    new { Title = new DbString { Value = "Dune", IsAnsi = true, Length = 200 } });

Console.WriteLine(duneId);

4

IsAnsi = true sends the parameter as a non-Unicode string (DbType.AnsiString), and Length gives it a fixed size; with IsFixedLength you can also match fixed-length columns such as char. SQLite has a single text type, so the result does not change here. On SQL Server, however, comparing a varchar column with an nvarchar parameter can cause an implicit type conversion, which in turn can prevent index use. The README is clear about SQL Server: query Unicode columns with Unicode parameters and ANSI columns with ANSI parameters. We will see this on a real query plan in the SQL Server post.

Common Pitfalls

The most common pitfall is trying to make a column name a parameter. The user picks the sort column and you try to pass it with ORDER BY @Column:

var notSorted = connection.Query<string>(
    "SELECT title FROM books ORDER BY @Column LIMIT 3",
    new { Column = "price" });

Console.WriteLine(string.Join(", ", notSorted));

A Wizard of Earthsea, The Left Hand of Darkness, The Dispossessed

No exception, but the result is not sorted by price either. Parameters carry values, not column or table names; the database sorted every row by the constant string 'price', which means it did not sort at all. If SQL fragments such as column names or sort direction come from outside, run them through a whitelist:

static string ToOrderColumn(string sortBy) => sortBy switch
{
    "price" => "price",
    "title" => "title",
    "year" => "published_year",
    _ => "id"
};

var sorted = connection.Query<string>(
    $"SELECT title FROM books ORDER BY {ToOrderColumn("price")} LIMIT 3");

Console.WriteLine(string.Join(", ", sorted));

The Hitchhiker's Guide to the Galaxy, Solaris, A Wizard of Earthsea

Interpolation is safe here, because what enters the SQL is not the text the user typed but one of the constants returned by the switch.

The second is LIKE. Put the % wildcards into the value, not into the SQL: WHERE title LIKE @Pattern with new { Pattern = "%" + term + "%" }. If you write '%@Term%', the parameter stays inside the quotes and is searched for as plain text.

The third is the IN (@Ids) parentheses you saw above, and the fourth is letter case on SQLite. Both are noticed on the first try; the truly dangerous ones are those like ORDER BY @Column that silently give wrong results.

A Small Experiment

★☆☆ Write a search that finds books whose title contains a given word. Add the % characters to the parameter value and try it with "the".

★★☆ Add an int? minYear filter to the DynamicParameters example. What happens to Exhalation, whose published_year is NULL, with this filter? Guess, then run it.

★★★ Write a method that takes both a column and a direction ("asc"/"desc") for sorting. Run both through a whitelist; if a value is not on the list, throw an ArgumentException instead of falling back to a default. Which is the better behavior, and why?

Now we can read safely. In the next post we move to the write side: INSERT, UPDATE and DELETE with Execute, the number of affected rows, getting the id of a newly inserted row, and why calling Execute with a list is not a bulk operation. And the series does not end there; as new Dapper versions come out, we will keep going with new posts.