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.
sql
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
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:
is a value,
not a table. A dynamic identifier has to enter the SQL text, so
's documented edges are
load bearing. Its input,
, is
sysname:
- Up to 128 characters, returns the name in brackets. An embedded is doubled
correctly, so escaping is not the defect.
- Over 128 characters, returns . It does not truncate the name and it raises no
error. An unacceptable quote character also returns .
- A concatenated into a batch makes the whole batch , and executing a batch
does nothing and reports nothing. The long name is not rejected; the statement silently does not
run.
sql
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
on the same
behaves identically: this is the
batch, not one calling
convention. Nothing reports it at any severity:
bash
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;"
exits non-zero at severity 11 and above and
lowers the print level, but there is no
message at any level:
is 0 and so is the exit code. The
has to be the check.
Neither helper checks that the object exists. brackets a string and
splits a qualified name; Learn says each part is
sysname, a part over 256 bytes comes back
, 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:
sql
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
, which only brackets names read back
from the catalog. The lookup matches no row, because no catalog name is longer than 128 characters,
so
stays
and the
raises error 50001. The long name is rejected with an error
by the lookup, instead of turning into a silent
batch at
. 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
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 |
|---|
| / | anything, including neither | , operand type clash, int against date |
| / | , the higher-precedence side | runs clean |
| / | , the lower-precedence side | , conversion failed from character string |
| / | , the lower-precedence side | , conversion failed converting nvarchar to int |
and
have no implicit conversion, so that pairing is refused at compile time and a
surrounding
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
per candidate column, each carrying a single type, has no common
type to coerce toward; non-matching columns evaluate to
and tie, so this composes with
/
and a direction toggle:
sql
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.
sql
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.
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
returning
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
before
putting
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
sql
-- 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 (%';
sql
-- 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
once per sort key a caller may send, not only the one
tested: any raising
,
or
means a mixed-type
is still there.
Do not
- Do not present as sufficient for a caller-supplied identifier without checking its
result for , and do not say it truncates a long name or raises an error for one. It returns
, and nothing reports it.
- Do not say either helper checks that an object exists. Neither reads the catalog.
- Do not build a caller-chosen from one spanning columns of different types, even
when the columns tried in testing share a type.
- Do not add to 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 , linked servers or against 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.