Secure Coding 101: Remediating SQL Injection Vulnerabilites

Injection is the first item in the OWASP Top 10 list for good reasons: We still find very often that developers concatenate user input into SQL queries (which is the basis for SQL injection) and the resulting risks are often critical. If the prevention of SQL Injection is not addressed early in the development life cycle, fixing the issues can be very time consuming and - if not done properly - fixes might be incomplete and prone to more sophisticated attacks.

The following examples show how SQL queries should be implemented as well as common mistakes that should be avoided. 

Note: This article presupposes that the reader is familiar with the technical details of SQL injection attacks. For those who want to learn more about the attack, we suggest reading the attack description on OWASP.

Prepared Statements

Usage of prepared statements has two main advantages:

• They are used to execute the same (or similar) SQL statements repeatedly with high efficiency.

• The SQL query is prepared with a question mark used to substitute with a value that will not be interpreted as SQL command.

If prepared statements are used correctly, a clear separation between the SQL query and the data that is used to assemble the query can be achieved and SQL Injection will therefore be impossible, as can be seen in the screenshot below. 

Nevertheless, we sometimes see prepared statements being mixed with string concatenation, often to extend the existing SQL query due to a hastily implemented change request, which again results in SQL injection. Have a look at the following example, which is a bad practice and should be avoided. 

A more detailed overview including best practice code snippets for Prepared Statements of different programming languages like Java and .Net can be found in the OWASP SQL Injection Prevention Cheat Sheet

Stored Procedures

Using stored procedures achieves the same result as prepared statements, which is a separation of data from the SQL query syntax. The usage of Stored Procedures also achieves an encapsulation of (critical) database queries into the database itself. Therefore, a clear separation can be realized between the backend and the application, which adds an additional layer of defence. A typical stored procedure for MySQL that is not prone to SQL injection might look like this:

CREATE PROCEDURE sp_InsertMovie(
    IN title varchar(50), 
    IN genre varchar(50),
    IN imdb varchar(50)
)
BEGIN 
    INSERT INTO movies (title, genre, imdb) 
    Values (title, genre, imdb); 
END 

A common mistake when using stored procedure is to mix concatenation into the stored procedure using functions like CONCAT(). The Stored Procedures would therefore again be prone to SQL injection and this mixture should be avoided.

Validation of User Input

With correctly implemented Stored Procedures and/or Prepared Statements it shouldn't be possible for an attacker to modify the syntax of the SQL query. But what if there is no validation and sanitization of the input? Only some data types like INT (integer) are actually validating for numeric input. But what about char or varchar and what if the query is not a SELECT, but an INSERT or UPDATE statement?

If data coming from the user is saved persistently as varchar into the database and used somewhere else in the backend this might still be a problem and lead to 2nd order SQL injection attacks. We often observe in code reviews that best practices are applied consistently for untrusted data that is coming from the client side, but data coming from the database queries is implicitly trusted and concatenated into SQL queries. 2nd order SQL injection is a bit more difficult and time consuming to exploit, but definitely within the capabilities of an average attacker.

Be aware that all data outside the application should be treated as untrusted.

TL/DR;

Always use prepared statements in order to prevent SQL injection vulnerabilities. Don't trust any external data sources - even data from the backend database should be treated with care.

Are you ready for a challenge?

Practice preventing injection attacks by solving the injection challenges on Bugrank. No worries, it's completely free :)

OWASP Top 10 - Level 1

OWASP Top 10 - Level 3