prevent-sql-injection

# Prevent SQL injection: the value, the identifier, and the context it runs under 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. ```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); ``` 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: ```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;" ``` `-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: ```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; ``` 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: ```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. `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 ```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 `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 `QUOTENAME` as sufficient for a caller-supplied identifier without checking its result for `NULL`, and do not say it truncates a long name. It returns `NULL`. - Do not say either helper checks that an object exists. Neither reads the catalog. - Do not build a caller-chosen `ORDER BY` from one `CASE` spanning columns of different types, even when the columns tried in testing share a type. - Do not add `WITH EXECUTE AS OWNER` 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 `xp_cmdshell`, linked servers or `OPENROWSET` against arbitrary providers. None exist on Azure SQL Database to escalate to. ## References - Open [the measured runs](references/quotename-null-and-order-by-clashes.md) 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](https://learn.microsoft.com/sql/t-sql/functions/quotename-transact-sql), [PARSENAME](https://learn.microsoft.com/sql/t-sql/functions/parsename-transact-sql), [secure dynamic SQL](https://learn.microsoft.com/sql/connect/ado-net/sql/writing-secure-dynamic-sql).