Backend / Security / 02_sql_injection.md

SQL injection

Updated 3 min read source
On this page7
  1. The basic attack
  2. The fix: parameterized queries
  3. What’s still dangerous
  4. ORM safety
  5. Stored procedures
  6. Detection in your stack
  7. Interview angle

SQL injection

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

The basic attack

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

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

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

-- comments out the rest. Login bypassed.

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

sql 
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

python 
# 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

python 
# 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():
python 
from psycopg2 import sql
cursor.execute(
    sql.SQL("SELECT * FROM {} WHERE id = %s").format(sql.Identifier("users")),
    (user_id,)
)

LIKE injection

python 
# 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:

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

IN clauses

python 
# 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:

python 
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.

python 
# 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:

python 
# 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 4

  • 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 OWASP Top 10 (2021), SQLAlchemy.

Previous
OWASP Top 10 (2021)