SQL Injection

A SQL injection vulnerability exists whenever an application builds a SQL statement by concatenating strings that include untrusted input, then sends that combined string to a database engine to be parsed and executed. The core problem is a confusion of channels: the query text is supposed to carry the program's instructions, while the input is supposed to carry inert data. String concatenation merges these two channels into one, so anything the attacker can type becomes something the SQL parser might interpret as syntax — a quote character, a comment marker, a keyword like `UNION` or `OR`. The canonical vulnerable pattern looks like this in pseudocode: ``` query = "SELECT FROM users WHERE username = '" + userInput + "' AND password = '" + passInput + "'" execute(query) ``` If `userInput` is the string `admin' -- `, the resulting query becomes: ``` SELECT FROM users WHERE username = 'admin' -- ' AND password = '...' ``` The `--` begins a SQL comment, so everything after it — including the password check — is discarded by the parser. The attacker is authenticated as `admin` without knowing the password. This is possible because the single quote in the input was not treated as data; it was interpreted by the SQL parser as the end of a string literal, a piece of syntax. The term injection describes this general class of vulnerability: an interpreter (SQL, a shell, an XML parser, a template engine) receives a string that mixes trusted structure with untrusted content, and the untrusted content escapes its intended role as inert data to alter the structure itself. SQL injection is the most famous instance because relational databases are ubiquitous and because a successful attack often yields the entire contents of a database in one step.

An attacker exploiting SQL injection typically follows a discovery-then-exploitation process. First they probe for the vulnerability, then they escalate to extract or manipulate data.

Step 1 — Detect the injection point. Submit a single quote (`'`) into every input field and observe the response. A database error message, a blank page, or an altered result set (compared to normal input) suggests the input reaches a SQL query unescaped.

Step 2 — Determine the query context. The attacker infers whether the input sits inside a quoted string, a numeric comparison, an `ORDER BY` clause, or elsewhere, since the injection payload must be syntactically valid once inserted.

Step 3 — Craft a payload. Common payload families:

• Tautology: `' OR '1'='1` makes a `WHERE` clause always true, returning all rows or bypassing a login check.
• UNION-based extraction: `' UNION SELECT username, password FROM users -- ` appends a second query whose results merge with the first, letting the attacker read arbitrary tables — provided the column count and types match.
• Stacked queries: `'; DROP TABLE users; -- ` (where the database driver allows multiple statements per call) executes an entirely separate destructive command.
• Blind/boolean-based: when no data is echoed back, the attacker asks yes/no questions, e.g. `' AND SUBSTRING(password,1,1)='a` and observes whether the page behaves differently (login succeeds vs. fails, page loads vs. errors).
• Time-based blind: `' AND IF(1=1, SLEEP(5), 0) -- ` — the attacker measures response latency instead of content, useful when the application gives no visible signal at all.

Step 4 — Automate extraction. Because payloads like the boolean-based ones only reveal one bit (or one character) at a time, attackers script a loop that asks about each character of each field, using binary search over the character's ASCII value to make it efficient. Here is the character-by-character blind extraction algorithm an attacker (or a security tester) would run, which is worth understanding precisely because it shows why even a single true/false leak is catastrophic: ``` function blind_extract(query_prefix, max_length, charset_low, charset_high): result = "" for position in 1 to max_length: low = charset_low # e.g. 32 (space) high = charset_high # e.g. 126 (~) found_char = null while low <= high: mid = (low + high) / 2 payload = query_prefix + " AND ASCII(SUBSTRING((" + TARGET_QUERY + "), " + position + ", 1)) > " + mid response = send_request(payload) if response.is_true_branch(): low = mid + 1 else: high = mid found_char = low if found_char == charset_low_sentinel: # no character at this position break result = result + chr(found_char) return result ``` This is a binary search over the ASCII value of each character, so extracting one character of a secret takes about `log2(range)` requests rather than `range` requests — roughly 7 requests per character for the printable ASCII range instead of up to 95. A password hash of length 64 can be exfiltrated in on the order of `64 * 7 ≈ 450` requests, entirely through a single boolean signal (page A vs. page B), with no error messages or visible data ever required.

The defense: parameterized queries

The structural fix separates the query's shape from its data at the API level, so the database parser never re-parses user input as SQL syntax. A parameterized (prepared) statement sends the query template first, gets it compiled/parsed by the database, and only afterward sends the data values, tagged explicitly as data: ``` statement = prepare("SELECT FROM users WHERE username = ? AND password = ?") statement.bind(1, userInput) statement.bind(2, passInput) resultSet = statement.execute() ``` Crucially, `userInput` here is never concatenated into the SQL string at all. The database driver transmits the query template `SELECT FROM users WHERE username = ? AND password = ?` to the server, which parses and plans it — deciding what a "username" comparison means, what type it expects — before it has even seen the actual value. The value is then sent as a separate piece of data bound to that already-fixed slot. Even if `userInput` literally contains the string `' OR '1'='1`, it is placed into the query as the literal string value to compare against the `username` column; it can never be interpreted as `OR`, a keyword, or a comment marker, because the parser has already finished parsing the query's grammar by the time the value arrives.

Attack-side complexity. In-band extraction (error-based or UNION-based) typically retrieves an entire result in `O(1)` to `O(k)` requests, where `k` is the number of rows/columns needed, since the database returns data directly. Blind boolean extraction requires `O(n log c)` requests, where `n` is the length of the secret string and `c` is the size of the character set (the `log c` factor from the binary search above). Time-based blind extraction has the same request complexity but each request costs extra wall-clock time (the injected `SLEEP`), making it slower but not fundamentally different in query count.

Defense-side complexity. Parameterized queries add negligible overhead: one extra round trip on the first use of a query shape (to prepare/compile it), and most drivers and database engines cache prepared statement plans, so repeated executions of the same query template cost the same as an ad-hoc query, `O(1)` additional overhead amortized, sometimes even faster because the query plan is reused rather than reparsed. Space overhead is a small statement handle held by the driver, `O(1)` per distinct query shape.

Correctness argument. The security property we want is: > For all possible values of untrusted input `x`, the set of SQL operations executed depends only on the fixed query template `T`, not on the syntactic content of `x`. Parameterized queries satisfy this because of a two-phase protocol enforced by the database wire protocol itself: 1. Parse phase. The client sends `T` (containing placeholders `?` or named parameters) to the server. The server parses `T` into an abstract syntax tree and produces a query plan. This plan is fixed once parsing completes — it fully determines which tables are scanned, which columns are compared, what operators are applied — and it does not depend on parameter values, because no values have been sent yet. 2. Bind phase. The client sends parameter values, each tagged with its position and (in typed protocols) its type. The server substitutes these values into the already-fixed plan's parameter slots as literal data — the equivalent of loading a constant into a variable, not textually inserting a token into a program. Since step 1 finishes parsing before step 2 begins, there is no possible parser state in which characters from `x` can be interpreted as SQL grammar tokens: the grammar has already been resolved into an AST that has no dependency left on `x`'s contents. This is the same reasoning that protects a printf-style format string when arguments are passed via `%s` rather than interpolated into the format string itself — the interpreter's syntax analysis is complete before the untrusted content is examined.

Why string-escaping alone is weaker. Escaping functions (e.g., doubling quotes, backslash-escaping) attempt to neutralize special characters before concatenation, so the same single-channel string is still built and re-parsed. This is correct only if the escaping function's model of "special character" exactly matches the database's parser for every encoding, character set, and dialect quirk in use — a genuinely large and historically leaky attack surface (multi-byte encodings, alternate comment syntaxes, second-order re-decoding). Escaping is a heuristic patch on a single, mixed channel; parameterization removes the mixed channel entirely, so its correctness does not depend on enumerating attacker tricks.

