Parameters, SQL injection and IN lists — Dapper — The Micro-ORM from Basics to Advanced

2. Parameters, SQL injection and IN lists

Basic★ Must knowLesson 2 of 8

Dapper always sends values as real parameters — never concatenate user input into SQL.

csharp

// ❌ SQL injection: name = "x'; DROP TABLE Products;--"
var bad = conn.Query<Product>($"SELECT * FROM Products WHERE Name = '{name}'");

// ✅ parameterised
var good = conn.Query<Product>("SELECT * FROM Products WHERE Name = @name", new { name });

// IN list: Dapper expands a collection into (@ids1, @ids2, ...)
var some = conn.Query<Product>("SELECT * FROM Products WHERE Id IN @ids", new { ids = new[] { 1, 2, 3 } });

// Explicit types/sizes with DynamicParameters (helps plan caching for strings)
var p = new DynamicParameters();
p.Add("@Name", name, DbType.String, size: 200);
p.Add("@Category", new DbString { Value = cat, IsAnsi = true, Length = 100 });  // VARCHAR, avoids implicit conversion
var result = conn.Query<Product>("SELECT * FROM Products WHERE Name = @Name AND Category = @Category", p);

Dynamic sort columns or table names can't be parameters — whitelist them:

csharp

string orderBy = sort switch { "price" => "Price", "name" => "Name", _ => "Id" };   // never use raw input
var sql = $"SELECT * FROM Products ORDER BY {orderBy} OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY";