SQL Injection

SQL Injection is kind of old news. We've been hearing about it for years and things like cross-site scripting is more interesting now anyway. But how many of you have actually been paying attention to how SQL injections attacks happen? How many of you are actually doing something about it?

An SQL injection is when someone passes arbitrary SQL to your application. This usually happens through the html forms on your site. For example, you have a user login form where a user types in their email address and password. Without knowing anything about the database structure of the application I could enter something like this in the email field.

blah' or 'n'=='n

or worse...

'; drop table user;--

When your code drops those strings into the middle of an SQL statement bad things are going to happen. I'm not going to spend a lot of time explaining how to mount an SQL injection attack. People much smarter than me have already done so. Fire up Google and learn how it's done by the pros. The question is, are you protected? Here's how.

Sanitize Input
All data entering your database should be considered suspect. Any value than can vary in an SQL statement needs to be sanitized before it's used. Don't try to code this routine yourself. Use what the database vendor already gives you. If you're using MySQL then you're going to want to use mysql_real_escape_string()

Hide Error Messages
The public should never ever see error messages generated by the application server. Those are for developers to look at and no one else. These messages give away all kinds of information useful to attackers. Many times you're going to want to avoid even indicating an error occurred. This paper shows how an attacker was able to use the presence or absence of a 500 error page to know if they were able to generate valid SQL or not. How to handle errors and bad user input is entirely application dependent. Use your best judgement.

Limit Database Permissions
The web application should connect to the database as a user with a limited set of permissions. If the user anonymous you're going to want to use a read-only db user in that case. Again, this is application dependent. Use your judgment but think about database permissions. Think about it hard.

Parameterized Queries
I saved the best for last. In my experience 99% of all web application piece together SQL on the fly. This is bad for performance and even worse for security. I'm not judging because I've written tons of code like that myself. However, there is a better way. Using parameterized queries gives you some performance gains as well as a huge improvement in security. No longer can your original statement be modified into something you never intended.

Parameterized queries is where you construct your SQL statement using '?' placeholders for variable data and then bind the actual values to those placeholders. Once again, an example speaks far better than my prose. This is in PHP5.

$sql = "select first_name, last_name from user where email = ?";
$stmt = $mysqli->prepare($sql);

$email = "someone@yahoo.com";
$stmt->bind_param("s", $email);

$stmt->execute();
$stmt->bind_result($first, $last);

while($stmt->fetch()) {
 print("$first $last");
}