SQL Injection in Python (psycopg2, SQLAlchemy, Django)

An f-string or % inside cursor.execute() lets a request parameter rewrite your SQL, turning a single-user lookup into a full table dump (CWE-89). The safe call for each Python database API, plus the two cases placeholders cannot cover: column names and IN lists.^{[1], [2], [3], [4], [5], [6], [7], [9]}

💡 Plain English Explainer (ELI5)

Imagine a mail-sorting desk with a fixed instruction card: 'File the parcel labeled ___.' If a worker copies the sender’s label into the instruction card before reading it, extra words can become new routing orders. A Python f-string or % formatting does that to SQL: it creates one command string before the driver sees the value. execute(sql, params) keeps the command card fixed and sends the label separately, so it remains data. A column name is part of the command card; choose it from a code-owned allowlist.^{[10], [1], [2], [9]}

Core Concepts & Subsystem Terms

cursor.execute(sql, params): The DB-API 2.0 call (PEP 249). The driver sends the values in params separately from the SQL text, so they can never change the statement. It is only safe when the values are in the second argument; an f-string or % expression in the first argument is the bug.^{[2], [3]}
paramstyle: %s, ?, :name: Each driver declares its placeholder in its paramstyle attribute: psycopg2 and PyMySQL use %s or %(name)s, sqlite3 uses ? or :name. The %s is read by the driver, not by Python's % operator, so never format the string yourself.^{[2], [3], [5]}
SQLAlchemy text(): Wraps a raw SQL string for SQLAlchemy. Safe when values are named bind parameters such as :email and are passed to execute() as a dict. Unsafe when the string given to it is already an f-string: text() does not escape anything inside it.^{[6], [9]}
Django raw() / RawSQL / extra(): Django's escape hatches from the ORM. raw(sql, params) and RawSQL(sql, params) are safe only when every value goes in params; Django sends whatever is interpolated into sql verbatim. The Django docs call extra() a last resort, so ban it in review.^{[7], [9]}
psycopg2.sql.Identifier: Placeholders carry values only, never table or column names. sql.Identifier double-quotes a name so it cannot break out of the identifier. Check the name against an allowlist first so users cannot pick columns you did not intend to expose.^{[4], [9]}
IN lists and expanding binds: A Python list cannot be bound to one ? in sqlite3, so people fall back to joining strings. Use an expanding bindparam in SQLAlchemy (one placeholder per element), id = ANY(%s) with a list in psycopg2, or generate one ? per element for sqlite3.^{[6], [3], [5]}

Framework mappings

CWE reference for the weakness described in this guide: CWE-89
OWASP Top 10:2025 — A05:2025 Injection

Step-by-Step Attack Flow

Step 1

Request value reaches the data layer

A request parameter reaches get_user() as text. A Flask view can read request.args["id"]; a Django view reads request.GET["id"]. The view passes that unvalidated text to get_user().^{[11], [12], [9]}

Step 2

An f-string splices it into the SQL

f"... WHERE id = {user_id}" produces one flat string. With the benign test value 0 OR 1=1, the text becomes WHERE id = 0 OR 1=1, and nothing marks where the value was meant to end.^{[10], [1], [9]}

Step 3

The driver receives no parameters

execute(query) is called with the SQL alone, so psycopg2 sends the text unchanged and PostgreSQL parses OR 1=1 as part of the WHERE clause, not as data.^{[3], [1], [9]}

Step 4

The endpoint returns every row

The condition is true for every row, so a single-user lookup returns the whole table. psycopg2 also accepts several statements separated by semicolons in one call; sqlite3 rejects them, which limits the damage but does not prevent data theft.^{[13], [5], [1], [9]}

Source Code: Flaw vs. Secure Implementation

UNPATCHED FLAW

# repository.py: every query below splices request data into the SQL text
import sqlite3

from sqlalchemy import text


def get_user(pg_cursor, user_id):
    # psycopg2: the f-string builds the statement before the driver sees it
    pg_cursor.execute(f"SELECT id, email FROM users WHERE id = {user_id}")
    return pg_cursor.fetchone()