Consider a vulnerable search feature for a bookstore application: ``` query = "SELECT title, price FROM books WHERE category = '" + userInput + "'" ``` Suppose the normal use sends `userInput = "fiction"`, producing: ``` SELECT title, price FROM books WHERE category = 'fiction' ``` This returns rows normally. Now trace an attack step by step.

Now trace the same request through the corrected, parameterized version: ``` statement = prepare("SELECT title, price FROM books WHERE category = ?") statement.bind(1, userInput) ``` With `userInput = "nonexistent' UNION SELECT username, password FROM accounts -- "`, the driver sends the fixed template `WHERE category = ?` to the server, which is parsed and planned as: compare the `category` column, a `VARCHAR`, against one bound parameter. Only afterward does the driver transmit the parameter value — the entire attacker string, including the quote, `UNION`, and `--` characters — as one opaque `VARCHAR` literal. The executed query is logically: ``` SELECT title, price FROM books WHERE category = "nonexistent' UNION SELECT username, password FROM accounts -- " ``` read as a single 66-character string being compared for equality against every category name. No category matches this bizarre string, so the query correctly returns zero rows. The attacker's syntax was never syntax at all from the database's point of view — it was one long, harmless value.

A second case: second-order injection

Not all injection happens at the moment of input. Suppose a user registration form does use parameterized queries to insert a username safely: ``` insertStmt = prepare("INSERT INTO users (username) VALUES (?)") insertStmt.bind(1, "attacker'; DROP TABLE orders; --") insertStmt.execute() ``` This insert is safe — the malicious string is stored verbatim as a username, exactly as intended. The danger appears later, when a different piece of code reads that stored username back and reuses it, unsafely, to build another query: ``` storedName = lookupUsername(userId) report = "SELECT FROM logs WHERE actor = '" + storedName + "'" execute(report) ``` Here the trust boundary was never at the input form; it is at every* point where a value — regardless of origin — is spliced into a query string. This is called second-order injection, and it is the reason the correct mental model is "parameterize every query that touches variable data," not "sanitize input once when it arrives."

SQL injection was not invented by a single researcher; it emerged as web applications began gluing user-facing forms directly onto relational databases in the mid-1990s. Its formal documentation and popularization, however, is credited to security researchers who published the first widely read technical descriptions of the flaw as web applications proliferated.

Parameterized queries are now baked into virtually every mainstream database access layer: JDBC's `PreparedStatement` in Java, `psycopg2`/`SQLAlchemy` parameter binding in Python, ADO.NET's parameterized commands in C#, and PDO's prepared statements in PHP all provide the bind-then-execute protocol described here as the default or strongly recommended path. Web frameworks (Django, Rails, Laravel, Spring) build their ORMs on top of this mechanism specifically so that ordinary application code rarely constructs raw SQL strings at all. Security testing tools such as automated scanners and manual penetration-testing suites include SQL injection detection as a baseline check, because it remains common in legacy codebases, quickly assembled internal tools, and applications that mix ORM convenience methods with raw-query escape hatches for performance. Web application firewalls attempt to detect injection payloads in transit as a stopgap, but — consistent with the correctness argument above — they are explicitly a compensating control, not a substitute for fixing the query construction code itself. Compliance regimes for payment processing and healthcare data explicitly call out injection flaws as a required area of testing, precisely because a single successful injection can expose an entire backing database in one exploit. The deeper lesson — that a system must strictly separate a trusted control channel from untrusted data, and that this separation must be enforced structurally rather than patched with filters — recurs throughout software security, from command injection and cross-site scripting to deserialization attacks. It is a first, concrete instance of the broader theme explored in Security Engineering and the Human Factor: technical mechanisms fail not because engineers don't understand the theory, but because real systems make the unsafe pattern (string concatenation) easier to write than the safe one, and human habit fills that gap.

Ask the tutor: Can you show how a real ORM like Django or SQLAlchemy turns a `.filter()` call into a parameterized query under the hood?