One of the most obvious vulnerabilities of web sites that interact with a database is the SQL injection attack. What’s that you ask? Suppose your site allows visitors to enter information that is used to generate a database query. A common example would be for the user to enter a password. A hacker will attempt to enter unexpected values that will cause unexpected SQL statements to be executed.

I won’t delve into much more detail because the concept is explained pretty thoroughly on Wikipedia’s SQL Injection page. What I do want to do is distill some of that information into what has been most useful to me as a web developer.

The input you receive from the user is probably either a string or a number. When dealing with a string, it’s important to make sure the input provided does not contain characters (such as a single quote) that allows part of the input to be treated as SQL query text instead of just a parameter. It’s actually less complicated than that sounds.

I’ll use PHP for the following examples, but the concepts are language agnostic. Consider this query:

$sql = “ select * from order_history where username = ‘$userinput’ ”;

Assuming the user enters a valid username, there’s no problem. What happens if a user enters the text a’ or ‘a’ = ‘a? Then the query sent to the database would look like this:

$sql = “ select * from order_history where username = ‘a’ or ‘a’ = ‘a’ ”;

That query would return every record in the table order_history. It doesn’t stop with just viewing records. An SQL injection vulnerability allows an unscrupulous user to make any changes to your data that your database permissions allows. In many cases, that includes inserting, updating, and deleting data.

The defense is to escape special characters (such as a single quote). PHP has a predefined function for this, but you could also use your language’s string replace function to change all single quotes to escaped single quotes. Let’s adjust the example we used above:

$sql = “ select * from order_history where username = ‘” .
mysql_real_escape_string($userinput) .
”’ ”;

//or//

$sql = “ select * from order_history where username = ‘” .
str_replace(“’”, ”\’”, $userinput) .
”’ ”;

The resulting query sent to the database would not return any records (unless you had a user with this particularly eccentric username):

$sql = “ select * from order_history where username = ‘a\’ or \‘a\’ = \‘a’ ”;

That protects your strings, but what about numeric input?

$sql = “ select * from order_history where order_id = $userinput ”;

What if instead of just the number 4, your user entered 4; delete * from order_history? Escaping the single quotes isn’t going to change anything. What you can do is verify that the input is a number. It doesn’t matter whether you do this via the database or your application code. I prefer to do it in my code for consistency, but the choice is up to you.

if ( !is_numeric($userinput) ) { /* error handling here*/ }
$sql = “ select * from order_history where order_id = $userinput ”;

When you first start reading through books on using database back ends for your web sites, this topic isn’t given nearly enough attention. (At least it wasn’t in the books I read.) Any additional tips from the veteran developers out there?

filed under: Web Development