Published / updated29 Aug 2026

Parameters and SQL Injection

Before you start

You need: commands (Article 02).

Time: about 45 minutes, plus the practice. This is the security article of the track.

Learning objective

Write data access that cannot be injected, and explain why AddWithValue can make a fast query slow.

Topics

What SQL injection is, and how concatenation enables it
Parameters.Add versus AddWithValue
Type and size, and why they matter to the query plan
DBNull.Value versus null
Output, input-output and return-value parameters
LIKE and IN with parameters
Identifiers cannot be parameters

Concatenation is the vulnerability

// NEVER
string sql = "SELECT * FROM dbo.Student WHERE Name = '" + searchName + "'";

With searchName = "O'Brien", the apostrophe closes the literal early and SQL Server reports a syntax error. That is the harmless case.

With searchName = "'; DROP TABLE dbo.Student; --", the statement becomes:

SELECT * FROM dbo.Student WHERE Name = ''; DROP TABLE dbo.Student; --'

Two statements, both executed. The -- comments out the trailing quote so it parses cleanly.

Real attacks are quieter than dropping a table. ' OR 1=1 -- turns a login check into "return the first user". ' UNION SELECT Email, PasswordHash, 1, 1 FROM dbo.[User] -- appends stolen data to a legitimate result set, and the page renders it without noticing.

Escaping quotes is not a fix

// Still wrong
string safe = searchName.Replace("'", "''");

It fails on numeric and date contexts, where no quotes are involved at all:

string sql = "SELECT * FROM dbo.Student WHERE Id = " + userInput;
// userInput = "1 OR 1=1"

It also fails against Unicode homoglyph tricks and second-order injection, where the value is stored safely and then concatenated later by different code.

Parameters are the fix, and the only one. The value never becomes part of the SQL text — it travels separately, and SQL Server treats it strictly as data.

Parameters.Add

command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;
command.Parameters.Add("@RollNumber", SqlDbType.NVarChar, 20).Value = rollNumber;
command.Parameters.Add("@TotalFees", SqlDbType.Decimal).Value = totalFees;
command.Parameters.Add("@PublicId", SqlDbType.UniqueIdentifier).Value = publicId;
command.Parameters.Add("@DateOfBirth", SqlDbType.Date).Value = dateOfBirth;
command.Parameters.Add("@IsAbsent", SqlDbType.Bit).Value = isAbsent;

For DECIMAL, set precision and scale when it matters:

SqlParameter amount = command.Parameters.Add("@Amount", SqlDbType.Decimal);
amount.Precision = 18;
amount.Scale = 2;
amount.Value = paymentAmount;

Without them the provider infers a scale, and a value of 8000.50 can arrive as 8000 or 8001. On money, that is a defect.

AddWithValue and why it hurts

command.Parameters.AddWithValue("@RollNumber", rollNumber);

Convenient, and it infers the type from the .NET value. Two consequences.

Plan cache bloat

A string becomes NVARCHAR sized to the value's length. "NCA-2024-0012" is inferred as NVARCHAR(13); a different call with a 9-character value is NVARCHAR(9). SQL Server caches a separate execution plan for each size, so one query can occupy dozens of plan cache entries and none of them is reused.

Index-defeating conversion

The worse problem. When the column is VARCHAR and the parameter arrives as NVARCHAR, SQL Server must make the types match — and its rules convert the column, not the parameter:

WHERE CONVERT(NVARCHAR(20), s.RollNumber) = @RollNumber

The column is now wrapped in a function, so the index on RollNumber cannot be used and the query scans the table. A lookup that was instant on 400 rows becomes seconds on 400,000, with no code change and nothing in the logs.

Add with an explicit type prevents both:

command.Parameters.Add("@RollNumber", SqlDbType.VarChar, 20).Value = rollNumber;

Rule: AddWithValue in a throwaway script is fine. In application code, use Add with the type and size that match the column.

null versus DBNull.Value

// Wrong — a null Value means "parameter not supplied"
command.Parameters.AddWithValue("@Address", student.Address);

When student.Address is null, the provider treats the parameter as absent and SQL Server reports:

Must declare the scalar variable "@Address".

A confusing message for what is really a null-handling bug.

// Correct
SqlParameter address = command.Parameters.Add("@Address", SqlDbType.NVarChar, 250);
address.Value = (object?)student.Address ?? DBNull.Value;

The cast to object? is needed so the null-coalescing operator can produce either a string or DBNull.

A small helper removes the repetition:

private static SqlParameter AddNullable(
    SqlCommand command, string name, SqlDbType type, int size, object? value)
{
    SqlParameter parameter = command.Parameters.Add(name, type, size);
    parameter.Value = value ?? DBNull.Value;

    return parameter;
}

AddNullable(command, "@Address", SqlDbType.NVarChar, 250, student.Address);
AddNullable(command, "@Remarks", SqlDbType.NVarChar, 200, result.Remarks);

For a nullable value type, the same rule applies:

command.Parameters.Add("@MarksObtained", SqlDbType.Decimal).Value =
    result.MarksObtained.HasValue ? result.MarksObtained.Value : (object)DBNull.Value;

Passing 0 instead of DBNull.Value for an absent student's marks is the version of this bug that reaches a report card.

Parameter directions

using (SqlCommand command = new SqlCommand("dbo.usp_CreateStudent", connection))
{
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;
    command.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = student.Name;
    command.Parameters.Add("@RollNumber", SqlDbType.NVarChar, 20).Value = student.RollNumber;

    SqlParameter newId = command.Parameters.Add("@NewId", SqlDbType.Int);
    newId.Direction = ParameterDirection.Output;

    SqlParameter newPublicId = command.Parameters.Add("@NewPublicId", SqlDbType.UniqueIdentifier);
    newPublicId.Direction = ParameterDirection.Output;

    SqlParameter returnValue = command.Parameters.Add("@Return", SqlDbType.Int);
    returnValue.Direction = ParameterDirection.ReturnValue;

    connection.Open();
    command.ExecuteNonQuery();

    student.Id = (int)newId.Value;
    student.PublicId = (Guid)newPublicId.Value;
}

Direction	Meaning
`Input`	Default
`Output`	Procedure sets it
`InputOutput`	Passed in and possibly changed
`ReturnValue`	The procedure's `RETURN` value

Output values are populated only after the command completes — and if a reader was opened, only after that reader is closed:

using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        students.Add(MapStudent(reader));
    }
}   // reader closed here

int total = (int)totalCount.Value;   // only valid now

Reading totalCount.Value inside the loop returns null. This is a genuinely puzzling bug the first time, because the procedure clearly sets it.

An output parameter that the procedure never assigns comes back as DBNull.Value, not null — check before casting.

LIKE with parameters

// The wildcards go in the VALUE, not the SQL
const string sql = @"
    SELECT s.Id, s.Name, s.RollNumber
    FROM   dbo.Student AS s
    WHERE  s.SchoolId = @SchoolId
      AND  (s.Name LIKE @Search OR s.RollNumber LIKE @Search);";

command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;
command.Parameters.Add("@Search", SqlDbType.NVarChar, 102).Value = "%" + searchTerm + "%";

Size 102 allows a 100-character term plus two wildcards. Sizing it at 100 silently truncates the last characters of a long search.

Parameters stop injection but do not change LIKE semantics: a user searching for 50% still gets a wildcard. Escape when the term should be literal:

string escaped = searchTerm
    .Replace("[", "[[]")
    .Replace("%", "[%]")
    .Replace("_", "[_]");

command.Parameters.Add("@Search", SqlDbType.NVarChar, 302).Value = "%" + escaped + "%";

