SQL Injection Exploitation: Error-based
Author: HollyGraceful Published: 19 October 2020 Last Updated: 03 November 2022
This article is Part 2 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.
With error-based injection, data can be extracted from the database where an error message can be crafted which contains confidential data. For example:
MySQL: AND ExtractValue('',Concat('=',@@version))
MSSQL: AND 1 in (@@version)
With the MSSQL payload above the intention is to cause a string to be converted to an integer – which may throw an error where the error will contain the contents of the string. With the MySQL payload above a similar thing is attempted however this is achieved through an XPath function.
The ExtractValue function expects XPath for the second parameter, if you supply anything other than XPath you will receive an error such as:
XPATH syntax error: 'foobar'
The payload also uses the concat() function to prepend an equals sign to the front of the string to be extracted, in case the first few characters are valid as XPath (such as integers) which would cause them not to be shown. Appending an equals sign prevents this.
The following screenshot shows this being used against a MySQL database:
Here an XPath error was caused using the ExtractValue function with @@version supplied to cause the error to contains the database version: 5.5.64-MariaDB.
Extracting Table Names
Using the error message to extract information from the database is simple, however only a single item can be retrieved at once, so extracting table contents requires a little more work.
Firstly it should be noted that table and column names can be extracted from the database using the built-in information_schema database. With queries such as:
SELECT table_name FROM information_schema.tables SELECT column_name FROM information_schema.columns
However as this will retrieve the complete list this could not be used with error-based extraction. Therefore it’s required that the query be modified to pull one name at a time, which on MySQL is achieveable using the LIMIT keyword. Such as:
SELECT table_name FROM information_schema.tables LIMIT 0,1
Here the LIMIT keyword is being used to reduce the returned rows to 1 row, starting from row 0. The next rows can then be retried using:
SELECT table_name FROM information_schema.tables LIMIT 1,1
SELECT table_name FROM information_schema.tables LIMIT 2,1
SELECT table_name FROM information_schema.tables LIMIT 3,1
SELECT table_name FROM information_schema.tables LIMIT 4,1
Combining this payload, into the ExtractValue() payload used to craft an error message would look like this:
1 AND ExtractValue('',Concat('=',(SELECT table_name FROM information_schema.tables LIMIT 0,1)))
It might also be useful to know that the number of tables can be retrieved using a simliar payload but with the count() function, which returns just the number of tables, as:
1 AND ExtractValue('',Concat('=',(SELECT count(table_name) FROM information_schema.tables)))
This can be seen in the screenshots below, showing table names being retrieved from the database:
Now that we have retrieved all of the table names, we can modify the query to retrieve the column names as described previous. However if we want to retrieve the column names for a specific table we can supply the table name within the WHERE clause, as:
SELECT column_name FROM information_schema.columns WHERE table_name = 'challenge1' LIMIT 0,1
Therefore we can retrieve the amount of columns, again using count(), and then retrieve each column name with a query similar to above (but placed within the ExtractValue() function as before.
1 AND ExtractValue('',Concat('=',(SELECT column_name FROM information_schema.columns WHERE table_name = 'challenge1' LIMIT 0,1)))
This would produce something like:
Here the count() function is used to retreive the number of columns the challenge1 table has, it is shown as 2.
Now that we have a list of tables, and for each table a list of columns, we can retrieve content from tables. This is simply achieved by using a SELECT statement with the LIMIT keyword. By adding the SELECT statement into the ExtractValue() function to allow it to be retrieved from the error message you would end up with something like:
1 AND ExtractValue('',Concat('=',(SELECT flag FROM challenge1 LIMIT 0,1)))
The row content is retrieved, showing the content as “SQL1-Complete”.
To read more about SQL Injection, try the following articles: