SQL injection and prepared statements

Overview

In SQL basics: querying a relational database you learned how to ask a database questions with SELECT, WHERE, INSERT, and friends. Those queries are written as text and sent to the database engine, which reads the text, figures out what you want, and runs it.

That last sentence hides the entire problem of this lesson. A SQL query is a program written in text. When your application builds that text by gluing user input directly into it, the user can write part of your program. That is SQL injection (SQLi) — and it remains one of the most damaging and most common vulnerabilities in real software.

The key idea is the separation of structure from data:

• The structure is the shape of the query: which tables, which columns, the WHERE keyword, the comparison operators. This is code — it decides what the database does.
• The data is the values you compare against: a username, a search term, a number. This should be inert — it should never decide what the database does.

When input is concatenated into the query string, those two categories blur together, and data can become code. The cure is a parameterized query, also called a prepared statement: you send the SQL structure with placeholders, then send the data on a separate channel. The database compiles the structure first and only afterward plugs the values into the placeholders as pure data. Input can never be parsed as SQL.

This topic sits at the centre of web security. The next lesson, NoSQL, password storage, and database hardening, builds on it: injection ideas reappear in NoSQL query languages, and the least-privilege and hardening habits introduced here are expanded there.

Why it matters

A single injectable query can expose or destroy an entire database. Because the database usually holds the most valuable assets a company has — credentials, personal data, payment records, intellectual property — SQL injection is consistently ranked among the top web application risks (it has appeared on the OWASP Top Ten for two decades).

What makes SQLi especially serious:

• Impact is broad. One flaw can read every row in every table the application's database user can reach, not just the row the page was supposed to show.
• It enables more than reading. Depending on the database and driver, an attacker may modify or delete data, escalate to other tables via UNION, or run multiple statements at once (stacked queries).
• It is quietly common. Any place that builds a query from input — login forms, search boxes, sort/filter parameters, IDs in URLs, even HTTP headers stored in logs — can be a vector.

There is also a professional reason to understand the exact remediation. If you find SQLi (in a code review, a security assessment, or your own project), saying "add a web application firewall" or "escape the quotes" is wrong and will not close the hole. Knowing that parameterization is the fix lets you both identify the flaw and recommend a credible, complete remediation.

Core concepts

1. Structure vs. data: the root cause

Definition. A SQL query is text that the database parses into a plan. The structure (keywords, table/column names, operators) tells the database what to do; the data (literal values) is what it does it with. SQL injection happens when untrusted input crosses from the data side into the structure side.

How it works internally. When you build a query by string concatenation, the database never sees a boundary between your SQL and the user's input — it receives one flat string and parses the whole thing as code. If the input contains SQL syntax (a quote, OR, ;, --), the parser happily treats it as part of the query.

String-built query (DANGEROUS):

   application SQL  +  user input  ->  one flat string  ->  parser
   "...WHERE name='"   user typed:      "...WHERE name=''     reads BOTH
                       ' OR '1'='1       OR '1'='1'"          as code

The quote in the input "closes" your string literal early,
and everything after it is parsed as SQL structure.

When this matters: anywhere input reaches a query — and that is almost everywhere in a data-driven app. Pitfall: assuming only "obvious" fields like login forms are at risk. Sort orders, page numbers, JSON fields, and re-displayed log data are all common entry points.

Knowledge check (explain in your own words): Why is it accurate to say a SQL query is "code," and why does that make pasting user input into it dangerous?

2. Classic injection techniques

You do not need to be an attacker to recognise these patterns — recognising them is how you spot and fix the bug.

Boolean tautology. Input like ' OR '1'='1 turns a specific WHERE clause into one that is always true, matching every row. Often used to bypass logins.

UNION SELECT (exfiltration). UNION glues the results of a second SELECT onto the first. An attacker can append a UNION SELECT to pull data out of other tables (for example, reading password hashes while querying a product list).

Stacked queries. Some drivers allow several statements separated by ; in one call. Input such as ; DROP TABLE users;-- can then modify or destroy data. The trailing -- comments out the rest of the original query.

Original intended query:
   SELECT * FROM users WHERE name = '<input>'

With input  ' OR '1'='1     -> matches every row (tautology)
With input  ' UNION SELECT pass FROM admins--
                            -> appends a second result set (exfiltration)
With input  '; DROP TABLE users;--
                            -> runs a second statement (stacked)

