SQL Injection/UNION Attack

This page covers UNION attacks, a type of SQL Injection attack.

For coverage of how to carry out this type of attack with Burpsuite, see Burpsuite/SQL Injection#UNION Attacks

Overview

A UNION attack is a type of SQL Injection attack that exploits the ability to run SQL code on a remote server by running cross-table queries to fetch (for example) username/password data from a product page, or to extract information about the database schema.

Example: Retrieving Data from Other Tables

Suppose a web application allows a user to list products by category, and uses the user-provided "category" field to run the following SQL query:

SELECT name, description FROM products WHERE category = 'Gifts'

Now, if the attacker can pass this as a category:

' UNION SELECT username, password FROM users--

and the user input is not sanitized, the query will return all usernames and passwords along with product listings.

Practically speaking, you may need to encode the category above by changing something like /filter?category=Gifts to /filter?category='+UNION+SELECT+username,password+FROM+users--

Determining Number of Columns Returned for an Attack

When performing a UNION attack, you may need to know how many columns are returned from the original query.

There are two ways to do it.

The first way is to submit a series of ORDER BY clauses (order by field 1, order by field 2, etc), increment which field/column index until you get an error:

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

Once the field/column index is too big, the application will return an error. The SQL error may be shown, or may return an error code, or may return no results.

The second way is to submit a series of UNION SELECT payloads, specifying a different number of null values:

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

Same as above - once there are more NULLs than fields, the application will return an error. This method could trigger a different error (null pointer error) than above.

Burp Suite Example

Fire up Burp Suite, switch to the Proxy tab, and open the browser. Log into the Port Swigger training site online.

Here is a simple e-commerce website with a built-in SQL injection vulnerability:

Note the category=xyz, which is the insecure portion of the application - this value is substituted into an SQL query without being sanitized first

We use the UNION SELECT payloads in this case. Trying with 1 or 2 NULL values returns a server error:

But once we try with 3 NULL values, the server successfully renders the page

Determining Column Data Types

The purpose of an SQL injection UNION attack is to retrieve results from an injected query

Since data of interest is typically in string format, this means you have to find one or more columns that are of type string, in order to be able to use a UNION to retrieve string data.

Using the above technique, determine how many columns are returned. Then, modify the SQL query used above to include a simple string, instead of NULL, for each column.

For example, suppose we have 4 columns. Then these four queries would tell you which column has string data:

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

If the column that is being UNIONed with the string is NOT a string, the SQL query will cause an error.

If the column that is being UNIONed with the string IS ALSO a string, then the SQL query will succeed.

Burp Suite Example

Start with the same vulnerable e-commerce application, and still using the un-sanitized "category" variable. Start by repeating the attack shown above, to verify we are still dealing with the same number of columns:

/filter?category='+UNION+SELECT+NULL,NULL,NULL--

confirm that the page renders and does not return any error, indicating we are dealing with 3 columns:

Now we modify the query:

/filter?category='+UNION+SELECT+'a',NULL,NULL--

This returns an internal server error, so the first column is not a string type:

References

Burp suite: https://portswigger.net/web-security/sql-injection/union-attacks