Protect Your Web Applications from SQL Injection

Many web scripting languages are dead simple to learn — get used to the syntax, become familiar with its core features, and you’re off and programming in just a few days. Heck, if you’re already familiar with some basic tenets of programming — variables, arrays, functions, loops, and company — you’ll probably be banging out meaningful code in a few hours. But if you go live without knowing how to make your code secure, you could be opening up a lot more than just your web application to the world.

An SQL injection is an attack on a database-backed application that attempts to exploit programmers who forget to prepare user input for use in queries. The attacker makes a few educated guesses about the format of your database query, and tries to fool your database into executing code he enters into your form fields.

Let’s take a look at a web application that is vulnerable to an SQL injection attack. I like PHP, so I will use it to illustrate my point.

<?php
$article = $_GET['article_id'];
$sql = 'SELECT * FROM articles WHERE article_id = ' . $article;
$result = mysql_query($sql)
  or die(mysql_error());
?>

The author of this code has made a fatal mistake: he trusts the user to submit an integer through the article_id portion of the query string. He expects his SQL query to turn out looking like this:

SELECT * FROM articles WHERE article_id = 3

If I were a malicious hacker, I could easily deduce that the value of the query string parameter article_id is an integer that corresponds to the primary key column of a table that contains the site’s article. It wouldn’t be hard to visualize this select query without actually seeing the site’s source code. I’m also going to take a guess and say that you’re using an obvious name for your articles table, like…. articles.

After figuring this out, I might manually alter the query string so that the value of $_GET['article_id'] is not an integer, but instead: 3; delete from articles;. Now your query looks like this:

SELECT * FROM articles WHERE article_id = 3; delete from articles;

Now, let’s see what happens when this query is executed.

Yeah, I just deleted the entire contents of your table.

How to Guard Against SQL Injection

The simplest advice I can give is that you should, as a programmer, trust no one. A programmer should protect his application against SQL injection by validating every last bit of user input before using it in a query. You expect that ID parameter to have a numerical value? Great. Does it really? Find out, and hit the brakes if it does not. Does a form field contain a string of characters that will end up in some part of a query? Make sure it is properly escaped.

Here is the insecure script I showed you earlier, modified to validate the user input:

<?php
if (is_numeric($_GET['article_id'])) {
  $article = $_GET['article_id'];
  $sql = 'SELECT * FROM articles WHERE article_id = ' . $article;
  $result = mysql_query($sql)
    or die(mysql_error());
} else {
  // Log the event and display an error message.
}
?php>