4. Error-based SQLi

Posted Dec 22, 2023 Updated Jan 11, 2024

By Charalampos Spanias 9 min read

Error-based SQLi

Error-based SQLi refers to cases where we are able to use error messages to either extract or infer sensitive data from the database, even in blind contexts. The possibilities depend on the database configuration and the error types we are able to trigger:

We may be able to induce the app to return a specific error response based on the result of a boolean expression. We can exploit this in the same way as the condition responses.
We may be able to trigger error messages that output the data returned by the query. This effectively turns the otherwise blind SQLi vulnerabilities into visible ones (extracting sensitive data via verbose SQL error messages).

Some apps carry out SQL queries but their behavior does not change, regardless of whether the query returns any data, thus, the Blind SQLi techniques won’t work, because injecting different boolean conditions makes no difference to the app’s responses.

It’s often possible to induce the app to return a different response depending on whether a SQL error occurs. We can modify the query so that it causes a database error only if the condition is true. Very often, an unhandled error thrown by the database causes some difference in the app’s response time, such as an error message. This enables us to infer the truth of the injected condition.

Suppot that two requests are sent containing the following TrackingId cookie values in turn:

These inpus use the CASE keyword to test a condition and return a different expression depending on whether the expression is true:

With the first input, the CASE expression evaluates to 'a', which does not cause any error.
With the second input, it evaluates to 1/0, which causes a divide-by-zero error.

If the error causes a difference in the app’s HTTP response, we can use this to determine whether the injected condition is true. Using this technique, we can retrieve data by testing one character at a time:

  
xyz' AND (SELECT CASE WHEN (Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') THEN 1/0 ELSE 'a' END FROM Users)='a

There are different ways of triggering conditional errors, and different techniques work best on different database types. For more details, see the SQLi Cheatsheet.

Objective: This lab contains a blind SQLi vulnerability. The application uses a tracking cookie for analytics, and performs a SQL query containing the value of the submitted cookie. The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows. If the SQL query causes an error, then the application returns a custom error message. The database contains a different table called users, with columns called username and password. You need to exploit the blind SQLi vulnerability to find out the password of the administrator user. To solve the lab, log in as the administrator user.

Let’s start as usual, by testing for SQLi,
The above test suggests that a syntax error is having a detectable effect on the response. We now need to confirm that the server is interpreting the injection as a SQL query, i.e., that the error is a SQL syntax error as opposed to any other kind of error. To do this, we first need to construct a subquery using valid SQL syntax:
1 2 3 -- translation of the above query WHERE TrackingId == 'xxxxx'||(SELECT '')||'' -- result: 'xxxxx ' (followed by 2 spaces)
The queries above indicate that the sever is indeed interpreting the injection as a SQL query, and that we are dealing with an Oracle database which requires all SELECT statements to explicitly specify a table name. We can confirm the database type as follows:
Now we now the database type, we can pass an invalid query while preserving valid SQL syntax to be sure that our injection is being processed as a SQL query by the server:
The FROM test part of the SELECT '' FROM test query is executed first, and because this table does not exist, it will return an error.
As long as we inject syntactically valid SQL queries, we can use this error response to infer key info about the database. For example, in order to verify that the users table exists:
The WHERE ROWNUM=1 condition is important here to prevent the query from returning more than one row, which would break our concatenation.
Next, we want confirm that the user administrator exists. If we use a normal query (without inducing any error) we wouldn’t be able to confirm that the user exists because no matter which user we tested for, we would always received a HTTP 200 reponse:
The FROM users part of the SELECT '' FROM users WHERE username='xxxx' query is executed before the WHERE username='xxxx part, and because the users table exists it is a valid query that simply returns no data if the user does not exist, so it won’t return any errors regardless of the user passed.
What we need to do is to construct a query that includes an error-generating expression and make sure that it works as intended:
1=1 evaluates to True, so it will then try to execute 1/0 which will return a “division-by-0” error.
So we can now swap the dual table with the info we need to test, i.e., check if the user administrator exists:
The above works due to SQL’s order of operations:
Now we can modify our query and find out the password length. Again, when we get a 500 response that confirms our query:
The password length is bigger than 8 characters.
The password length is not bigger than 20 characters.
The password length is exactly 20 characters.
The password is 20 characters long, so we can now use Intruder to brute force its characters one-by-one:
Now we can log in with the administrator account and mark this lab as solved:

Burp-Intruder alternative (Python3)

We can create our own brute-forcing script using Python3 that works in exactly the same way (with the exception that we have now modified our payload to include only lowercase alphanumeric characters) as the Intruder : it will try all lowercase alphanumeric characters for each of the twenty positions of the password and will keep the correct one for each position based on a 500 HTTP status code response.

The script is a modification of Rana’s code. The major modifications are the automation of cookie grabbing and the addition of comments throughout the code, among other minor changes. We can run the modified script by supplying the page’s URL as an argument and get the administrator’s password as a result:

Extracting sensitive data via verbose SQL error messages

Misconfiguration of the database sometimes results in verbose error messages. These can provide info that may be useful to an attacker. For example, consider the following error message, which occurs after injecting a single quote into an id parameter:

Unterminated string literal started at position 52 in SQL SELECT * FROM tracking WHERE id = '''. Expected char

This shows the full query that the app constructed using our input. This makes it easier to construct a valid query containing a malicious payload. Commenting out the rest of the query would prevent the superfluous single-quote from breaking the syntax.

Occasionally, we may be able to induce the app to generate an error message that contains some of the data that is returned by the query. This turns an otherwise blind SQLi vulnerability into a visible one. We can use the CAST() function to achieve this: it enables us to convert one data type to another. For instance, imagine a query containing the following statement:

  
CAST((SELECT example_column FROM example_table) AS int)

Often, the data that we ‘re trying to read is a string. Attempting to convert this to an incompatible data type, such as an int, may cause an error such as: ERROR: invalid input syntax for type integer: "Example data". This type of query may also be useful if a character limit prevents us from triggering conditional responses.

Lab: Visible error-based SQL injection

Objective: This lab contains a SQLi vulnerability. The application uses a tracking cookie for analytics, and performs a SQL query containing the value of the submitted cookie. The results of the SQL query are not returned. The database contains a different table called users, with columns called username and password. To solve the lab, find a way to leak the password for the administrator user, then log in to their account.

When we test if a SQLi vulnerability exist by injecting a single quote character, we get back the entire server query:
If we comment out the rest of the query after our injected single quote character, we will notice that the error disappears which means our query is now syntactically valid:
If we inject a simple payload casting our result to integer, we get a new error:
When we modify our payload to result in a boolean value, the error disappears again:
We can now try getting some info from our queries:
This time it seems to be a character limit that truncated our query. We can delete the TrackingId value to free up some space:
We get a database error, so our query is still valid, but it returned more than one row. We can use LIMIT 1 and try again:
The administrator user is leaked which also informs us that this is the first user in the username column. We can try to get his password as follows: