L30 · SQL injection

Module 5: Web Application Security · ~20 min · Lesson 30 of 71

🎯 What you'll learn

• Why SQL injection happens: code and data glued into one string
• What an attacker can do with it, and the main kinds: union-based, error-based, and blind
• How parameterized queries remove the problem, and why escaping and WAFs don't
• Where SQL injection still hides in a modern stack: ORM escape hatches, Airflow SQL templates, dynamic sorting

🔥 Why this matters

In May 2023, the Cl0p ransomware group exploited a SQL injection flaw (CVE-2023-34362) in MOVEit Transfer, a file-transfer product used by banks, insurers, payroll providers and government agencies. Over the following months, the stolen data was traced to thousands of organizations. The bug class was first described publicly in 1998, and it's still in the OWASP Top 10 (A05:2025, see L29). Even if your services mostly talk to a document store like MongoDB, reporting databases, Airflow jobs and vendor products often speak SQL, and the fix is a habit you can check in every code review.

1. Code and data in one string

A transactions endpoint filters by account. The developer builds the SQL by concatenation:

String sql = "SELECT id, amount FROM txns WHERE account_id = '"
           + accountId + "'";

With accountId = ACC-1001 this works. Now the caller sends ACC-1001' OR '1'='1:

  YOUR CODE                          CALLER'S INPUT
  "... WHERE account_id = '" + acct + "'"
                                     acct = ACC-1001' OR '1'='1
              │ 1. glued into one string
              ▼
  SELECT id, amount FROM txns
   WHERE account_id = 'ACC-1001' OR '1'='1'
                      └───┬────┘ └───┬────┘
                     data (meant)  CODE (injected)
              │ 2. the database parses the whole string
              ▼
  WHERE (account_id = 'ACC-1001') OR (TRUE)
              │ 3. every row matches
              ▼
  every customer's transactions come back

The quote in the input closes the string literal early, and everything after it is parsed as SQL. That's SQL injection: input that changes the structure of a query, not just its values.

The root cause is general. Whenever you build code for another interpreter (SQL, a shell, a Mongo filter, HTML, LDAP) by pasting strings together, a special character in the data can switch the interpreter from "reading data" to "reading instructions". The rest of the injection family (L31, L32) is the same idea in other languages.

2. What an attacker can do

Depending on the query, the database, the driver and the account's privileges:

• Change a filter: ' OR '1'='1 returns rows the caller shouldn't see (a broken access control outcome through an injection cause).
• Bypass a login: a username of admin'-- turns WHERE user = 'admin'--' AND pw = '...' into a query that never checks the password, because -- starts a comment. (A reason login code should never compare passwords in SQL; L18.)
• Read other tables: UNION SELECT appends rows from another table to the results.
• Change or destroy data: stacked queries ('; DROP TABLE txns--) work only when the driver and API allow several statements per call. Many PostgreSQL and SQL Server drivers do; MySQL Connector/J needs allowMultiQueries=true; Python's sqlite3 refuses. Driver behavior varies by version, so never count on it as protection.
• Reach the operating system: some databases can read files or run commands for privileged accounts: PostgreSQL's COPY ... PROGRAM (superusers or members of pg_execute_server_program), or SQL Server's xp_cmdshell when enabled. That's why the database account's privileges matter so much (section 5).

3. Seeing the result, or not

Attackers classify injection by how they get data back out.

  Can the attacker SEE query results or DB errors in the response?
        │
   yes ─┴──────────────────────┐ no
   │                            │
   IN-BAND                      BLIND
   ├─ union-based: UNION        ├─ boolean-based: does the row
   │  SELECT adds rows from     │  count or page change when a
   │  other tables to the       │  condition is true vs false?
   │  normal output             ├─ time-based: make the DB
   └─ error-based: data leaks   │  sleep only when it's true
      inside DB error messages  └─ out-of-band: make the DB
                                   send data out (DNS, HTTP)

Union-based injection works when the response shows query results. The injected SELECT must return the same number of columns with compatible types, so the attacker first probes with UNION SELECT NULL, NULL until the error stops.

Error-based injection uses error messages as the output channel. In PostgreSQL, CAST((SELECT current_user) AS int) fails with invalid input syntax for type integer: "reports_rw": the data is in the error. Returning raw database errors to clients (A10:2025, L38) turns a bug into a data leak.

Blind injection is for when the response shows neither. The attacker asks yes/no questions: "is the first character of the admin hash $?" If the answer changes the row count (boolean-based) or the response time (time-based, with pg_sleep(5) in PostgreSQL, SLEEP(5) in MySQL, or WAITFOR DELAY '0:0:5' in SQL Server), they learn one bit per request. Tools such as sqlmap automate thousands of these requests, so "the endpoint doesn't return anything" is no defense.

Out-of-band injection makes the database itself send data to the attacker, typically via a DNS lookup of a hostname containing the data. Database egress filtering (L12's egress deep dive) blocks it.

4. The fix: parameterized queries

A parameterized query (or prepared statement) sends the SQL text and the values separately. The SQL contains placeholders, and values are bound to them later as bind parameters.

  app                                  database
   │ 1. PREPARE                           │
   │  "SELECT id, amount FROM txns        │
   │   WHERE account_id = $1" ──────────▶ │ parse + plan NOW:
   │                                      │ one comparison,
   │                                      │ one slot ($1)
   │ 2. EXECUTE, $1 = ──────────────────▶ │
   │    "ACC-1001' OR '1'='1"             │ the slot holds a
   │                                      │ string; it's never
   │                                      │ parsed as SQL
   │ ◀──────────── 3. 0 rows ──────────── │ (no account has
   │                                      │  that odd ID)

The query's structure is fixed before the data arrives, so no value can change it. (Some drivers, such as Python's psycopg2, substitute values on the client instead, with correct escaping done by the library. That's still safe; the point is that you never build the SQL from strings.)

Placeholders look different per library: ? in JDBC and sqlite3, %s or %(name)s in psycopg and PyMySQL, :name in JPA, Hibernate and SQLAlchemy. One trap catches many Python developers:

# WRONG: Python % formatting builds the string first
cur.execute("SELECT * FROM txns WHERE account_id = '%s'" % acct)
# RIGHT: the driver binds the value (note the comma, not %)
cur.execute("SELECT * FROM txns WHERE account_id = %s", (acct,))

Why not just escape quotes? Escaping is easy to get wrong (character sets, backslashes, numeric contexts where no quote is needed: WHERE id = 1 OR 1=1). Parameterization makes the whole class of mistake impossible. And a WAF (L12) only matches known patterns; treat it as a speed bump that buys time while you fix the code, never as the fix.

What can't be parameterized? Placeholders hold values, not identifiers or keywords. Table names, column names and ASC/DESC in ORDER BY must come from an allow-list in your code (second deep dive).

5. ORMs, escape hatches, and defense in depth

ORMs such as Hibernate/JPA, Spring Data and SQLAlchemy parameterize for you, which is why SQL injection is rarer in modern code. It survives in the escape hatches, where a developer drops back to a string:

The Airflow row deserves a closer look: Jinja templating is string concatenation with nicer syntax. If a templated value can come from a DAG run's conf, an upstream table or an API, it's an injection point.

Then add layers, so one missed query isn't a breach:

• Least-privilege database accounts (L04): the reporting service gets SELECT on the tables it reads, nothing else. Schema changes run under a separate migration account. A DROP TABLE from a read-only role simply fails.
• Validate input types early: if account IDs match ^ACC-\d{4,10}$, reject anything else at the API layer (or at a gateway such as Kong with a request schema). This is a backstop, not the fix.
• Generic errors to clients: log the database error server-side with a correlation ID; return {"error": "internal_error"} (L38).
• Static analysis (L54): SAST tools flag string-built queries reliably; a CI rule catches the next one.

🧪 Worked example: the transactions endpoint, before and after

The attack, as the request arrives (URL-decoded for reading):

GET /v1/accounts/ACC-1001/transactions?status=POSTED' OR '1'='1
Host: api.example.com
Authorization: Bearer eyJ...      (a valid token for ACC-1001)

Before: the account ID comes from the path and is checked by the authorization layer (L27), but the status filter is concatenated.

// TxnDao.java: VULNERABLE
List<Txn> find(String accountId, String status) throws SQLException {
    String sql = "SELECT id, amount, status FROM txns"
               + " WHERE account_id = '" + accountId + "'"
               + " AND status = '" + status + "'";
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery(sql)) {
        return map(rs);
    }
}

The database receives:

SELECT id, amount, status FROM txns
 WHERE account_id = 'ACC-1001' AND status = 'POSTED' OR '1'='1'

AND binds tighter than OR, so this is (account = ACC-1001 AND status = POSTED) OR TRUE: every transaction for every customer. The authorization check on the path didn't help, because the query itself was rewritten after it.

After: placeholders, plus an allow-list on a field with a small set of values.

// TxnDao.java: FIXED
private static final Set<String> STATUSES =
        Set.of("PENDING", "POSTED", "REVERSED");

List<Txn> find(String accountId, String status) throws SQLException {
    if (!STATUSES.contains(status)) {
        throw new BadRequestException("invalid status");
    }
    String sql = "SELECT id, amount, status FROM txns"
               + " WHERE account_id = ? AND status = ?";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, accountId);
        ps.setString(2, status);
        try (ResultSet rs = ps.executeQuery()) {
            return map(rs);
        }
    }
}

