SQL Injection Prevention

In simple words#

Learning objectives#

• See how injection happens
• Fix it with parameters
• Handle dynamic sort columns safely
• Add defence in depth

Explanation#

Injection happens when user input is concatenated into SQL text. Input such as ' OR '1'='1 changes the meaning of the query.

Parameters fix it completely for values, because values are never parsed as SQL.

Table names, column names and sort directions cannot be parameters. Map user choices to a fixed whitelist instead.

Stored procedures are safe only if they do not build dynamic SQL from their inputs. Use sp_executesql with parameters if they must.

Defence in depth: the app’s login should have only the permissions it needs — never sysadmin or db_owner.

Code example: Vulnerable vs safe#

1// ❌ VULNERABLE: input "x' OR '1'='1" logs in as the first user
2var bad = 
quot;SELECT Id FROM Users WHERE Email = '{email}' AND PasswordHash = '{hash}'";
3

4// ✅ SAFE: values are parameters
5await using var cmd = new SqlCommand("SELECT Id FROM Users WHERE Email = @email", conn);
6cmd.Parameters.Add("@email", SqlDbType.NVarChar, 256).Value = email;
7

8// ✅ SAFE dynamic ORDER BY: whitelist, never raw input
9string orderBy = sort switch
10{
11    "price" => "Price",
12    "name"  => "Name",
13    _       => "Id",
14};
15string dir = descending ? "DESC" : "ASC";
16var listSql = 
quot;SELECT Id, Name, Price FROM Products ORDER BY {orderBy} {dir}";

The vulnerable query returns a user for any password. The safe versions treat input only as data, and ORDER BY can only ever be one of three known columns.

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

1. String interpolation puts attacker text directly into the SQL.
2. The parameterised query compares Email with the input as a value.
3. The switch maps user input to known column names; anything else falls back to Id.
4. Verify the password hash in C# with a proper hashing library, not in SQL text.

Real-world use case#

Common mistakes#

Best practices#

• Parameters for every value
• Whitelist identifiers
• Least-privilege database logins
• Code review and static analysis for concatenated SQL

Remember this#

Practice exercise#

1await using var cmd = new SqlCommand("DELETE FROM Products WHERE Id = @id", conn);
2cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

Mini quiz#

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

Related topics#

My notes#

Saved in this browser.