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.

The application has a login function, and the database contains a table that holds usernames and passwords. You need to determine the name of this table and the columns it contains, then retrieve the contents of the table to obtain the username and password of all users. To solve the lab, log in as the administrator user.

Looking at the Lab#

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

The categories are All, Accessories, Clothing/shoes/accessories, Food & Drink, 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.

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 UNION SELECT NULL and just keep incrementing.

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

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

Step 3 - Find Columns with a String Data Type#

Now that we know there are two 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--
' UNION SELECT NULL,'x'--

Doing this, we find that both columns support string data. We can even confirm both at once by dropping a string into each.

Step 4 - Retrieve the List of Tables in the Database#

' UNION SELECT table_name, NULL FROM information_schema.tables--

We will use this payload to retrieve the table names from the database. The explanation for it is as follows.

information_schema is a special, built-in "metadata" database that exists in most relational database systems (MySQL, PostgreSQL, SQL Server, and others). It doesn't store your actual application data it stores information about the database itself, such as what databases exist, what tables they contain, what columns those tables have, data types, and constraints. It's essentially the database describing itself. And table_name is the actual data being pulled it's the column in information_schema.tables that holds each table's name. It sits in the first output column so the page displays those table names to the attacker. The NULL beside it is just filler to match the original query's column count.

And we got every table name from the database. Now note it down, and next we need to figure out which table would contain user data.

Let's go back to the retrieved table response and search for the term "user", and we find the table name users_fqxejo.

Now we will retrieve the columns from the table. To do this we will use this payload.

' UNION SELECT column_name, NULL FROM information_schema.columns WHERE table_name='users_fqxejo'--

We have a little change here from the previous one, so let me explain. column_name is the column in information_schema.columns that holds the names of columns, so it lists the column names of the users_fqxejo table.

So we found our column names username_nevvem and password_xrmydz.

So by now we have all the details we need in order to retrieve the administrator user's password. To retrieve the credentials we will use a very simple payload.

' UNION SELECT username_nevvem, password_xrmydz FROM users_fqxejo--

Imagine this as

' UNION SELECT username, password FROM users--

We successfully retrieved the administrator user's password. Now just log in to the application.

With this, the lab is solved!