2. SQLi UNION attacks

Posted Dec 21, 2023 Updated Jan 11, 2024

By Charalampos Spanias 12 min read

SQLi UNION attacks

When an app is vulnerable to SQLi and the results of the query are returned within the app’s responses, we can use the UNION keyword to retrieve data from other tables within the database. This keyword enables us to execute one or more additional SELECT statements and append the results to the original query. For example:

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

For this to work, two key requirements must be met:

The individual queries must return the same number of columns.
The data types in each column must be compatible between the individual queries.

As a result, to carry out an SQLi UNION attack, we should find out:

How many columns are being returned from the original query.
Which columns returned from the original query are of a suitable data type to hold the results from the injected query.

Determining the number of columns required

There are two effective methods to determine the number of columns returned from the original query:

Injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs.
For example, if the injection point is a quoted string within the WHERE clause of the original query:
The column in an ORDER BY clause can be specified by its index, so we don’t need to know the names of any columns. When the specified column index exceeds the number of actual columns in the result set, the database returns an error, such as:
The ORDER BY position number 3 is out of range of the number of items in the select list.
The app might actually return the database error in its HTTP response, but it may also issue a generic error response. In other cases, it may simply return no results at all. Either way, as long as we can detect some differece in the response, we can infer the number of columns.
Submitting a series of UNION SELECT payloads specifying a different number of null values:
If the number of nulls does not match the number of columns, the database returns an error, such as:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
We use NULL as the values returned from the injected SELECT query because the data types in each column must be compatible between the original and the injeceted queries. NULL is convertible to every common data type, so it maximizes the chance that the payload will succeed when the column count is correct. As with the ORDER BY technique, the app might actually return the database error in its HTTP response, but many return a generic error or no results.
When the number of nulls matches the number of columns, the database returns an additional row in the result set, containing null values in each column. The effect on the HTTP response depends on the app’s code. If we are lucky, we will see some additional content within the response, such as an extra row on an HTML table. Otherwise, the null values might trigger a different error, such as a NullPointerException. In the worst case, the response might look the same as a response caused by an incorrect number of nulls.

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

Objective: This lab contains a SQLi 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 SQLi UNION attack that returns an additional row containing null values.

The site provides an option to filter products based on category:
We can test for an SQLi vulnerability by inserting a single quote character:
Our goal is to determine the number of columns returned by the original query, so we can use an ORDER BY clause and increment its index until we get an error:
Since we got an error on the fourth index, we can infer that the number of colums are three and use a UNION attack to solve the lab:

Database-specific syntax

On Oracle, every SELECT query must use the FROM keyword and specify a valid table. There is a built-in table on Oracle called dual which can be used for this purpose. So the injected queries on Orance would need to look like:

The payloads described use the -- to comment out the remainder of the original query following the injection point. On MySQL, the -- must be followed by a space, -- . Alternatively, the # character can be used to identify a comment.

SQLi Cheatsheet

Finding columns with a useful data type

The interesting data that we want to retrieve is usually in string form. This means that we need to find one or more columns in the original query whose data type is, or is compatible with, string data.

After we determine the number of columns, we can probe each one to test whether it can hold string data. We can submit a series of UNION SELECT payloads that place a string value into each column in turn. For example, if the query returnes four columns, we would submit:

If the column data is not compatible with strings, the injected query will cause a database error, such as:

Conversion failed when converting the varchar value 'a' to data type int.

If an error does not occur, and the app’s response contains some additional content including the injected string value, then the relevant column is suitable for retrieving string data.

Lab: SQL injection UNION attack, finding a column containing text

Objective: This lab contains a SQLi 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. To construct such an attack, you first need to determine the number of columns returned by the query. You can do this using a technique you learned in a previous lab. The next step is to identify a column that is compatible with string data. The lab will provide a random value that you need to make appear within the query results. To solve the lab, perform a SQL injection UNION attack that returns an additional row containing the value provided. This technique helps you determine which columns are compatible with string data.

Upon visiting the site, we can see the string that is required to inject for our SQLi attack, i.e., O35Psr:
We can first find out the number of columns that the original query returns using ORDER BY:
Since we got an error at four, we can infer that it returns three columns. Now, we must discover which field is compatible with string data by injecting the specified payload into one of the three columns, until it works:

Using a SQLi UNION attack to retrieve interesting data

When we have determined the number of columns retured from the original query and which columns can hold string data, we are ready to retrieve interesting data. Support that:

The original query returns two columns, both of which can hold strings.
The injection point is a quoted string within the WHERE clause.
The database contains a table called users with the columns username and password.

We can retrieve the contents of users by:

' UNION SELECT username, password FROM users--

In order to perform this attack, you need to know that there is a table called users with two columns called usernames and passwords. Without this info, we would have guess the names of the tables and columns. All modern databases provide ways to examine the database structure.

Lab: SQL injection UNION attack, retrieving data from other tables

Objective: This lab contains a SQLi 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. To construct such an attack, you need to combine some of the techniques you learned in previous labs. The database contains a different table called users, with columns called username and password. To solve the lab, perform a SQLi UNION attack that retrieves all usernames and passwords, and use the information to log in as the administrator user.

Let’s start by testing if this site is indeed vulnerable to an SQLi attack:
Next, let’s determine the number of columns returned from the original query:
Now we know that there are just two columns, let’s see which one is compatible with strings:
It seems that both columns are compatible with string data. Let’s retrieve the required data and log in as administrator:

Retrieving multiple values within a single column

In some cases the query may only return a single column. We can retrieve multiple values together within this single column by concatenating the values together and include a separator to let us distinguish the combined values. For example, on Oracle we could submit the input:

  
' UNION SELECT username || '~' || password FROM users--

This uses the double-pipe sequence, ||, which is a string concatenation operator on Oracle. The injected query concatenates together the values of the username and password fields, separated by the ~ character. The results will look like this:

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

Objective: This lab contains a SQLi 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 SQLi UNION attack that retrieves all usernames and passwords, and use the information to log in as the administrator user.

For the sake of muscle memory, we will repeat the usual process: test for SQLi, find the number of columns returned, and check which columns are compatible with strings:
We have determined that the site is vulnerable to an SQLi attack, the number of columns returned from the original query is two, and only the second column is compatible with strings. Now we can construct our SQLi UNION attack using concatenation:

Examining the database in SQLi attacks

To exploit SQLi vulnerabilities, it’s often necessary to find info about the database, such as:

The type and version of the database software.
The tables and columns that the database contains.

We can potentially identify both the database type and version by injecting provider-specific queries to see what works. The following are some queries to determine the database version for some popular database types:


Database type	Query
Microsoft, MySQL	`SELECT @@version`
Oracle	`SELECT * FROM v$version`
PostgreSQL	`SELECT version()`

For example, we could use a UNION attack with the following input:

' UNION SELECT @@version--

This might return something like this:

Lab: SQL injection attack, querying the database type and version on MySQL and Microsoft

Objective: This lab contains a SQLi vulnerability in the product category filter. You can use a UNION attack to retrieve the results from an injected query. To solve the lab, display the database version string.

Repetition is the mother of learning, so once again we will go through all the steps: test for SQLi, find the number of columns returned, and check which columns are compatible with strings:
Now, we need to find the database version:

Listing the contents of the database

Most database types (except Oracle) have a set of views called the information schema which provides info about the database. For example, we can query information_schema.tables to list the tables in the database:

  
SELECT * FROM information_schema.tables

This returns an output like this:

This output indicates that there are three tables: Products, Users, and Feedback. We can then query information_schema.columns to list the columns in individual tables:

  
SELECT * FROM information_schema.columns WHERE table_name = 'Users'

This returns an output like this:

Lab: SQL injection attack, listing the database contents on non-Oracle databases

Objective: This lab contains a SQLi 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 application has a login function, and the database contains a table that holds usernames and passwords. You need to determine the name of this table and the columns it contains, then retrieve the contents of the table to obtain the username and password of all users. To solve the lab, log in as the administrator user.

First things first: test for SQLi, find the number of columns returned, check which columns are compatible with strings, and find out the database’s version:
We now need to list the table names so we can start enumerating anything of interest:
After some trial and error, we managed to identify a table called users_teymvz. We can check the table’s column names as follows:
Now, all we have to do is display the contents of the table, and log in as administrator to solve the lab: