SQL Injection: Union-based attacks

Introduction

In web applications, understanding SQL injections is imperative. The OWASP top 10 2021 update lists Injection in the third position; which is quite high. Therefore, understanding SQL and variations of attacks will only help expand knowledge concerning web application security.

What is the UNION operator?

In SQL, the UNION operator is used to procure data from other tables. However, the data selected is limited to the number of columns selected by the first statement. For example:

SELECT username, password FROM users UNION SELECT item, description FROM items;

Here, the UNION statement will only return data if it is also selecting two columns from another table. As this is the case, the query should return data to the user. The data types between the two queries are also the same, VARCHAR, meaning they hold string data types, like usernames.

Why is this important?

When performing an injection attack, we must know how many columns are being returned by the initial SELECT statement, and what data type each column can hold. We cannot return an integer type of data from a column that holds strings (VARCHAR), for example.

Test environment

To show these UNION attacks in action, I'll set up a vulnerable web application using a docker container. The container I'll be using is badstore. It is quite old but is valuable in showing how these attacks can be exploited.

First, install docker.io on your system. The steps may be different depending on your distribution.
Secondly, head to the badstore GitHub link and follow the installation instructions.
Once started, you should have the application on localhost:80.

You should be greeted with this

Determining the number of columns

1. Typically, we want to first determine the number of columns being returned by a vulnerable query. This is usually done using ORDER BY clauses. This simply orders the data in a specific order. For example:

' ORDER BY 1 #
' ORDER BY 2 #
' ORDER BY n #

The column index is incremented by one until an error is observed. The query is closed via ' and ORDER BY is injected into the query.

2. Another way to perform this is by using null data within a UNION SELECT statement. For example:

' UNION SELECT NULL #
' UNION SELECT NULL, NULL #
' UNION SELECT NULL, NULL, NULL #

No data is selected using null, of course, however, data must be selected from a valid table. The table used in the following query is found in the badstore web application. Of course, further exploitation must be conducted of the system to find table names. However, this is not covered in this post.

Therefore, the query would be:

' UNION SELECT null FROM userdb #

Executing this statement on badstore provides an error.

This tells us that the number of columns selected by the original query is larger than one. This will continue until an error is not observed. On badstore, the original query is displayed in the error, which selects four columns from itemdb. This can be verified via:

' UNION SELECT null, null, null, null FROM userdb #

An empty table is returned, as null data was selected. However, this proves that four columns are selected from the original query.

Determining the data type of a column

Now the number of columns selected is known, it is now imperative to know what data type each column can hold. These will generally be VARCHAR or INT data types, although they can differ.

This is accomplished by replacing incrementally each null value with a character, such as 'a' . If data is returned, this means the column can hold the VARCHAR data type. The contrary is true if an error is observed. Typically malicious actors will aim for passwords, emails and so on. Thus, identifying a column with the VARCHAR data type is critical for exploiting the vulnerability further.

For example:

' UNION SELECT 'a', null, null, null FROM userdb #
' UNION SELECT 'a', 'b', null, null FROM userdb #
' UNION SELECT 'a', 'b', 'c', null FROM userdb #

On badstore, the following query returns no errors.

' UNION SELECT 'a', 'b', 'c', 'd' FROM userdb #

All columns filled with VARCHAR type data

All the columns are filled with our inputted characters meaning all four columns accept data of type VARCHAR.

Procuring emails and passwords

Now let's get to the best part - procuring emails and passwords. We already know the name of the table, but we don't know the names of the columns. For MySQL/MariaDB version of SQL the table information_schema contains information concerning the names of the database or tables, data types of columns and privileges. Issuing the following query, we get the columns of the tables within the tables:

' UNION SELECT table_name, column_name, null, null FROM information_schema.columns #

A quick of search 'userdb' using Ctrl+F on the page provides all the columns of userdb. Now we can craft a query to return the data that we want. We know already all four columns can take string data and so we can obtain: user emails, passwords, full names and roles.

' UNION SELECT email, passwd, fullname, role FROM userdb #

The column headers are still the same, however, the data is now being returned from the user table! This is extremely bad and must be resolved immediately.

Conclusion

UNION SELECT attacks can be used to access sensitive data. Web applications should take steps to ensure user input is interpreted as SQL, leading to injection vulnerabilities. Using prepared statements, sanitising and validating user input can minimise the risk of such vulnerabilities.