Skip to main content

🗂️PortSwigger Lab Writeup: SQL Injection UNION Attack - Retrieving Data from Other Tables

PortSwigger lab description page for SQL Injection – Retrieve Data from Other Tables

🎯 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 different table - users

💉 Payloads Used​

1. For determining no of columns :-​

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

2. For extracting usernames and passwords :-​

  • Payload 2 - ✅
Pets'+UNION+SELECT+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. Homepage of the vulnerable shopping website showing product categories
    Login page of the lab application showing username and password fields
  • 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. URL structure displaying the category parameter used in SQL query
  • 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 and both of string datatype, but still we will verify the the no of columns by using payload - Pets'+UNION+SELECT+NULL,NULL-- Successful SQL injection using UNION SELECT NULL,NULL showing column structure
  • Hence, it is confirmed that the application is returning two columns and you can also see that both the columns might be of string datatype because the website have rows containing title and description which is a high indicator of string datatype.
  • Now, we will exploit this functionality by retrieving all the usernames and passwords from a table users as mentioned in the lab description.
  • Based on my observation, the application might be running a SQL query - SELECT title, description FROM products WHERE category = '$category';. So, to exploit this query, we will inject the payload - Pets'+UNION+SELECT+username,password+FROM+users-- which after injection will become - SELECT title, description FROM products WHERE category = 'Pets' UNION SELECT username,password FROM users--'; and after successful execution, it will return all the usernames and passwords. Extracted usernames and passwords from users table using UNION SELECT
  • Hence, it successfully returned all the three usernames and password stored in users table.
  • Now, open the login page in new tab and copy paste the details of administrator to complete the final task of this lab. Login form filled with administrator credentials obtained from SQL injection
    Successful login as administrator after performing 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 Pets'+UNION+SELECT+username,password+FROM+users-- in the category parameter, we are able to retrieve all the usernames and passwords stored in the database.