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

Another Example: Order By, and Non-Microsoft SQL Server

MS SQL databases accept -- to start comments, but if it's not an MS SQL database, it may not accept the --. Instead, you need to use a hash sign to terminate the query early.

In this case, the procedure covered above needs to change slightly. Let's also cover how to use ORDER BY instead of SELECT NULL.

We start with the same vulnerable web application with its vulnerable "category" URL parameter:

/filter?category=xyz

Start out by browsing to that page in the Burp proxy. Turn on Intercept Traffic and refresh the page. You should now see the request for the /filter URL, with its "category" value set.

Now right click on this request, and send to repeater. Then you can turn off Intercept Traffic, and switch over to Repeater.

Here's what the un-tampered-with request might look like:

Now we can use the following SQL query in place of the category: ' ORDER BY 1--

(This will try to order the results that are being returned by the /filter URL by the first column. Increment to 2, 3, 4, etc. to discover how many columns the SQL query being attacked is returning.)

This request, when URL-encoded, is easy enough that we probably don't need Burp to do it for us: '+ORDER+BY+1--

However, in some cases, this will still return a 500 error:

In this case, we can try terminating the query string with a hash sign: ' ORDER BY 1 #

The hash sign must be URL-encoded, and Burp Suite will come in handy because it has built-in capabilities to obtain the URL-encoded version of a string.

When in repeater, and modifying the request URL, you can type the SQL query in a way that is NOT URL-encoded, and then select the text, and click Command-U to URL-encode the text.

Before:

After:

Once we send that request, we see the server return a 200 code, meaning we can continue on with our ORDER BY attacks to enumerate columns.

Retrieving Data from Other Columns

Using the same example we've been running with, the vulnerable e-commerce site is running some kind of 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.

Note that we might have deduced this anyway from the nature of the results shown on the /filter page - each product has a name and a description, but no other information, which indicates that the query probably returns two columns of text.

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 not particularly realistic. We just so happened to have a product page that was already running an SQL query that returned two columns of string data, so we could easily UNION the results of that query (two columns of text data) with the information returned from the second part of the UNION query, which retrieves the usernames and passwords (also two columns of text data).

But suppose the products page only returns one string column. What do we do then?

In that case, we can concatenate information from multiple columns into a single column using ||:

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

|| indicates string concatenation, ~ is an arbitrary separator.

As a URL, this looks like:

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

(You can always request the /filter URL in Burp suite with Intercept Traffic on, and send that request to Repeater, where it's a bit easier to craft more complex SQL injection queries.)

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