SQL Injection: How It Works and How to Stop It

SQL injection happens when untrusted input becomes part of a command’s structure. Parameters keep values separate from SQL syntax, which is the central fix rather than a clever blacklist.

The Boundary Is Between Data and Syntax

A search term should be a value the query compares with a column. If code pastes that term into the statement, quote characters can change how the statement is parsed. The application has handed input control over part of its grammar.

The problem can arise in application code or inside a stored procedure. Using a stored procedure does not automatically make concatenated dynamic SQL safe. Review how the final command is constructed.

DECLARE @Search nvarchar(100) = N'O''Brien';
DECLARE @UnsafeSql nvarchar(max) =
    N'SELECT name FROM sys.objects WHERE name = N''' + @Search + N''';';
SELECT @UnsafeSql AS constructed_text;

This example only displays the constructed text. It shows how an ordinary apostrophe already disrupts the intended quoting. You do not need an elaborate attack string to see that the boundary is wrong.

Pass Values as Parameters

With sp_executesql, put placeholders in the command and declare their types separately. Supply the values through the parameter arguments. The command structure stays fixed when the search value changes.

DECLARE @Search nvarchar(128) = N'O''Brien';
EXEC sys.sp_executesql
    N'SELECT name, type_desc
      FROM sys.objects
      WHERE name = @Name;',
    N'@Name nvarchar(128)',
    @Name = @Search;

The same principle applies through application drivers. Bind parameters with appropriate SQL types and lengths instead of formatting values into strings. Dates, numbers, and binary values benefit from that separation too.

Calling sp_executesql with an already concatenated unsafe string does not repair it. Its name is not a protective wrapper. The protection comes from keeping untrusted values out of the command text.

Treat Identifiers as a Separate Problem

Parameters represent values, not table names, column names, or keywords. A dynamic identifier therefore needs a different approach. Prefer a small approved mapping when users choose among known options.

DECLARE @SortChoice nvarchar(20) = N'name';
DECLARE @Column sysname =
    CASE @SortChoice WHEN N'name' THEN N'name'
                     WHEN N'type' THEN N'type_desc' END;
IF @Column IS NULL
    THROW 50001, 'Unsupported sort choice.', 1;
DECLARE @Sql nvarchar(max) =
    N'SELECT name, type_desc FROM sys.objects ORDER BY '
    + QUOTENAME(@Column) + N';';
EXEC sys.sp_executesql @Sql;

The mapping decides what is permitted, and QUOTENAME makes the chosen identifier syntactically safe. These are separate responsibilities. Quoting an arbitrary object name does not establish that the caller should be allowed to access it.

QUOTENAME accepts identifiers up to 128 characters and returns NULL for longer input. Validate input before narrowing it into a smaller buffer. Silent truncation can defeat assumptions made earlier in the code.

Keep Values Parameterized in Dynamic Shapes

A statement can have a validated dynamic sort column and still parameterize its filter. Do not abandon value parameters merely because one structural element varies. Construct only the part that genuinely needs to change.

DECLARE @Column sysname = N'name';
IF @Column NOT IN (N'name', N'type_desc') OR @Column IS NULL
    THROW 50002, 'Unsupported column.', 1;
DECLARE @Sql nvarchar(max) =
    N'SELECT name, type_desc FROM sys.objects
      WHERE type = @ObjectType ORDER BY ' + QUOTENAME(@Column) + N';';
EXEC sys.sp_executesql @Sql,
    N'@ObjectType char(2)', @ObjectType = 'U';

For a sort direction, choose between fixed ASC and DESC tokens through an approved mapping. Do not paste a free-form direction string into the command. Similar rules apply to selectable operators and optional query fragments.

Reduce the Damage Available to a Bug

The application account should have only the permissions its work requires. A narrow permission set does not remove an injection defect, but it limits reachable operations. Separate deployment privileges from ordinary runtime access.

Input validation remains useful for business rules and reasonable lengths. It is not a replacement for parameterization. Removing selected punctuation often rejects legitimate names while missing other unsafe constructions.

Also review values read from stored data before reusing them in dynamic SQL. A value does not become trusted merely because it was saved earlier. Keep the same data-versus-syntax boundary at every construction point.

Test the Contract at Its Edges

Test apostrophes, Unicode text, empty values, and maximum supported lengths through the actual application path. Confirm that invalid structural choices are rejected clearly. Verify both the result and the parameter metadata reaching SQL Server.

Search code for concatenation near EXEC, sp_executesql, and driver command creation. Review each case rather than assuming every occurrence is unsafe. The goal is an explainable boundary, not a keyword count.

Injection prevention is not punctuation cleanup, it is keeping data out of command syntax.

This post was rewritten from scratch in September 2026. The original, published on 2010-03-10, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.