<!-- Generated from models/_canonical by scripts/build-model-variants.js. Edit the canonical source, not this file. Behavioural profile for Gemini: scripts/model-profiles.json -->

#Purpose

Rules for building SQL that cannot be subverted by input. Scope is injection into SQL specifically. Command injection is Security/command-injection; template and NoSQL injection are noted here only where the reasoning differs.

The single rule underneath everything: data must never be parsed as code. Every technique below is a way of keeping that boundary intact.

---

#Parameterised queries

#Bind values. Never concatenate them.

js
// WRONG — the query text changes shape with the input
db.query(`SELECT * FROM users WHERE email = '${email}'`);

// RIGHT — one fixed query, values sent separately
db.query("SELECT * FROM users WHERE email = $1", [email]);

The parameterised form is not "escaping done for you". The driver sends the statement and the values over separate protocol fields, so the database plans the query before it ever sees the data. Input cannot alter the parse tree because parsing already happened.

Placeholder syntax by dialect:

Never build SQL with string concatenation, template literals, +, format(), sprintf, or f-strings — in any language. If the query text varies with user input, the boundary is already broken.

Never rely on escaping functions like mysql_real_escape_string as your primary defence. They are dialect-specific, charset-sensitive, and historically bypassable — GBK multibyte sequences defeated exactly this pattern.

#Parameters bind values, not identifiers

A placeholder cannot stand in for a table name, a column name, ASC/DESC, or LIMIT in most drivers:

js
// This does NOT work — and the failure pushes people back to concatenation
db.query("SELECT * FROM $1", [table]);

When structure must be dynamic, allow-list it:

js
const SORTABLE = { created: "created_at", name: "display_name" };
const DIRECTION = { asc: "ASC", desc: "DESC" };

const column = SORTABLE[req.query.sort] ?? "created_at";
const order  = DIRECTION[req.query.dir]  ?? "ASC";

db.query(`SELECT * FROM users ORDER BY ${column} ${order} LIMIT $1`, [limit]);

The interpolated values came from a map the server controls, never from the request. An unknown key falls back to a default rather than passing through.

Never allow-list by regex (/^[a-z_]+$/) instead of an explicit map. A permissive pattern still admits valid identifiers you did not intend to expose, including columns holding password hashes.

---

#ORMs and query builders

An ORM is not automatic protection. Every major ORM has a raw-SQL escape hatch, and that hatch is where injection lives.

js
// Prisma — parameterised
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;

// Prisma — NOT parameterised, string is built before the driver sees it
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`);

The tagged-template form ($queryRaw) parameterises. The Unsafe variants do not — the name is the warning.

Equivalents to audit in review:

Never pass user input into LIKE without escaping the wildcards. % and _ are pattern metacharacters, and an unescaped % turns a lookup into a full scan — a denial-of-service vector even when injection is prevented.

js
const escaped = term.replace(/[\\%_]/g, (c) => "\\" + c);
db.query("SELECT * FROM docs WHERE title LIKE $1 ESCAPE '\\'", [`%${escaped}%`]);

---

#Stored procedures and dynamic SQL

A stored procedure is only safe if it does not itself build SQL from its arguments.

sql
-- Vulnerable despite being "a stored procedure"
CREATE PROCEDURE find_user(IN email VARCHAR(255))
BEGIN
  SET @q = CONCAT('SELECT * FROM users WHERE email = ''', email, '''');
  PREPARE stmt FROM @q; EXECUTE stmt;
END

Inside EXECUTE/sp_executesql/EXECUTE IMMEDIATE, bind parameters exactly as you would in application code. In PostgreSQL, use format() with %L (literal) or %I (identifier) — never %s — and prefer USING for values.

---

#Defence in depth

These do not replace parameterisation. They limit the damage when it fails.

• Least privilege. The application role should not hold DROP, CREATE, or GRANT. A read path should use a read-only role. Injection into a connection that cannot write is a disclosure bug, not a destruction bug.
• Disable multi-statement execution where the driver allows it. mysql2's multipleStatements defaults to false; keep it there. It converts '; DROP TABLE users; -- from catastrophic to a syntax error.
• Validate shape, then bind. Rejecting a non-numeric id early is good hygiene. It is not the control that stops injection — the bind is.
• Never expose raw database errors. ERROR: column "x" does not exist is a schema oracle. Log the detail server-side, return a generic message.
• Set statement timeouts so a pathological injected query cannot hold resources indefinitely.

---

#Anti-patterns

---

#Checklist

• Verify: Every query sends values as bound parameters, not concatenated text
• Verify: No template literal, +, format() or f-string builds SQL from input
• Verify: Dynamic identifiers and sort direction come from an explicit allow-list map
• Verify: No Unsafe ORM variant receives interpolated input
• Verify: LIKE patterns escape % and _ and declare ESCAPE
• Verify: Stored procedures bind inside EXECUTE, never CONCAT
• Verify: Application database role lacks DROP, CREATE and GRANT
• Verify: Multi-statement execution is disabled in the driver
• Verify: Database errors are logged server-side and never returned to clients
• Verify: Statement timeouts are configured

---

#Anchors (restated last, read last)

The rules that must hold when you stop, repeated here because the end of the context is what you act on:

• Never build SQL with string concatenation, template literals, +, format(), sprintf, or f-strings — in any language. If the query text varies with user input, the boundary is already broken.

• Never rely on escaping functions like mysql_real_escape_string as your primary defence. They are dialect-specific, charset-sensitive, and historically bypassable — GBK multibyte sequences defeated exactly this pattern.

• Never allow-list by regex (/^[a-z_]+$/) instead of an explicit map. A permissive pattern still admits valid identifiers you did not intend to expose, including columns holding password hashes.

• Never pass user input into LIKE without escaping the wildcards. % and _ are pattern metacharacters, and an unescaped % turns a lookup into a full scan — a denial-of-service vector even when injection is prevented.

• Every query sends values as bound parameters, not concatenated text

• No template literal, +, format() or f-string builds SQL from input

• Dynamic identifiers and sort direction come from an explicit allow-list map

• No Unsafe ORM variant receives interpolated input

• LIKE patterns escape % and _ and declare ESCAPE

• Stored procedures bind inside EXECUTE, never CONCAT

Before reporting done, prove the module still imports — run the line for this stack and paste its output:

bash
python -c "import <package>"          # Python: the package you changed
node -e "require('./<entry>')"       # Node CJS, or: node --input-type=module -e "import './<entry>.js'"
go build ./...                        # Go