Aug 15, 17 6:31 pm

Was this post helpful?

Introduction to SQL Injection Mitigation

Aug 15, 2017
| by:
Sherif Koussa

An article with updates to the 2021 OWASP Top 10 and how it affects your application can be found here.

What is SQL Injection?

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

SQL Injection Mitigation Strategies

Secure Coding & SDLC

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.

Input Validation & Sanitation

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.

Stored Procedures & Parametrization

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

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( );

Program Analysis Techniques & Proxies

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.

Final Thoughts

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.

Recommended Reading

Was this post helpful?

About the Author

Sherif Koussa
Sherif Koussa is OWASP Ottawa Chapter Co-Leader, Software Developer, Hacker, and founder and CEO of Software Secured and Reshift. In addition to contributing to OWASP Ottawa for over 14 years, Sherif contributed to WebGoat, and OWASP Cheat Sheets. Sherif also helped the SANS and GIAC organizations launch their GSSP-Java and GSSP-NET exams and contributed to a few of their courses. After switching from the software development field to the security field, Sherif took on the mission of supporting developers shifting security left, and ship more secure code organically.
Share This Post

Leave a Reply

Your email address will not be published.

Related Post

May 17, 2023 by Omkar Hiremath

Risk of Security and Monitoring Logging Failures

Read more

Was this post helpful?

May 1, 2023 by Omkar Hiremath

Intro to Identification and Authentication Failures

Read more

Was this post helpful?

Dec 22, 2022 by Warren Moynihan

3 Types of XSS Attacks & 4 XSS Mitigation Strategies

Read more

Was this post helpful?

Office

301 Moodie Dr. Unit 108
Ottawa ON K2H 9C4

Designed by WP Expert
© 2023
Software Secured
cross