What Is SQL Injection? Examples, Risks & Prevention Strategies

What is Structured Query Language (SQL) Injection?

The popularity of SQL injection attacks has grown significantly over the years, and implementing relevant mitigation practices will help keep your application off 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 and damages in the millions, earning it the #1 spot in the 2017 OWASP Top 10.

In 2008, one such attack occurred when the Heartland Payment Systems (HPS) was compromised due to an SQL Injection vulnerability, exposing 138 million credit cards and causing $140 million in damage. Secure use of SQL could have prevented this.

SQL injection attacks occur when specially crafted input can cause an application to misinterpret 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,s compromising the integrity of a database and the applications it provisions.

It is possible to cultivate an environment that enables secure coding practices to prevent SQL injection vulnerabilities from making their way into an application. Although this article focuses on outlining technical practices that 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 components of the Software Development Life Cycle (SDLC) that contribute to an application’s ability to defend against SQL Injection attacks and to 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 of the risks, tools, and techniques that can mitigate SQL vulnerabilities is your first and best line of defense. Cultivating secure programming techniques will require a commitment to their implementation throughout the SDLC. Developing security-minded education, planning, testing, and review practices are just a few components of an SDLC that help prevent SQL Injection vulnerabilities from entering 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 be useful to provide feedback on a proposed username, indicating whether it meets the application’s criteria. However, client-side sanitization and validation can be bypassed; as such, server-side solutions should be employed.

Server-side sanitization and input validation ensure that data supplied by the user does not contain characters such as single or double quotes that could modify an SQL query and return data not originally intended in the application’s design. Specifically, validation ensures that user-supplied data satisfies an application’s criteria, while sanitization is the process that modifies user input to meet the criteria established by validation. Combining both results in a scenario where single quotations contained within a user-submitted string are modified or removed during sanitization, then validated, ensuring single quotations are no longer present, satisfying the application's requirements.

Parameterized Queries & Stored Procedures

Stored Procedures & Parametrization

Query parameterization occurs when stored procedures, which are subroutines an application uses to interact with a relational database management system (RDBMS), employ variable binding. Variable binding is a process that requires defining an SQL statement before inserting variables,, allowing for clear delineation of code and user input. Essentially, prepared statements that 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)

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 server-side parameterized queries, and when used with secure coding techniques, can produce equally secure code. Simply put, the construction of a secure prepared statement automatically parametrizes user input. However, the use of dynamically constructed queries should be avoided unless specialized libraries and techniques are used to mitigate gaps in security coverage that may arise. Libraries like opaleye for Haskell and SQL Builder for Python can be used for this. However, if dynamic SQL must be used, proper sanitization and validation of user input will be necessary to safeguard an application.

Prepared Statement Example (Java)

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

The following code example uses a Prepared Statement, Java's parameterized query implementation, to execute the same database query.

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 creating secure SQL code should be your priority, tools can help facilitate the process. SQL-specific static analysis tools, such as 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 that intercepts, analyzes, and discards potentially malicious queries before they reach the database. It should be noted that tools that rely on predefined patterns to identify malicious queries become less effective as their rulesets age and new attacks are discovered.

Final Thoughts

Given the ease of executing SQL injection and its serious consequences, mitigating it should be a priority for all application developers. With numerous defensive techniques and tools available, including stored procedures, parametrization, program analysis, and even black-box tools like OWASP’s Zed Attack Proxy, developers should have no trouble improving the security of their applications and their users against SQL Injection attacks.

Recommended Reading

• Huang, H., Zhang, Z., Cheng, H., and Shieh, S. (2017). Web Application Security: Threats, Countermeasures, and Pitfalls. Computer, 50(6), pp.81-85.
• Mahapatra, R. (2012). A Survey Of Sql Injection Countermeasures. International Journal of Computer Science & Engineering Survey, 3(3), pp.55-74.
• https://en.wikipedia.org/wiki/Stored_procedure
• https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
• https://security.stackexchange.com/questions/15214/are-prepared-statements-100-safe-against-sql-injection
• https://lifars.com/2015/06/the-10-costliest-cyber-attacks-in-the-history-of-internet
• http://download.oracle.com/oll/tutorials/SQLInjection/html/lesson1/les01_tm_ovw3.htm
• https://hackage.haskell.org/package/opaleye

‍