Escape [ first, or the replacements corrupt each other.

IN lists

A parameter is one value, so this does not work:

// Wrong — the whole string is one value, matching nothing
command.Parameters.AddWithValue("@Ids", "1,2,3");
// WHERE s.Id IN (@Ids)

Three correct approaches.

Generated parameters — fine for a small, bounded list:

int[] ids = { 12, 18, 31 };
string[] names = new string[ids.Length];

for (int index = 0; index < ids.Length; index++)
{
    names[index] = "@Id" + index.ToString();
    command.Parameters.Add(names[index], SqlDbType.Int).Value = ids[index];
}

command.CommandText = "SELECT Id, Name FROM dbo.Student WHERE Id IN (" +
                      string.Join(", ", names) + ")";

The SQL text is built from generated names, never from user input, so nothing is injectable. Each distinct list length produces a different plan, so keep lists small.

STRING_SPLIT — for a longer list:

const string sql = @"
    SELECT s.Id, s.Name
    FROM   dbo.Student AS s
    JOIN   STRING_SPLIT(@Ids, ',') AS parts ON parts.value = CAST(s.Id AS NVARCHAR(20))
    WHERE  s.SchoolId = @SchoolId;";

command.Parameters.Add("@Ids", SqlDbType.NVarChar, -1).Value = string.Join(",", ids);

Table-valued parameter — the right answer for large sets, covered in the CRUD article.

Identifiers cannot be parameters

// Does not work — this becomes ORDER BY 'Name', a constant
command.CommandText = "SELECT * FROM dbo.Student ORDER BY @SortColumn";

Parameters carry values. Table names, column names, and sort directions are part of the statement's structure and are fixed when the plan is built.

A run-time sort column must come from a whitelist:

private static string ResolveSortColumn(string? requested)
{
    switch (requested)
    {
        case "Name":       return "s.Name";
        case "RollNumber": return "s.RollNumber";
        case "ClassName":  return "s.ClassName, s.Section";
        default:           return "s.Name";
    }
}

string orderBy = ResolveSortColumn(sortColumn);

command.CommandText = @"
    SELECT   s.Id, s.Name, s.RollNumber, s.ClassName, s.Section
    FROM     dbo.Student AS s
    WHERE    s.SchoolId = @SchoolId AND s.Status <> 1
    ORDER BY " + orderBy + ", s.Id;";

The concatenated text can only be one of four fixed strings the code controls. Never pass the caller's string through, even after escaping it.

Errors you will hit

Message	Cause	Fix
`Must declare the scalar variable '@SchoolId'`	Parameter named in SQL but never added	Add it to `Parameters`
`Incorrect syntax near 'Brien'`	A name with an apostrophe in concatenated SQL	Parameterise — this is also the injection hole
`Operand type clash`	Parameter type does not match the column	Set `SqlDbType` explicitly
`String or binary data would be truncated`	Parameter longer than the column	Set `Size`, and validate input
A `LIKE` search matches everything	User input contains `%`	Escape wildcards
Query is slow only from the application	Implicit conversion from `nvarchar` to `varchar`	Set the parameter type to match the column

AddWithValue guesses the type, and its guess is what causes the last row — a parameter typed nvarchar against a varchar column stops the index being used. Set the type explicitly.

Common mistakes

Concatenating any value into SQL
Escaping quotes and believing the problem is solved
AddWithValue in application code, causing plan bloat or an index-defeating conversion
No size on a string parameter, so values are truncated or plans multiply
No precision and scale on a DECIMAL money parameter
Passing null instead of DBNull.Value
Passing 0 instead of DBNull.Value for an absent student's marks
Reading an output parameter before the reader is closed
Casting an unassigned output parameter without checking for DBNull.Value
Undersizing a LIKE parameter and truncating the wildcards
Not escaping % and _ when the search term should be literal
Passing a comma-separated string to an IN clause
Trying to parameterise a column or table name

Practice

The course exercise is execute parameterized CRUD.

Write a student search using string concatenation. Search for O'Brien and record the error. Then search for ' OR 1=1 -- and record how many rows come back.
Convert it to parameters. Repeat both searches and confirm one returns nothing and the other returns nothing dangerous.
Write an insert using Add with explicit types and sizes for every parameter, including DECIMAL precision and scale.
Insert a student with a null address using AddWithValue. Record the exact error. Fix it with DBNull.Value.
Insert an absent exam result. Confirm MarksObtained is NULL in the database, not 0.
Call usp_CreateStudent with two output parameters. Then read one before closing a reader on the same command and record what you get.
Write a LIKE search with a parameter sized at 100 and search for a 100-character term. Confirm the truncation, then size it at 102.
Search for 50% and confirm the wildcard behaviour. Add escaping and confirm the literal match.
Build an IN clause with generated parameter names for three ids. Confirm the generated SQL contains no user input.
Add a sortable column with a whitelist. Pass Name; DROP TABLE dbo.Student-- as the sort column and confirm it falls through to the default.

Then run the course debugging exercise — fix connection and parameter errors. Trigger each and record the exact message: a missing parameter, a parameter with a mismatched name, a null where DBNull.Value was needed, a value longer than the declared size, and a DECIMAL with no scale.

Finally, the performance check: create a VARCHAR(20) indexed column, query it with AddWithValue on a string, and capture the execution plan. Then query with Add(..., SqlDbType.VarChar, 20) and compare. The first should show a scan with a CONVERT_IMPLICIT on the column.

You can now

Write data access that cannot be injected
Say why concatenation breaks on O'Brien and why that matters
Set parameter types and sizes explicitly rather than using AddWithValue
Escape wildcards in a LIKE search
Explain how a parameter type mismatch causes a slow query

Review questions

Why is escaping apostrophes not a defence against SQL injection?
How can AddWithValue cause a query to stop using an index?
What error does passing null instead of DBNull.Value produce, and why is the message misleading?
Why can a column name not be passed as a parameter, and what is the safe alternative?

Next: Readers and object mapping

Before you start​

Learning objective​

Topics​

Concatenation is the vulnerability​

Escaping quotes is not a fix​

Parameters.Add​

AddWithValue and why it hurts​

Plan cache bloat​

Index-defeating conversion​

null versus DBNull.Value​

Parameter directions​

LIKE with parameters​

IN lists​

Identifiers cannot be parameters​

Errors you will hit​

Common mistakes​

Practice​

You can now​

Review questions​