Module 05.03

UNION-Based Extraction

Combining result sets to steal hidden data
Rolling Thunder Security · SQL Injection
Concepts

Understanding UNION-Based Injection

When an application displays query results on a page, an attacker can append a UNION SELECT statement to the original query. This merges an entirely separate result set into the output the user sees, allowing data from any table in the database to appear where only product names or usernames were intended.

The critical requirement: the injected UNION SELECT must return the same number of columns as the original query. If the original query selects 2 columns, your UNION must also select exactly 2.

Column Enumeration

Use ORDER BY n with increasing values of n. When n exceeds the column count, the database throws an error, revealing the exact count.

NULL Placeholders

When column types must match, use NULL as a universal placeholder that satisfies any data type requirement.

Schema Discovery

Query information_schema.tables and information_schema.columns to discover every table and column in the database.

Data Extraction

Once you know the column count and table names, use UNION to pull sensitive data (passwords, credit cards, salaries) through the visible output.

The Attack Pattern

Step 1: Find the column count with ' ORDER BY 1--, incrementing until an error occurs.

Step 2: Confirm injection with ' UNION SELECT NULL, NULL-- (matching the column count).

Step 3: Discover tables via ' UNION SELECT table_name, NULL FROM information_schema.tables--.

Step 4: Extract the data you want from the discovered tables.

Lab 1 of 4
Column Counting
Unsolved
Scenario: Acme Corp has a product search page. It queries the database for name and price columns. Your goal: determine how many columns the query returns using ORDER BY.
SELECT name, price FROM products WHERE name LIKE '%[YOUR INPUT]%'
Search Products
Try: ' ORDER BY 1-- (works), then ' ORDER BY 2-- (works), then ' ORDER BY 3-- (error!). The error on 3 tells you there are 2 columns.
Lab 1 Complete — The query has 2 columns!
Lab 2 of 4
Your First UNION
Unsolved
Scenario: Now that you know the query has 2 columns, use UNION SELECT to inject your own row into the results. Make an injected row appear in the product listing.
SELECT name, price FROM products WHERE name LIKE '%[YOUR INPUT]%'
Search Products
Try: ' UNION SELECT 'injected', 'data'--. The first value appears in the name column, the second in the price column.
Lab 2 Complete — You injected your own data into the results!
Lab 3 of 4
Stealing User Credentials
Unsolved
Scenario: The real danger of UNION injection: extracting data from other tables. Use the product search to steal usernames and passwords from the users table.
SELECT name, price FROM products WHERE name LIKE '%[YOUR INPUT]%'
Search Products
Try: ' UNION SELECT username, password FROM users--. The usernames appear in the name column, passwords in the price column.
Lab 3 Complete — You extracted every user credential through a product search!
Lab 4 of 4
Schema Discovery
Unsolved
Scenario: In a real attack, you would not know what tables exist. Use information_schema.tables to discover all tables in the database, then explore their columns.
SELECT name, price FROM products WHERE name LIKE '%[YOUR INPUT]%'
Search Products
Discover tables: ' UNION SELECT table_name, 'x' FROM information_schema.tables--
Then discover columns: ' UNION SELECT column_name, 'x' FROM information_schema.columns WHERE table_name='users'--
Lab 4 Complete — You mapped the entire database schema!