SQL Injection Exploitation: Error-based

Published: 02 March 2020


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 vulnerabilties 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:

The count() function is used to determine how many tables, here 63 is the number.

The name of the first table is retrieved using LIMIT 0,1 – here it is shown to be CHARACTER_SETS.

By changing the LIMIT keyword you can retrieve other table names, here 62 is supplied to retrieve the last time in the list, shown to be challenge1.

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.

Next the first column is retrieved using LIMIT 0,1 – it is shown as “id”.

This can be continued as long as necessary until all columns are retrieved, here the second column is shown as “flag”.

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”.

Read More

To read more about SQL Injection, try the following articles: