PY-L8-31 · A03 — SQL Injection: The Defence

Learning Goals

3 min

By the end of this lesson you can:

Write parameterised queries (positional ? and named) — the real fix.
Explain why parameterisation makes injection impossible (code/data separation).
Handle the cases parameters can't cover (table/column names) with allow-listing.
Add defence in depth: ORMs, least-privilege DB users, input validation.

Warm-Up · Separate Code From Data

5 min

Last lesson the bug was input becoming code. The fix removes that possibility at the source: you give the database the query (with placeholders) and the values separately. The database treats the values as pure data — never as SQL — no matter what they contain.

Today's big idea

Parameterised (a.k.a. prepared) queries are the fix — not escaping, not blacklisting bad words, not regex-filtering. With a parameterised query, admin'-- is just a username that happens to contain a quote and a dash; the database searches for a user literally named admin'-- (finds none) and moves on. The dangerous characters lose all power because they're never in the code context. Make this your unbreakable habit and SQLi simply cannot happen.

New Concept · Parameterisation & Defence in Depth

14 min

The fix: parameterised queries

import sqlite3
conn = sqlite3.connect("demo.db")

# ✓ POSITIONAL parameters: ? placeholders, values in a tuple
conn.execute("SELECT * FROM users WHERE name = ? AND pw = ?",
             (username, password))      # values sent SEPARATELY as data

# ✓ NAMED parameters: :name placeholders, values in a dict
conn.execute("SELECT * FROM users WHERE name = :n", {"n": username})

The placeholder (? in sqlite/most drivers, %s in psycopg2/mysql — driver-specific) marks where data goes; the driver sends the SQL and the data on separate channels. Never format the value into the string yourself.

Why this is bulletproof

VULNERABLE  f"...name = '{username}'"   → input parsed as part of the SQL
PARAMETERISED  "...name = ?", (username,)  → input is bound as a DATA VALUE
With ?, username = "admin'--" is searched for LITERALLY (a 8-char name).
The quote and dashes are just characters in a string — never SQL syntax.

Why not just escape or filter?

Escaping (doubling quotes) and blacklisting ("reject inputs containing OR/UNION/--") are fragile: encodings, edge cases, and new payloads slip through, and you'll block legitimate input (someone named O'Brien). Parameterisation isn't a filter — it removes the code/data confusion entirely, so there's nothing to filter. Always prefer it.

The case parameters can't cover: identifiers

# parameters bind VALUES, not table/column names or keywords.
# This does NOT work (and you must NOT f-string it either):
# conn.execute("SELECT * FROM ? ", (table,))           ✗ wrong / unsafe

# When you must vary an identifier, ALLOW-LIST it:
ALLOWED_SORT = {"name", "created", "price"}            # known-good columns
def search(sort_by: str):
    if sort_by not in ALLOWED_SORT:                    # reject anything else
        raise ValueError("invalid sort column")
    # safe: the value came from a fixed allow-list, not user text
    return conn.execute(f"SELECT * FROM items ORDER BY {sort_by}")

You can't parameterise a column/table name. The safe pattern is an allow-list: map user choices to a fixed set of known-good identifiers and reject anything else — never interpolate raw user text as an identifier.

Defence in depth (belt and braces)

Use an ORM (SQLAlchemy, Django ORM) — they parameterise by default, so you rarely write raw SQL.
Least-privilege DB user — the app's account can't DROP tables or read system tables, limiting damage if something slips.
Input validation — validate types/ranges at the boundary (a defence-in-depth layer, not a replacement for parameterisation).
Error handling — don't leak SQL errors to users (they aid attackers — Lesson 34).

Worked Example · Fix the Vulnerable Login

12 min

Goal: take Lesson 30's exploitable demo and fix it with parameterised queries — then re-run the same attack and watch it fail completely.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (name TEXT, pw TEXT, role TEXT)")
db.executemany("INSERT INTO users VALUES (?, ?, ?)", [
    ("admin", "super-secret-pw", "admin"),
    ("aisha", "aisha-pw", "user"),
])

# ✗ the OLD vulnerable login (for comparison)
def vulnerable_login(u, p):
    return db.execute(
        f"SELECT name, role FROM users WHERE name='{u}' AND pw='{p}'").fetchone()

# ✓ the FIXED login — parameterised
def secure_login(u, p):
    return db.execute(
        "SELECT name, role FROM users WHERE name = ? AND pw = ?",
        (u, p)).fetchone()          # u, p are DATA — never SQL

