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.
And this time there is a hint.
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
Keep that hint in your pocket, it shapes every payload we write from here on.
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, Lifestyle, Pets, 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. This time we will use the ORDER BY method and just keep incrementing.
' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
ORDER BY sorts the results by a column position, so ORDER BY 1 sorts by the first column, ORDER BY 2 by the second, and so on. The moment we point it at a column that does not exist, the query errors out. We get an error at 3 columns and it behaves normally at 2 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. And here is where the hint kicks in. This is an Oracle database, and every SELECT must have a FROM, so we tack on FROM dual to keep the query legal.
' 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.
Step 4 - Retrieve the List of Tables in the Database#
We will use this payload to retrieve the table names from the database.
' UNION SELECT table_name,NULL FROM all_tables--
In Oracle there is no information_schema. Oracle uses its own set of built-in metadata views called the data dictionary, and all_tables is one of them. all_tables lists every table that the current database user is allowed to see - that is, tables they own plus any tables they have been granted access to. And table_name is the column inside all_tables that holds each table's name, exactly like it did in information_schema.tables. So this payload does the same recon job as the non-Oracle version, it just reads from Oracle's dictionary instead.
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_EGWIUR.
Now we will retrieve the columns from the table. To do this we will use this payload.
' UNION SELECT column_name,NULL FROM all_tab_columns WHERE table_name='USERS_EGWIUR'--
all_tab_columns is the Oracle counterpart to information_schema.columns, and column_name is the column inside it that holds the names of columns, so it lists the column names of the USERS_EGWIUR table. With that we found PASSWORD_JDKTTM and USERNAME_BOSTJB.
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_BOSTJB, PASSWORD_JDKTTM FROM USERS_EGWIUR--
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!