When Input Rewrites the Query#

SQL Injection (SQLi) occurs when an application builds a database query by mixing its own SQL with untrusted input, so the input can change the structure of the query rather than just supplying a value. It remains one of the highest-impact web vulnerabilities: depending on the database and the account it runs as, the impact ranges from bypassing a login to reading every table, modifying data, or running commands on the database host. It is the canonical injection flaw in the OWASP Top 10.

Anatomy of the Flaw#

The vulnerability is not "the database"; it is string concatenation. Consider a login check that builds SQL with an f-string:

# VULNERABLE - never build SQL from user input like this
query = (
    "SELECT * FROM users "
    f"WHERE username = '{username}' AND password = '{password}'"
)
db.execute(query)

If someone submits the username admin and a password value of ' OR '1'='1, the assembled query becomes:

SELECT * FROM users WHERE username = 'admin' AND password = '' OR '1'='1'

The trailing OR '1'='1' is always true, so the WHERE clause matches every row and the password check is meaningless. The input escaped its quotes and became part of the query's logic. The same mechanism lets an attacker comment out the rest of a query, append conditions, or (on drivers that allow it) chain a second statement.

You can reproduce the mechanism safely in SQLite to see it with your own eyes:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript(
    "CREATE TABLE users(username TEXT, password TEXT);"
    "INSERT INTO users VALUES('admin','s3cret');"
)

def vulnerable_login(username, password):
    q = ("SELECT * FROM users WHERE username = '%s' AND password = '%s'"
         % (username, password))
    return db.execute(q).fetchall()

print(vulnerable_login("admin", "wrong"))         # [] - no match
print(vulnerable_login("admin", "' OR '1'='1"))   # [('admin', 's3cret')] - bypassed

The second call returns the admin row despite a wrong password. That is the entire bug, demonstrated in a lab you own.

Injection Techniques Beyond Login Bypass#

Login bypass is only the introduction. Attackers extract data with several families of technique, and knowing their names helps you read scanner output and pentest reports:

  • UNION-based: appends UNION SELECT ... to pull rows from other tables (users, tokens, payment records) into the application's normal result set, so the data comes back in the page you already render.
  • Error-based: provokes database error messages that leak table names, column names or values in the error text.
  • Boolean-blind: the response does not contain data, but it differs (a product appears or not, HTTP 200 vs 404) depending on whether an injected condition is true. The attacker reconstructs data one yes/no question at a time.
  • Time-blind: when the response is identical regardless of truth, the attacker injects a delay function so a true condition makes the response take measurably longer, reading data one bit at a time by the clock.
  • Stacked queries: where the driver permits multiple statements per call, a second statement (an update or a drop) is appended after a semicolon. This is the most destructive variant and depends on both the driver and the privileges of the database account.

A subtler variant is second-order (stored) injection: the malicious value is safely stored first (perhaps it was parameterized on the way in) and only becomes dangerous later when some other code path reads it back and concatenates it into a new query. A username like admin'-- might be stored fine, then break a query that builds UPDATE ... WHERE username = '<value>' from the stored string. The lesson is that data is untrusted every time it enters a query, not just when it first arrives from the user, so parameterization must be applied at every query, including those fed from your own database.

The Fix: Parameterized Queries#

The only reliable defence is to stop building query text from input at all. Send the SQL template and the data to the driver separately using placeholders (parameterized queries, also called prepared statements). The database parses the query structure first, then treats each parameter strictly as a literal value that can never change the parse:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript(
    "CREATE TABLE users(username TEXT, password TEXT);"
    "INSERT INTO users VALUES('admin','s3cret');"
)

def safe_login(username, password):
    return db.execute(
        "SELECT * FROM users WHERE username = ? AND password = ?",
        (username, password),          # data, kept separate from the SQL
    ).fetchall()

print(safe_login("admin", "s3cret"))            # [('admin', 's3cret')]
print(safe_login("admin", "' OR '1'='1"))       # [] - treated as a literal string

The injection string now returns no rows, because the driver looks for a password whose literal value is the characters ' OR '1'='1, which no user has. The placeholder style varies by driver: SQLite and many others use ?, psycopg (PostgreSQL) uses %s, and named styles like :username are common. Critically, %s here is a driver placeholder, not Python string formatting; you must pass the values as the second argument, never use Python's % or f-strings to fill them in.

ORMs such as SQLAlchemy, Django's ORM and Prisma parameterize automatically, which is a strong reason to prefer them. But the safety only holds while you use them normally. The moment you drop to raw SQL (session.execute(text("... " + user_input)), Django's .raw(), .extra(), or string-formatted filter fragments) you are back to manual parameterization and back at risk.

Identifiers Cannot Be Parameterized#

There is one gap every developer eventually hits: placeholders bind values, not identifiers. You cannot parameterize a table name, a column name, or the column in an ORDER BY. If a user chooses a sort column, you must map their input through an allowlist of known-good identifiers rather than interpolate the raw string:

ALLOWED_SORT = {"name": "name", "price": "price", "newest": "created_at"}

def list_products(sort_key: str):
    column = ALLOWED_SORT.get(sort_key)
    if column is None:
        raise ValueError("unsupported sort")
    # column is now one of our own trusted strings, not user text
    return db.execute(f"SELECT * FROM products ORDER BY {column} ASC").fetchall()

The user supplies the key, and you look up the actual column name from a dictionary you control. Their string never reaches the SQL. This same pattern handles dynamic LIMIT, direction (ASC/DESC), and optional filters.

Defence in Depth#

Parameterization removes the injection vector; these layers shrink the blast radius if something slips through a dependency or a forgotten raw query:

  • Least privilege for the database account. The application should connect with an account that can only do what it needs. A read path can use a SELECT-only account; almost nothing needs DROP, CREATE, or access to other schemas. Then even a working injection cannot drop tables or read the credentials table.
  • Input validation as a secondary filter. Reject obviously wrong inputs early (an "age" that is not an integer, an ID that is not numeric). This is not a substitute for parameterization, but it narrows the surface.
  • A WAF at the edge. A web application firewall blocks common injection patterns and buys visibility, but it is bypassable and must never be your only control.
  • Monitoring. Log and alert on unexpected UNION, SLEEP/pg_sleep, or comment sequences reaching the database, and on query error spikes. That catches the attempt even when the code is patched.

Testing and Checklist#

Practise on DVWA, OWASP Juice Shop, and the PortSwigger SQL injection labs, which are built to be attacked legally. sqlmap automates detection and exploitation against targets you own or are authorised to test. For your own codebase:

  • Grep for query construction with +, %, .format(, and f-strings adjacent to execute, cursor, query, or raw.
  • Confirm every dynamic query uses placeholders and passes values separately.
  • Confirm every dynamic identifier (sort column, table) goes through an allowlist.
  • Confirm the app's database account lacks destructive and cross-schema privileges.
  • Add a test that submits a quote-heavy string to each input and asserts it is stored/returned as a literal, not executed.