The Lab

Here's how PortSwigger describes it

This lab contains a SQL injection vulnerability in the product category filter. You can use a UNION attack to retrieve the results from an injected query. To solve the lab, you have to display the database version string.

PortSwigger also gives us a hint, and it's worth reading before we start

On Oracle databases, every SELECT statement must specify a table to select FROM. If your UNION SELECT attack does not query from a table, you will still need to include the FROM keyword followed by a valid table name.

There is a built-in table on Oracle called dual which you can use for this purpose. For example: UNION SELECT 'abc' FROM dual.

So our goal is simple identify the SQLi endpoint, use a UNION SQL injection, and return the database version

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 and accessories, Food & Drink, Gifts, Toys & Games

Clicking any one of them sends a request like this

/filter?category=Gifts

What we need for a successful UNION injection#

Before touching anything, it helps to keep a few things in mind. to make the UNION injection work we need to

1. Identify the injection point
2. Determine the number of columns being returned by the query
3. 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 because 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. 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. There are two ways to do this

Method 1 - ORDER BY#

We can use ORDER BY and just keep incrementing the number:

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
...

Testing this out, ORDER BY 1 and ORDER BY 2 work out normally

but ORDER BY 3 returns "Internal Server Error". That tells us the query has 2 columns

when the column index you specify goes past the actual number of columns in the result set, the database returns an error. So the moment ORDER BY 3 fails while ORDER BY 2 works, you know exactly where the column count stops

Method 2 - UNION SELECT NULL#

there's another method too submitting a series of UNION SELECT payloads with a different number of NULL values each time

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

If the number of NULLs doesn't match the number of columns, the database returns an error. The payload that doesn't error is the one with the right column count. either method gets you to the same answer

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 version 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'--

And if there were more columns, you'd just keep probing them the same way.

But here's where the Oracle hint comes back in. Remember, on Oracle every SELECT needs a FROM, and Oracle gives us the built-in dual table for exactly this. So we adjust our probes to include FROM dual

'+UNION+SELECT+'x',NULL+FROM+dual--
'+UNION+SELECT+NULL,'x'+FROM+dual--

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

'+UNION+SELECT+'y','x'+FROM+dual--

Both come back fine, so we've got two string columns to work with

Step 4 - Get the database version#

At this point we know the database is Oracle. Now, at first i didn't actually know which query would return the version number either but a quick Google search revealed V$VERSION, which comes straight from the Oracle documentation

V$VERSION has a column called BANNER that holds the version string, so we craft our final query like

'+UNION+SELECT+BANNER,+NULL+FROM+v$version--

with that we get the version back and the lab is solved