SQL Injection in Dynamic SQL: Use sp_executesql

A name search can return every row when the input becomes part of the SQL text. Use sp_executesql with a parameter for values; use QUOTENAME only when an identifier must vary.

Set Up a Small Name Search

The labels are synthetic. This table gives the test one ordinary row, one other row, and one admin row that should not appear in a name lookup.

CREATE TABLE dbo.DynSqlDemo
(
    UserName sysname NOT NULL PRIMARY KEY,
    IsAdmin bit NOT NULL
);
INSERT dbo.DynSqlDemo(UserName, IsAdmin)
VALUES (N'UserA',0),(N'UserB',0),(N'AdminUser',1);

See What String Concatenation Does

The input below contains text that changes the query structure. When the application inserts it into the command string, the predicate becomes true for every row.

DECLARE @input nvarchar(100) = N'Nobody'' OR ''1''=''1';
DECLARE @unsafe nvarchar(max) =
    N'SELECT UserName, IsAdmin FROM dbo.DynSqlDemo WHERE UserName = N''' + @input + N''' ORDER BY UserName;';
EXEC(@unsafe);

EXEC() is not automatically unsafe. The problem is putting untrusted text into the SQL command.

Bind the Search Value

Keep the statement fixed and pass the search text as a parameter. The same crafted text is now one value, not executable SQL.

DECLARE @input nvarchar(100) = N'Nobody'' OR ''1''=''1';
EXEC sys.sp_executesql
    N'SELECT COUNT(*) AS MatchingRows FROM dbo.DynSqlDemo WHERE UserName = @name;',
    N'@name nvarchar(100)',
    @name = @input;

Bind every user-supplied value. The input stays data instead of becoming part of the command.

Quote a Dynamic Object Name

SQL parameters cannot stand in for table or column names. If the identifier must vary, validate it against an allowlist and quote it with QUOTENAME.

DECLARE @table sysname = N'DynSqlDemo';
DECLARE @sql nvarchar(max) =
    N'SELECT COUNT(*) FROM dbo.' + QUOTENAME(@table) + N';';
EXEC sys.sp_executesql @sql;

QUOTENAME makes the name a delimited identifier. It does not decide which objects a caller is allowed to query, so keep the allowlist.

Frequently Asked Questions