Skip to content
Skills Library
Security · Review & audit

prevent-sql-injection

/prevent-sql-injection

Handles SQL injection on Azure SQL Database beyond parameterisation: a typed sp_executesql parameter matches nothing where the same input concatenated into EXEC() returns every row; QUOTENAME returns NULL above 128 characters, so the batch built from it becomes NULL and does nothing; a dynamic ORDER BY built from one CASE over mixed types fails only for the sort key on the lower-precedence branch; dynamic SQL breaks the ownership chain, so EXECUTE AS decides what it may touch; and Always Encrypted refuses a literal (Msg 206). Use for a general injection question or a pre-production review, whe

Licence
MIT
Last repository change
29.09.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
2
Scripts
0
HTML comments
0
URLs in SKILL.md
3
Deprecation

No signals detected. Repository changed on 29.09.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Handles SQL injection on Azure SQL Database beyond parameterisation: a typed sp_executesql parameter matches nothing where the same input concatenated into EXEC() returns every row; QUOTENAME returns NULL above 128 characters, so the batch built from it becomes NULL and does nothing; a dynamic ORDER BY built from one CASE over mixed types fails only for the sort key on the lower-precedence branch; dynamic SQL breaks the ownership chain, so EXECUTE AS decides what it may touch; and Always Encrypted refuses a literal (Msg 206). Use for a general injection question or a pre-production review, whe

Original author description

Handles SQL injection on Azure SQL Database beyond parameterisation: a typed sp_executesql parameter matches nothing where the same input concatenated into EXEC() returns every row; QUOTENAME returns NULL above 128 characters, so the batch built from it becomes NULL and does nothing; a dynamic ORDER BY built from one CASE over mixed types fails only for the sort key on the lower-precedence branch; dynamic SQL breaks the ownership chain, so EXECUTE AS decides what it may touch; and Always Encrypted refuses a literal (Msg 206). Use for a general injection question or a pre-production review, whe

How it works

  1. 1. A parameter is a value, a concatenation is code, and the difference is measurable
  2. 2. No parameter binds an identifier, and QUOTENAME's ceiling is a NULL
  3. 3. One CASE over mixed types in a dynamic ORDER BY fails for some sort keys and not others
  4. 4. Dynamic SQL breaks the ownership chain, so EXECUTE AS is part of the answer
  5. 5. On an Always Encrypted column a literal does not run at all
  6. Check it worked
  7. Do not
  8. References
Original SKILL.md headings · 198 lines, 1,631 words
SKILL.md preview
---
name: prevent-sql-injection
description: >-
  Handles SQL injection on Azure SQL Database beyond parameterisation: a typed sp_executesql
  parameter matches nothing where the same input concatenated into EXEC() returns every row;
  QUOTENAME returns NULL above 128 characters, so the batch built from it becomes NULL and does
  nothing; a dynamic ORDER BY built from one CASE over mixed types fails only for the sort key on
  the lower-precedence branch; dynamic SQL breaks the ownership chain, so EXECUTE AS decides what
  it may touch; and Always Encrypted refuses a literal (Msg 206). Use for a general injection
  question or a pre-production review, when a QUOTENAME-built statement returns and raises
  nothing, when a sort-by-column feature throws an operand type clash for one column only, when a
  procedure works until its query becomes dynamic, or when an encrypted column will not take a
  literal. Row level tenant isolation is rls-multi-tenant.
---

# 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).
Files in this package (2)
  • SKILL.md 11 KB
  • references/quotename-null-and-order-by-clashes.md 10 KB

Versions

The database has no other skills named prevent-sql-injection.

Identical content appears in 1 other GitHub folders. We show one copy with the highest score.

Database snapshot: 13.11.2026 · package version dccd74116fda (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100injection-prevention
    OWASP A01 - Injection Prevention. Use this skill when reviewing code for SQL injection, NoSQL injection, command injection, LDAP injection, or any user input that reaches databases, shells, or interpreters. Activate when: SQL query, database query, user input, command execution,…
  • 100sql-injection-prevention
    Use when writing any custom database query in WordPress with $wpdb — get_results, get_var, get_row, query, or building WHERE/IN/LIKE/ORDER BY clauses. Uses $wpdb->prepare() with correct placeholders (%d, %s, %f, %i), $wpdb->esc_like() for LIKE, and allowlists for identifiers…
  • 100input-validation
    Use when writing or reviewing code that processes user input — validates and sanitizes to prevent SQL injection, XSS, CSRF, and other injection attacks. NOT when the code path doesn't touch user-supplied data.
  • 100injection-prevention
    Prevent injection and unsafe input handling: missing validation, SQL and NoSQL injection, command injection, insecure deserialization and path traversal. Use when code builds queries, shell commands, file paths or deserializes data.
  • 100input-validation-injection
    Apply when reviewing or writing code that processes untrusted input, constructs queries or commands, or handles user-supplied data. Covers SQL, LDAP, OS command injection, prototype pollution, and general validation strategy.
  • 100golang-security
    Secure Go backend services against common vulnerabilities. Use when implementing input validation, crypto, or SQL injection prevention in Go.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.