SQL Injection Prevention skill for Codex

Done well means: every value reaches the database as a bound parameter, every identifier or keyword built from input comes from a hard-coded allowlist, the app’s DB role could not drop or read other tenants’ tables even if one query were injectable, and a test proves the fixed query treats a payload as data.

Find the candidates

1. Read AGENTS.md for the ORM, driver and test command.
2. Search for query strings built with concatenation, interpolation or formatting:

# Interpolation into SQL-looking strings
rg -n -i "(select|insert|update|delete|where|order by)[^\"'\`]*[\"'\`]\s*(\+|%|\.format\(|\$\{)" -g '!node_modules' -g '!*.min.js'
rg -n -i "f[\"'](select|insert|update|delete|with)\b" --type py
rg -n "fmt\.Sprintf\(\s*\"(SELECT|INSERT|UPDATE|DELETE)" --type go
# ORM escape hatches
rg -n "\$queryRawUnsafe|\$executeRawUnsafe|Prisma\.raw\(|sequelize\.literal|Sequelize\.literal|knex\.raw|\.whereRaw|\.orderByRaw|\.extra\(|RawSQL\(|\.raw\(|text\(f|createNativeQuery|createQuery\(\"[^\"]*\"\s*\+|find_by_sql|\.order\(params" -g '!node_modules'

3. If Semgrep is available, semgrep --config p/sql-injection finds more patterns (registry rules need network on first download; see $semgrep-sast).
4. Trace each hit to its inputs. Values from request params, headers, cookies, files, queues, other services and previously stored rows (second-order injection) are untrusted.

Fix by what is being inserted

Inserted thing	Fix
A value (string, number, date)	Bound parameter
A list for IN (...)	Driver array support (= ANY($1) in Postgres) or generate one placeholder per item
Column or table name	Map from an allowlist dict; never pass input through
Sort direction	"desc" if x == "desc" else "asc"
LIMIT / OFFSET	Parse to int, clamp, then bind (or format the int)
LIKE pattern	Escape %, _ and the escape char, then bind
Whole WHERE clause from a query language	Parse into an AST and generate SQL with the query builder

Correct patterns

Python (DB-API, psycopg 3, SQLAlchemy 2):

cur.execute("SELECT * FROM orders WHERE customer_id = %s AND status = %s", (cid, status))

from sqlalchemy import text, select
session.execute(text("SELECT * FROM orders WHERE id = :id"), {"id": order_id})

SORTABLE = {"created": Order.created_at, "total": Order.total}
col = SORTABLE.get(sort_key, Order.created_at)
stmt = select(Order).order_by(col.desc() if direction == "desc" else col.asc())

Node:

// pg
await pool.query("SELECT * FROM users WHERE email = $1", [email]);
// Prisma: tagged template binds values; the Unsafe variants do not
await prisma.$queryRaw`SELECT * FROM "User" WHERE email = ${email}`;
// knex: ?? binds identifiers, ? binds values (still allowlist the identifier)
await knex.raw("SELECT * FROM ?? WHERE id = ?", [allowedTable, id]);

Java (JDBC / JPA):

PreparedStatement ps = conn.prepareStatement("SELECT * FROM accounts WHERE owner = ?");
ps.setString(1, owner);
em.createQuery("SELECT a FROM Account a WHERE a.owner = :owner", Account.class)
  .setParameter("owner", owner);

Go: placeholders depend on the driver: $1 for pgx and lib/pq, ? for MySQL and SQLite.

row := db.QueryRowContext(ctx, "SELECT id FROM users WHERE email = $1", email)

Rails:

User.where(email: params[:email])
User.where("name LIKE ?", "%#{User.sanitize_sql_like(q)}%")
ORDERS = { "created" => :created_at, "total" => :total }.freeze
Order.order(ORDERS.fetch(params[:sort], :created_at) => (params[:dir] == "asc" ? :asc : :desc))

Traps inside “safe” tools

• Prisma $queryRawUnsafe(sql, ...values) is safe only for the positional values; any interpolation into sql is injectable. Composing fragments needs Prisma.sql and Prisma.join, not string building. Prisma.raw() inserts text verbatim.
• SQLAlchemy text(f"... {x}") is raw SQL. text() is only safe with :name binds.
• Django .extra(), RawSQL() and .raw() accept params lists; f-strings inside them are injectable. QuerySet.order_by(user_input) lets users sort by any field, including related sensitive ones, so allowlist.
• Sequelize literal(), where: sequelize.where(sequelize.literal(...)) and the replacements option with string concatenation around it.
• TypeORM .where("name = '" + name + "'") instead of .where("name = :name", { name }).
• sql.Named, %s placeholders, and ? differ by driver; using Python % string formatting with a %s placeholder is a classic false fix.
• Bound parameters do not protect identifiers or keywords; the driver will quote a bound column name as a string literal or reject it.

Dynamic SQL in the database

-- PL/pgSQL: %I quotes identifiers, values go through USING
EXECUTE format('SELECT count(*) FROM %I WHERE owner_id = $1', tbl) INTO n USING owner;

-- SQL Server: sp_executesql with typed parameters, QUOTENAME for identifiers
EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE CustomerId = @c', N'@c int', @c = @CustomerId;

EXEC('... ' + @input) and EXECUTE 'SELECT ... ' || input are injectable even inside a stored procedure.

Limit the blast radius

• App DB role: only DML on its own schema; no superuser, no CREATE, no FILE (MySQL), no pg_read_server_files, no xp_cmdshell.
• Separate read-only role for reporting endpoints.
• Disable stacked queries where the driver allows (most do by default; MySQL multipleStatements: false in mysql2).
• Return generic errors to clients; detailed SQL errors in responses make exploitation far faster.
• Postgres row-level security as a tenant boundary: $postgres-row-level-security.

Verification

For each fixed query, add a test that sends a payload and asserts it is treated as data:

def test_search_treats_payload_as_text(client, seed_orders):
    r = client.get("/orders", params={"q": "x' OR '1'='1", "sort": "id; DROP TABLE orders"})
    assert r.status_code in (200, 400)
    assert r.json()["items"] == []            # no rows leaked
    assert Order.objects.count() == len(seed_orders)

Re-run the searches above; remaining hits must each have a comment explaining why the interpolated part is a constant. Dynamic scanners like sqlmap are only for targets the user owns or has written authorisation to test, and never against production without explicit confirmation.

Output template

## SQL injection review: <scope>
| # | File:line | Query builder | Untrusted input | Severity | Fix |
|---|---|---|---|---|---|
| 1 | api/search.ts:58 | $queryRawUnsafe | req.query.sort | Critical | Allowlist map + Prisma.sql |
DB role privileges: <findings>
Remaining raw SQL (reviewed constant-only): <list>

Gotchas

• Second-order injection: a value stored safely and later concatenated into a query in a batch job or admin report.
• JSON and array operators: building data->>' + key + ' from input is injection.
• Escaping functions (mysql_real_escape_string, manual quote doubling) fail on charset tricks and numeric contexts. Replace them with binds.
• ORMs in “raw” mode inside migrations or seed scripts get reused in runtime code later; review them too.