SQL Injection Exploitation: Union-Based
Author: HollyGraceful Published: 19 October 2020 Last Updated: 03 July 2023
This article is Part 3 of a series; to read about detecting and fixing SQL injection in Part 1, click here.
There are several methods for exploiting SQL Injection vulnerabilities depending on the context of the injection point, any potential filters and Web Application Firewalls (WAF) in place. These methods are generally broken down into: Error-based, Blind-Boolean, Blind Time-based, Union-Based, and Out-of-Band. Here we’ll be looking at Error-based exploitation.
UNION SELECT statements can be used for retrieving the results of a second SELECT statement by appending it to the end of another query. This is useful for SQL injection as it allows you to append a query to the end of a query executed by a developer to retrieve arbitrary database contents. It’s important to note that the details of the second query must match the first, specifically they must have the same number of columns and those columns must match in type.
Therefore the first step to exploiting SQL injection through UNION injection is to determine how many columns there are in the original query. This is possible in two main ways – either by creating a select statement and increasing the column count until the query executes or alternatively using “ORDER BY” syntax and increasing the column count until an error occurs – which implies that the number which causes an error is higher than the number of columns in use.
For example, the following payloads could be used to determine how many columns are in use:
UNION SELECT 1
UNION SELECT 1,2
UNION SELECT 1,2,3
UNION SELECT 1,2,3,4
UNION SELECT 1,2,3,4,5
Here a single column is attempted, which works without error – showing that exactly one column is in use.
Here two columns are used which causes an error, indicating that the number of columns used is not two.
Here the query used is “UNION ALL SELECT” as opposed to “UNION SELECT”, the difference being that the ALL keyword will allow duplicate results to be displayed, whereas without this keyword duplicates will not be shown.
As an alternative to guessing the row count in this way, it is possible to use ORDER BY, as:
ORDER BY 1
ORDER BY 2
ORDER BY 3
Here the query is ordered by the first column, without error, showing that there is at least one column in the query.
Once the number of columns is known, if the contents of those columns is included in application output, it can be used to extract content from the database. For example the version can be retrieved with a payload such as (ensure the number of columns matches the number determined previously, here only one is needed):
UNION SELECT @@version
Here the UNION SELECT is retrieving the version of the database, which is displayed as 5.5.64-MariaDB
It is possible that an injection point only returns a single row and therefore the UNION select will not be able to retrieve multiple rows in output – it’s still possible to exploit these inputs using UNION statements using LIMIT syntax in a similar way as described in our Error-based injection article. Here we will show how to exploit where multiple rows are retrieved.
Now that we have demonstrated how to retrieve data, it is possible to extract information from database tables. The first step being to retrieve the list of table names and then column names. The following queries can be used:
SELECT table_name FROM information_schema.tables
SELECT column_name FROM information_schema.columns
These can be formatted as UNION SELECT statements (remember to match the number of columns retrieved still), as such:
UNION SELECT table_name FROM information_schema.tables
Here all table names have been retrieved with a single request, using UNION syntax.
Once a target table has been identified, the column names can be retrieved with a payload such as:
UNION SELECT column_name FROM information_schema.columns WHERE table_name = 'challenge2'
Here the list of columns for the table “challenge2” are displayed.
Once the target columns have been listed, the table content can be retrieved using a payload such as:
UNION SELECT flag FROM challenge2
Here the table content has been extracted as is shown as “SQL2-Finished”.
To read more about SQL Injection, try the following articles: