SQL Injection Exploitation: Union-Based
Published: 02 March 2020
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 vulnerabilties depending on the context of the injection point, any potential filters and Web Application Firewalls (WAF) in place.
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 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
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
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
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'
Once the target columns have been listed, the table content can be retrieved using a payload such as:
UNION SELECT flag FROM challenge2
To read more about SQL Injection, try the following articles:
Posts broken down by category
Articles concentrating on network and operating system level attacks.
Articles covering attacks against web applications and their associated APIS.
Articles concentrating on past data breaches, looking for lessons learned.
Articles covering breaking into wireless networks and how to keep them safe.