PortSwigger Lab: SQL Injection UNION Attack, Finding a Column Containing Text

Target: PortSwigger Web Security Academy

Objective

The objective of this lab is to execute a SQL injection UNION attack to determine the number of columns returned by the query, identify which of those columns can accept text data, and retrieve a specific random string provided by the lab instructions (in this case, 'isiDps').

The Vulnerability Concept

When performing a UNION attack, two strict rules apply:

1. The injected query must return the exact same number of columns as the original query.
2. The data types in each column must be compatible between the original and injected queries.

If you attempt to inject a string literal (like 'abc') into a column that expects an integer, the database will throw an error. By systematically injecting a string into each column one by one while keeping the others as NULL, an attacker can map out the data types of the underlying table and identify where sensitive text data (like usernames or passwords) can be extracted.

Step-by-Step Exploitation

1. Reconnaissance & Interception

To start, I accessed the lab interface and checked the specific requirements. The lab provided a target string to retrieve: 'isiDps'.

I navigated to the storefront and clicked on the Food & Drink category to apply a filter.

Using Burp Suite, I monitored the HTTP history to locate the GET /filter?category=Food+%26+Drink request.

I sent this request to Burp Repeater for testing.

2. Determining the Column Count

Before finding the string column, I needed to determine the total number of columns using the ORDER BY technique:

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

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

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

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

Because column index 4 caused an error, I confirmed the original query returns exactly 3 columns.

3. Finding the String-Compatible Column

With the column count confirmed as 3, I tested each column sequentially to see which one could hold string data by injecting 'abc' alongside NULL values.

• Testing Column 1: I injected '+UNION+SELECT+'abc',+NULL,+NULL--. The server returned a 500 Internal Server Error, indicating the first column does not accept strings.

• Testing Column 2: I moved the string to the second position and injected '+UNION+SELECT+NULL,+'abc',+NULL--. The server returned a 200 OK, confirming the second column accepts string data.

4. Injecting the Target String

Now that I knew the second column was the correct target, I swapped out my 'abc' test string for the specific string required to solve the lab: 'isiDps'.

The final payload was: '+UNION+SELECT+NULL,+'isiDps',+NULL--

Executing this request successfully reflected the target string into the HTML response.

5. Verification

Rendering this successful response in the browser triggered the "Congratulations, you solved the lab!" banner, confirming the completion of the exercise.

Remediation

To prevent UNION-based SQL injections entirely, developers must use parameterized queries (prepared statements) when handling user input. This ensures that user-supplied data in the category parameter is treated safely as a string literal and cannot modify the structure of the underlying SQL query to append arbitrary 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.