Notice that the SQL string still uses +, but only to join constant pieces. The rule for code review: no variable data may ever be part of the SQL text.

🛠 Try it (optional, ~5 min)

This runs entirely on your machine with Python's built-in sqlite3: an in-memory database, no install, no network. Save it as sqli_demo.py:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE txns (id TEXT, account_id TEXT, amount REAL);
INSERT INTO txns VALUES ('T-1', 'ACC-1001', 120.00),
                        ('T-2', 'ACC-1001', 75.50),
                        ('T-3', 'ACC-2002', 9800.00);
CREATE TABLE users (username TEXT, pw_hash TEXT);
INSERT INTO users VALUES ('admin', '$2b$12$fakehashfakehash');
""")

def unsafe(acct):
    sql = f"SELECT id, amount FROM txns WHERE account_id = '{acct}'"
    return db.execute(sql).fetchall()

def safe(acct):
    sql = "SELECT id, amount FROM txns WHERE account_id = ?"
    return db.execute(sql, (acct,)).fetchall()

tests = ["ACC-1001",
         "ACC-1001' OR '1'='1",
         "x' UNION SELECT username, pw_hash FROM users--"]
for t in tests:
    print(f"input : {t}")
    print(f"unsafe: {unsafe(t)}")
    print(f"safe  : {safe(t)}")
    print()

Real output (Python 3.15 on macOS):

$ python3 sqli_demo.py
input : ACC-1001
unsafe: [('T-1', 120.0), ('T-2', 75.5)]
safe  : [('T-1', 120.0), ('T-2', 75.5)]

input : ACC-1001' OR '1'='1
unsafe: [('T-1', 120.0), ('T-2', 75.5), ('T-3', 9800.0)]
safe  : []

input : x' UNION SELECT username, pw_hash FROM users--
unsafe: [('admin', '$2b$12$fakehashfakehash')]
safe  : []

What to look for: the union payload returned a row from a different table through an endpoint that only queries transactions. Then try boolean-based blind injection yourself:

q = "ACC-1001' AND substr((SELECT pw_hash FROM users),1,1)='{}'--"
print(len(unsafe(q.format("$"))), len(unsafe(q.format("a"))))

You'll get 2 0: two rows when the guess is right, none when it's wrong. Repeat per position and character, and the hash leaks one character at a time. Finally, call unsafe("x'; DROP TABLE txns--"): sqlite3 raises You can only execute one statement at a time., a driver-level protection against stacked queries that other drivers don't give you.

🧠 Key takeaways

• SQL injection happens when input becomes part of the SQL text and changes the query's structure.
• Union and error-based injection read data through the response; blind injection extracts it one yes/no question at a time, so "we return nothing" isn't a defense.
• Parameterized queries are the fix: structure first, values bound separately. Escaping and WAFs aren't.
• ORMs are safe until someone drops to a string: watch raw queries, JPQL concatenation and Airflow Jinja-templated SQL.
• Identifiers and sort directions can't be bound: map them through an allow-list.
• Least-privilege DB accounts and generic errors limit the damage of the one query you missed.

📖 Words to know

⏭ Next time

L31 moves to MongoDB: no SQL, but the same mistake, when {"$ne": null} in a JSON body turns a password check into "any password at all".

Most testing sends a payload and watches the response. Second-order SQL injection breaks that model: the payload is stored safely in step one and does damage later, in a different query, often in a different service.

The textbook case

A partner portal lets users register and later change their password.

 STEP 1: register (parameterized, safe)
   POST /register  {"username": "admin'--", "password": "..."}
   INSERT INTO portal_users (username, pw_hash) VALUES (?, ?)
   → stored exactly as typed:  admin'--
                │
                │ days later, user logs in as admin'-- and
                ▼ changes their password
 STEP 2: change password (string-built, unsafe)
   username comes from the DB row, "so it's trusted"
   UPDATE portal_users SET pw_hash = '<new>'
    WHERE username = 'admin'--' AND pw_hash = '<old>'
                     └──┬──┘ └──────── commented out ───┘
                  the REAL admin's password is changed

The developer of step 2 reasoned that the username "came from our own database". But it was typed by a user; storing it didn't change that.

Where this hides in a data platform

A stack like the one in these examples has a natural second-order path: data written by APIs and read by batch jobs. Imagine an Airflow DAG that builds a per-branch summary:

# nightly_branch_summary.py: VULNERABLE
for (branch,) in src.execute(
        "SELECT DISTINCT branch_code FROM accounts").fetchall():
    dst.execute(f"""
        INSERT INTO branch_summary
        SELECT '{branch}', count(*), sum(balance)
          FROM accounts WHERE branch_code = '{branch}'
    """)

branch_code was written by an onboarding API that validated nothing beyond "is a string". One record with branch_code = BOS' OR '1'='1 corrupts tonight's summary, and a more creative value does worse, under the DAG's database account, which is often more privileged than any API's.

The accidental version shows up first: a customer named O'Brien breaks the job with a syntax error. Treat every "random" SQL syntax error on a quote as a security bug, because it is one.

The fix is the same as always: bind values, whatever their source.

dst.execute("""
    INSERT INTO branch_summary
    SELECT %(b)s, count(*), sum(balance)
      FROM accounts WHERE branch_code = %(b)s
