Menu
close
An article with updates to the 2021 OWASP Top 10 and how it affects your application can be found here.
The popularity of Structured Query Language (SQL) injection attacks has grown significantly over the years and employing relevant mitigation practices will help your application from being added to a growing list of insecure applications implicated in significant data breaches. Despite its release nearly 30 years ago, SQL injection has been responsible for millions of lost records with damages also in the millions, earning itself the #1 rung in the 2017 OWASP Top 10. In 2008 one such attack occurred when the Heartland Payment System (HPS) was compromised as a result of an SQL Injection vulnerability which exposed 138 million credit cards and caused $140 million in damages. Secure use of SQL could have prevented this.
SQL injection is an attack that occurs when specifically constructed input can provoke an application into misconstructing a database command, resulting in unforeseen consequences. Those consequences can include the circumvention of authentication and authorization mechanisms allowing the attack to add, modify, delete, and retrieve records compromising the integrity of a database and the applications it provisions.
Cultivating an environment which enables secure coding practices that prevent SQL injection vulnerabilities from making their way into an application is possible. Although this article focuses on outlining the technical practices which can prevent injection attacks, developing an organization-wide security minded culture should also be an objective. Education, design, and code review are just a few of the components within the Software Development Life Cycle (SDLC) that will contribute to an application’s ability to successfully defend itself from SQL Injection attacks and its overall success
Security driven programming practices will always be the best defense against SQL Injection attacks. Ensuring developers are aware the the risks, tools, and the techniques which can mitigate SQL vulnerabilities is your first and best line of defense. However, cultivating the use of secure programming techniques will also require a commitment to their implementation throughout the SDLC. Developing security minded education, planning, testing, and review practices are just a few components within an SDLC that will help prevent SQL Injection vulnerabilities from making their way into your application.
Client side input sanitization and validation should only be considered a convenience for the end user, improving their user experience. For example, it could prove useful to provide feedback on a proposed username, indicating whether or not it will meet the application’s criteria. However, client side sanitization and validation can be bypassed, and as such, server side solutions should be employed.
Server side sanitization and input validation ensures data supplied by the user does not contain characters like single or double quotes that could modify an SQL query and return data not originally intended in the application’s design. Specifically, validation makes sure that user supplied data satisfies an application’s criteria while sanitization refers to the process which modifies user input in order to satisfy the criteria established by validation. Combining both results in a scenario where single quotations contained within a user submitted string are modified or removed as a result of sanitization and then validated ensuring single quotations are no longer present satisfying the application's requirements.
Query parameterization occurs when stored procedures, defined as sub-routines an application uses to interact with a relational database management system (RDBMS), employ variable binding. Variable binding is a process which requires the definition of an SQL statement prior to the insertion of variables allowing for a clear delineation of code and user input. Essentially, prepared statements which parametrize queries protect the intent of an SQL query. For example, if an attacker were to submit the string ' OR '1'='1 a prepared statement would literally attempt to match the string ' OR '1'='1 to a field in the database, rather than evaluating the boolean expression.
// Stored Procedure Example (Java)
// Source: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
String custname = request.getParameter("customerName"); // This should REALLY be validated
try {
CallableStatement cs = connection.prepareCall("{call sp_getAccountBalance(?)}");
cs.setString(1, custname);
ResultSet results = cs.executeQuery();
// … result set handling
} catch (SQLException se) {
// … logging and error handling
}
Prepared statements are essentially serverside parameterized queries and when used with secure coding techniques, can produce equally secure code. Simply, the construction of a secure prepared statement results in the automatic parametrization of user input. However, the use of dynamically constructed queries should be avoided unless special libraries and techniques are used to protect against gaps in security coverage that might emerge. Libraries like opaleye for Haskell and SQL Builder for Python can be used to this effect. However, if dynamic SQL must be used proper sanitization and validation of user input will be necessary to safeguard an application.
The following code example uses a PreparedStatement, Java's implementation of a parameterized query, to execute the same database query.
// Prepared Statement Example (Java)
// Source: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
String custname = request.getParameter("customerName"); // This should REALLY be validated too
// perform input validation to detect attacks
String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, custname);
ResultSet results = pstmt.executeQuery( );
Although the creation of secure SQL code should be your first priority, there exist tools that can facilitate the process. SQL specific static analysis tools like SQL Code Guard (https://www.red-gate.com/products/sql-development/sql-code-guard/) can analyze a database and its queries against a set of rules to reveal vulnerabilities. Other tools like SQLProb operate between an application and its database acting as a proxy which intercepts, analyzes, and discards potentially malicious queries before they reach the database. It should be noted that tools which rely on predefined patterns to identify malicious queries become less effective as their rulesets age and new attacks are discovered.
With the relative ease of executing SQL injection and their compelling consequences, their mitigation should be a priority for all application developers. With numerous defensive techniques and tools available in the form of stored procedures, parametrization, program analysis techniques, and even black box tools like OWASP’s Zed Attack Proxy, developers should have no trouble improving the security of their application and its users against SQL Injection attacks.
301 Moodie Dr. Unit 108
Ottawa ON K2H 9C4
