Defeating SQL Injection

PERSPECTIVES The best strategy for combating SQL injection, which has emerged as the most widespread website security risk, calls for integrating defensive coding practices with both vulnerability detection and runtime attack prevention methods. S tructured Query Language injection is a code injec-tion technique commonly used to attack websites in which the attacker inserts SQL characters or key-words into a SQL statement via unrestricted user input parameters to change the intended query’s logic.1 This threat exists in any Web application that accesses a data- base via SQL statements constructed with external input data. By manipulating this data to modify the statements, an attacker can cause the application to issue arbitrary SQL commands and thereby compromise the database. The Open Web Application Security Project (OWASP) ranks SQL injection as the most widespread website secu- rity risk (www.owasp.org/index.php/Top_10). In 2011, the National Institute of Standards and Technology’s National Vulnerability Database (nvd.nist.gov) reported 289 SQL injection vulnerabilities (7 percent of all vulnerabilities) in websites, including those of IBM, Hewlett-Packard, Cisco, WordPress, and Joomla. In December 2011, SANS Insti- tute security experts reported a major SQL injection attack (SQLIA) that affected approximately 160,000 websites using Microsoft’s Internet Information Services (IIS), ASP.NET, and SQL Server frameworks (isc.sans.org/diary/SQL+Injection+ Attack+happening+ATM/12127). Inadequate validation and sanitization of user inputs make websites vulnerable to SQL injection, and research- ers have proposed various ways to address this problem, ranging from simple static analysis to complex dynamic analysis. In 2006, William Halfond, Jeremy Viegas, and Alessandro Orso2 evaluated then-available techniques and called for more precise solutions. In reviewing work during Lwin Khin Shar and Hee Beng Kuan Tan, Nanyang Technological University, Singapore the past decade, we found that developers can effectively combat SQL injection using the right combination of state- of-the art methods. However, they must develop a better understanding of SQL injection and how to practically inte- grate current defenses. INSECURE CODING PRACTICES SQL is the standard language for accessing database servers, including MySQL, Oracle, and SQL Server.1 Web programming languages such as Java, ASP.NET, and PHP provide various methods for constructing and executing SQL statements, but, due to a lack of training and develop- ment experience, application developers often misuse these methods, resulting in SQL injection vulnerabilities (SQLIVs). Developers commonly rely on dynamic query building with string concatenation to construct SQL statements. During runtime, the system forms queries with inputs directly received from external sources. This method makes it possible to build different queries based on vary- ing conditions set by users. However, as this is the cause of many SQLIVs, some developers opt to use parameterized queries or stored procedures. While these methods are more secure, their inappropriate use can still result in vul- nerable code. In the PHP code examples below, name and pwd are the “varchar” type columns and id is the “integer” type column of a user database table. Absence of checks. The most common and serious mis- take developers make is using inputs in SQL statements without any checks. The following PHP code is an example of such a dynamic SQL statement: Defeating SQL Injection 0018-9162/13/$31.00 © 2013 IEEE Published by the IEEE Computer Society March 2013 69 70 coMputer PERSPECTIVES $query = “SELECT info FROM user WHERE name = ‘$_GET[“name”]’ AND pwd = ‘$_GET[“pwd”]’”; Attackers can use tautologies to exploit this insecure practice. In this case, by supplying the value x’ OR ‘1’=‘1 to the input parameter name, an attacker could access user information without a valid account because the WHERE- clause condition becomes WHERE name = ‘x’ OR ‘1’=‘1’ AND …”; which the system will evaluate to be true. Insufficient escaping. If a developer escapes special characters meaningful to a SQL parser, the parser will not interpret them as SQL commands. For example, the above tautology-based attack could be prevented by escaping the ’ character (to avoid its being interpreted as a string delimiter) from the inputs. However, many developers are either not aware of the full list of characters that have spe- cial meanings to the SQL parser or they are not familiar with the proper usage patterns. Consider the following PHP code, mysql_real_escape_ string, which is a function used to escape MySQL special characters: $name = mysql_real_escape_string($_GET[“name”]); $query = “SELECT info FROM user WHERE pwd LIKE ‘%$pwd%’”; The function mysql_real_escape_string would protect SQL statements that do not use pattern-matching database operators such as LIKE, GRANT, and REVOKE. In this case, however, an attacker could include the additional wildcard characters % and _ in the password field to match more password characters than the beginning and end characters because mysql_real_escape_string does not escape wildcard characters. Absence of data type checks. Another error that develop- ers make is failing to check data types before constructing SQL statements. Instead, they often apply programming language or database-provided sanitization functions such as addslashes and mysql_real_escape_string to the input parameters before using them in SQL statements. However, when the query is to access the database columns of numeric data and other non-text-based data types, a SQLIA need not contain the escaped/sanitized characters. For example, the following PHP code shows a SQL statement for which a tautology-based attack could be conducted by supplying the value 1 OR 1=1 to the param- eter id: $id = mysql_real_escape_string($_GET[“id”]); $query = “SELECT info FROM user WHERE id = $id”; For such queries, instead of escaping characters, devel- opers should use a data type check—for example, if(is_numeric($id))—to prevent SQLIAs. Absence or misuse of delimiters in query strings. When constructing a query string with inputs, a programmer must use proper delimiters to indicate the input’s data type. The absence or misuse of delimiters could enable SQL injection even in the presence of thorough input validation, escaping, and type checking. For example, the following PHP code does not include delimiters to indicate the input string used in the SQL statement: $name = mysql_real_escape_string($_GET[“name”]); $query = “SELECT info FROM user WHERE name = $name”; In this case, when the database server has the automatic type conversion function enabled, an attacker could use an alternate encoding method that circumvents input sani- tization routines. For instance, if the attacker supplies the encoded HEX string 0x270x780x270x200x4f0x520x200x 310x3d0x31 to the parameter name, the database parser may convert it to the “varchar” value, resulting in the tau- tology string ‘x’ OR 1=1. Because the conversion occurs in the database, the server program’s escaping function would not detect any special characters encoded in the HEX string. Improper parameterized queries or stored procedures. Most developers believe that SQL injection is impossible when using parameterized queries or stored procedures to run SQL statements. Although this is generally true, some developers are not aware that SQL injection is still possible if parameterized query strings or stored procedures accept nonparameterized inputs. Consider, for example, the following PHP code: $query = “SELECT info FROM user WHERE name = ?”.“ORDER BY ‘$_GET[“order”]’”; $stmt = $dbo->prepare($query); $stmt->bindParam(1, $_GET[“name”]); $stmt->execute(); Although an attacker could not conduct a SQLIA through the parameter name, SQL injection is still possible through order, which is not parameterized. An attacker could inject piggy-backed query attacks—malicious queries attached to the original query—such as ASC; DROP TABLE user; -- into the parameter order. SQL INJECTION DEFENSES SQL injection defense methods can be broadly classified into three types: defensive coding, SQLIV detection, and SQLIA runtime prevention. Table 1 compares the strengths and weaknesses of various approaches in each category. March 2013 71 Table 1. Comparison of SQL injection defenses. Defense type Defense User involvement Vulnerability locating Verification assistance Code modifi- cation Generate test suite Usage stage Infra- structure Defensive coding Manual defensive coding practices Very high No No Manual No Develop- ment Developer training SQL DOM High No No Manual No Develop- ment Developer training Parameter- ized query insertion Medium No No Auto- mated No Testing and debugging Tool for code replacement SQLIV detection SQL- UnitGen Medium Automated Unit test reports No Yes Testing and debugging Static analysis tool MUSIC Very high Manual inspection Test inputs that expose the weaknesses of implemented defense mechanisms Manual Yes Testing and debugging Manual tests Vulnerabil- ity and attack injection Low Manual inspection Test inputs that expose the weaknesses of implemented defense mechanisms Auto- mated Yes Testing and debugging Injection tool SUSHI Low Automated Path conditions that lead to SQLIVs Auto- mated Yes Testing and debugging Symbolic execution engine Ardilla Low Automated Concrete attacks Auto- mated Yes Testing and debugging Concolic exe- cution engine String analyzer Medium Automated Static dataflow traces No No Code verification Static string analysis tool PhpMinerI Low Automated Statistics of sani- tization methods implemented No No Code verification Static analysis and data mining tool Runtime SQLIA preven- tion SQLrand High No No Manual No Deploy- ment Runtime checker AMNESIA Low No Static dataflow traces Auto- mated No Deploy- ment Static analy- sis tool and runtime checker SQLCheck Low No No No No Deploy- ment Runtime checker WASP Low No No Auto- mated No Deploy- ment Instrumenta- tion tool and runtime checker SQLProb High No No No No Deploy- ment Runtime checker CANDID Low No No Auto- mated No Deploy- ment Instrumenta- tion tool and runtime checker 72 coMputer Developers could overcome the shortcomings of individual methods by combining schemes, as Figure 1 shows. Defensive coding Defensive coding is a straightforward solution, as SQLIVs are the direct consequence of developers’ insecure coding practices. Manual defensive coding practices. Many secu- rity reports, such as OWASP’s SQL Injection Prevention Cheat Sheet (http://owasp.org/index.php/SQL_Injection_ Prevention_Cheat_Sheet) and Chris Anley’s white paper,1 provide useful manual defensive coding guidelines. Parameterized queries or stored procedures. Replacing dynamic queries with properly coded parameterized que- ries or stored procedures would force developers to first define the SQL code’s structure before including param- eters to the query. Because parameters are bound to the defined SQL structure, it is not possible to inject additional SQL code. Escaping. If dynamic queries cannot be avoided, escaping all user-supplied parameters is the best option. However, as insufficient or improper escaping practices are common, developers should identify all input sources to realize the parameters that need escaping, follow database-specific escaping procedures, and use standard escaping libraries instead of custom escaping methods. Data type validation. In addition to escaping, develop- ers should use data type validation. Validating whether an input is string or numeric could easily reject type- mismatched inputs. This could also simplify the escaping process because validated numeric inputs need no further cleansing action and could be safely used in queries. White list filtering. Develop- ers often use black list filtering to reject known bad special char- acters such as ’ and ; from the parameters to avoid SQL injec- tion. However, accepting only inputs known to be legitimate is safer. This filtering approach is suitable for well-structured data such as email addresses, dates, zip codes, and Social Security numbers. Developers could keep a list of legitimate data patterns and accept only matching input data. SQL DOM. Although manual defensive coding practices are the best way to defeat SQL in- jection, their application is labor- intensive and error-prone. To alleviate these problems, Russell McClure and Ingolf Krüger3 created SQL DOM, a set of classes that enables automated data type validation and escaping. Developers provide their own database schema and construct SQL statements using its APIs. SQL DOM is especially useful when developers need to use dynamic queries instead of parameterized queries for greater flexibility. However, they can only use it with new software projects, and they must learn a new query- development process. Parameterized query insertion. An automated vul- nerability removal approach finds potentially vulnerable (dynamic) SQL statements in programs and replaces them with parameterized SQL statements.4 For example, this approach would replace the PHP code $rs = mysql_query(“SELECT info FROM user WHERE id = ‘$id’); with the following code: $dbh=new PDO(“mysql:host=xxx;dbname=xxx;”,“root”, “pwd”); $PSinput00[] = Array(); $PSquery00 = “SELECT info FROM user WHERE id = ?”; $PSInput00[] = $id; $stmt = $dbh->prepare($query); $i = 1; foreach($PSinput00 as $input){ $stmt->bindParam($i++, $input); } $rs = $stmt->execute(); Figure 1. Web application developers could overcome the shortcomings of individual SQL injection methods by combining various schemes. Code Potentially vulnerable code Deployment Code Code veri�cation Vulnerability prediction Predicted vulnerable code sections Development Testing and debugging Test cases Con�rmed vulnerabilities Defensive coding practices Vulnerability detection Runtime attack prevention Attack vector generation Vulnerability removal Vulnerability testing PERSPECTIVES March 2013 73 A shortcoming of this method is that it only works on SQL structures built with explicit strings; developers must incorporate program analysis techniques to deduce SQL structures built with data objects or through function calls. SQLIV detection Researchers have developed several methods to detect SQLIVs. Code-based vulnerability testing. This approach gener- ally aims to generate an adequate test suite for detecting SQLIVs. However, it does not explicitly find vulnerable pro- gram points, necessitating manual inspection. SQLUnitGen5 is a prototype tool that uses static analysis to track user inputs to database access points and generate unit test reports containing SQLIA patterns for these points. MUSIC (mutation-based SQL injection vulnerability checking)6 uses nine mutation operators to replace origi- nal queries in a Web program with mutated queries. José Fonseca, Marco Vieira, and Henrique Madeira7 developed a tool that automatically injects SQLIVs into Web programs and generates SQLIAs. Both tools assess the effectiveness of the security mechanisms implemented in the application under test based on the injected mutants/vulnerabilities detected. Concrete attack generation. This type of approach uses state-of-the-art symbolic execution techniques to automati- cally generate test inputs that actually expose SQLIVs in a Web program. Symbolic execution generates test inputs by solving the constraints imposed on the inputs along the path to be exer- cised. Traditionally, symbolic-execution-based approaches use constraint solvers that only handle numeric operations. Because inputs to Web applications are by default strings, if a constraint solver can solve myriad string operations applied to inputs, developers could use symbolic execution to both detect the vulnerability of SQL statements that use inputs and generate concrete inputs that attack them. Xiang Fu and Chung-Chih Li developed a vulnerability detection tool consisting of JavaSye, a symbolic execution engine, and SUSHI, a powerful hybrid (numeric and string) constraint solver.8 SUSHI solves path conditions that lead to SQL statements and extracts test inputs containing SQLIAs from the solution pool. As the “SUSHI Constraint Solver” sidebar describes, if the tool generates such a test input, the corresponding SQL statement is vulnerable. Although effective, symbolic execution alone is gener- ally not scalable to large programs due to path explosion. Researchers have thus proposed various solutions to improve code coverage. Ardilla9 incorporates concrete exe- cution into symbolic execution, using randomized concrete test inputs to exercise program paths that constraint solv- ers cannot symbolically solve. SWAT10 uses a search-based algorithm that formulates test input adequacy criteria as fitness functions. It uses these functions to compare pooled SUSHI Constraint Solver c onsider the following snippet of vulnerable PHP code: 1 $id = addslashes($_COOKIE[“id”]); 2 $query = “SELECT info FROM user WHERE ”; 3 if($id!=null) { 4 $query .= “id = $id”; //path 1 } else { 5 $name = addslashes($_GET[“name”]); 6 $order = addslashes($_GET[“order”]); 7 $query .= “name = ‘$name’ ORDER BY ‘$order’”; //path 2 } 8 $rs = mysql_query($query); The function addslashes() escapes string delimiters. Therefore, a SQL injection attack is not possible through path 2 because a single-quote string delimiter is required to cancel out the delimiters ($name) used in the query; however, a SQLIA is possible through path 1 because the cookie parameter id is an integer type and thus a string delimiter is not required to create an attack. X c→\c is the symbolic expression of the addslashes() function— which escapes the four SQL special characters ’, ”, \, and \0—performed on id. When the symbolic execution engine reaches the query execution statement at line 8, SUSHI constructs and solves the following constraint, a conjunction of two string equations in which + represents string concate- nation and ≡ separates the left- and right-hand sides of the equations: !(X c→\c ≡ null) ^ (“id = ” + X c→\c ≡ id = [0-9]* OR 1=1 --) The second equation asks: Is it possible to obtain a solu- tion for X such that the string on the left-hand side is matched by the regular expression on the right-hand side? If yes, the query structure constructed with the string on the left-hand side is vulnerable because the regular expression on the right-hand side is a representation of a tautology attack (SUSHI maintains a set of regular expressions that represent different types of attack patterns). In this case, SUSHI clearly has a solution for this constraint, thereby detecting an SQL injection vulnerability: 1 → $id: X c→\c Path Condition: true 2 → $id: X c→\c $query: “SELECT …” Path Condition: true 3 → $id: X c→\c $query: “SELECT …” Path Condition: X c→\c =null 4 → $id: X c→\c $query: “SELECT …”.“id = X c→\c ” Path Condition: X c→\c !=null 8 → Database access point found, constraint solver is invoked! 74 coMputer $query .= “name = ‘$name’ ORDER BY ‘$order’”; //path 2 } $rs = mysql_query($query); Running PhpMinerI on this code would produce the follow- ing vulnerability predictor in the form of a classification tree: sql_sanit < 0 : Vulnerable sql_sanit ≥ 0 |