David S. Read

SQL Injection is commonly given as a  root cause when news sites report about stolen data. Here are a few recent headlines for articles describing data loss related to SQL injection: Hackers steal customer data by accessing supermarket database¹, Hacker swipes details of 4m Pirate Bay users², and Mass Web Attack Hits Wall Street Journal, Jerusalem Post³. I understand that SQL injection is prevalent; I just don’t understand why developers continue to write code that offers this avenue to attackers.

From my point of view SQL injection is very well understood and has been for many years. There is no excuse for a programmer to create code that allows for such an attack to succeed. For me this issue falls squarely on the shoulders of people writing applications. If you do not understand the mechanics of SQL injection and don’t know how to effectively prevent it then you shouldn’t be writing software.

The mechanics of SQL injection are very simple. If input from outside an application is incorporated into a SQL statement as literal text, a potential SQL injection vulnerability is created. Specifically, if a parameter value is retrieved from user input and appended into a SQL statement which is then passed on to the RDBMS, the parameter’s value can be set by an attacker to alter the meaning of the original SQL statement.

Note that this attack is not difficult to engineer, complicated to execute or a risk only with web-based applications. There are tools to quickly locate and attack vulnerable applications. Also note that using encrypted channels (e.g. HTTPS) does nothing to prevent this attack. The issue is not related to encrypting the data in transit, rather, it is about keeping the untrusted data away from the backend RDMBS’ interpretation environment.

Here is a simple example of how SQL injection works. Assume we have an application that accepts a last name which will be used to search a database for contact information. The program takes the input, stores it in a variable called lastName, and creates a query:

String sql = "select * from contact_info where lname = '" + lastName + "'";

Now, if an attacker tries the input of: ‘ or 1=1 or ’2′=’

It will create a SQL statement of:

select * from contact_info where lname = '' or 1=1 or '2'=''

This is a legal SQL statement and will retrieve all the rows from the contact_info table. This might expose a lot of data or possibly crash the environment (a denial of service attack). In any case, using other SQL keywords, particularly UNION, the attacker can now explore the database, including other tables and schemas.

Sometime developers attempt to sanitize or “clean up” the incoming data. This is a valuable technique and should be a part of a defense-in-depth strategy. However, it is vital that the external input never be passed to the database in a way that the database engine could interpret it as part of the DDL or DML. Most modern development environments make this issue quite easy to prevent.

The best rule to establish when coding database interactions is to always use prepared statements. They are no harder to use than direct execution of the SQL and can be used to improve performance if the same query is being used more than once.

In any language that supports prepared statements the technique is always the same. You setup a template statement with placeholders for the dynamic (externally supplied) data. The dynamic portion will be filled-in using bound variables and will not be treated as part of the SQL statement itself. This means that there is no way a carefully crafted input can trick the environment and alter the structure of the SQL statement.

Here is an example of using a prepared statement in PHP:

$stmt = $dbh->prepare("select * from contact_info where name = ?");
if ($stmt->execute(array($_GET['lastName']))) {
  while ($row = $stmt->fetch()) {
  print_r($row);
}

Here is a prepared statement example in Java:

PreparedStatement prepSt =
      con.prepareStatement("select * from contact_info where name = ?");
prepSt.setString(1, lastName);
ResultSet rs = prepSt.executeQuery();
while (rs.next()){
  // Process the data
}

And here is a prepared statement example in C#:

using (SqlCommand myCommand =
    new SqlCommand("select * from contact_data where lname = @LNAME", myConnection)) {
  myCommand.Parameters.AddWithValue("@LNAME", lastName);
  myConnection.Open();
  SqlDataReader myReader = myCommand.ExecuteReader())
  // process data
}

What I hope you notice is that using prepared statements doesn’t add a lot of extra work to writing the program. That is why I don’t believe that there is any excuse for developers to be writing code that is vulnerable to such an attack. In all the examples provided (above) the input (in the variable lastName) will only be used to search the lname column, it will never be interpreted as part of the SQL syntax.

Next time you find yourself writing or maintaining code that connects to a database, please verify that you have done your due diligence by using prepared statements in all cases of database interaction. Doing anything less means that you are part of the problem rather than being part of the solution.

I know that there are other techniques, including the use of stored procedures, which are also used as a mitigation approach. Each technique has it place, but the use of prepared statements can typically be used regardless of other mitigating strategies. Again, it is all about defense-in-depth. Using a prepared statement is a simple yet powerful technique to create more secure software.

I am curious to hear from software authors that believe that they have to write software that is vulnerable to SQL injection. Is there some reason, other than a lack of understanding, that we as members of the software engineering profession continue to produce such vulnerable applications?

¹http://www.japantoday.com/category/crime/view/hackers-steal-customer-data-by-accessing-supermarket-database
²http://www.theregister.co.uk/2010/07/08/pirate_bay_hacked/
³http://www.computerworld.com/s/article/9177904/Mass_Web_attack_hits_Wall_Street_Journal_Jerusalem_Post?taxonomyId=17

Tags: application security, data security, Information Systems, Java, linkedin, mitigation, programming, Security, SQL injection, vulnerability

This entry was posted on Monday, August 23rd, 2010 at 20:57 and is filed under Information Systems, Java, Security, Software Development, Software Security. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.