Item #2 in this year’s Top 25 is CWE-89 [1].  It is officially called Improper Sanitization of Special Elements used in an SQL Command (‘SQL Injection’).

There are many public examples that show the devastating impact that SQL Injection can have including the Mass SQL Injection attacks that began in 2008 [2,3,4] as well as the recent SQL Injection vulnerability in RockYou that led to the disclosure of 32 million plaintext passwords [5,6].

SQL Injection occurs when your SQL statements are created dynamically without appropriate encoding or escaping.  Despite its prevalence, SQL Injection is very easy to address at a code level.  One of the best ways to prevent SQL Injection is to make sure that you use parameterized queries correctly.  This post uses Java for illustration purposes.  The PreparedStatement class is Java’s implementation of a parameterized query.  You can use it in the following manner to prevent SQL Injection:

// GOOD code that prevents SQL Injection
String query = "SELECT id FROM users WHERE userid = ?";
PreparedStatement stmt = con.prepareStatement(query);
query.setString(1, userid);
ResultSet rs = stmt.executeQuery();

However, it is important to keep in mind that a PreparedStatement can still be used incorrectly.  The following code creates a SQL statement using string concatenation and does not use bind variables.

// BAD code that is still vulnerable to SQL Injection
String query = "SELECT id FROM users WHERE userid = '" + userid + "'";
PreparedStatement stmt = con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();

Additionally, to ensure that the PreparedStatement class prevents SQL Injection, you must be using a correctly implemented JDBC driver.  A buggy driver can negate the benefits of correctly using a PreparedStatement.  Fortunately, I don’t know of any such bugs in the major JDBC drivers that are being used but would love to hear about it if you have.

In some special cases you may not want to use a PreparedStatement at all.  In those cases you can manually encode the input for your specific database.  Fortunately, ESAPI [7] provides an encodeForSQL method in the Encoder class that can be used for just this purpose.  However, PreparedStatements are the preferred approach since manually doing this encoding may open you up to potential attacks.  Chris Shiflett had a great post about multi-byte characters, character encoding, and escaping for MySQL that still allowed SQL Injection [8].

If you happen to be using an object/relational mapping tool like Hibernate you must still take care not to create your queries dynamically.  Instead of creating your Hibernate queries using string concatenation you must create them using Hibernate’s binding syntax (i.e. using a colon like :value).

Stored procedures are also vulnerable to SQL Injection if they are not coded correctly.   In Java, stored procedures are called using the CallableStatement class.  It may appear that you’re not creating dynamic queries in your Java code, but if the backend stored procedure code is via an “execute” call, then you’re still vulnerable to SQL Injection.

In addition to writing secure code as we’ve described you should also limit the privileges that are assigned to the account which is being used to connect to the database.  You can even do all your database access from stored procedures and only grant your application database account access only to these stored procedures.  That way, an attacker will only ever have access to call the stored procedures themselves.  However, in many large organizations DBAs control stored procedure code and it may not be convenient for developers who are in a separate organization to depend on an external party to maintain their database queries.

[1] http://cwe.mitre.org/top25/#CWE-89
[2] http://blogs.zdnet.com/security/?p=1059
[3] http://securitylabs.websense.com/content/Alerts/3070.aspx
[4] http://www.f-secure.com/weblog/archives/00001427.html
[5] http://www.techcrunch.com/2009/12/14/rockyou-hacked/
[6] http://www.techcrunch.com/2009/12/14/rockyou-hack-security-myspace-facebook-passwords/
[7] http://www.owasp.org/index.php/Category:OWASP_Enterprise_Security_API
[8] http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string

Item #2 in this year’s Top 25 is CWE-89 [1].  It is officially called Improper Sanitization of Special Elements used in an SQL Command (‘SQL Injection’).

There are many public examples that show the devastating impact that SQL Injection can have including the Mass SQL Injection attacks that began in 2008 [2,3,4] as well as the recent SQL Injection vulnerability in RockYou that led to the disclosure of 32 million plaintext passwords [5,6].

SQL Injection occurs when your SQL statements are created dynamically without appropriate encoding or escaping.  Despite its prevalence, SQL Injection is very easy to address at a code level.  One of the best ways to prevent SQL Injection is to make sure that you use parameterized queries correctly.  This post uses Java for illustration purposes.  The PreparedStatement class is Java’s implementation of a parameterized query.  You can use it in the following manner to prevent SQL Injection:

// GOOD code that prevents SQL Injection
String query = "SELECT id FROM users WHERE userid = ?";
PreparedStatement stmt = con.prepareStatement(query);
query.setString(1, userid);
ResultSet rs = stmt.executeQuery();

However, it is important to keep in mind that a PreparedStatement can still be used incorrectly.  The following code creates a SQL statement using string concatenation and does not use bind variables.

// BAD code that is still vulnerable to SQL Injection
String query = "SELECT id FROM users WHERE userid = '" + userid + "'";
PreparedStatement stmt = con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();

Additionally, to ensure that the PreparedStatement class prevents SQL Injection, you must be using a correctly implemented JDBC driver.  A buggy driver can negate the benefits of correctly using a PreparedStatement.  Fortunately, I don’t know of any such bugs in the major JDBC drivers that are being used but would love to hear about it if you have.

In some special cases you may not want to use a PreparedStatement at all.  In those cases you can manually encode the input for your specific database.  Fortunately, ESAPI [7] provides an encodeForSQL method in the Encoder class that can be used for just this purpose.  However, PreparedStatements are the preferred approach since manually doing this encoding may open you up to potential attacks.  Chris Shiflett had a great post about multi-byte characters, character encoding, and escaping for MySQL that still allowed SQL Injection [8].

If you happen to be using an object/relational mapping tool like Hibernate you must still take care not to create your queries dynamically.  Instead of creating your Hibernate queries using string concatenation you must create them using Hibernate’s binding syntax (i.e. using a colon like :value).

Stored procedures are also vulnerable to SQL Injection if they are not coded correctly.   In Java, stored procedures are called using the CallableStatement class.  It may appear that you’re not creating dynamic queries in your Java code, but if the backend stored procedure code is via an “execute” call, then you’re still vulnerable to SQL Injection.

In addition to writing secure code as we’ve described you should also limit the privileges that are assigned to the account which is being used to connect to the database.  You can even do all your database access from stored procedures and only grant your application database account access only to these stored procedures.  That way, an attacker will only ever have access to call the stored procedures themselves.  However, in many large organizations DBAs control stored procedure code and it may not be convenient for developers who are in a separate organization to depend on an external party to maintain their database queries.

[1] http://cwe.mitre.org/top25/#CWE-89
[2] http://blogs.zdnet.com/security/?p=1059
[3] http://securitylabs.websense.com/content/Alerts/3070.aspx
[4] http://www.f-secure.com/weblog/archives/00001427.html
[5] http://www.techcrunch.com/2009/12/14/rockyou-hacked/
[6] http://www.techcrunch.com/2009/12/14/rockyou-hack-security-myspace-facebook-passwords/
[7] http://www.owasp.org/index.php/Category:OWASP_Enterprise_Security_API
[8] http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string