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

UNION Operator

UNION operator used in SQL to combine the results of two or more SELECT statements into a single result set. When an application contains a SQL injection vulnerability that occurs in a SELECT statement and the results of the query are returned within the application’s response, you can use the UNION operator to perform another second query and combine it’s result with the first query. In that way you can retrieve data from other tables within the database. It is the quickest way to retrieve arbitrary information from the database in situations where query results are returned directly.

Let’s consider a web application that enables users to search for different product categories. Searching for products in a particular category (‘Gifts’) causes the application to execute the following sql query.

SELECT product_name,details FROM products WHERE category = 'Gifts'

Above SQL query returns the following result.

An attacker is able to supply a crafted input using the UNION operator to inject a second SELECT query and append it’s result to the original query. This second query can be used to retrieve users data from the users table as follows:

' UNION SELECT username,password FROM users--

It causes the application to execute the following query.

SELECT product_name,details FROM products WHERE category = 'Gifts' UNION SELECT username,password FROM users--

This returns the result of the original query followed by the contents of the users table.

When the results of two or more SELECT queries are combined with the UNION operator, the column names of the result set are the same as those returned by the first(original) SELECT query. As shown in the above table, username appears in the product_name column, and password appears in the details column.

This implies that when the application processes the results of the modified query,it has no way of finding that the data returned has originated from the same or a different table in the database.

For a UNION query to work, two key requirements must be met:

1. When the results of two queries are combined using the UNION operator, the two result sets must have the same structure. That means, they must contain the same number of columns, which have the same or compatible data types, appearing in the same order.
2. To inject a second query, the attacker must have a clear idea about the database table that he/she wants to target, and the relevant column names of it.

NOTES

• For the injected query to be capable of being combined with the first, it is not strictly necessary that it contain the same data types. Rather, they must be compatible. In other words, each data type in the second query must either be identical to the corresponding type in the first or be implicitly convertible to it. In fact, the value NULL can be converted to any data type. Hence, if you do not know the data type of a particular field, you can simply SELECT NULL for that field.
• In most cases, you can achieve your objectives simply by identifying a single field within the original query that has a string data type.This is sufficient for you to inject arbitrary queries that return string-based data and retrieve the results, enabling you to systematically extract any desired data from the database.

STEP #1

Your first task is to determine the number of columns returned by the original query being executed by the application. You can do this in two ways:

1. The first method involves injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs. For example, assuming the injection point is a quoted string within the WHERE clause of the original query, you would submit:

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

This series of payloads modifies the original query to order the results by different columns in the result set. Since the column in the “order by” clause can be specified by its index, you don’t need to know the column names.If the specified column index exceeds the number of columns in the original query,the database returns an error.

In many real-world cases, the database error messages shown are trapped by the application and are not returned in HTTP response. However, if you are able to detect some kind of difference in the application response, you can infer how many columns are being returned by the original query.

2. The second method involves submitting 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 --

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

When your query is executed, the application might return an error message, or might just return a generic error or no result at all. If the application doesn’t return a database error message as you expected, you can still tell when your injected query was successful. When the number of nulls matches the number of columns, an additional row of data will be returned in the result set, containing either the word NULL or an empty string. Note that the injected row may contain only empty cells and so may be hard to see when rendered as HTML. For this reason it is preferable to look at the raw response when performing this attack.

In Oracle databases, every SELECT statement must include a FROM attribute, so injecting UNION SELECT NULL produces an error regardless of the number of columns. You can satisfy this requirement by selecting from the globally accessible table DUAL. For example:

' UNION SELECT NULL FROM DUAL--

Let’s solve the Lab-3 SQL injection UNION attack, determining the number of columns returned by the query

SQLi vulnerability: Product category

OPTION #1

Intercept the GET request using Burp Suite and inject a series of ORDER BY clauses incrementing the specified column index until the application gives an error message.

' ORDER BY 1 --

' ORDER BY 2 --

' ORDER BY 3 --

' ORDER BY 4 --

' ORDER BY 4 ----> Returns an error message.

Therefore number of columns returned by the original query is 3

OPTION #2

submitting a series of UNION SELECT payloads specifying a different number of null values until the application gives a HTTP 200 response code.

' UNION SELECT NULL --

' UNION SELECT NULL, NULL --

' UNION SELECT NULL, NULL, NULL --

In this Lab exercise We can clearly see the additional row of data which contains null values when rendered as HTML.