Prevention — Python
DB-API placeholders (and why they are not %-formatting), psycopg's sql.Identifier for safe dynamic identifiers, and SQLAlchemy bindparams.
The Rule
Pass parameters as the second argument to execute(). The %s in a DB-API query is a placeholder, not Python string formatting — the distinction is the whole game.
Python's specific trap is exactly that visual overlap: cursor.execute("... %s" % value) (formatting, injectable) versus cursor.execute("... %s", (value,)) (parameter, safe) differ by one character and a comma.
DB-API (psycopg, mysqlclient, sqlite3)
# Every one of these is Python string formatting, done BEFORE
# the driver sees the query. All injectable.
cursor.execute("SELECT * FROM users WHERE name = '%s'" % name)
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")
cursor.execute("SELECT * FROM users WHERE name = '" + name + "'")
cursor.execute(
"SELECT * FROM users WHERE name = '{}'".format(name))Dynamic Identifiers — psycopg.sql
Placeholders bind values, not identifiers. psycopg (2 and 3) provides a composition API that quotes identifiers safely, which is better than a hand-rolled allowlist when the set of columns is large or genuinely dynamic.
Safe dynamic identifiers
# Column name interpolated into the query text.
cursor.execute(
f"SELECT * FROM products ORDER BY {sort_column}")SQLAlchemy
from sqlalchemy import text
# f-string inside text() interpolates before binding.
session.execute(
text(f"SELECT * FROM users WHERE name = '{name}'"))
# .filter with a raw string
session.query(User).filter(f"name = '{name}'")Checklist
- Parameters as the second
execute()argument. Never%,f"",+, or.format()to build SQL. %sis a placeholder, not formatting — the comma before the tuple is load-bearing.- Identifiers:
psycopg.sql.Identifieror an allowlist. - SQLAlchemy:
text(...).bindparams/ dict params, or stay in the ORM. - Django ORM parameterises automatically; for
.raw()andRawSQL, pass params — see ORM Injection. - Add Bandit (
B608) or Semgrep to CI to flag string-built SQL. - Least-privilege database role; do not connect as a PostgreSQL superuser — see PostgreSQL.
Related
Every ORM has an escape hatch to raw SQL, and every one of them is a footgun. Where the abstraction stops protecting you, and which method names to grep for.
Strict typing, stacked queries, and COPY TO PROGRAM — the engine where an injection most reliably becomes command execution.
Where your input lands in the query determines which payloads can possibly work. Get the context wrong and every payload fails for the wrong reason.
No sleep, no network, no users, no information_schema — but sqlite_master hands you the entire schema in one query, and ATTACH writes files.
Parameterisation removes the bug; these layers limit the blast radius when a parameterisation is missed. Least privilege, allowlists, monitoring, and what each is actually worth.