Skip to main content

🗂️PortSwigger Lab Writeup: SQL Injection UNION Attack - Finding a Column Containing Text

PortSwigger lab description page for SQL Injection – Find Column Containing Text

🎯 Objective​

The objective of this lab is to exploit a 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 determine the exact column returning data in text in the underlying SQL query by performing a UNION-based SQL injection.

💉 Payloads Used​

1. For determining no of columns :-​

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

2. For determining column containing text :-​

  • Payload 2 - ❌
Pets'+UNION+SELECT+'WmCXJG',NULL,NULL--
  • Payload 3 - ✅
Pets'+UNION+SELECT+NULL,'WmCXJG',NULL--

🧪 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. SQLi lab instance showing vulnerable shopping site
  • At first glance, the website seems to be a shopping website with an option to filter products on different categories. 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 shown in the URL as user interacts with product categories
  • Here, the site filters the products by supplying the category name in URL - /filter?category=...

🚀Step 3: Inject the Payload​

  • Since this lab is similar to the previous one of determining the number of columns, we can inject the '+UNION+SELECT+NULL,NULL,NULL-- along with the category name to determine the exact number of columns. Injected payload with UNION SELECT NULLs to identify column count in SQL query
  • Now, We know the exact no of columns and will proceed towards determining a column which is returning data in text form.
  • For this, you need to keep trying by placing a text(eg.. 'a') in place of each NULL in the payload and see if the application returns the exact same text in the returned data.
  • Also, In the Lab description it is mentioned that use the provided random value in lab instance for checking which column is returning text data. Therefore, In my lab instance the value is - 'WmCXJG' which I will be using in my payload.
  • So, lets first place the text value in place of first NULL and use the payload - Pets'+UNION+SELECT+'WmCXJG',NULL,NULL--. Server response showing error after injecting payload with string in first column
  • Hence this payload did not worked and returned an Internal Server Error. So, we try another payload - Pets'+UNION+SELECT+NULL,'WmCXJG',NULL-- Successful server response after injecting payload with string in second column
  • And💥Booom!, We got the correct response which means we are able to know the correct column returning data in text form.
  • 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 '+UNION+SELECT+NULL,'WmCXJG',NULL-- in the category parameter, we are able to determine which column was compatible with string data.
  • Determining the correct number of columns and column compatible with string data is a critical step in UNION-based SQL injection attacks. Without this, further exploitation is impossible. This lab builds foundational knowledge for more advanced SQLi exploitation.

🧾 Related CWEs​

CWE IDTitleDescription
CWE-89Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')Application fails to properly sanitize user input before using it in SQL queries.
CWE-20Improper Input ValidationThe app does not validate user input properly, allowing malicious input to influence behavior.
CWE-116Improper Encoding or Escaping of OutputFailure to safely encode input before inserting it into SQL queries.