Pitfall: thinking "my input has no quotes, so I'm safe." Numeric contexts (WHERE id = <input> with no quotes) are injectable too — 1 OR 1=1 needs no quote at all.

Knowledge check (predict the output): Given SELECT * FROM users WHERE name = '<input>', what rows are returned if <input> is ' OR '1'='1? Why?

3. Prepared statements (parameterized queries): the fix

Definition. A prepared statement is a query sent to the database with placeholders (e.g. ? or :name) instead of inline values. The application then binds the actual values to those placeholders through a separate API call.

How it works internally. The database receives and compiles the query structure first, while the placeholders are still empty. The bound values arrive afterward on a separate channel and are inserted into the already-compiled plan strictly as data. Because the structure was fixed before any value was seen, no value can change it. A quote in the data stays a literal quote; OR '1'='1 stays a literal string.

Parameterized query (SAFE):

   Step 1  SQL with holes  ->  database compiles structure
           "...WHERE name = ?"      (plan is fixed here)

   Step 2  bind value      ->  value inserted as pure DATA
           name = "' OR '1'='1"     (treated as one literal string)

   Result: searches for a user literally named  ' OR '1'='1
           (zero rows) instead of matching everything.

When to use: for every query that includes any value influenced by input. When NOT to use it as the only tool: placeholders bind values, not identifiers. You cannot parameterize a table or column name or the ASC/DESC direction — those need an allowlist instead (see the related exercise input-validation-allowlist).

Pitfall: building the query string with concatenation and then calling a "prepared" API. If the value is already glued into the text, the placeholder gave you nothing. The value must travel as a bound parameter, not as part of the SQL text.

Knowledge check (find the bug): A teammate writes cur.execute("SELECT * FROM users WHERE name = '" + name + "'") and says "it's fine, I'm using execute()." What is wrong with that reasoning?

4. Defence-in-depth (and what is NOT a fix)

Parameterization closes the hole. These additional layers limit the blast radius if something else slips:

Pitfall: treating a WAF or escaping as the solution. They can reduce risk but leave the underlying flaw open; attackers routinely bypass pattern matchers.

Knowledge check (concept): Why is "we added a WAF" a mitigation rather than a remediation for SQL injection?

Syntax notes

Placeholders differ slightly by language and driver, but the shape is always the same: SQL text with holes, then values bound separately.

# Python, sqlite3 / many drivers use ? positional placeholders
cur.execute(
    "SELECT id, email FROM users WHERE name = ? AND active = ?",
    (name, True)        # values bound as a tuple, never concatenated
)
#                ^ one placeholder per value, in order

# Named placeholders make order-independent, readable bindings
cur.execute(
    "SELECT id FROM users WHERE name = :name AND age >= :min_age",
    {"name": name, "min_age": 18}
)

Key rules:

• One placeholder per value. Do not put quotes around ? — '?' would be a literal question mark, not a parameter.
• Placeholders bind values only, never table/column names or keywords.
• The values argument is a separate tuple/dict — the value never appears inside the SQL string.

Lesson

SQL injection (SQLi) is a flaw where untrusted input is pasted directly into a database query.

The danger is not the data itself. It is that the input changes the structure of the query, not just the values it operates on. Once an attacker can rewrite the query, they control what the database does.

The bug

# VULNERABLE — input becomes part of the SQL text
q = "SELECT * FROM users WHERE name = '" + name + "'"

Here the user's name is glued straight into the SQL string.

Suppose the input is ' OR '1'='1. The query becomes:

... WHERE name = '' OR '1'='1'

The condition '1'='1' is always true, so the query matches every row.

Other inputs are worse:

• UNION SELECT can pull data out of other tables (exfiltration).
• Stacked queries (multiple statements in one request) can modify or delete data.

The fix: prepared statements

A parameterized query (also called a prepared statement) sends the SQL structure and the data separately. Because the data travels on its own channel, input can never be read as code.

# SAFE — ? is a bound parameter, not concatenated text
cur.execute("SELECT * FROM users WHERE name = ?", (name,))

The ? is a placeholder. The database treats whatever you bind to it strictly as a value.

This is the single correct fix. It is not the same as blacklisting quotes or escaping characters by hand.

Reporting note

When you find SQLi, the remediation is always parameterized queries / prepared statements.

