SQL Injection [CWE-89]

SQL Injection is a weakness that is caused by improper neutralization of special elements used in an SQL query.

Created: September 11, 2012
Latest Update: December 29, 2020

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. Common Fix Errors and Bypasses
10. References
11. Latest Related Security Advisories

Want to have an in-depth understanding of all modern aspects of
SQL Injection [CWE-89]? Read carefully this article and bookmark it to get back later, we regularly update this page.

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.

Modern SQL injections are being used to spread malware, they can be leveraged to turn innocent websites into drive-by-download sites, which will serve malware to unsuspecting visitors.

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:

• CAPEC-7: Blind SQL Injection
• CAPEC-66: SQL Injection
• CAPEC-108: Command Line Execution through SQL Injection
• CAPEC-109: Object Relational Mapping Injection
• CAPEC-110: SQL Injection through SOAP Parameter Tampering
• CAPEC-470: Expanding Control over the Operating System from the Database

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:

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

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

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

SQL injection influences confidentiality, integrity and availability of application it should be scored as C:H/I:H/A:H. The common CVSS score for SQL injection vulnerabilities in publicly accessible scripts is:
9.8 [CVSS:3.0/AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:H/A:H] – Critical severity.

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:

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:

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:

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

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:

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 / database	MySQL	PostgreSQL	Oracle / Microsoft SQL
PHP	mysqli_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():

PERL

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

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:

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

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:

Supplying values for PreparedStatement parameters and executing query:

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

NodeJS

Prepared statements can be used, an example being with the mysql NPM module.

Example:

var sql = "SELECT * FROM table WHERE userid = ?";
var inserts = [message.author.id];
sql = mysql.format(sql, inserts);

For more information please refer to the mysql NPM module GitHub page:

https://github.com/mysqljs/mysql#preparing-queries

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}'"

As a temporary solution to block a known SQL injection attack vector you can use the following universal ModSecurity rule that allows only digits and letters in the vulnerable parameter <PARAM>: SecRule ARGS:<PARAM> !^([a-zA-Z0-9]+)$ "block,phase:2,msg:'Possible SQL Injection attack'"

9. Common Fix Errors and Bypasses

Naive filtering mechanisms can be bypassed with a clever combination of good DBMS knowledge and obfuscation techniques. Some common, basic examples of filtering attempts are shown below, along with potential techniques an attacker may use to work around them. We will use MySQL syntax as an example.

Keyword Filtering

A common restriction, developers may choose to sanitize or block certain keywords via a regex that may have a reputation for being used in SQL injections. Below are some common bypasses.

Encoding

Before data reaches the web application, it may go through appliances such as proxies, firewalls, load balancers, etc. Depending on how each intermittent appliance decodes the input, it may be possible to use encoding tricks to fool the process. An example is below.

Double URL Encoding

If data is being decoded once, due to the double encoded payload it may not notice any characters it deems malicious. The second time a decode happens, the underlying payload may be processed:

%25%35%35%25%34%65%25%34%39%25%34%66%25%34%65

Mixed Cases

If a basic defense is not checking the case of the keyword, then by simply providing it in mixed case may fool the implementation. For example:

UNioN ALL SeleCT * FROM …

Alternate Keywords

Due to the different variations of SQL Injections, if one keyword that is assumed necessary for an attack to be successful is blacklisted, then others can step up to replace them, only delaying a successful attack. An example is below:

WHERE – Used to define the returned database entries, if blocked, then HAVING can be used, as demonstrated below:

...FROM INFORMATION_SCHEMA.TABLES HAVING…..

HAVING/GROUP BY – These keywords are commonly used to control the output of returned results. If both are blocked, an alternative technique is by using a function such as GROUP_CONCAT:

...UNION SELECT GROUP_CONCAT(condition) FROM table_name...

AND/OR

If the AND/OR keywords are not permitted, the Boolean operators && and || often have success. If these are too filtered, then an attacker can use UNION.

...WHERE id=1 || ...

Whitespace

Very commonly used, it will however be bypassed by a knowledgeable attacker. One such approach is by using parenthesis:

...UNION(SELECT(*) …

Common keyword combinations

A regular expression may filter two important keywords being used together, such as UNION SELECT. A few bypasses follow:

...UNION ALL SELECT… ...UNION DISTINCT SELECT…

10. 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]
8. mysql NPM Module [github.com]

11. SQL Injection Vulnerabilities, Exploits and Examples

• HTB23301: SQL Injection in GLPI
• HTB23286: SQL Injection in SocialEngine
• HTB23298: Multiple Vulnerabilities in CubeCart
• HTB23296: SQL Injection and RCE in WebsiteBaker
• HTB23287: SQL Injection in Osclass

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

↑ Back to Top