How does SQL injection actually work, and how do parameterized queries stop it?

#The core mechanism: query text vs. query data

A SQL query has two fundamentally different kinds of content: the grammar (SELECT, WHERE, AND, table and column names) and the data (the actual values being compared or inserted). SQL injection happens the instant those two get mixed into the same string before the database ever sees them. If a value coming from user input can change where a clause ends, add a new clause, or comment out part of the query, the attacker isn't just supplying data anymore — they're co-authoring the SQL your application runs.

#The vulnerable pattern: string concatenation

# VULNERABLE — user input concatenated directly into SQL text
def find_user(cursor, username, password):
    query = f"""
        SELECT id, username, is_admin
        FROM users
        WHERE username = '{username}' AND password_hash = '{password}'
    """
    cursor.execute(query)
    return cursor.fetchone()

This looks completely reasonable until username contains a single quote. Send username = "admin' --" with any password at all, and the query the database actually executes is:

SELECT id, username, is_admin
FROM users
WHERE username = 'admin' --' AND password_hash = 'anything'

-- starts a SQL comment, so everything after it — including the password check — is discarded before the database even parses it. The attacker is authenticated as admin without ever knowing the password. A different payload, ' OR '1'='1, turns the WHERE clause into a tautology that matches every row, which is exactly as dangerous against a query that wasn't even about login (bulk data exfiltration from any endpoint that filters by user-supplied text).

#The fix: parameterized queries

# FIXED — the query's shape is fixed; input travels as data, never as SQL text
def find_user(cursor, username, password_hash):
    query = """
        SELECT id, username, is_admin
        FROM users
        WHERE username = %s AND password_hash = %s
    """
    cursor.execute(query, (username, password_hash))
    return cursor.fetchone()

The mechanism here isn't "the driver escapes quotes better" — it's structurally different. The database driver sends the query template and the parameter values to the database as two separate pieces over the wire protocol. The database compiles the template — deciding what the grammar is — before it ever sees the values, then binds the values into the already-fixed plan purely as data. A value like admin' -- can never become part of the grammar, no matter what characters it contains, because by the time the database sees it, the grammar has already been decided.

#What parameterization does not cover

Placeholders work for values — things that go where a literal would go. They don't work for identifiers — table names, column names, ORDER BY direction (ASC/DESC) — because those are part of the grammar itself, not a value being compared. If you need to build a query with a dynamic column or table name, you must validate it against a fixed allowlist of known-safe names in your own code; you cannot parameterize your way out of that case.

#Beyond the classic case

🧠 Check your understanding
0/3 · 0/3 answered
1. A login query is built with a Python f-string: WHERE username = '{username}' AND password = '{password}'. An attacker submits username = admin' --. What happens?
The database automatically escapes the quote and treats it as literal textNothing — modern databases block all SQL comments in user inputThe query fails to parse and the login is rejectedThe database comments out the rest of the query including the password check, logging the attacker in as admin
2. Why do parameterized queries prevent SQL injection, structurally, rather than just 'escaping dangerous characters better'?
They run every query in a sandboxed transaction that auto-rolls-back suspicious statementsThey encrypt all query data before sending it to the databaseThe database compiles the query template's grammar before binding parameter values, so a value can never be reinterpreted as part of the SQL grammarThey forbid the use of the WHERE clause entirely
3. Why can't table or column names be supplied through a parameterized placeholder?
Placeholders only work with numeric valuesParameterized queries don't support SELECT statementsIdentifiers are part of the query's grammar, not a value being compared, so they must instead be validated against a fixed allowlist in application codeDatabases automatically reject any query with more than one placeholder