SQL for .NET developers Advanced

Dynamic SQL and SQL injection

Dynamic SQL builds a query as a string at run time, which is useful for optional search filters and dynamic sorting but dangerous when user input is joined into the string. This lesson shows what SQL injection is, how sp_executesql with parameters prevents it, and how to handle names safely.

Safe dynamic search
  1. StartRequest arrivesOptional name, price and sort values
  2. Step 2Build the query shapeAdd WHERE parts only for filters that were given
  3. Step 3Allow-list namesSort column picked from a fixed list
  4. Step 4sp_executesqlValues passed as typed parameters
  5. EndResultInjection-proof, with a cached plan per shape

SQL injection in one example

Joining input into SQL lets the input change the query. If a search box receives ' OR 1=1 -- the WHERE clause becomes always true; worse inputs can drop tables or read other users' data.

C#
// NEVER do this
var sql = "SELECT * FROM Users WHERE Email = '" + email + "'";

// Always use parameters
var cmd = new SqlCommand("SELECT * FROM Users WHERE Email = @Email", conn);
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = email;

// EF Core: interpolated strings become parameters automatically
var user = await db.Users
    .FromSql($"SELECT * FROM Users WHERE Email = {email}")
    .SingleOrDefaultAsync();

Safe dynamic SQL with sp_executesql

For an optional-filter search, build only the shape of the query dynamically and pass every value as a parameter. The plan is cached per shape, and values never touch the SQL text.

SQL
DECLARE @sql nvarchar(max) = N'SELECT ProductId, Name, Price FROM dbo.Products WHERE 1 = 1';

IF @Name     IS NOT NULL SET @sql += N' AND Name LIKE @Name + N''%''';
IF @MinPrice IS NOT NULL SET @sql += N' AND Price >= @MinPrice';

-- Column names cannot be parameters: allow only known values
SET @sql += N' ORDER BY ' + CASE @SortBy WHEN 'price' THEN N'Price'
                                        WHEN 'name'  THEN N'Name'
                                        ELSE N'ProductId' END;

EXEC sp_executesql @sql,
     N'@Name nvarchar(200), @MinPrice decimal(18,2)',
     @Name = @Name, @MinPrice = @MinPrice;

Rules that keep you safe

  • Values always go in parameters, never in the string.
  • Table or column names come from an allow-list (CASE or a C# switch), or are wrapped in QUOTENAME.
  • Never use EXEC(@sql) with values joined in; use sp_executesql.
  • Give the app's database login only the permissions it needs, so a mistake cannot drop tables.
  • FromSqlRaw with string concatenation is as unsafe as plain ADO.NET; prefer FromSql with interpolation.

Interview question

How do you sort by a column the user picks without risking SQL injection?

Column names cannot be passed as parameters, so never join the user's text into ORDER BY. Map the input to a fixed list of allowed columns, with a CASE expression in SQL or a switch in C#, and fall back to a default for anything else. In LINQ, choose between OrderBy expressions in code.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.