# replay the SAME attack from Lesson 30:
print("attack on VULNERABLE:", vulnerable_login("admin'--", "anything"))
print("attack on FIXED:     ", secure_login("admin'--", "anything"))
print("legit on FIXED:      ", secure_login("aisha", "aisha-pw"))

attack on VULNERABLE: ('admin', 'admin')   ← still bypassed (string-built)
attack on FIXED:      None                 ← attack FAILS (no such user)
legit on FIXED:       ('aisha', 'user')    ← normal login still works

Read the result

Same attack, opposite outcome. On the fixed route, admin'-- is bound as a value, so the database searches for a user literally named admin'--, finds none, and returns None — the login correctly fails. The quote and dashes that broke the vulnerable query are now just harmless characters in a string. Notice we changed one thing: from an f-string to execute(sql, params). That single habit eliminates the entire SQL-injection class — which is why "always parameterise" is one of the most important rules in web development.

Try It Yourself

13 min

01 🟢 Fix & verify

Take your Lesson 30 vulnerable demo, convert every query to parameterised form, and re-run all your attacks (admin'--, ' OR '1'='1, UNION). Confirm every one now fails while legitimate use still works.

02 🟡 Safe dynamic sorting

Implement a search with a user-chosen sort column using the allow-list pattern. Confirm a valid column sorts correctly and an injection attempt in the sort field is rejected by the allow-list, not interpolated.

Hint

ALLOWED = {"name", "price", "created"}
def search(sort="name"):
    if sort not in ALLOWED:
        raise ValueError("bad sort column")   # reject, don't interpolate raw input
    return db.execute(f"SELECT * FROM items ORDER BY {sort}").fetchall()

03 🔴 Convert to an ORM

Reimplement the user lookup with SQLAlchemy (or Django ORM). Show that the ORM produces parameterised SQL automatically (inspect the generated query) — so you get injection safety "for free" without writing raw SQL.

Hint

# SQLAlchemy core example — note: NO string formatting of user input
from sqlalchemy import create_engine, text
engine = create_engine("sqlite://")
with engine.connect() as c:
    # :u is a bound parameter; SQLAlchemy parameterises it
    c.execute(text("SELECT * FROM users WHERE name = :u"), {"u": username})

Mini-Challenge · Auto-Fix the Detector's Findings

8 min

Take the SQLi detector from Lesson 30 and, for each flagged f-string query, write the parameterised replacement by hand and verify it's safe. Then add least-privilege as defence in depth: create a DB user/role for the app that can't DROP tables or read other schemas, and explain how it limits blast radius even if a query were ever vulnerable.

Show the approach

# 1) Convert each finding:
#    BEFORE:  db.execute(f"SELECT * FROM t WHERE id = {x}")
#    AFTER:   db.execute("SELECT * FROM t WHERE id = ?", (x,))

# 2) Least privilege (Postgres example) — the app account is limited:
#    CREATE USER app_user WITH PASSWORD '...';
#    GRANT SELECT, INSERT, UPDATE ON app_tables TO app_user;
#    -- NO DROP, NO DELETE on system tables, NO superuser.
# Blast radius: even if an injection slipped through, app_user
# cannot drop tables, read pg_shadow, or escalate — damage is capped.

# 3) Defence in depth stack for A03:
#    parameterise (primary) + ORM + least-privilege DB user
#    + input validation + don't leak SQL errors.

Non-negotiables: parameterised rewrites of real findings + a least-privilege DB-user plan with the blast-radius reasoning.

Recap

3 min

The complete defence against SQL injection is parameterised queries: execute(sql_with_placeholders, params) sends code and data on separate channels, so input can never become SQL — admin'-- becomes a harmless literal value. This beats escaping/blacklisting (fragile) because it removes the code/data confusion entirely. Parameters can't bind identifiers (table/column names) — use an allow-list there. Add defence in depth: ORMs (parameterise by default), a least-privilege DB user (caps damage), input validation, and not leaking SQL errors. One habit — always parameterise — eliminates one of the most damaging vulnerability classes in existence.

Vocabulary Card

parameterised query: SQL with placeholders + separately-bound values; input can't become code.
placeholder: The marker (?, %s, :name) where a bound value goes.
allow-list (identifiers): Restricting variable table/column names to a fixed known-good set.
least privilege (DB): An app DB account with only the permissions it needs, to cap damage.

Homework

4 min

Fully remediate your Lesson 30 vulnerable demo with parameterised queries and prove every attack now fails. Apply the allow-list pattern to one dynamic-identifier case. Convert one query to an ORM and confirm it parameterises. Write a before/after note: the one-line change that fixes SQLi, and the defence-in-depth layers you'd add around it.

Sample · SQLi remediation note

The one-line fix:
  BEFORE: db.execute(f"... WHERE name='{u}' AND pw='{p}'")
  AFTER:  db.execute("... WHERE name=? AND pw=?", (u, p))
Result: admin'--, ' OR '1'='1, and UNION attacks all return None /
fail; legit logins still work. The values are bound as DATA, so SQL
syntax inside them is inert.

Dynamic identifier (sort column): used an allow-list
{"name","price","created"}; an injection in the sort field raises
ValueError instead of being interpolated.

ORM: rewrote the lookup in SQLAlchemy with a :u bound param — it
parameterises automatically, so I write no raw SQL.

Defence in depth I'd add: ORM by default, a least-privilege DB user
(no DROP, no system tables), input validation at the boundary, and
generic error pages so SQL errors never leak to users.

Non-negotiables: all attacks failing after parameterisation, the allow-list case, an ORM conversion, and the defence-in-depth layers.

import sqlite3 conn = sqlite3.connect("demo.db") # ✓ POSITIONAL parameters: ? placeholders, values in a tuple conn.execute("SELECT * FROM users WHERE name = ? AND pw = ?", (username, password)) # values sent SEPARATELY as data # ✓ NAMED parameters: :name placeholders, values in a dict conn.execute("SELECT * FROM users WHERE name = :n", {"n": username})

VULNERABLE f"...name = '{username}'" → input parsed as part of the SQL PARAMETERISED "...name = ?", (username,) → input is bound as a DATA VALUE With ?, username = "admin'--" is searched for LITERALLY (a 8-char name). The quote and dashes are just characters in a string — never SQL syntax.

# parameters bind VALUES, not table/column names or keywords. # This does NOT work (and you must NOT f-string it either): # conn.execute("SELECT * FROM ? ", (table,)) ✗ wrong / unsafe # When you must vary an identifier, ALLOW-LIST it: ALLOWED_SORT = {"name", "created", "price"} # known-good columns def search(sort_by: str): if sort_by not in ALLOWED_SORT: # reject anything else raise ValueError("invalid sort column") # safe: the value came from a fixed allow-list, not user text return conn.execute(f"SELECT * FROM items ORDER BY {sort_by}")

import sqlite3 db = sqlite3.connect(":memory:") db.execute("CREATE TABLE users (name TEXT, pw TEXT, role TEXT)") db.executemany("INSERT INTO users VALUES (?, ?, ?)", [ ("admin", "super-secret-pw", "admin"), ("aisha", "aisha-pw", "user"), ]) # ✗ the OLD vulnerable login (for comparison) def vulnerable_login(u, p): return db.execute( f"SELECT name, role FROM users WHERE name='{u}' AND pw='{p}'").fetchone() # ✓ the FIXED login — parameterised def secure_login(u, p): return db.execute( "SELECT name, role FROM users WHERE name = ? AND pw = ?", (u, p)).fetchone() # u, p are DATA — never SQL # replay the SAME attack from Lesson 30: print("attack on VULNERABLE:", vulnerable_login("admin'--", "anything")) print("attack on FIXED: ", secure_login("admin'--", "anything")) print("legit on FIXED: ", secure_login("aisha", "aisha-pw"))

attack on VULNERABLE: ('admin', 'admin') ← still bypassed (string-built) attack on FIXED: None ← attack FAILS (no such user) legit on FIXED: ('aisha', 'user') ← normal login still works

ALLOWED = {"name", "price", "created"} def search(sort="name"): if sort not in ALLOWED: raise ValueError("bad sort column") # reject, don't interpolate raw input return db.execute(f"SELECT * FROM items ORDER BY {sort}").fetchall()

# SQLAlchemy core example — note: NO string formatting of user input from sqlalchemy import create_engine, text engine = create_engine("sqlite://") with engine.connect() as c: # :u is a bound parameter; SQLAlchemy parameterises it c.execute(text("SELECT * FROM users WHERE name = :u"), {"u": username})

# 1) Convert each finding: # BEFORE: db.execute(f"SELECT * FROM t WHERE id = {x}") # AFTER: db.execute("SELECT * FROM t WHERE id = ?", (x,)) # 2) Least privilege (Postgres example) — the app account is limited: # CREATE USER app_user WITH PASSWORD '...'; # GRANT SELECT, INSERT, UPDATE ON app_tables TO app_user; # -- NO DROP, NO DELETE on system tables, NO superuser. # Blast radius: even if an injection slipped through, app_user # cannot drop tables, read pg_shadow, or escalate — damage is capped. # 3) Defence in depth stack for A03: # parameterise (primary) + ORM + least-privilege DB user # + input validation + don't leak SQL errors.