Dynamic SQL in SQL Server: sp_executesql and Avoiding SQL Injection

Dynamic SQL builds a query as a string at runtime — necessary for variable table/column names, which can’t be parameterized normally (you can parameterize a WHERE value, but not “which table to query”). Done wrong, it’s also the single most common source of SQL injection, a vulnerability that has caused real, well-documented data breaches. This lesson treats that risk with the seriousness it deserves.

The Safe Pattern

DECLARE @tableName SYSNAME = 'Product';
DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT COUNT(*) AS row_count FROM ' + QUOTENAME(@tableName);
EXEC sp_executesql @sql;

-- Parameterized dynamic SQL — the SAFE way to inject user-supplied VALUES
DECLARE @minPrice DECIMAL(10,2) = 20.00;
SET @sql = N'SELECT name, price FROM dbo.Product WHERE price >= @MinPrice';
EXEC sp_executesql @sql, N'@MinPrice DECIMAL(10,2)', @MinPrice = @minPrice;

How Injection Actually Happens

-- NEVER do this — direct string concatenation of user input is a SQL injection hole
DECLARE @userInput NVARCHAR(100) = ''';DROP TABLE dbo.Product;--';
DECLARE @unsafeSql NVARCHAR(MAX) = N'SELECT * FROM dbo.Product WHERE name = ''' + @userInput + '''';
-- @unsafeSql is now: SELECT * FROM dbo.Product WHERE name = '';DROP TABLE dbo.Product;--'
-- If executed, this drops the table.

Walk through exactly why this works: the attacker’s input closes the intended string literal early with a stray ', appends a semicolon to start a brand-new statement, adds their own malicious SQL (DROP TABLE...), and then -- comments out whatever was supposed to follow in the original query, so it doesn’t cause a syntax error. Every part of that trick relies on user input being treated as executable code text rather than as inert data — which is exactly what sp_executesql parameters prevent, by keeping the query’s shape fixed and passing values through a separate channel the engine can never reinterpret as code.

The Rule, Visualized

Table/column/schema names must be concatenated (there’s no parameter placeholder for “which table”), sanitized with QUOTENAME(), which wraps the identifier in brackets and escapes any embedded bracket characters — this is what makes it safe, not just convention. But actual data values must always go through sp_executesql parameters, never string concatenation.

QUOTENAME Isn’t Optional Even for “Trusted” Input

-- Without QUOTENAME, a table name containing a bracket or reserved word breaks or worse:
DECLARE @table SYSNAME = 'Order'; -- a reserved keyword
SET @sql = N'SELECT * FROM ' + @table; -- syntax error, or worse if attacker-controlled

-- With QUOTENAME, it's safely wrapped regardless of content:
SET @sql = N'SELECT * FROM ' + QUOTENAME(@table); -- becomes: SELECT * FROM [Order]

Why Use Dynamic SQL At All?

Given the risk, it’s worth being clear about when dynamic SQL is genuinely the right tool: building a search query with an unpredictable number of optional filters, generating administrative scripts that operate across a variable list of tables, or building reports where the pivoted columns aren’t known until runtime. For everything else — the vast majority of real T-SQL — a normal parameterized query or stored procedure with fixed parameters is simpler, safer, and lets the query optimizer cache and reuse execution plans more effectively.