Add least-privilege database accounts and input validation as defence-in-depth — extra layers that limit damage if one control fails.

"We added a WAF" (web application firewall) is mitigation, not a fix. It can reduce risk but does not close the underlying flaw.

Code examples

Below is a complete, runnable Python example using the built-in sqlite3 module (no extra packages). It creates a tiny database, shows the insecure query side by side with the secure one, and demonstrates that the classic ' OR '1'='1 payload fails against the parameterized version.

import sqlite3

def build_demo_db():
    """Create an in-memory database with two users for the demo."""
    conn = sqlite3.connect(":memory:")        # throwaway DB in RAM
    conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, secret TEXT)")
    conn.executemany(
        "INSERT INTO users (name, secret) VALUES (?, ?)",
        [("alice", "alice-token"), ("bob", "bob-token")],
    )
    conn.commit()
    return conn

def login_insecure(conn, name):
    # WARNING: Intentionally vulnerable training example — use only in a local,
    # isolated, authorized lab. Do not deploy.
    # Input is concatenated into the SQL text, so it can change the query structure.
    query = "SELECT name, secret FROM users WHERE name = '" + name + "'"
    return conn.execute(query).fetchall()

def login_secure(conn, name):
    # SAFE: the ? is a bound parameter; 'name' is sent as data, never as code.
    query = "SELECT name, secret FROM users WHERE name = ?"
    return conn.execute(query, (name,)).fetchall()

def main():
    conn = build_demo_db()
    payload = "' OR '1'='1"   # classic tautology injection string

    print("Insecure query with malicious input:")
    print(" ", login_insecure(conn, payload))   # leaks every row

    print("Secure query with the SAME malicious input:")
    print(" ", login_secure(conn, payload))      # finds no such user

    print("Secure query with a legitimate name:")
    print(" ", login_secure(conn, "alice"))       # returns alice only

    conn.close()   # release the connection / resources

if __name__ == "__main__":
    main()

What it does. build_demo_db sets up an in-memory table with two users. login_insecure glues the input into the SQL string; login_secure uses a ? placeholder and binds the value separately. main runs the same attack string through both.

Expected output.

Insecure query with malicious input:
  [('alice', 'alice-token'), ('bob', 'bob-token')]
Secure query with the SAME malicious input:
  []
Secure query with a legitimate name:
  [('alice', 'alice-token')]

The insecure call returns both rows because ' OR '1'='1 rewrote the WHERE clause to always-true. The secure call returns nothing, because the database searched for a user whose name is literally the string ' OR '1'='1 — which does not exist.

