Rob's Blog

Synopsis

Presenting a simple extension for MySQLi that provides convenience functions similar to mysql->fetch_array() and mysql->fetch_assoc() for prepared MySQL statements.

The Problem

It’s the middle of 2008, and web servers everywhere are still being hacked with simple SQL injection attacks. If you’re a web developer, and you don’t know what an SQL injection attack is, you should not write a single line of code until you have read and fully understood this. Allowing an SQL injection attack to occur in your code is just plain lazy, and you can do better.

Since about 50% of all web servers are powered by Apache, it’s a fairly safe bet that the majority of the dynamic sites they host run PHP and MySQL. These free tools make it easy to produce interactive, database-driven websites even for non-programmers, but unfortunately make it too easy for anyone (developer or not) to generate code which opens up your site to SQL injection possibilities.

Before the advent of MySQLi and prepared statements, MySQL statements in PHP queries to a MySQL database would look like:

/*
 * Example 1
 */
$sql ="SELECT ID, Firstname FROM Users WHERE Username = 'myusername' AND Password = SHA1(CONCAT('mypasswordsalt', 'mypassword'))";

(By the way, you’re not storing passwords in plain text, are you? You are adding a salt and storing the hashed password? If not, this explains what you are risking.)

So in PHP, your code might look something like:

/*
 * Example 2
 */
  $username = $_POST["username"];
  $password = $_POST["password"];
  $sql = sprintf("SELECT ID, Firstname FROM Users WHERE Username = `%s` AND Password = SHA1(CONCAT(Salt, `%s`))", mysql_real_escape_string($username, $password);
  $query = mysql_query($sql);
  // etc

Read the rest of this entry »