ADO - Parameterization & SQL Injection Prevention

Parameterization means sending SQL code and data separately to the database.
This is the only correct and reliable way to prevent SQL Injection in ADO.NET.

1. What is SQL Injection (fact)

SQL Injection happens when user input is treated as SQL code.

Vulnerable example ❌

string sql = "SELECT * FROM Users WHERE Username = '" + user + "'";
SqlCommand cmd = new SqlCommand(sql, conn);

Input:

' OR 1=1 --

Result:

SELECT * FROM Users WHERE Username = '' OR 1=1 --'

✔ Attacker bypasses authentication

2. How parameterization stops injection

With parameters:

• SQL structure is fixed

• User input is data only

• Database never executes input as code

3. Correct parameterized query (ADO.NET)

SqlCommand cmd = new SqlCommand(
    "SELECT * FROM Users WHERE Username = @username",
    conn);

cmd.Parameters.AddWithValue("@username", userInput);

✔ Injection impossible
✔ Query plan reuse
✔ Cleaner code

4. Best practice: define parameter types (important)

Avoid AddWithValue for non-trivial queries.

Correct way

cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50)
              .Value = userInput;

Why

• Prevents implicit type conversion

• Avoids index scans

• Improves performance

5. Parameterization for INSERT / UPDATE

SqlCommand cmd = new SqlCommand(
    "INSERT INTO Users (Name, Age) VALUES (@name, @age)", conn);

cmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = name;
cmd.Parameters.Add("@age", SqlDbType.Int).Value = age;

6. Stored procedures + parameters (safe)

SqlCommand cmd = new SqlCommand("CreateUser", conn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = name;

✔ Safe
✔ Reusable
✔ Centralized validation

7. What does NOT prevent SQL Injection ❌

Only parameters truly prevent injection.

8. Dynamic SQL (safe pattern)

If SQL must be dynamic:

string sql = "SELECT * FROM Orders WHERE Status = @status";

if (isPaid)
    sql += " AND PaidDate IS NOT NULL";

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@status", SqlDbType.VarChar, 20).Value = status;

✔ SQL structure dynamic
✔ Data still parameterized

9. Edge cases to watch

• Table/column names cannot be parameterized
  → Must be whitelisted

• IN clauses need multiple parameters

• LIKE queries still safe with parameters

cmd.Parameters.Add("@name", SqlDbType.NVarChar)
              .Value = "%" + search + "%";

10. Security checklist (real-world)

✔ Always use parameters
✔ Use least-privilege DB accounts
✔ Validate input (for logic, not security)
✔ Avoid dynamic SQL when possible
✔ Log and monitor failed attempts