The Lab

Here is how PortSwigger describes it.

This lab contains a SQL injection vulnerability in the product category filter. The results from the query are returned in the application's response, so you can use a UNION attack to retrieve data from other tables. To construct such an attack, you first need to determine the number of columns returned by the query. You can do this using a technique you learned in a previous lab. The next step is to identify a column that is compatible with string data. The lab will provide a random value that you need to make appear within the query results. To solve the lab, perform a SQL injection UNION attack that returns an additional row containing the value provided. This technique helps you determine which columns are compatible with string data.

Looking at the Lab#

When we access the lab, we get the product category filter on the index page.

The categories are All, Clothing/shoes/accessories, Corporate gifts, Lifestyle, Tech gifts, and Toys & Games.

Clicking any one of them sends a request like this.

/filter?category=Gifts

What We Need for a Successful UNION Injection#

Just like the previous labs, before touching anything it helps to keep a few things in mind. To make the UNION injection work we need to do three things.

• Identify the injection point
• Determine the number of columns being returned by the query
• Figure out which columns contain text data, so we can use them to return information as a string

The reason we care about all of this is that a UNION query has two hard requirements.

• The individual queries must return the same number of columns
• The data types in each column must be compatible between the queries

So in practice that normally involves two things - finding out how many columns are being returned from the original query, and finding out which of those columns are of a suitable data type to hold the results from our injected query.

This lab is all about that second part, pinning down a column that can hold text.

Step 1 - Identify the Injection Point#

The injection point here is the filter, so let's start poking at it.

First, let's input a single quote.

/filter?category='

This returns "Internal Server Error".

Now let's input two single quotes instead.

/filter?category=''

This one works. It still doesn't give us any product information, but that's fine - the key takeaway is what's happening under the hood. A single quote (') breaks the query, and two single quotes ('') balance it back out and make it a valid query again.

That behaviour is enough to confirm our input is landing inside a SQL string, so we've found our injection point.

Step 2 - Determine the Number of Columns#

Now we need to know how many columns the original query returns. We can use the UNION SELECT NULL method and just keep incrementing.

' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL,NULL--

We get an error at 4 NULL columns and it behaves normally at 3 NULL columns, so the total number of columns is 3

Step 3 - Find Columns with a String Data Type#

Now that we know there are three columns, we need to figure out which ones can actually hold string data, because the value we want to pull out is text.

We can probe each column by placing a string value into it one at a time, leaving the others as NULL.

' UNION SELECT 'x',NULL,NULL--
' UNION SELECT NULL,'x',NULL--
' UNION SELECT NULL,NULL,'x'--

Whichever position does not throw an error is a column that happily holds text. Doing this, we find that the second column can hold text.

Now the lab hands us a random value to prove the point. The description asks us to make the database retrieve the string nPHFqo, so we just drop that value into the column we found instead of our test 'x'.

' UNION SELECT NULL,'nPHFqo',NULL--

And by doing it the lab is solved, because that random string now shows up right in the application's response.

With this, the lab is solved!