Parameterized Queries — Stop SQL Injection

The single most important rule

Never build SQL by string concatenation or f-strings with user input. Use placeholders and pass parameters separately. The driver handles escaping; SQL injection becomes impossible by construction.

SQLite supports two placeholder styles:

Qmark: ? with a tuple/list. Order-based.
Named: :name with a dict. Self-documenting.

Warning: conn.execute(f'SELECT * FROM users WHERE name = "{name}"') is a security bug. The same pattern in production has been the root cause of countless data breaches. Use placeholders, always — even for 'trusted' input. Trusted is a slippery property.

The one thing placeholders cannot parameterize is identifiers (table names, column names). For those, validate against an allowlist and only then format into the SQL string.

Code

Both placeholder styles, both safe·python

# Qmark — order-based
conn.execute(
    'SELECT * FROM users WHERE email = ? AND archived = ?',
    ('a@x.com', 0),
)

# Named — dict-based, self-documenting
conn.execute(
    'SELECT * FROM users WHERE email = :email AND archived = :archived',
    {'email': 'a@x.com', 'archived': 0},
)

# Multiple rows — same placeholders, executemany
conn.executemany(
    'INSERT INTO users(email, username) VALUES (?, ?)',
    [('a@x.com', 'alice'), ('b@x.com', 'bob')],
)

Identifiers — validate against an allowlist·python

ALLOWED_SORT = {'created_at', 'updated_at', 'id'}

def list_messages(conn, sort_by: str):
    if sort_by not in ALLOWED_SORT:
        raise ValueError(f'invalid sort: {sort_by}')
    # Now safe to format because sort_by is a known constant value
    return conn.execute(
        f'SELECT * FROM messages ORDER BY {sort_by} DESC LIMIT 100'
    ).fetchall()

What a SQL injection attempt looks like — and why placeholders kill it·python

# UNSAFE — never do this
name = "alice'; DROP TABLE users; --"
conn.execute(f"SELECT * FROM users WHERE name = '{name}'")
# Goodbye users.

# SAFE — placeholder treats the whole string as a value
conn.execute('SELECT * FROM users WHERE name = ?', (name,))
# Just returns no rows; the table survives.

Exercise

Build a tiny vulnerable function (string-concatenated SQL) and an exploit input that drops a table or escalates a query. Then rewrite the function with placeholders and confirm the same input is now harmless. Document the diff. Bonus: search a real codebase you have access to for f-string SQL — bet you find at least one.

Parameterized Queries — Stop SQL Injection

The single most important rule

Code

External links

Exercise

Progress

Comments 0