Skip to main content

🗂️PortSwigger Lab Writeup: SQL Injection UNION Attack - Determining the number of Columns returned by the query

PortSwigger lab description page for SQL Injection – UNION Column Discovery

🎯 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 number of columns returned in the underlying SQL query by performing a UNION-based SQL injection.

💉 Payloads Used​

  • Payload 1 - ❌
Gifts'+UNION+SELECT+NULL--
  • Payload 2 - ❌
Gifts'+UNION+SELECT+NULL,NULL--
  • Payload 3 - ✅
Gifts'+UNION+SELECT+NULL,NULL,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. Browser address bar showing URL with filter parameter in category query string
  • Here, the site filters the products by supplying the category name in URL - /filter?category=...

🚀Step 3: Inject the Payload​

  • Based on the provide info in lab description, we can inject the '+UNION+SELECT+NULL along with the category name to determine the number of columns.
  • We will increase the number of NULL in our payload till we get a correct response as wrong number of NULL will result in error. Error response after injecting SQL UNION payload with incorrect number of columns
  • Hence, we tried our first payload - Gifts'+UNION+SELECT+NULL which did not worked and returned an Internal Server Error.
  • Therefore, we tried second payload with one more NULL - Gifts'+UNION+SELECT+NULL,NULL which also did not worked and returned an Internal Server Error. Error response after injecting SQL UNION payload with incorrect number of columns
  • Now, we will try our third payload with one more NULL - Gifts'+UNION+SELECT+NULL,NULL,NULL. Successful SQL injection using UNION SELECT with three NULLs revealing correct column count
  • And💥Booom!, We got the correct response which means we are able to know the correct number of columns being used in query.
  • 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,NULL,NULL-- in the category parameter, we are able to determine the number of columns returned by the query.
  • Determining the correct number of columns 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.