ImmuniWeb® by High-Tech Bridge


High-Tech Bridge Newsletter

Subscribe to our newsletter and receive some or all of our corporate news, invitations to security events or HTB Security Advisories – you choose what you want to receive.

SQL Injection [CWE-89]

This weakness type describes improper neutralization of special elements used in an SQL query.

Created: September 11, 2012
Latest Update: March 28, 2014

Table of Content

  1. Description
  2. Potential impact
  3. Attack patterns
  4. Affected software
  5. Exploitation Examples
  6. Severity and CVSS Scoring
  7. Mitigations
  8. Vulnerability Remediation Techniques and Examples
  9. References
  10. Latest Related Security Advisories

1. Description

The basic form of SQL injection describes direct insertion of attacker-controlled data into variables that are used to construct SQL commands. As a result, an attacker can tamper with the original query by permanently terminating the string, appending new commands etc.

2. Potential impact

An attacker can view, add, delete or modify information stored in the database with privileges of the current database user. In case of web application this weakness often leads to a website deface or a database exfiltration.

Insecure configuration of database may allow an attacker to write files into arbitrary locations on the system (e.g. SELECT ... INTO OUTFILE construction in MySQL), which might lead to system compromise.

3. Attack patterns

An attacker exploits this weakness in software that constructs SQL commands based on user input. According to CAPEC classification there are the following attack patterns:


WASC Threat Classification describes SQL injection weakness as an attack technique under WASC-19.

4. Affected software

Software that uses a backend database to store or read information is potentially vulnerable to this weakness. The weakness is common for many web applications since all modern content management systems are using database to store dynamic content.

SQL injection is also possible inside stored procedures or functions within database engines.

5. Exploitation Examples

We will use as an example the HTB23101 security advisory (CVE-2012-4034), specifically vulnerability 1.7.

This vulnerability allows execution of arbitrary SQL commands by modifying HTTP POST parameters. Successful exploitation requires that HTTP Referer belongs to the vulnerable website.

To demonstrate vulnerability exploitation we will use the Burp Suite. To do so we will need to configure our browser to use it as a proxy:

HTB23101 advisory (CVE-2012-4034) CWE-89 PoC exploitation example

Then we will intercept every request to the vulnerable website and change it according to instruction in the advisory:

HTB23101 advisory (CVE-2012-4034) CWE-89 PoC exploitation example

After PoC was executed on the website we should check for existence of the /file.php file:

HTB23101 advisory (CVE-2012-4034) CWE-89 PoC exploitation example

Successful exploitation of the vulnerability allowed us creation of file on the file system with arbitrary contents. Instead of phpinfo() function we can store a web-shell and gain control over the system.

6. Severity and CVSS Scoring

Since SQL injection influences confidentiality, integrity and availability of application it should be scored as C:P/I:P/A:P. The common CVSS score for SQL injection vulnerabilities in publicly accessible scripts is:
7.5 (AV:N/AC:L/Au:N/C:P/I:P/A:P) – High severity.

HTB23107 security advisory (CVE-2012-4232) can be used as an example of such score.

If some level of privilege is required to exploit this vulnerability (e.g. user must be logged-in and have some privilege level within the application) it should be scored as follows:
6.5 (AV:N/AC:L/Au:S/C:P/I:P/A:P) – Medium severity.

HTB23122 security advisory (CVE-2012-5698) can be used as an example of this score, since successful exploitation requires that the user is logged-in. In most cases SQL injection vulnerabilities that require certain privileges within the application can be also executed by an anonymous user via CSRF vector.


We use CVSSv2 scoring system in our HTB Security Advisories to calculate the risk of the discovered vulnerabilities. Not all of the vulnerabilities are scored in strict accordance to FIRST recommendations. Our CVSSv2 scores are based on our long internal experience in software auditing and penetration testing, taking into consideration a lot of practical nuances and details. Therefore, sometimes they may differ from those ones that are recommended by FIRST.

7. Mitigations

Neutralization of input data is considered the main defense approach against SQL injection attacks. This should be achieved by sanitizing input data before using it in SQL queries within application or by means of security software such as WAF or IDS/IPS system.

We will try to demonstrate common errors that occur during development and give advices on how to avoid them. Since SQL injection occurs when data from untrusted source is passed to the application or untrusted data is used to construct dynamic queries it is obvious to perform neutralization of this input. But this might not be enough to protect the application.

Example 1:
The vulnerable script receives information from the HTTP GET parameter and uses it to construct query:

  1. $id = $_GET[‘id’];
  2. $res = mysqli_query("SELECT * FROM news WHERE id = '". $id."'");