def find_orders(conn: sqlite3.Connection, status):
    # sqlite3: this % is Python string formatting, not a driver placeholder
    return conn.execute("SELECT id FROM orders WHERE status = '%s'" % status).fetchall()


def find_by_email(session, email):
    # SQLAlchemy: text() does not escape what is already inside the string
    return session.execute(text(f"SELECT id FROM users WHERE email = '{email}'")).all()


def find_users(User, username):
    # Django: raw() sends interpolated values to the database verbatim
    return User.objects.raw(f"SELECT * FROM app_user WHERE username = '{username}'")


def get_orders_by_ids(session, ids):
    # IN list: joined strings let one element carry extra SQL
    id_list = ", ".join(ids)
    return session.execute(text(f"SELECT id FROM orders WHERE id IN ({id_list})")).all()


def list_users(pg_cursor, sort_column):
    # identifier: the sort column comes straight from the query string
    pg_cursor.execute(f"SELECT id, email FROM users ORDER BY {sort_column}")
    return pg_cursor.fetchall()

HARDENED SECURE PATCH

# repository.py: values travel as bound parameters, names come from an allowlist
import sqlite3

from psycopg2 import sql
from sqlalchemy import bindparam, text

SORTABLE_COLUMNS = {"id", "email", "created_at"}


def get_user(pg_cursor, user_id: int):
    # psycopg2: %s is the driver's placeholder; the value goes in the second argument
    pg_cursor.execute("SELECT id, email FROM users WHERE id = %s", (user_id,))
    return pg_cursor.fetchone()


def find_orders(conn: sqlite3.Connection, status: str):
    # sqlite3 uses the qmark style: ? placeholders, values in a tuple
    return conn.execute("SELECT id FROM orders WHERE status = ?", (status,)).fetchall()


def find_by_email(session, email: str):
    # SQLAlchemy: a named bind parameter, value supplied at execute() time
    stmt = text("SELECT id FROM users WHERE email = :email")
    return session.execute(stmt, {"email": email}).all()


def find_users(User, username: str):
    # Django: raw() sends params separately; an ORM filter is simpler still
    return User.objects.raw("SELECT * FROM app_user WHERE username = %s", [username])


def get_orders_by_ids(session, ids: list[int]):
    # IN list: an expanding bind renders one placeholder per element
    stmt = text("SELECT id FROM orders WHERE id IN :ids").bindparams(
        bindparam("ids", expanding=True)
    )
    return session.execute(stmt, {"ids": ids}).all()


def list_users(pg_cursor, sort_column: str):
    # identifier: allowlist first, then quote with sql.Identifier
    if sort_column not in SORTABLE_COLUMNS:
        raise ValueError(f"unsupported sort column: {sort_column!r}")
    query = sql.SQL("SELECT id, email FROM users ORDER BY {}").format(sql.Identifier(sort_column))
    pg_cursor.execute(query)
    return pg_cursor.fetchall()

Engineering & System Hardening Checklist

✓Block any execute(), executemany(), text(), raw() or RawSQL() whose SQL argument is an f-string, a %-formatted string, a str.format() call or a + concatenation. Enable Bandit B608 or Ruff S608 in CI so the build fails on it.^{[8], [14], [1], [9]}
✓Pass every value through the driver's parameter argument using its own paramstyle: %s for psycopg2, ? for sqlite3, :name for SQLAlchemy text().^{[2], [3], [5]}
✓Take table names, column names and sort directions only from a hard-coded allowlist, and quote them with sql.Identifier. Never read them straight from the request.^{[4], [9]}
✓Bind IN lists as one parameter per element (an expanding bindparam, = ANY(%s), or generated ? markers), never as a joined string.^{[6], [3], [5]}
✓In Django, prefer ORM filters. Any raw(), RawSQL or connection.cursor() in a pull request must pass a params argument, and new extra() calls are rejected.^{[7], [8], [9]}
✓Connect with a database role that has only the privileges the application needs (no DDL, no superuser), so an injection that slips through cannot drop tables or read other schemas.^[9]