Beware SQL Injection in Web Applications

by Brian Jepson

When you're developing a web database application, you may need to build your SQL statements on the fly and send them to the database. Consider this Perl example, which queries a database using the zipcode parameter:

my $zipcode = param("zipcode");
my $sql = "SELECT * FROM PEOPLE WHERE zipcode = '$zipcode'";
my $sth = $dbh->prepare($sql);

So, you could search for all the people in the 02881 zipcode with this URL: http://webserver/cgi-bin/search?zipcode=02881. It looks simple and safe enough, but it's easy for a remote user to subvert your intentions. Suppose a remote user sends this URL to your web server: http://webserver/cgi-bin/search?zipcode=02881'+or+'0'='0. Now, $sql looks like this:

WHERE zipcode='02881' or '0' = '0'

This would return all rows from the PEOPLE table, which could be a Bad Thing. Consider this:

  • If the PEOPLE table is very large, this could be a potential denial-of-service attack.
  • If you have a database, such as a company roster, on your website, you'd want to let users query portions of it, but you'd prefer not to let someone download the entire thing.

Whether or not either of these cases apply to your application, the overriding concern should be that you want to stay in control of what code is executed on your server! Other databases, such as Microsoft SQL Server, have powerful stored procedures like xp_cmdshell, which runs command-line programs with the same credentials that the server runs under.

For a good overview of this problem, see the Open Web Application Security Project's page on this topic. It includes examples for various database servers.

One simple solution to this problem is to use parameter binding. This lets you put placeholders in the SQL statement and bind them to a particular parameter. Here is how this example would be rewritten to use parameter binding:

my $zipcode = param("zipcode");
my $sql = "SELECT * FROM PEOPLE WHERE zipcode = ?";
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, $zipcode);

Now when a remote attacker attempts to inject SQL, all the single quotes will be properly escaped, and this is what the statement will look like:

WHERE zipcode='02881'' or ''0''=''0'

In other words, the SELECT statement attempts to match the zipcode to the literal "02881'+or+'0'='0", which turns up no match.

It really gets down to one thing: if you're going to take something the user typed and execute it dynamically (whether through SQL or eval), sanitize the input first!

How are you sanitizing your input?