SQL Injection – Portswigger: SQL injection UNION attack, retrieving multiple values in a single column

Introduction

Ever wondered what secrets lie hidden deep within a website’s database? What if I told you there’s a way to make it confess? That’s the power of SQL Injection, and today, we’re going to act as digital detectives. We’ll use a UNION attack to have a secret conversation with the server, compelling it to reveal its most valuable treasure: the usernames and passwords of its users. Join me as we exploit a live PortSwigger lab, turning a simple URL into a key that unlocks the database’s inner sanctum.

Understanding the UNION Attack

A UNION attack is a type of SQL injection that uses the UNION operator to combine the results of two SELECT statements into a single output. This allows an attacker to retrieve data from other tables in the database that are not part of the original query.

For this attack to be successful, two conditions must be met:

1. Both SELECT queries must return the same number of columns.
2. The data types in each corresponding column must be compatible.

The attacker exploits a vulnerable input field to append their own SELECT query. If the original query returns three columns, the attacker’s query must also return three columns to successfully combine the data. This is how they can trick the database into displaying information from a sensitive table, like the users table, within a seemingly normal web page.

The Role of SELECT and ORDER BY

Before launching the attack, you need to understand the structure of the database query. You can use two key clauses for this:

SELECT Clause

The SELECT clause is a fundamental SQL command used to retrieve data. In a UNION attack, we use it to specify the columns we want to steal. The query will look like this: SELECT username, password FROM users.

To successfully combine our malicious query with the original one, we must first determine the number of columns. We do this by using a SELECT query with NULL values, progressively adding more until the application responds successfully.

Payload example: ' UNION SELECT NULL, NULL, NULL--

ORDER BY Clause

The ORDER BY clause is used to sort the results of a query. We use it in our reconnaissance to discover the number of columns the original query has. We’ll test with different numbers until we get an error. For example, if we test ORDER BY 3 and it works, but ORDER BY 4 throws an error, we know the query has exactly three columns. This is a crucial step to correctly form our UNION attack payload.

Challenge

This lab contains a SQL injection vulnerability in the product category filter. The results from the query are returned in the application’s response, so you can use a UNION attack to retrieve data from other tables. To construct such an attack, you need to combine some of the techniques you learned in previous labs.

The database contains a different table called users, with columns called username and password.

To solve the lab, perform a SQL injection UNION attack that retrieves all usernames and passwords, and use the information to log in as the administrator user.

Walkthrough

Step 1- We begin by examining the category parameter, which the lab documentation highlights as vulnerable. This parameter will serve as our entry point for the SQL injection attack.

Step 2- Next, we need to confirm whether the parameter is truly vulnerable. To do this, we append a single quote (') to the end of the category parameter value. Submitting this modified URL typically causes the server to return an error message. This error serves as clear evidence that the parameter is susceptible to SQL injection.

Step 3- To better understand how the injection works, we can use the SQL comment operator --. This allows us to “cut off” the rest of the query and observe how the backend responds.

For example, the original query executed by the application looks like this:

SELECT * FROM products WHERE category = 'Gifts' AND released = 1

If we append a comment tag after the input, the portion of the query following it will be ignored. Submitting our payload changes the query into something like this:

SELECT * FROM products WHERE category = 'Gifts'-- AND released = 1

Here, the AND released = 1 clause is never executed because it comes after the comment marker. This confirms that we can manipulate the query structure.

Step 4- Next, we’ll determine the number of columns using the

Query- ‘ order by 1 --

This query asks the database to order the results by the first column. Since it executes without error, we can confirm that the query has at least one column.

Step 5- Next, we increase the column number in our ORDER BY query to check if the table has more columns.

Query- ‘ order by 2 --

This query asks the database to order the results by the second column. Since it executes successfully without any error, we confirm that the underlying query has at least two columns.

Step 6- We continue by increasing the column number in the ORDER BY clause to check for a third column.

Query- ‘ order by 3 --

This time, the query returns an error, which means the database query cannot be ordered by a third column. This confirms that the underlying query has exactly two columns.

Note: I’ve detailed two effective techniques for determining the number of columns in a UNION-based SQL injection:

1. UNION SELECT NULL method
2. ORDER BY method

Feel free to refer to my earlier blog post for a comprehensive walkthrough of both methodologies:

Read from here

Step 7- With the number of columns confirmed, we can now attempt to extract sensitive data from the database — specifically the username and password fields from the users table. Query- ' UNION SELECT username, password FROM users--

This query uses the UNION operator to combine the results of the original query with the data from the users table. Since we discovered that the vulnerable query has two columns, our injected query also returns two columns: the username and password.

Step 8- We receive an error message, which does not mean that our query syntax is incorrect, but the conditions in the original query are not matching.

Step 9- When checking the columns, we found that the query has two columns: the first column shows the category, and the second column shows the name of the gift.

We cannot directly put the username or password into the category column because it is meant to display category names. If we try, the query will break or show an error.

The goal now is to find a way to display the username and password in the column that can actually show data, so we can extract it successfully.

Step 10 – Now, we will check if the username can be retrieved in the second column by placing NULL in the first column.

Query- ‘ union select null, username from users -- This makes it easier to understand that NULL is just a placeholder for the first column while we focus on fetching the username in the second column.

Step 11 – We have successfully retrieved the username from the users table.

Step 12 – Now, we will craft a query to fetch both the username and password in a single column. This is the first challenge in this lab.

Query- ' UNION SELECT NULL, username || '**' || password FROM users--

Breakdown of the query

• '
  • This closes the original query’s string inside the vulnerable category parameter.
• UNION SELECT NULL, username || '**' || password
  • The UNION keyword is used to combine the results of our malicious query with the original query.
  • NULL is placed in the first column because the original query has two columns (as we discovered).
  • The second column is replaced with username || '**' || password.
• username || '**' || password
  • The || operator in SQL (for most databases, like Oracle and SQLite) concatenates strings.
  • This means it will join the values of username and password into a single string.
  • The '**' part is a delimiter, so the output looks like:

    admin**secret123
    testuser**qwerty
    

    This makes it easier to distinguish between usernames and passwords.

• FROM users
  • Tells the database to fetch data from the users table (where credentials are stored).
• -
  • Comments out the rest of the original SQL query to prevent syntax errors.

This approach combines the username and password into one column using || '**' || as a separator.

Step 13 – We have successfully retrieved both the username and password from the users table in a single column.

Step 14 – Now, we complete the second challenge by logging in as the administrator.

Step 15 – We have successfully logged in as the administrator and completed the lab.