UNION-Based Data Extraction

Chapter 4
UNION-Based Data Extraction
Column counts, types, and reading the schema — the workhorse of data theft

Chapter 3 introduced UNION-based SQLi as the most powerful in-band technique. This chapter works it in full, because it's the canonical way an attacker turns "I can inject" into "I have your entire users table." The idea: UNION lets you bolt a second query onto the first, so data from any table the DB account can read appears in the app's normal output. (Defensive framing as always — practise only on targets you own / training labs.)

What UNION Does

UNION SELECT combines the rows of two SELECT statements into one result set. If the app displays the results of its query, an injected UNION SELECT makes your chosen data show up alongside (or instead of) the legitimate rows:

// original query (a product search): SELECT name, price FROM products WHERE name LIKE '%INPUT%' // injected: append rows from the users table into the visible output INPUT = ' UNION SELECT username, password FROM users-- // result list now contains usernames + passwords where products used to be

But UNION has two strict requirements that the attacker must satisfy first, and the early steps of every union attack are about discovering them.

Requirement 1: Matching Column Count

A UNION requires both queries to return the same number of columns, or the database errors. So step one is finding how many columns the original query has. Two standard methods:

ORDER BY probing

Increase the ORDER BY column index until it errors — the last value that works is the column count:

' ORDER BY 1-- // ok ' ORDER BY 2-- // ok ' ORDER BY 3-- // ok ' ORDER BY 4-- // ERROR → there are 3 columns

UNION SELECT NULL probing

Or increase the number of NULLs until the union succeeds (NULL is type-compatible with anything, so it isolates the count question from the type question):

' UNION SELECT NULL-- // error (wrong count) ' UNION SELECT NULL,NULL-- // error ' UNION SELECT NULL,NULL,NULL-- // success → 3 columns

Requirement 2: Compatible Column Types

The columns you inject must be type-compatible with the original's columns in the positions you want to display — specifically, the data you want to read (usually text) must go in a column the app renders as a string. Find a string-friendly column by placing a test marker:

// which columns can hold/display text? put a marker in each position: ' UNION SELECT 'aaa',NULL,NULL-- // does "aaa" appear? → col 1 is text ' UNION SELECT NULL,'aaa',NULL-- // col 2? ' UNION SELECT NULL,NULL,'aaa'-- // col 3?

Whichever marker shows up in the page is a column you can exfiltrate string data through. Now you know the count and a usable column position — you can extract.

Reading the Schema: information_schema

To steal data you need to know what tables and columns exist. SQL databases expose their own structure through a metadata catalogue — most commonly information_schema — which you can query through the union:

// list all table names: ' UNION SELECT table_name, NULL, NULL FROM information_schema.tables-- // list columns of the "users" table: ' UNION SELECT column_name, NULL, NULL FROM information_schema.columns WHERE table_name='users'-- // then extract the actual data: ' UNION SELECT username, password, NULL FROM users--
Concatenate multiple values into one column to beat a single display slot
Often only one column is rendered, but you want several values (username and password). The trick is to concatenate them into that one column with a separator: SELECT username || ':' || password (standard / Postgres / Oracle), CONCAT(username,':',password) (MySQL), or username + ':' + password (SQL Server). One visible column then carries an entire row's worth of stolen data — which is how a single injectable search box dumps a whole credentials table row by row. (Syntax differs per database, which is also how attackers fingerprint which database they're hitting.)

The Full Attack, Start to Finish

  1. Confirm injectable — a lone ' or ' OR '1'='1 changes behaviour (Chapter 2).
  2. Find the column count — ORDER BY n until it errors, or UNION SELECT NULL,... until it works.
  3. Find a displayable text column — place a string marker in each position.
  4. Map the schema — query information_schema for tables and columns.
  5. Extract — UNION SELECT the target columns (concatenated if needed) and read them from the output.
This is exactly what sqlmap automates — minutes from "injectable" to "whole database"
Every step above is mechanical, which is why tools like sqlmap (Chapter 10) perform the entire sequence automatically: detect the injection, determine the column count and types, fingerprint the DBMS, enumerate information_schema, and dump tables — often in minutes, with no manual SQL. The practical consequence: a single union-injectable parameter is not a "theoretical" risk; it is a near-automatic full-database disclosure. And — the recurring point — none of this is possible if the query was parameterized (Chapter 7), because the input never becomes part of the SQL to UNION with.

Hands-On Exercises

Exercise 1

Explain why a UNION attack must first determine the column count, and show both methods (ORDER BY probing and UNION SELECT NULL) for a query that turns out to have 3 columns. Explain why NULL is used in the second method.

📄 View solution
Exercise 2

Given a 3-column product search where only one column is displayed, write the sequence of payloads to: find which column shows text, list the tables, list the users columns, and dump username+password as a single concatenated value. Note the DB-specific concatenation syntax.

📄 View solution
Exercise 3

Walk the full 5-step union attack from "is it injectable?" to "whole table dumped," and explain why each step is mechanical/automatable (sqlmap). Then explain precisely why parameterized queries make the entire sequence impossible.

📄 View solution

Chapter 4 Quick Reference

  • UNION SELECT appends a second query's rows to the first — data from any readable table shows in the app's output
  • Requirement 1 — column count must match: find it via ORDER BY n (until error) or UNION SELECT NULL,NULL,… (until success)
  • NULL is type-compatible with everything → isolates the count question from the type question
  • Requirement 2 — compatible types: place a string marker ('aaa') per position to find a displayable text column
  • Read the schema via information_schema.tables / .columns — list tables, then columns, then extract
  • Concatenate multiple values into one display column (|| / CONCAT / +, DB-specific) to dump a whole row through one slot
  • Full attack: confirm → column count → text column → map schema → extract — all mechanical & automated by sqlmap
  • A single union-injectable parameter ≈ full-database disclosure; impossible under parameterized queries (Chapter 7)
  • Next chapter: blind SQLi in depth — boolean & time-based extraction when there's no visible output