Preventing SQL Injection

Every database call sends text to the database engine, and the engine reads that text as a statement: keywords, identifiers, operators and literal values, all in one string.

SQL injection is what happens when a value supplied by a user ends up inside that string before the engine parses it. The engine has no way to tell which characters came from your source code and which arrived in a form field — it receives one string and parses all of it. If the value alters the structure of the statement, the engine runs the altered structure, because as far as it is concerned, that is the statement.

So the cause is not a lack of filtering. The cause is that data was placed where code goes. Fix that and the problem stops existing, rather than being repeatedly caught.

// FLAWED SHAPE. The value becomes part of the statement text before the
// database ever sees it. Never write a query this way.
var flawed = "SELECT Id, Reference, Total FROM Orders WHERE CustomerReference = '"
             + customerReference + "'";

// The same flaw with nicer syntax. Interpolation is concatenation.
var alsoFlawed = $"SELECT Id, Reference, Total FROM Orders WHERE CustomerReference = '{customerReference}'";

// CORRECT. The statement text is fixed, and the value travels separately.
const string query = """
    SELECT Id, Reference, Total
    FROM   Orders
    WHERE  CustomerReference = @CustomerReference
    """;

using var command = new SqlCommand(query, connection);
command.Parameters.Add("@CustomerReference", SqlDbType.NVarChar, 40).Value = customerReference;

using var reader = await command.ExecuteReaderAsync(ct);

• In the first two versions the value is glued into the statement before it leaves your process. The database receives one string and parses the whole of it, so anything in the value that reads as SQL syntax becomes SQL syntax.
• Those two lines are here so you can recognise the shape in existing code: a quote character, then a concatenation or an interpolation hole, then another quote. That pattern anywhere near a database call is the thing to look for in review.
• In the corrected version the statement text is a constant. @CustomerReference is a placeholder, not a substitution — nothing is pasted into that string at any point.
• The value is sent to the database as a separate, typed argument. The engine parses the statement first and binds the value afterwards, so the value fills a slot in a finished plan. It is a value, always, whatever it contains.
• Declaring the type and length is worth the extra characters. Letting the driver infer them from each value produces a different inferred type per call, which can stop the engine reusing a cached plan and, when the inferred type does not match the column, can prevent an index being used at all.
• The triple-quoted string is a C# raw string literal. It lets the SQL keep its line breaks and indentation without escaping, which makes a long statement readable in code review.

-- Roughly what a parameterised call looks like on arrival:
-- statement text, parameter declarations and values kept apart.
EXEC sp_executesql
     N'SELECT Id, Reference, Total FROM Orders WHERE CustomerReference = @CustomerReference',
     N'@CustomerReference nvarchar(40)',
     @CustomerReference = N'CR-00184';

-- FLAWED SHAPE. A stored procedure is not automatically safe. This one
-- builds a statement out of a value, which is the same flaw moved inside
-- the database.
CREATE PROCEDURE dbo.SearchOrdersFlawed @Reference nvarchar(40)
AS
BEGIN
    DECLARE @sql nvarchar(max) =
        N'SELECT Id, Reference FROM Orders WHERE Reference = ''' + @Reference + N'''';
    EXEC (@sql);
END;

-- CORRECT. The text is still built dynamically, but the value stays a parameter.
CREATE PROCEDURE dbo.SearchOrders @Reference nvarchar(40)
AS
BEGIN
    DECLARE @sql nvarchar(max) =
        N'SELECT Id, Reference FROM Orders WHERE Reference = @Ref';
    EXEC sp_executesql @sql, N'@Ref nvarchar(40)', @Ref = @Reference;
END;

• The first statement shows the separation on arrival: the statement text, the parameter declarations, and the values, as three distinct things.
• Moving a query into a stored procedure is often described as a fix for this. It is not one by itself. A procedure that concatenates its parameter into a string and executes the result has exactly the problem that application code doing the same thing has.
• The corrected procedure still assembles its text at runtime — sometimes you need to, for a genuinely variable set of filters — but it passes the value to sp_executesql as a parameter instead of pasting it into the text.
• One limit to know: an identifier such as a table name, a column name or a sort direction cannot be a parameter. Parameters are values only. When the caller influences an identifier, map their input to a fixed set of known-good names in your own code and never pass the input through. Sorting is the usual case — accept 'oldest' or 'highest-value' and translate, rather than accepting a column name and a direction.