Parameterising a value is the part an agent already gets right. 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.
Measured 2026-08-29 against an engine reporting EngineEdition 5 and Edition SQL Azure.
QUOTENAME, PARSENAME, sp_executesql, ownership chaining and Always Encrypted were checked
against Microsoft Learn on 2026-09-03; flags from sqlcmd 1.10.0 help.
1. A parameter is a value, a concatenation is code, and the difference is measurable
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);
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's ceiling is a NULL
`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. Learn: `character_string` is **sysname**, and an input over 128 characters returns
`NULL`, as does an unacceptable quote character. Escaping is not the defect: an embedded `]` is
doubled correctly. Crossing 128 is, because a `NULL` in a batch makes the whole batch `NULL`:
```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
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;
The rejection there is the lookup's, not QUOTENAME's: an over-long name matches no row because no
table can carry one. 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. Measured 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. Documented, not reproduced here:
it needs a column master key provisioned outside the database.
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. It returnsNULL. - 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 measured runs 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.