In this case an attacker can pass a specially crafted string to the id parameter and change the query as desired:
http://[host]/?id=' or 'a'='a

So the actual query to the database looks like this:

  1. SELECT * FROM news WHERE id = '' or 'a'='a'

As one can see, the single quote in the id parameter makes it possible to insert additional lines into the query. The logical solution here would be to escape this symbol. In case of PHP, it is possible to be done with native function such as mysqli_real_escape_string() or addslashes(). These functions escape special characters and make the input safe for the application:

  1. $id = mysqli_real_escape_string($_GET["id"]);

Example 2:
In this example we have basically the same script and the same query:

  1. $id = $_GET[‘id’];
  2. $res = mysqli_query("SELECT * FROM news WHERE id = $id");

The only difference is that there is no single quotes around the $id variable. To exploit this vulnerability an attacker can send the following query:
http://[host]/?id=0 or 1=1

So the actual query to the database looks like this:

  1. SELECT * FROM news WHERE id = 0 or 1=1

The common mistake here is to use the mysqli_real_escape_string() function on the "id" parameter. There are no quotes or any other special symbols in the string, so this function will not escape any data and the SQL injection will occur. In this case one should use the intval() function to ensure that the input string corresponds to the expected variable type.

8. Vulnerability Remediation Techniques and Examples

8.1 General recommendations for software developers

Quote and backslash escaping for strings is the most popular and effective strategy against SQL injection attacks. It should be performed according to requirements of each particular database.

Casting operations of digits (e.g. int, float) is usually enough to defend application against SQL injection.

In certain cases, parameter values should be checked against existing templates and treated according to these templates.

If the programming language contains APIs for prepared statements , developers should use them to build SQL queries.

The following examples contain regular expressions for popular languages that might be useful against SQL injection attacks:


PHP

PHP is a very popular language and it has built-in functions that can protect application from SQL injection attacks. The following table displays available functions for popular databases:


Language/databaseMySQLPostgreSQLOracle / Microsoft SQL
PHPmysqli_real_escape_string(), addslashes()pg_escape_string(), addslashes()PDO::quote()

Note that old versions of PHP use mysql_real_escape_string() and mysql_escape_string() which are considered insecure and are deprecated. Do not use these functions to escape untrusted input.

For numeric values it is advised to use integer casting, e.g. PHP function intval():

  1. $param=intval($param);

PERL
  1. $s = $dbh->prepare("select login from users where login=?");
  2. $r = $s->execute($param);

ASP/ASP.NET

There are no built-in functions to prepare input for using it in a SQL query, so the main approach is to perform all necessary checks and escapes. Microsoft advises to use the following steps:

  1. Step 1. Constrain input
    Developer is advised to use regular expressions to validate the input (e.g. RegularExpressionValidator control or the System.Text.RegularExpressions namespace).

    Example 1:

    1. <%@ language="C#" %>
    2. <form id="form1" runat="server">
    3.     <asp:TextBox ID="SSN" runat="server"/>
    4.     <asp:RegularExpressionValidator ID="regexpSSN" runat="server"
    5.                                     ErrorMessage="Incorrect SSN Number"
    6.                                     ControlToValidate="SSN"
    7.                                     ValidationExpression="^\d{3}-\d{2}-\d{4}$" />
    8. </form>

    Example 2:

    1. using System.Text.RegularExpressions;
    2. if (Regex.IsMatch(Request.Cookies["SSN"], "^\d{3}-\d{2}-\d{4}$"))
    3. {
    4.     // access the database
    5. }
    6. else
    7. {
    8.     // handle the bad input
    9. }
  2. Step 2. Use parameters with stored procedures and dynamic SQL
    The main idea is to use parameters with stored procedures and dynamic SQL statements.
    The following code:

    1. myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
    2. myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;

    makes the application treat the @au_id parameter as literal value and not as executable code. The input value also cannot be longer than 11 characters.

    The following document describes basic techniques to protect application from SQL injection attacks: msdn.microsoft.com/en-us/library/ff648339.aspx


ColdFusion
  1. <cfscript>
  2.   param varNumeric = 10;
  3.   param varChar = 'Lorem ipsum';
  4.   queryExample = new Query();
  5.   queryExample.SetDatasource('dbTest');
  6.   queryExample.SetName('GetRow');
  7.   queryExample.AddParam(name='number',value='#varNumeric#',cfSQLType='cf_sql_integer');
  8.   queryExample.addParam(name='text',value='#varChar#',cfSQLType='cf_sql_varchar');
  9.   queryExample.SetSQL('SELECT * FROM db_table WHERE t_number=:number and t_text=:text');
  10.   result=queryExample.Execute();
  11. </cfscript>

