Introduction

In this section I'll cover several topics:

• SQL injection UNION attacks
• Determining the number of columns required in a SQL injection UNION attack
• Finding columns with a useful data type in a SQL injection UNION attack
• Using a SQL injection UNION attack to retrieve interesting data
• Retrieving multiple values within a single column

SQL injection UNION attacks

In cases where an application is susceptible to SQL injection and the query results are incorporated into the application's responses, malicious actors can exploit the SQL injection vulnerability using the UNION keyword. This leads to what is known as a SQL injection UNION attack.

The UNION keyword allows the execution of one or more supplementary SELECT queries, and their outcomes are appended to the original query, facilitating unauthorized data retrieval from other tables within the database.

For a UNION query 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

To carry out a SQL injection UNION attack, you need to ensure that your attack meets these two requirements. This generally involves figuring 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 in a SQL injection UNION attack

During a SQL injection UNION attack, there are two effective approaches to determine the number of columns returned by the original query.

The first method entails injecting a sequence of ORDER BY clauses while gradually incrementing the specified column index until an error is triggered. To illustrate, if the injection point is within a quoted string in the WHERE clause of the original query, you would use the following submission as an example:

These payload sequences alter the original query to arrange the results by different columns in the result set. By using the index of the desired column in the ORDER BY clause, there's no need to know the column names explicitly. If the specified column index surpasses the number of actual columns in the result set, the database will return an error.

Indeed, the application's response to the SQL injection UNION attack can vary. It might return the actual database error in the HTTP response, a generic error message, or even no results at all. However, by detecting any difference in the application's response, it becomes possible to infer the number of columns being returned from the query.

The second method entails employing a series of UNION SELECT payloads while specifying a varying number of null values in each payload:

If the number of nulls does not match the number of columns, the database returns an error.

Once again, the application's response to the SQL injection UNION attack may differ. It could either return an error message, a generic error, or no results at all. In this method, when the number of nulls specified matches the number of columns in the result set, the database returns an additional row with null values in each column. The impact on the resulting HTTP response relies on the application's underlying code.

If you're fortunate, the response might display additional content, like an extra row in an HTML table. Alternatively, the presence of null values may trigger a different error, such as a NullPointerException. However, in the worst-case scenario, the response may be indistinguishable from one caused by an incorrect number of nulls, rendering this approach ineffective for determining the column count.

The reason for using NULL as the values returned from the injected SELECT query is that the data types in each column must be compatible between the original and the injected queries. Since NULL is convertible to every commonly used data type, using NULL maximizes the chance that the payload will succeed when the column count is correct.

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 Oracle would need to look like:

' UNION SELECT NULL FROM DUAL--

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

Finding columns with a useful data type in a SQL injection UNION attack

The purpose behind conducting a SQL injection UNION attack is to retrieve results from an injected query. Usually, the sought-after data that needs to be extracted exists in string format. Therefore, it becomes necessary to identify one or more columns in the original query results that are either of string data type or compatible with it.

After determining the number of columns required, the next step involves probing each column to assess whether it can hold string data. This is achieved by submitting a series of UNION SELECT payloads, where each payload places a string value into each column one at a time. For instance, if the query returns four columns, you would submit the following:

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

Using a SQL injection UNION attack to retrieve interesting data

Once you have identified the number of columns returned by the original query and established which columns can store string data, you are now able to retrieve the desired information.

For instance, consider the following scenario:

• The original query returns two columns, both capable of holding string data.
• The injection point is within a quoted string in the WHERE clause.
• The database contains a table named "users" with the columns "username" and "password."

In this situation, you can obtain the contents of the "users" table by submitting the following input:

However, it's essential to have prior knowledge of the table's existence, along with the names of its columns. Without this information, attempting to guess the table and column names would be challenging. Luckily, modern databases offer methods for examining the database structure, facilitating the discovery of tables and columns it contains. This knowledge makes it significantly easier to perform successful attacks and retrieve the desired data.

Retrieving multiple values within a single column

In the given scenario, if the query only returns a single column, you can still retrieve multiple values within that column by concatenating the values together and using a suitable separator to differentiate the combined values.

For instance, on Oracle, you could use the following input:

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

In this injected query, the double-pipe sequence || serves as the string concatenation operator in Oracle. It concatenates the values of the "username" and "password" fields, separated by the ~ character.

This technique allows you to extract and view the necessary data even when the original query only returns a single column.

Note that different databases use different syntax to perform string concatenation.