Defeating SQL Injection

Your code is the walls which protect your intellectual ideas. The best attack your enemy has is SQL injection. Are your walls providing a suitable defense? (*groan* That was a horrible opening but it was the best I could do)

A few comments on this blog were made on SQL Injection, so I thought I'd post some thoughts. There are many articles about how to prevent SQL injection, I am going to cover just a few techniques.

The Problem
If you don't know what SQL Injection is Google it, but quite simply it is a way for external forces to execute SQL statements on your database. Statements like:

delete from user (scary)
update user set money= 100000000 (hacker)

Most of these types of hacks happen when a user types specific things into a text box or address bar. This being the case you want to "clean" all incoming input. Rule of Thumb: Very rarely trust and always verify. (Trust but verify?)

The reason this hack works is because when you use a variable in a sql statement it can contain malicious code. For example the following piece of codes is suspect to SQL Injection:

$sql = "Select * from user where username = '$username' and password = '$password';"
mysql_execute_query($sql);

If the user uses the following as their username ';delete from user; it will delete all users from your tables.

The Solution
Clean all your variables. I run all variables, regardless of how I use them through a clean function. The clean function is responsible for removing quotes and cleaning up odd characters.

The function takes a variable, cleans it and returns it. If you use this code, please add your own steps to ensure protection of your data, this is a simplistic clean function. Below is some of the function:

function clean($value)
{
$value = trim($value);

$value = strip_tags($value);

$value = mysql_real_escape_string($value);

if (!get_magic_quotes_gpc())
{
$value = addslashes($value);
}

$value = rtrim($value);

return $value;
}

I use this function like so:

$sql = "Select * from user where username = '" . clean($username) . "' and password = '" . clean($password) . "';"
mysql_execute_query($sql);

Another Solution
The other step I take is that I have overwritten the mysql_query function so that it replaces my table names. I wanted to make it difficult for people to guess my table names, so I have the following function:

function executeQuery($sql)
{
$sql = str_replace("s_", "game_", $sql);
$q = mysql_query($sql) or die("SQL Error on $PHP_SELF: " . $sql);

return $q;
}

This replaces any s_ with game_, I might name my table "game_user" however my sql would be select * from s_user.

Hopefully this helps those who have questions about SQL Injection.