logicbreaker

SQL injection UNION attack, retrieving multiple values in a single column

4 min read Easy PortSwigger
SQL injection
#SQL injection
Contents

On this page

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 database contains a different table called users, with columns called username and password. To solve the lab, perform a SQL injection UNION attack that retrieves all usernames and passwords, and use the information to log in as the administrator user.

At a glance this looks identical to the previous lab, but there is a catch that changes everything this time only one of the returned columns can hold text. We are told the data lives in two columns, username and password, but we only have a single usable slot to print them in. So the whole lab comes down to one question how do we squeeze two values into one column.

Looking at the Lab#

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

The categories are the usual

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.

SQL
' 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 values we want to pull out are text.

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

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

And here is the twist that makes this lab different. The first payload throws an error, but the second one works. That tells us the first column cannot hold text, and only the second column accepts string data.

That is a problem. We have two values to steal the username and the password but only one column that can actually display text. Putting one value in each column is off the table. We need another way.

Step 4 - Retrieve Both Values in a Single Column#

The fix is string concatenation. Instead of handing each value its own column, we glue both of them together into one string and drop that into the single text column we have.

SQL
' UNION SELECT NULL,username||'~'||password FROM users--

Let's break this payload down piece by piece.

  • NULL - this sits in the first column. We already proved that column cannot hold text, so we park a NULL there purely as filler to satisfy the "same number of columns" rule. It carries no data, it just keeps the column counts matched.
  • || - this is the string concatenation operator It takes the string on its left and the string on its right and joins them into one longer string.
  • username||'~'||password - reading left to right, this takes the username, sticks a ~ on the end of it, then sticks the password on the end of that. For a row where the username is administrator and the password is s3cr3t, the database builds the single string administrator~s3cr3t.
  • '~' - the tilde is just a separator, a delimiter we choose so that when we read the output we can clearly see where the username ends and the password begins. Any character that will not appear inside the actual data works fine here, people often use ~, |, or :.

So the whole trick is one column, two values, held together by a separator

dildo

Find the administrator's entry, take those credentials, and log in to the application.

ssssss

With this, the lab is solved!