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:
- Both
SELECTqueries must return the same number of columns. - 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 – According to the lab instructions, our goal is to extract the username and password from the users table. Before we can do that, we must identify how many columns are returned by the original query. This is important because a UNION-based SQL injection will only succeed if the number of columns in both queries match.
To test this, we use a simple UNION query with NULL values. Each NULL represents one column. If the number of columns doesn’t match, the database will throw an error.
For example, we start with:
' UNION SELECT NULL--
If this returns an error, it means there is more than one column. We then progressively add more NULL values until the query executes successfully.
Step 5- As expected, the application returns an error when we test with a single column. This confirms that the underlying query involves more than one column, and we’ll need to continue testing with additional NULL values until we find the correct count.
Step 6- To continue testing, we add a second NULL value to check whether the query contains two columns.
Our payload becomes: ' UNION SELECT NULL, NULL--
If this executes without error, it means the query structure has exactly two columns. Otherwise, we’ll need to keep increasing the number of NULL values until it matches.
Step 7 – This time, our payload executed successfully, which means the query has exactly two columns. Now that we know the column count, we can move forward and craft our UNION queries properly to extract sensitive information.
CASE 2
Step 8 – Another way to determine the number of columns is by using the ORDER BY clause.
Query 1: ' ORDER BY 1--
This checks whether the query can be ordered by the first column. Since we already know the query has two columns, this test succeeds without an error.
Step 9- Next, we increase the column index in our ORDER BY test to further confirm the number of columns.
Query 2: ' ORDER BY 2--
This checks whether the query can be ordered by the second column. Since the query executes successfully, it confirms that the table has two columns.
Now that we know the column structure, we’re ready to identify which columns can display string data and craft our UNION payload to extract sensitive information.
Step 10- We continue increasing the column index in our ORDER BY test to pinpoint the exact number of columns.
Query 3: ' ORDER BY 3 --
This query throws an error, confirming that the table does not have a third column. From this, we can clearly conclude that the query has exactly two columns.
With the column count now verified, we are ready to test which columns can display string data, which is the next step before crafting our UNION SELECT payload.
Step 11- Now that we understand the query structure and column count, it’s time to extract the usernames and passwords. We craft a UNION query to retrieve this sensitive information from the users table.
Query: UNION SELECT username, password FROM users--
After executing this query, we successfully retrieve the username and password of the administrator.
Step 12- With the administrator credentials now in hand, we can proceed to log in as the administrator. This confirms that the extracted data is valid and that our SQL injection attack was successful.
Step 13- Finally, we successfully log in as the administrator. This confirms that our SQL injection attack worked as intended, and the lab challenge is now fully solved.