Edge cases. The secure version is also correct for ordinary inputs containing quotes (a user genuinely named O'Brien works without breaking the query). Note that sqlite3's execute() runs only a single statement, so stacked-query payloads (;) are rejected outright — but you should still rely on parameterization, not on that driver quirk.

Line by line

Walkthrough of the key behaviour, focused on what the database actually sees.

1. build_demo_db() opens :memory: (a temporary database that disappears on close) and inserts Alice and Bob. Note the inserts themselves use a ? placeholder — good habit even for setup data.

2. payload = "' OR '1'='1" is the untrusted input. It contains a single quote followed by SQL logic.

3. login_insecure(conn, payload) builds:

   SELECT name, secret FROM users WHERE name = '' OR '1'='1'
   

   Trace of the parser's view:

   Fragment	Parsed as	Effect
   name = ''	empty-string comparison	matches no one on its own
   OR '1'='1'	extra boolean condition	always true
   whole WHERE	(false) OR (true)	true for every row

   So the function returns both Alice and Bob, including their secret values. The data became code.

4. login_secure(conn, payload) sends SELECT ... WHERE name = ? first. The database compiles this plan with the placeholder still empty — the structure is now frozen. Then (payload,) is bound as a single value. The database compares name against the literal string ' OR '1'='1. No user has that name, so the result is [].

5. login_secure(conn, "alice") binds the harmless value alice; the frozen plan compares against it and returns Alice's row only.

6. conn.close() releases the connection. In a real app you would also use a try/finally or context manager so the connection is closed even if a query raises.

The decisive difference is timing: in the secure path the query structure is fixed before any user value is seen, so no value can alter it.

Common mistakes

Mistake 1 — "I used the execute() method, so I'm safe."

# WRONG: value is already inside the SQL string before execute() runs
cur.execute("SELECT * FROM users WHERE name = '" + name + "'")

Why it's wrong: the protection comes from binding, not from the function name. Concatenating first defeats it entirely.

# RIGHT: placeholder + separate value
cur.execute("SELECT * FROM users WHERE name = ?", (name,))

How to recognise it: look for +, f-strings, .format(), or % building a SQL string. Any of those around a query is a red flag.

Mistake 2 — Trying to parameterize a column or sort direction.

# WRONG: placeholders bind values, not identifiers — this errors or sorts wrong
cur.execute("SELECT * FROM users ORDER BY ? ?", (column, direction))

Why it's wrong: ? can only stand for a data value. A column name and ASC/DESC are structure.

# RIGHT: validate identifiers against an allowlist, then build safely
ALLOWED = {"name", "id", "created_at"}
if column not in ALLOWED:
    raise ValueError("invalid sort column")
direction = "DESC" if direction.upper() == "DESC" else "ASC"
cur.execute(f"SELECT * FROM users ORDER BY {column} {direction}")

Here concatenation is acceptable only because every possible value was checked against a fixed allowlist first.

Mistake 3 — Escaping quotes by hand instead of parameterizing.

# WRONG: manual escaping is fragile and misses encodings/contexts
safe = name.replace("'", "''")
cur.execute("SELECT * FROM users WHERE name = '" + safe + "'")

Why it's wrong: you will eventually miss a case (numeric context, backslashes, Unicode), and one miss reopens the hole. Use the driver's parameter binding, which handles all of this correctly.

Mistake 4 — Forgetting numeric contexts. WHERE id = " + str(uid) with no quotes is still injectable (1 OR 1=1). Parameterize numbers too.

Debugging tips

Logic error: the secure query returns no rows when you expect some. Check that you passed a sequence of parameters, not a bare string. execute(sql, name) is wrong; it must be execute(sql, (name,)) — note the trailing comma that makes a one-element tuple. A bare string gets iterated character by character.

Runtime error: "Incorrect number of bindings supplied." The count of ? placeholders does not match the number of bound values. Count them on both sides.

Runtime error: "near '?': syntax error" or the placeholder appears literally. You may have quoted the placeholder ('?') or tried to bind an identifier (table/column). Remove the quotes; use an allowlist for identifiers.

Logic error: it still looks injectable in testing. Search the code for string building near queries (+, f-strings, %, .format()). The presence of a placeholder elsewhere does not protect a different concatenated query.

Questions to ask when a query misbehaves:

• Is every user-influenced value passed as a bound parameter, or is any of it inside the SQL text?
• Does the placeholder count equal the value count?
• Am I trying to bind something that is structure (a column, keyword, or direction) rather than data?
• For a suspected injection, does feeding ' OR '1'='1 change the result set? With parameterization it should not.

Memory safety

Security & safety

Authorization and ethics: Only ever test injection against software you own or are explicitly authorized to assess, running locally or in an isolated lab. The example here uses a throwaway in-memory database. Never probe third-party systems.

Threat model (text diagram):

Entry points              Trust boundary            Asset
----------------          ----------------          -----------------
User input  ----------->  | application code |  ->  database
(form, URL, header)       | builds queries   |      (all rows the app
                          |                  |       user can reach)
   ^ untrusted            ^ must keep data           ^ what we protect
     here                   != code here

The defensive goal is to make sure nothing that crosses the trust boundary from "untrusted input" can reach the database as code.

Why the vulnerable example is unsafe: concatenating input lets it alter query structure, exposing every reachable row and potentially allowing modification or deletion.

Secure fix: parameterized queries (shown in the code section). Add as defence-in-depth: a least-privilege database account (read-only where possible, no DROP/ALTER), and input validation/allowlists for any identifier you must place in the query.

How to test the fix (mitigation verification): feed known payloads (' OR '1'='1, ' UNION SELECT ...--, a numeric 1 OR 1=1) and confirm the result set does not change beyond a literal match. A secure query treats the payload as an ordinary value and returns the same (usually empty) result.

Detection and logging: log failed lookups and queries that error on suspicious characters, the source IP/user, and a timestamp — enough to spot probing. Never log secrets: do not write passwords, tokens, session IDs, or full credit-card numbers to logs. Log the fact of an attempt, not the sensitive payload, and use placeholders such as API_KEY=<development-placeholder> in examples and configs.

Real-world uses

Where this shows up. Every data-driven application — web apps, mobile back ends, admin dashboards, internal tools, reporting pipelines — runs SQL built from input. Login forms, search bars, filters, sort controls, pagination, and ID-in-URL lookups are all classic injection surfaces. Major breaches over the years have started with a single injectable parameter, which is why parameterization is a baseline expectation in code review and security audits.

Professional best practices.

Beginner rules (do these always):

• Parameterize every query that contains any input-derived value — including numbers.
• Never build SQL with +, f-strings, %, or .format().
• For table/column/order identifiers, validate against a fixed allowlist, then insert.
• Close connections/cursors reliably (context managers or try/finally).

Advanced habits:

• Run the application's database account with least privilege; separate read and write roles.
• Centralise data access (a repository/DAO layer or query builder/ORM that parameterizes by default) so individual developers cannot accidentally concatenate.
• Add automated tests that fire known injection payloads at endpoints and assert results are unaffected.
• Add monitoring/logging for injection signatures and query errors, without logging sensitive values.
• Treat WAFs and escaping as extra layers only — never as the remediation.

Practice tasks

Beginner 1 — Spot the vulnerability. Given three short query snippets (one concatenated string, one f-string, one parameterized), identify which are injectable and explain in one sentence each why. Concepts: structure vs. data, recognising string building.

Beginner 2 — Convert to safe. Take cur.execute("SELECT * FROM products WHERE name = '" + term + "'") and rewrite it as a parameterized query. Then add a second condition AND in_stock = ? bound to True. Confirm the placeholder count matches the values. Concepts: placeholders, binding multiple values.

Intermediate 1 — Demonstrate the difference. Using the in-memory sqlite3 pattern from the lesson, write a script with lookup_insecure and lookup_secure functions, feed both the input ' OR '1'='1, and print the row counts. Requirement: the insecure version must return all rows and the secure version zero. Input/output: malicious input -> insecure returns N rows, secure returns 0. Concepts: parameterization, mitigation verification. Hint: reuse the build_demo_db shape.

Intermediate 2 — Safe dynamic sorting. Write a function list_users(conn, sort_by, direction) that supports sorting by name, id, or created_at, ascending or descending. Requirement: reject any sort_by not in an allowlist and normalise direction to exactly ASC or DESC before building the query. Constraint: values still go through placeholders; only the validated identifier is concatenated. Concepts: allowlists for identifiers, why placeholders can't bind columns.

Challenge — Build a minimal safe data-access layer. Create a small UserRepo class wrapping a connection with methods find_by_name(name), create(name, email), and search(term, sort_by, direction). Requirements: all value inputs use bound parameters; sort_by/direction use an allowlist; the connection is closed reliably via a context-manager method; and a short docstring on each method states which inputs are validated. Add a tiny test that fires ' OR '1'='1 at find_by_name and asserts an empty result. Concepts: centralised parameterization, allowlists, resource cleanup, mitigation testing. Hint: expose __enter__/__exit__ or a close() method; do not log the payload value.

Summary

• Root cause. SQL queries are code written as text. SQL injection happens when untrusted input is concatenated into the query and crosses from the data side to the structure side, letting input rewrite what the database does.
• Recognise it. Watch for ' OR '1'='1 (tautology), UNION SELECT (exfiltration), and stacked ; statements (modify/delete). Numeric contexts are injectable too — no quote required.
• The fix. Parameterized queries / prepared statements: send SQL with placeholders, bind values separately. The structure is compiled before any value is seen, so input can never become code. This is the complete, correct remediation.
• Key syntax. cur.execute("... WHERE name = ?", (name,)) — one placeholder per value, values in a separate tuple/dict, never quoted, and never used for identifiers.
• Common mistakes. Concatenating then calling execute(); trying to bind a column or sort direction (use an allowlist); hand-escaping quotes; forgetting numeric fields.
• Defence-in-depth, not substitutes. Least-privilege DB accounts, input validation/allowlists, logging of attempts (never logging secrets). WAFs and escaping are mitigations, not fixes.
• Remember: keep data and code on separate channels, parameterize everything with a value, and verify the fix by confirming injection payloads no longer change the result.

Practice with these exercises

• Detect Parameterized vs Concatenated SQL
• Count SQL Parameter Placeholders
• Does the query use a placeholder?
• Is the query built by concatenation?
• Validate a username