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.

The basic idea is to use SQL injection to craft UNION queries that look like this:

SELECT a, b FROM table1 UNION SELECT c, d FROM table2

UNION queries require the two tables being UNIONed to match in number of columns and type. Although NULL can be used as a placeholder for any type, it's still important to determine how many columns are returned by an SQL query being injection-attacked, and to find a column with the correct type for the data you are extracting from "table2".

UNION attacks are common anytime an application is filtering what data is being retrieved with a SELECT statement. These types of statements are vulnerable to UNION attacks because an attacker can chain additional queries to the original query using UNION.

Basics of UNION Attacks

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.

Example - Determine Number of Columns

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.

Example - Determine Data Type of Columns

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:

When we try the second column, the web application successfully renders the page, which means the second column returned is a string type:

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

The last column is not a string type either,

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

Retrieving Data from Other Columns

Using the same example we've been running with, the vulnerable e-commerce site is running an SQL query like

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

and using the user-provided category without sanitizing the inputs first, which allows for the SQL injection attack.

We covered above how to check how many columns are returned, and how to check which ones have type string. Now we can combine that with knowledge about other tables to craft a UNION query.

For example, suppose we know (or guess) that account usernames and passwords are stored under the "username" and "password" columns in the "user" table. Then we could craft a UNION query by searching for this category:

' UNION SELECT username, password FROM users--

To make this SQL query into a URL:

/filter?category='+UNION+SELECT+username,password+FROM+users--

Example - Retrieve Data from Other Columns

Start with the same e-commerce web application with the same SQL injection vulnerability in the category variable.

We start by repeating the two SQL injection attacks covered above, to verify that the products page is returning two fields, and that both fields are strings.

Now, we know that the products category page is fetching two string columns, and so we can do a UNION attack and fetch two other string columns. In this case, the username and password columns of the users table.

Craft the SQL injection query:

/filter?category='+UNION+SELECT+username,password+FROM+users--

This will create a query that is the union of usernames/passwords with the (empty) products query:

Retrieving Multiple Values in One Column

The above example was somewhat contrived, because we just so happened to have a products page returning two strings, which we could easily UNION with the usernames/passwords table.

But suppose the products page only returns one string column. In that case, we can concatenate information from multiple columns into a single column using some SQL syntax.

In that case, we can create a custom column result from multiple column names, like so:

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

The double pipe indicates string concatenation, the tilde is an (arbitrary) separator character

As a URL, this looks like:

/filter?category='+UNION+SELECT+NULL,username||"~"||password+FROM+users--

Example - Retrieving Multiple Values in One Column

Examining the Database

Querying the Database Type and Version

Start by identifying a parameter that is vulnerable to SQL injection.

Shortcut method: replace a given parameter with a single quote ' (which will make the SQL query invalid if the app is vulnerable to SQL injection) and see if the application returns an internal application error.

Different SQL servers use different syntax, so keep trying until something works.

Database Type	Version Query	Sample SQL Injection URL
Microsoft:	SELECT @@version	'+UNION+SELECT+@@version
MySQL	SELECT @@version	'+UNION+SELECT+@@version
PostgreSQL	SELECT version()	'+UNION+SELECT+@@version
Oracle	SELECT * FROM v$version	'+UNION+SELECT+BANNER,NULL+FROM+v$version--

References

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