SQL injection

User input concatenated into SQL gets interpreted as SQL. Old, well-understood, still common.

The basic attack

# Vulnerable
def login(username, password):
    cursor.execute(f"SELECT * FROM users WHERE name = '{username}' AND password = '{password}'")

Attacker submits username = "admin' --". The query becomes:

SELECT * FROM users WHERE name = 'admin' --' AND password = '...'

-- comments out the rest. Login bypassed.

Worse — username = "'; DROP TABLE users; --":

SELECT * FROM users WHERE name = ''; DROP TABLE users; --' AND ...

Most drivers reject multi-statement, but PG and MySQL allow it via certain APIs. Don’t rely on the driver to save you.

The fix: parameterized queries

# psycopg2 / asyncpg / sqlite3 — DBAPI placeholders
cursor.execute(
    "SELECT * FROM users WHERE name = %s AND password = %s",
    (username, password),
)

# SQLAlchemy Core
from sqlalchemy import text
session.execute(
    text("SELECT * FROM users WHERE name = :name AND password = :pw"),
    {"name": username, "pw": password},
)

# SQLAlchemy ORM (always safe by default)
session.query(User).filter(User.name == username).first()

The driver sends the SQL and the values separately. Values can never be parsed as SQL — they’re bound to the %s / :name placeholder positions in the prepared statement.

What’s still dangerous

f-strings or % for any SQL fragment

# Bad — even with placeholders
order = "DESC"  # sometimes "ASC", from request
cursor.execute(f"SELECT * FROM users ORDER BY name {order}")

Placeholders only bind values — column/table names and SQL keywords can’t be parameterized. Your code path lets order = "DESC; DROP TABLE users" through.

For dynamic identifiers:

• Allowlist — if order not in {"ASC", "DESC"}: raise ValueError. Only allowlist works for keywords.
• Identifier escaping — psycopg2.sql.Identifier, PG format():

from psycopg2 import sql
cursor.execute(
    sql.SQL("SELECT * FROM {} WHERE id = %s").format(sql.Identifier("users")),
    (user_id,)
)

LIKE injection

# Bad — user input contains % or _
cursor.execute("SELECT * FROM users WHERE name LIKE %s", (f"%{search}%",))

If search = "%", every row matches. If search = "_", every single-char name matches. Worse if you’re paginating by LIMIT.

Escape:

escaped = search.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
cursor.execute("SELECT * FROM users WHERE name LIKE %s ESCAPE '\\'", (f"%{escaped}%",))

IN clauses

# Bad
ids = [1, 2, 3]
cursor.execute(f"SELECT * FROM users WHERE id IN ({','.join(map(str, ids))})")

# Good — generate placeholders
placeholders = ",".join(["%s"] * len(ids))
cursor.execute(f"SELECT * FROM users WHERE id IN ({placeholders})", ids)

PG also supports = ANY(%s) with a list:

cursor.execute("SELECT * FROM users WHERE id = ANY(%s)", (ids,))

Second-order injection

User input is stored safely (parameterized), then later concatenated into a query.

# Storage: safe
cursor.execute("INSERT INTO users (name) VALUES (%s)", (user_input,))
# user_input = "admin' --"

# Later usage: bug
cursor.execute(f"SELECT * FROM users WHERE name = '{db_name}'")

Always parameterize, even with values you “know” came from the database.

ORM safety

Most ORMs are safe by default for typical operations. Where they break:

# SQLAlchemy — text(), with f-string
session.execute(text(f"SELECT * FROM users WHERE id = {user_id}"))  # vulnerable

# SQLAlchemy — column_property with raw fragment
User.full_name = column_property(text(f"first_name || ' ' || {raw_sql}"))  # vulnerable

# Django — extra(), raw() with %s formatting
User.objects.extra(where=[f"name = '{name}'"])  # vulnerable
User.objects.raw(f"SELECT * FROM users WHERE name = '{name}'")  # vulnerable
# Use:
User.objects.raw("SELECT * FROM users WHERE name = %s", [name])

Look for any text(f"..."), extra(where=[...]), raw("... %s ...") with f-string formatting in the codebase — those are the typical leaks.

Stored procedures

Don’t automatically protect you. A stored proc that builds dynamic SQL inside (EXECUTE IMMEDIATE / sp_executesql) has the same problem if it concatenates input.

Detection in your stack

• Static analysis: bandit -r . flags cursor.execute calls with f-string / % formatting.
• Code review: grep for execute(f", execute("..." %, execute("..." +.
• Runtime: PG’s pg_stat_statements shows the actual queries — review for unexpected dynamic SQL.

Interview angle

• Q: “What’s SQL injection and how do you prevent it?” — input parsed as SQL; parameterized queries.
• Q: “Why isn’t string formatting with placeholders enough?” — %s in cursor.execute(f"... {x}") interpolates before the driver sees it. The placeholder format must be the only formatting on the SQL string.
• Follow-up: “How do you safely use a dynamic table or column name?” — allowlist or use the driver’s identifier-quoting (psycopg2.sql.Identifier).
• Follow-up: “What’s LIKE injection?” — % and _ are wildcards in LIKE; escape them or wrap with allowlist.

See 01_owasp_top_10.md, 08_databases/sql/06_sqlalchemy.md.