logicbreaker

SQL injection UNION attack, determining the number of columns returned by the query

3 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 first step of such an attack is to determine the number of columns that are being returned by the query. You will then use this technique in subsequent labs to construct the full attack. To solve the lab, determine the number of columns returned by the query by performing a SQL injection UNION attack that returns an additional row containing null values.

Looking at the Lab#

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

The categories are All, Corporate gifts, Food & Drink, Gifts, Lifestyle, and Pets.

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 only asks for the first of those two, the column count, so that is where we will stop.

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

The idea is simple. A UNION only works when both queries return the same number of columns, so we keep adding NULLs until the error disappears. NULL is the value of choice here because it is compatible with any data type, so we do not have to worry about column types while we are just counting.

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

zxxxx

And by doing this the lab gets solved automatically, because returning that extra row of nulls that lines up with the original query is exactly what the lab was asking for.

With this, the lab is solved!