In the previous few days, I had numerous discussion with different people about the use of prepared statements to mitigate SQL injection vulnerabilities. Prepared statements definitely works well as part of the mitigation strategy for SQL injection if implemented properly. I still remember 4-5 years ago when SQL injection just started to become popular, the common mitigation suggested is to use prepared statement as if it is a magic bullet. As we understand the SQL injection problem better, we realize that even prepared statement can be vulnerable to SQL injection as well.

The fundamental problem in SQL injection is concatenation of untrusted data (raw user input) to trusted data and the whole strings is being sent to the backend database for execution. The moment you merge the raw untrusted data to other trusted data for execution, you got a problem.

Look at this prepared statement (Java)

PreparedStatement Stment = con.prepareStatement("SELECT * FROM table WHERE cond = ' + UserInput + ' ");

The UserInput which is raw input from the user is concatenated with the other string to form SQL statement then it is "prepared" for execution in the database. What's wrong here?  Untrusted data is concatenated with static strings and sent to database to execution, no validation whatsoever.... BOOM... SQL injection for ya.

Let's look at another version of this statement

Stment = "SELECT * FROM table WHERE cond = ? ";
PreparedStatement prepSQL = con.prepareStatement(Stment);
prepSQL.setString (1, UserInput);
ResultSet rs = prepSQL.executeQuery();

See the question mark in the first line? That's the character to tell prepared statement mechanism that there are more data coming into this space. Think "fill in the blanks" exercise here, question mark is an empty spot for filling, the setString function just fill a string into that spot. When statement is prepared, validation is performed on the user input, in the case of Java, the JDBC driver escapes the user input properly. Untrusted user input go through validation and become validated input. This type of passing user input to the statement as a parameter is sometimes referred to as parameterized queries.

One risk still remains here.... The implementation of the database driver (or data access mechanism) has to accurately escape the potentially offensive user input. So far, the track record of such mechanism across multiple languages are pretty good.

Extra note here about stored procedures which was regarded as another potential mitigations for SQL injection as well; Both prepared statement and stored procedures can be vulnerable to SQL injection if it is not done properly. Similar to prepared statement, stored procedure can be done in parameterized form to mitigate SQL injection.

Shameless plug - To get more info on web related security issues, SANS offers SEC 519 course on web application security.