5.4. Exploiting SQL Injection - UNION-based Method


Introduction to Exploitation Methods

Once a vulnerability has been found and confirmed, we can use it to retrieve the contents of the database or, in other words, exploit the vulnerability. Various SQL expressions, known as payloads, are applied for this purpose. There are different exploitation techniques, some of which we will explore further.

To begin, let's examine the simplest payload using an example of a user authentication vulnerability in Juice Shop. To log in to the site, you need to know the user's email. However, there is a way to log in to the site without knowing the correct email. Let's consider both options.

Since we don't know the users' emails, our only option is to examine the website itself for the presence of email addresses. Fortunately, we have access to some addresses in the product reviews and comments:

Exploring email address in reviews of Juice Shop

From the previous lesson, we know the SQL query itself, which looks like this:

SELECT * FROM Users WHERE email='user's email' AND password='password hash' AND deletedAt IS NULL

If we somehow remove the right part of the expression after the email, we can easily log in, knowing only the username. The most suitable payload would be This email address is being protected from spambots. You need JavaScript enabled to view it.'--. In the password field, you can enter any value; it does not affect the result. The final expression on the server will look like this:

SELECT * FROM Users WHERE email=This email address is being protected from spambots. You need JavaScript enabled to view it.'

Thanks to the commenting symbol, we were able to remove the interfering part of the expression and thereby log in as an administrator.

Now let's see what to do if the username is unknown to us. For this purpose, the payload 1' OR 1=1-- is suitable. Here we added a condition that is always true, so we can also log in. However, in this case, the first user in the database records will be selected. Sometimes it may be necessary to limit the output to only one record. To do this, simply add the LIMIT operator. As a result, we will have the entry 1' OR 1=1 LIMIT 1--.

This was just a small example. Depending on the situation and the configuration of the vulnerable application, one of the following techniques is applied to exploit the vulnerability:

  • UNION – combining two or more queries using the UNION operator.
  • Error-based – intentionally triggering an error message that will display the required information.
  • Time-based – causing a delay if a specific condition is met. Mainly used in Blind SQL injection.
  • Stacked (batched) queries – combining multiple queries for sequential execution. Queries are independent of each other.
  • Out-Of-Band – sending the required data to an external server that is under our control. This can be a web or DNS server. This technique is also used in Blind SQL injection.


The UNION-based Technique

The UNION operator in SQL statements is used to combine two or more queries with the same structure. For example, the queries

SELECT name, surname FROM users


SELECT order, price FROM goods

can be combined into one query like this:

SELECT name, surname FROM users UNION SELECT order, price FROM goods

The results of both queries will be displayed in a single result set. If we find a vulnerable access point, we can create our own query based on the UNION operator. This technique is applicable in cases where the contents of a database are displayed on a web page. This could be any table that displays content through GET or POST requests. It could be a site search or displaying products and blog posts.

For the UNION technique to work, two conditions must be met: the number of columns in both SQL queries must match, and the data types in these columns must also be the same. Let's illustrate all the above with an example using the bWAPP application, which uses MySQL.

Open the application, log in (bee/bug), and then in the upper-right corner, select SQL Injection (Search/GET) and click on the Hack button:

Selecting SQL test in bWAPP

The vulnerability exists in the website's search system through a GET request. Let's start by examining how the search works. Enter "man" or any other word:

Checking search operation in bWAPP

Let's try entering the following query in the search field:

1' UNION SELECT database() #

The database() function in MySQL allows us to retrieve the name of the current database, but the system returned a query error:

bWAPP SQL columns mismatch

This is because the second expression specifies only one column.

But how can we determine the exact number of parameters (columns) in an SQL statement?

At first glance, it may seem that there are only 5 columns in the query since there are 5 columns in the table. However, there could be more columns, as some displayed parameters may be hidden for various reasons. Therefore, to determine the exact number of parameters in an SQL query, we can use 2 methods.

1st Method:

Let's say we have a query like SELECT column_1, column_2, column_3 FROM table. If we add to it the query UNION SELECT 1, 2, 3 or UNION SELECT null, null, null, the system will not produce an error. This means we have met the condition of having an equal number of parameters.

Now, let's apply this technique to our example. If we enter the query

1' UNION SELECT 1, 2, 3, 4, 5 #

the system will produce an error again. Therefore, we increase the number of columns with each new attempt until we get a positive result.

After entering the query 1' UNION SELECT 1, 2, 3, 4, 5, 6, 7 #, the following result is displayed:

bWAPP SQL columns match

In the table, you can see the entered numbers, with some of them being hidden. This way, we have determined that there are 7 parameters (columns) in the query.


2nd method:

The second method is based on the ORDER BY sorting operator. This operator takes the name of the column or its number in the SQL query. For example, if we have a query SELECT name, price, stock FROM goods, we can sort the output by the column name:

SELECT name, price, stock FROM goods ORDER BY name

or by its number:

SELECT name, price, stock FROM goods ORDER BY 1

If you specify a nonexistent number, the system will generate an error. This method of determining the number of columns is based on this principle. To start, enter a small number, and then increase it with each new attempt until you get an error. In our example, we assume that there are 5 columns (based on the number of columns in the table), so we enter 6: man' ORDER BY 6 #. Then we increase this number, and after entering 8, we get an error:

bWAPP SQL columns mismatch

Now that we know the number of parameters, we can compose a query to retrieve some data from the database. To begin with, let's try to find out the database name, the DBMS version, and the database user's name. We can create the following query for that purpose:

1' UNION SELECT 1, database(), user(), version(), 5, 6, 7 #

Retrieving basic data from SQL bWAPP

Now, knowing the database name, we can proceed to discover which tables exist in that database and what each of these tables contains. In MySQL, you can retrieve the list of tables in a specific database with the following query:

SELECT table_name FROM information_schema.tables WHERE table_schema='database_name'

Let's modify our query to fit these new conditions:

1' UNION SELECT 1, table_name, 3, 4, 5, 6, 7 FROM information_schema.tables WHERE table_schema='bwapp' #

Retrieving tables from bWAPP database

The database "bwapp" contains 4 tables. I believe the most interesting table is likely to be "users." To view its contents, we need to know what columns it has. To retrieve information about the columns in the "users" table, you can use the following query:

SELECT column_name FROM information_schema.columns WHERE table_schema='database_name' AND table_name='table_name'

Let's apply these new conditions:

1' UNION SELECT 1, column_name, 3, 4, 5, 6, 7 FROM information_schema.columns WHERE table_schema='bwapp' AND table_name='users' #

This query will help us discover the columns in the "users" table:

Discovering columns in bWAPP database

It looks quite interesting! Let's see what's inside this table:

1' UNION SELECT 1, login, password, secret, admin, 6, 7 FROM users #

Retrieving and cracking user passwords from bWAPP database

And inside, we have 2 users, both are administrators, and they have the same passwords. Using the UNION technique, you can traverse the entire database in this manner.