SQL injection in psycopg

How it arises in psycopg

The placeholder is %s, which is also Python's string formatting marker. That overlap is the root of most mistakes. These two lines differ by one character and behave completely differently:

def lookup(cur, email):
    cur.execute("SELECT id FROM accounts WHERE email = %s" % email)
    cur.execute("SELECT id FROM accounts WHERE email = %s", (email,))

The first formats in Python and sends finished SQL. The second sends the statement and the value to the driver, which is the form the psycopg documentation calls correct. The documentation is unusually direct about the first: never use % or + to merge values into queries.

Four details from the same documentation matter in practice:

• The placeholder must not be quoted. '%s' is wrong; the driver adds quoting itself.
• It is always %s, even for numbers. %d and %f are not accepted.
• Named placeholders are written %(name)s and take a mapping.
• Table and column names cannot be parameters. cur.execute("INSERT INTO %s VALUES (%s)", ("numbers", 10)) fails, because the table name would be sent as a string literal.

The last point pushes developers back to string formatting for dynamic identifiers, which is why psycopg provides the psycopg.sql module.

Vulnerable and fixed

An export job that takes a table name and a tenant from a message:

import psycopg


def export_rows(conn: psycopg.Connection, table: str, tenant: str):
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM " + table + " WHERE tenant_id = '%s'" % tenant)
        return cur.fetchall()

sql.SQL is a template for the statement's fixed text, sql.Identifier quotes a name by PostgreSQL's identifier rules, and the tenant stays a bound value. The allowlist still decides which tables may be exported at all:

import psycopg
from psycopg import sql

EXPORTABLE = {"invoices", "payments"}


def export_rows(conn: psycopg.Connection, table: str, tenant: str):
    if table not in EXPORTABLE:
        raise ValueError("table is not exportable")
    query = sql.SQL("SELECT * FROM {} WHERE tenant_id = %s").format(sql.Identifier(table))
    with conn.cursor() as cur:
        cur.execute(query, (tenant,))
        return cur.fetchall()

What psycopg already does for you

The driver converts Python values to PostgreSQL's representation, so quoting is never the caller's job. psycopg.sql composes identifiers and fixed SQL without string operators. In psycopg 3, sql.SQL is annotated to take a LiteralString, so static type analysis can flag a call that builds its template from a variable. sql.Literal exists for the rare value that must be written into the statement, and the documentation steers you to placeholders first.

What es-python adds

psycopg2 and psycopg 3 are among the libraries that ship with es-python, and es-python's copy is the one your program imports by default. At the driver's query call it looks at the statement text. If part of that text came from a source you switched on, such as the network or a queue message read from it, an event is recorded with the sqli impact tag. For psycopg2 the events list names the operation psycopg2 cursor.execute().

Because the check sits at the driver, it does not matter what is above it. Django's cursor.execute(), a hand-written repository class and a migration script all end in the same call. Values sent as parameters are not statement text and produce nothing.

Three practical notes. An identifier composed with sql.Identifier is still outside data inside the statement, so the event can still appear; open it, confirm the allowlist, and record the decision with a rule. If you set ES2_PREFER_APP_LIBS=psycopg2 to use your own build of the driver, that driver is not watched, and the machine reports that as a coverage gap, so silence is not mistaken for safety. And the ready-made Block backend network attacks template blocks psycopg2 statements built from a request, the sql.Identifier case included, while parameterized queries run as usual.

Review checklist

1. Search for execute( followed by %, +, .format( or an f-string.
2. Remove quotes around placeholders and replace %d with %s.
3. Compose identifiers with sql.Identifier, after an allowlist check.
4. Give the application role only the privileges it needs.
5. Run static type analysis (mypy or pyright) so LiteralString annotations are enforced.

References

• Reference: psycopg 3 documentation: Passing parameters to SQL queries (checked 2026-10-12)
• Reference: psycopg 3 documentation: SQL string composition (checked 2026-10-12)
• Reference: psycopg2 documentation: Basic module usage (checked 2026-10-12)