SQL injection

Article.

SQL injection happens when input becomes part of the SQL text instead of a value inside it. “Use parameterized queries” is the right one-line answer; the senior answer explains what the driver actually sends, because that is where the remaining holes are: identifiers, ORM escape hatches, client-side escaping, and dictionaries of user keys reaching the ORM.

Your code only uses the ORM. Are you safe? Mostly, and the exceptions are the interview. Outputs below are from PostgreSQL 18.4 with psycopg 3.3.4 and psycopg2 2.9.13 on Python 3.14.5.

The attack, run

name, pw = "alice", "' OR '1'='1"
cur.execute(f"SELECT id, name FROM users WHERE name = '{name}' AND password = '{pw}'")
# [(1, 'alice'), (2, 'bob')]       every user: AND binds tighter than OR
cur.execute("SELECT id, name FROM users WHERE name = %s AND password = %s", (name, pw))
# []

A search box concatenated into LIKE does more than bypass a check:

term = "zzz' UNION SELECT id, name || ':' || password FROM users --"
cur.execute(f"SELECT id, name FROM users WHERE name LIKE '%{term}%'")
# [(1, 'alice:s3cret'), (2, 'bob:hunter2')]

UNION attaches any query whose columns line up; the -- comments out the rest. From there an attacker reads the catalogue and every table the application’s role can see.

What the driver sends

Server-side binding: psycopg 3, asyncpg

The PostgreSQL wire protocol has two ways to run a query. The simple protocol sends one string. The extended protocol sends the SQL with $1 placeholders (Parse), then the values separately (Bind). The server log shows the difference:

psycopg 3:  execute <unnamed>: SELECT 'marker-psycopg3', $1::text
            DETAIL:  Parameters: $1 = 'O''Reilly'
psycopg2:   statement: SELECT 'marker-psycopg2', 'O''Reilly'::text

With psycopg 3 the query was parsed before the value arrived, so the value cannot change its structure: there is no string for it to escape out of. asyncpg works the same way, with $1 in the SQL.

Client-side escaping: psycopg2, PyMySQL, mysqlclient

psycopg2 does not use prepared statements. It escapes each value and merges it into the SQL on the client: mogrify("SELECT %s", ("O'Reilly",)) gives SELECT 'O''Reilly'. That is safe as long as the escaping is correct for the server’s encoding and settings (standard_conforming_strings, on by default since PostgreSQL 9.1).

When escaping code has a bug, it is an injection: CVE-2025-1094 (February 2025) was libpq’s escaping functions mishandling invalid UTF-8, used in a real attack chain; fixed in 17.3, 16.7, 15.11, 14.16 and 13.19. psycopg 3’s ClientCursor behaves like psycopg2 for code that needs it.

Placeholders by driver

Stacked statements

psycopg 3, f-string, no params      both statements ran
psycopg 3, %s and a second stmt     cannot insert multiple commands
                                    into a prepared statement
psycopg2, with params               both ran

The extended protocol accepts one statement, so psycopg 3 refuses a stacked statement only when parameters are passed. The f-string case, the dangerous one, sends no parameters and runs everything. Do not count on the driver to stop ; DROP TABLE.

What cannot be a parameter

Placeholders stand for values. Each of these failed with syntax error at or near "$1":

cur.execute("SELECT * FROM %s", ("users",))                    # table name
cur.execute("SELECT * FROM users ORDER BY id %s", ("DESC",))   # keyword
cur.execute("SET statement_timeout = %s", ("5s",))             # utility statement
cur.execute("SELECT * FROM users WHERE id IN %s", ((1, 2),))   # IN with a tuple

The fixes, in order of preference:

• Allowlist keywords and directions: map "asc"/"desc" to fixed strings, reject anything else.
• Quote identifiers with psycopg.sql:

from psycopg import sql
q = sql.SQL("SELECT {col} FROM {tbl} ORDER BY {col}").format(
    col=sql.Identifier(col_name), tbl=sql.Identifier(table_name))
# sql.Identifier('users"; DROP TABLE users; --') renders as
#   "users""; DROP TABLE users; --"    one (odd) identifier, not code

• Lists as one array value: WHERE id = ANY(%s) with a Python list.
• Settings through a function: SELECT set_config('statement_timeout', %s, false) takes a parameter.

t-strings (Python 3.14, psycopg 3.3)

PEP 750 template strings look like f-strings but hand the driver the parts separately, so the safe form is also the natural one:

user_id = "1 OR 1=1"
cur.execute(t"SELECT id FROM users WHERE id::text = {user_id}")   # bound: []
cur.execute(t"SELECT count(*) FROM {tbl:i}")                       # identifier
cur.execute(t"SELECT count(*) FROM users WHERE {frag:q}")          # SQL snippet

{x} is a bound parameter, {x:i} an identifier, {x:l} a client-side literal, and {x:q} inserts SQL verbatim. That last one is the hole, and psycopg guards it: a plain str raised sql values must be sql.Composed, sql.SQL, or Template; got str. Only sql.SQL objects, written in code, get through.

ORMs and their escape hatches

The query builders parameterize everything they generate. The holes are where raw SQL or user-controlled names go in:

# SQLAlchemy
session.execute(text(f"SELECT * FROM users WHERE id = {user_id}"))   # injectable
select(User).order_by(text(request.args["sort"]))                    # injectable
session.execute(text("SELECT * FROM users WHERE id = :id"), {"id": user_id})  # safe

# Django
User.objects.raw(f"SELECT * FROM users WHERE name = '{name}'")       # injectable
User.objects.extra(where=[f"name = '{name}'"])                        # injectable
User.objects.raw("SELECT * FROM users WHERE name = %s", [name])       # safe

Also: RawSQL, Func(template=...), literal_column(), and connection.cursor(). Django documents extra() as a last resort it plans to deprecate.

When the ORM itself was injectable

Django’s own CVEs share one pattern: user-controlled keys reaching ORM internals.

• CVE-2025-64459 (CVSS 9.1): filter(**request.GET.dict()) or Q(**kwargs) let a request set the internal _connector; fixed in 5.2.8, 5.1.14 and 4.2.26.
• CVE-2025-57833: FilteredRelation aliases from user input.
• CVE-2024-42005: JSONField keys passed to values().
• CVE-2022-28346: kwargs to annotate(), aggregate() and extra().

Never pass a request’s dictionary as keyword arguments to the ORM; map allowed fields explicitly.

LIKE wildcards

cur.execute("SELECT * FROM users WHERE name LIKE %s", (f"%{search}%",))

This is bound, so not injection in the SQL sense, but % and _ in search are wildcards: "%" matches everything, and a leading % defeats any B-tree index, so a crafted search is a full scan. The frameworks escape it for you:

Django: title__icontains="50%_off"
  LIKE %s ESCAPE '\'   with '%50\%\_off%'
SQLAlchemy: .contains("50%_off", autoescape=True)
  ESCAPE '/'           with '50/%/_off'

Hand-written, escape \, % and _ and add ESCAPE.

Second-order and stored code

Input stored safely can be dangerous later: a report definition saves a column name, and a nightly job builds f"SELECT {col} FROM sales". The value came from your own database and is still attacker-controlled.

The same holds inside the database: a PL/pgSQL function that runs EXECUTE 'SELECT ... WHERE name = ''' || p_name || '''' is injectable. Use EXECUTE format('SELECT %I FROM t WHERE name = %L', col, p_name) or EXECUTE ... USING p_name. Stored procedures are not a fix in themselves.

The NoSQL sibling

MongoDB has no SQL text to inject into, but a JSON body can carry operators:

body = json.loads('{"name": "alice", "password": {"$ne": null}}')
db.users.find_one({"name": body["name"], "password": body["password"]})
# {'name': 'alice', 'password': 's3cret'}     logged in without the password

Validate types before querying (a Pydantic model with password: str), and disable server-side JavaScript ($where, $function) if unused (MongoDB).

Where it breaks in production

One dynamic ORDER BY

Every query is parameterized except a sort column taken from the URL, because placeholders cannot express it. That one f"ORDER BY {sort}" is the whole vulnerability. Allowlist it.

Error messages as an oracle

A 500 page that prints the database error lets an attacker extract data one error at a time (CAST((SELECT password ...) AS int) puts the password in the error text). Return generic errors; log the details.

A role that can do anything

The application connects as a superuser or the table owner, so an injection can DROP, read pg_shadow, or run COPY ... TO PROGRAM. Connect with a role that has only the privileges the application needs; since PostgreSQL 15, ordinary roles cannot create objects in public by default. Row-level security and statement_timeout limit the rest.

Relying on the WAF

A web application firewall blocks ' OR 1=1 and misses the same payload URL-encoded, split across parameters, or arriving through JSON. It is a detection layer, not a fix.

Detection

• Static: Ruff’s S608 (ported from Bandit’s B608) flags string-built SQL; Semgrep or CodeQL (py/sql-injection) follow tainted input across functions.
• Review: search for execute(f", text(f", raw(f", extra(, % or + next to SQL, and **request passed to ORM calls.
• Runtime: injection attempts make syntax errors. Bursts of SQLSTATE 42601 (syntax error), 42883 (undefined function) or 22P02 (invalid text representation) in the server log (%e in log_line_prefix) are a probe in progress; pgaudit records what ran. pg_stat_statements will not show injected values, because it replaces constants with $1.

The trade not taken

The alternative to binding is escaping: build the SQL string yourself with a correct quoting function. It works, psycopg2 does it for every query, and it fails exactly when the escaping is wrong for some encoding or setting, as CVE-2025-1094 showed.

Server-side binding removes the question: the value never passes through the SQL parser. Prefer drivers that bind on the server, keep identifiers in sql.Identifier or an allowlist, and treat every string that becomes SQL text as code.

Related

• OWASP Top 10 (2025) - injection in the OWASP list
• SQLAlchemy - SQLAlchemy basics
• pg_stat_statements - what the server records
• MongoDB - MongoDB
• What's new in Python 3.12-3.14 - t-strings