""", {"b": branch})

(Better still, one INSERT ... SELECT ... GROUP BY branch_code needs no loop and no per-branch values at all.)

Why scanners miss it

A DAST scanner (L54) sends the payload to /register and sees a normal response. The damage happens later, somewhere it isn't watching. Finding second-order bugs takes code review and SAST that tracks data from a database read into a query, or a reviewer applying one rule: the trust boundary is the query, not the HTTP edge.

🧠 Extra takeaways

• Data from your own database, a message queue or an upstream DAG is still data: bind it.
• Second-order injection crosses services and time, so black-box scanning rarely finds it.
• Batch jobs often run with powerful DB accounts, which makes their injection bugs worse.
• A syntax error on an apostrophe in a name is a free injection bug report.

REST APIs love ?sort=amount&order=desc. It's also the most common place a careful team still builds SQL from input, because placeholders can only hold values. ORDER BY ? binds a constant string; depending on the database it either errors or silently sorts by that constant, so developers reach for concatenation.

Why ORDER BY injection is dangerous

No quotes are needed, so "escape the quotes" does nothing. And an expression in ORDER BY is a perfect boolean-blind channel:

 GET /v1/transactions?sort=(CASE WHEN (SELECT substr(pw_hash,1,1)
     FROM users WHERE username='admin')='$' THEN amount ELSE id END)

  result sorted by amount  → guess was right
  result sorted by id      → guess was wrong

The response is perfectly normal both times; only the order changes.

The fix: map, don't pass through

The API's sort names are a public contract; the SQL column is an implementation detail. Map one to the other with a fixed table, and reject anything else:

private static final Map<String, String> SORT_COLUMNS = Map.of(
        "date",   "t.booked_at",
        "amount", "t.amount",
        "status", "t.status");

String col = SORT_COLUMNS.get(req.sort());
if (col == null) {
    throw new BadRequestException(
        "sort must be one of " + SORT_COLUMNS.keySet());
}
String dir = "desc".equalsIgnoreCase(req.order()) ? "DESC" : "ASC";

String sql = "SELECT id, amount, booked_at FROM txns t"
           + " WHERE t.account_id = ?"
           + " ORDER BY " + col + " " + dir   // constants only
           + " LIMIT ?";

Every piece of SQL text now comes from a string literal in your code. The user only chooses which literal.

The related cases

You can also enforce the contract at the edge: declare sort as an enum in your OpenAPI spec and validate it in the service (or at a gateway such as Kong with a request-schema plugin, depending on the edition). That rejects garbage early, but keep the allow-list in the code, the one place that can't be routed around.

🧠 Extra takeaways

• Placeholders bind values only; identifiers and keywords must come from your own constants.
• ORDER BY injection needs no quotes and makes a quiet boolean-blind channel.
• Map public sort and field names to columns through a fixed table; reject the rest with a 400.
• For IN lists, generate placeholders; for limits, parse integers and cap them.