PortSwigger Lab: SQL Injection UNION Attack, Determining the Number of Columns Returned by the Query

Difficulty: Practitioner

Target: PortSwigger Web Security Academy

Objective

The objective of this lab is to determine the number of columns returned by the application's original query by performing a SQL injection UNION attack that returns an additional row containing null values.

The Vulnerability Concept

When performing a UNION attack, the injected query must return the exact same number of columns as the original query, and the data types in each column must be compatible. Before extracting any data from external tables, an attacker must systematically figure out how many columns the original SELECT statement contains. This is typically done using either an ORDER BY clause or by injecting successive NULL values.

Step-by-Step Exploitation

1. Reconnaissance & Interception

To start, I accessed the lab interface and reviewed the lab instructions.

Navigating to the main storefront revealed the e-commerce category filtering functionality.

I clicked on the Lifestyle category filter to trigger a request to the vulnerable endpoint.

Using Burp Suite, I monitored the HTTP history tab to examine the incoming and outgoing traffic.

I located the target GET /filter?category=Lifestyle request and sent it to Burp Repeater to begin testing.

2. Determining Column Count via ORDER BY

The first method to find the column count is using the ORDER BY clause, which sorts the result set by the specified column index. If an index greater than the actual number of columns is provided, the database throws an error.

• Test 1: Injecting '+order+by+1-- returned an HTTP/2 200 OK.

• Test 2: Injecting '+order+by+2-- also returned an HTTP/2 200 OK.

• Test 3: Injecting '+order+by+3-- returned an HTTP/2 200 OK.

• Test 4: Injecting '+order+by+4-- triggered an HTTP/2 500 Internal Server Error.

Since column index 4 caused a database error while column 3 succeeded, I confirmed that the original query returns exactly 3 columns.

3. Verifying with UNION SELECT NULL

To confirm the column count and successfully solve the lab, I crafted a UNION payload with three NULL values:'+UNION+SELECT+NULL,+NULL,+NULL--

Injecting this into the category parameter resulted in a successful HTTP/2 200 OK response, appending an extra row of null values to the application output.

4. Verification

Loading the crafted payload in the browser successfully rendered the page without errors and triggered the "Congratulations, you solved the lab!" banner.

Remediation

To fix this vulnerability, parameterized queries (prepared statements) must be used for handling user input within database queries, ensuring that user-controlled values cannot manipulate the query structure or append unauthorized UNION operations.

This write-up is part of my ongoing PortSwigger Web Security Academy lab series. You can find the raw markdown files, payloads, and my full progress tracker in my Github Repository.