Prevent SQL injection: the value, the identifier, and the context it runs under
Parameterising a value is the familiar part. Confirm it in a sentence and spend the answer on what a parameter cannot reach: an identifier, the type of a runtime-chosen sort expression, the permissions a dynamic batch runs under, and an encrypted column. The behavior below is Azure SQL Database's.
1. A parameter is a value, a concatenation is code, and one query shows the difference
Do not assert this. Run it. Both statements get the same string.
CREATE TABLE dbo.Users (id int NOT NULL PRIMARY KEY, name nvarchar(50) NOT NULL);
INSERT INTO dbo.Users (id, name) VALUES (1, N'ann'), (2, N'bob');
DECLARE @input nvarchar(100) = N'ann'' OR 1=1--';
-- Typed parameter: the input is one value, and no row holds it. Returns 0.
EXEC sp_executesql N'SELECT COUNT(*) AS matched FROM dbo.Users WHERE name = @n',
N'@n nvarchar(100)', @n = @input;
-- Same input concatenated: the input is now part of the statement. Returns 2.
DECLARE @sql nvarchar(max) =
N'SELECT COUNT(*) AS matched FROM dbo.Users WHERE name = N''' + @input + N'''';
EXEC (@sql); -- vulnerable on purpose: do not copy
Zero against two, from one string. Learn's rule for @params is the discipline in one line: values
can only be constants or variables, never expressions built with operators.
2. No parameter binds an identifier, and QUOTENAME returns NULL past 128 characters
EXEC sp_executesql N'SELECT * FROM @t', N'@t sysname', @t = @name cannot work: @t is a value,
not a table. A dynamic identifier has to enter the SQL text, so QUOTENAME's documented edges are
load bearing. Its input, character_string, is sysname:
- Up to 128 characters,
QUOTENAMEreturns the name in brackets. An embedded]is doubled correctly, so escaping is not the defect. - Over 128 characters,
QUOTENAMEreturnsNULL. It does not truncate the name and it raises no error. An unacceptable quote character also returnsNULL. - A
NULLconcatenated into a batch makes the whole batchNULL, and executing aNULLbatch does nothing and reports nothing. The long name is not rejected; the statement silently does not run.
DECLARE @n129 nvarchar(200) = REPLICATE(N'a', 129);
SELECT LEN(QUOTENAME(REPLICATE(N'a', 128))) AS len_at_128, -- 130
IIF(QUOTENAME(@n129) IS NULL, 1, 0) AS null_at_129, -- 1
IIF(QUOTENAME(N'tbl', N'#') IS NULL, 1, 0) AS null_bad_quote; -- 1
DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + QUOTENAME(@n129);
EXEC sp_executesql @sql;
SELECT IIF(@sql IS NULL, 1, 0) AS batch_was_null, @@ERROR AS err; -- 1, 0
EXEC (@sql) on the same NULL behaves identically: this is the NULL batch, not one calling
convention. Nothing reports it at any severity:
sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C -b -m-1 \
-Q "DECLARE @s nvarchar(max)=N'SELECT 1 FROM '+QUOTENAME(REPLICATE(N'x',129)); EXEC sp_executesql @s; SELECT @@ERROR AS err;"
-b exits non-zero at severity 11 and above and -m-1 lowers the print level, but there is no
message at any level: err is 0 and so is the exit code. The NULL has to be the check.
Neither helper checks that the object exists. QUOTENAME brackets a string and PARSENAME
splits a qualified name; Learn says each part is sysname, a part over 256 bytes comes back
NULL, and it does not indicate whether an object of that name exists. Only a catalog lookup
decides existence, so resolve the caller's string against the catalog and build from what it
returned:
DECLARE @requested nvarchar(400) = N'dbo.Users', @safe nvarchar(300);
SELECT @safe = QUOTENAME(s.name) + N'.' + QUOTENAME(o.name)
FROM sys.objects AS o JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE s.name = PARSENAME(@requested, 2) AND o.name = PARSENAME(@requested, 1) AND o.type = 'U';
IF @safe IS NULL THROW 50001, 'No row in the catalog matched that name.', 1;
DECLARE @sql nvarchar(max) = N'SELECT COUNT(*) AS n FROM ' + @safe;
EXEC sp_executesql @sql;
In this pattern an over-long name never reaches QUOTENAME, which only brackets names read back
from the catalog. The lookup matches no row, because no catalog name is longer than 128 characters,
so @safe stays NULL and the THROW raises error 50001. The long name is rejected with an error
by the lookup, instead of turning into a silent NULL batch at QUOTENAME. Where the legal
identifiers are known in advance, an allowlist is simpler.
3. One CASE over mixed types in a dynamic ORDER BY fails for some sort keys and not others
A single CASE forces every branch to one common type chosen by type precedence, not by the branch
the sort key selects. On dbo.T (i int, d date, s nvarchar(50)):
| Branches | sort key selects | Result |
|---|---|---|
THEN i / THEN d |
anything, including neither | Msg 206, operand type clash, int against date |
THEN s / THEN d |
d, the higher-precedence side |
runs clean |
THEN s / THEN d |
s, the lower-precedence side |
Msg 241, conversion failed from character string |
THEN i / THEN s |
s, the lower-precedence side |
Msg 245, conversion failed converting nvarchar to int |
int and date have no implicit conversion, so that pairing is refused at compile time and a
surrounding BEGIN TRY does not catch it. The string pairings are the sharper trap: the identical
statement passes for one sort key and fails for another, so it survives a test against whichever
column the author tried. One CASE per candidate column, each carrying a single type, has no common
type to coerce toward; non-matching columns evaluate to NULL and tie, so this composes with
OFFSET/FETCH and a direction toggle:
DECLARE @col sysname = N'created', @dir char(1) = N'A';
SELECT id, name, created FROM dbo.Grid
ORDER BY CASE WHEN @col = N'id' AND @dir = N'A' THEN id END ASC,
CASE WHEN @col = N'id' AND @dir = N'D' THEN id END DESC,
CASE WHEN @col = N'created' AND @dir = N'A' THEN created END ASC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
4. Dynamic SQL breaks the ownership chain, so EXECUTE AS is part of the answer
Learn: dynamic SQL in procedural code breaks the ownership chain, so the engine checks the caller's permissions against every object the statement touches. A procedure a caller could run yesterday stops working the day its query becomes dynamic, and parameterising does not bring it back.
CREATE TABLE dbo.Ledger (id int NOT NULL PRIMARY KEY);
INSERT INTO dbo.Ledger (id) VALUES (1);
CREATE USER app_reader WITHOUT LOGIN;
GO
CREATE PROCEDURE dbo.CountLedgerDynamic AS
EXEC sp_executesql N'SELECT COUNT(*) AS n FROM dbo.Ledger';
GO
CREATE PROCEDURE dbo.CountLedgerAsOwner WITH EXECUTE AS OWNER AS
EXEC sp_executesql N'SELECT COUNT(*) AS n, CURRENT_USER AS running_as FROM dbo.Ledger';
GO
GRANT EXECUTE ON dbo.CountLedgerDynamic TO app_reader;
GRANT EXECUTE ON dbo.CountLedgerAsOwner TO app_reader;
GO
EXECUTE AS USER = 'app_reader';
EXEC dbo.CountLedgerDynamic; -- Msg 229, SELECT permission denied on 'Ledger'
EXEC dbo.CountLedgerAsOwner; -- 1, dbo
REVERT;
Learn names two remedies. EXECUTE AS replaces the caller's permissions for the whole module,
nested modules inherit the proxy context, and the identity functions report the proxy, which is what
running_as returning dbo shows, so anything keyed to the caller must arrive as a parameter.
Certificate signing merges the certificate user's permissions with the caller's and leaves the
execution context alone, at the cost of re-signing on every change. Open rls-multi-tenant before
putting EXECUTE AS on a module touching a table under a security policy: the proxy context is what
a tenant predicate reads. Neither remedy substitutes for parameterising.
5. On an Always Encrypted column a literal does not run at all
Learn lists it under Always Encrypted's limitations: comparing an encrypted column to a literal, or
inserting one, fails with Msg 206, Level 16, State 2, Operand type clash. Only a value bound as a
parameter over a connection with Column Encryption Setting=Enabled is encrypted before it leaves
the driver, so parameterising is the only thing that returns a row.
Check it worked
-- Every module that builds SQL, and the context it runs under. A NULL principal id means the
-- caller's permissions apply to whatever the dynamic statement touches.
SELECT o.name, m.execute_as_principal_id
FROM sys.sql_modules AS m JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE m.definition LIKE N'%sp_executesql%' OR m.definition LIKE N'%EXECUTE (%';
-- No dynamic batch executes while NULL. Expect the rejection, not zero rows.
DECLARE @batch nvarchar(max) = N'SELECT * FROM ' + QUOTENAME(REPLICATE(N'x', 129));
IF @batch IS NULL SELECT 'rejected before execution' AS result ELSE EXEC sp_executesql @batch;
Then re-run section 1's pair against the real table: parameterised matches zero rows, concatenated
every row. Run the generated ORDER BY once per sort key a caller may send, not only the one
tested: any raising Msg 206, 241 or 245 means a mixed-type CASE is still there.
Do not
- Do not present
QUOTENAMEas sufficient for a caller-supplied identifier without checking its result forNULL, and do not say it truncates a long name or raises an error for one. It returnsNULL, and nothing reports it. - Do not say either helper checks that an object exists. Neither reads the catalog.
- Do not build a caller-chosen
ORDER BYfrom oneCASEspanning columns of different types, even when the columns tried in testing share a type. - Do not add
WITH EXECUTE AS OWNERto clear a permission error without saying what the module can now reach and that the caller's identity is no longer visible inside it. - Do not tell a developer an agent-composed statement is made safe by parameterising the one value inside it. The model authored the whole statement, so the control is the permission boundary its connection runs under.
- Do not warn about
xp_cmdshell, linked servers orOPENROWSETagainst arbitrary providers. None exist on Azure SQL Database to escalate to.
References
- Open the runnable checks when a claim above disagrees with what you see, or to look up the message a column-type pairing produces.
- Microsoft Learn, if a number above looks stale: QUOTENAME, PARSENAME, secure dynamic SQL.