Python

To prevent SQL injection in Python one should use parameterized query e.g. cursor.execute("... %s ...%s", (param1, param2)). The following example shows vulnerable code:

  1. email = "' OR '1'='1"
  2. query = "SELECT * FROM user_info WHERE email = '" + email + "'"

To prevent SQL injection it is advised to use the following construction:

  1. cursor.execute("SELECT * FROM user_info WHERE email = %s", email)

This way MySQLdb will make the necessary escapes and the code will be safe.


JAVA/JSP

Prepared statements should be used to avoid SQL injection. The main feature of a PreparedStatement object is that it is given a SQL statement when it is created.

Example:
Creating a PreparedStatement object:

  1. String updateString =
  2.     "update " + dbName + ".COFFEES " +
  3.     "set SALES = ? where COF_NAME = ?";
  4. updateSales = con.prepareStatement(updateString);

Supplying values for PreparedStatement parameters and executing query:

  1. updateSales.setInt(1, e.getValue().intValue());
  2. updateSales.setString(2, e.getKey());
  3. preparedStatement.executeUpdate();

For more information on prepared statements see the JDBC tutorial: docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html.


These are general recommendations. Every case must be treated separately.

Caution: do not blindly copy-paste the above-mentioned solutions into your application code. In some cases this may result in incorrect behavior of the application or inconsistent patch. Carefully read the References or consult security specialists in case you are not sure how to patch a vulnerability.

8.2 Using Web Application Firewall (WAF)

Web Application Firewall can be an efficient solution to prevent vulnerability exploitation while you are developing or waiting for a security patch. We do not recommend using WAF as a long-term solution, neither as a replacement to properly developed security patch.

As an example, we will use an open source web application firewall ModSecurity developed by Trustwave. There are many rule sets for ModSecurity licensed under ASLv2 and widely distributed by security companies and organizations. These rule sets can be applied to cover all basic cases of vulnerabilities’ exploitation and can be used on production servers.

A majority of SQL injection attacks was covered in the modsecurity_crs_41_sql_injection_attacks.conf rule set. However, certain rules can introduce false positives and can be removed or modified.

In our demonstration we will use another approach to block SQL injection attacks. Let’s have a look at SQL injection vulnerability in Symphony described in security advisory HTB23148 (CVE-2013-2559). The injection occurs within the “id” parameter. We will use a simple rules that allows passing only digits to the vulnerable parameter and will not allow injection of SQL commands:

SecRule ARGS:id "!^([0-9]+)$" "phase:2,rev:'2',ver:'HTBRIDGE /0.1',maturity:'9',accuracy:'7', t:none,ctl:auditLogParts=+E,block,msg:'SQL injection in Symphony HTB23148',id:'10000000035,severity:'2',logdata:'Matched Data: %{TX.0} found within %{MATCHED_VAR_NAME}: %{MATCHED_VAR}', capture,tag:'HTBRIDGE/WEB_ATTACK/SQL', setvar:'tx.msg=%{rule.msg}'"

Let’s have a look at another SQL injection vulnerability in OrangeHRM described in HTB23119 (CVE-2012-5367). Parameter “sortField” should be alphabetical and cannot contain any other symbols. The following rule allows passing alphabetical characters only:

SecRule ARGS:id "!^([a-zA-Z]+)$" "phase:2,rev:'2', ver:'HTBRIDGE /0.1',maturity:'9',accuracy:'7',t:none,ctl:auditLogParts=+E, block,msg:'SQL injection in OrangeHRM HTB23119',id:'10000000039,severity:'2', logdata:'Matched Data: %{TX.0} found within %{MATCHED_VAR_NAME}: %{MATCHED_VAR}',capture,tag:'HTBRIDGE/WEB_ATTACK/SQL',setvar:'tx.msg=%{rule.msg}'"

9. References

  1. Guide to PHP Security, CH3: SQL Injection [dev.mysql.com]
  2. SQL Injection [php.net]
  3. SQL Injection [www.owasp.org]
  4. SQL Injection [msdn.microsoft.com]
  5. Using Prepared Statements [docs.oracle.com]
  6. sqlite3 — DB-API 2.0 interface for SQLite databases [docs.python.org]
  7. Psycopg: Basic module usage [packages.python.org]

10. Latest HTB Security Advisories with CWE-89


Copyright Disclaimer: Any above-mentioned content can be copied and used for non-commercial purposes only if proper credit to High-Tech Bridge is given.

↑ Back to Top