Last Good Quote: Son's are the seasoning on our lives. - Someone on Facebook

Tuesday, February 19

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';"

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) . "';"

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.


Spoom said...

Just came across this on Google. I, uh... wow.

Why would you use addslashes after mysql_real_escape_string? You're going to end up with double, triple, or more slashed data in your database, corrupting your data; not good. Just stick with mysql_real_escape_string (or the appropriate escape function for the database you're using). It was coded by the people who wrote the MySQL API. They know what they're doing. In general, you should never need to run addslashes. An even better method nowadays is to use the mysqli API and bound queries, which escapes strings automatically (though note that you'll have to switch entirely; you can't mix the mysql and mysqli APIs). See for further details.

Further, you're directly advocating security by obscurity, which is a *horrible* idea and completely the wrong path to tell someone to go. Security by obscurity is no security at all. Once a cracker has a successful injection, all they need do is run a SHOW TABLES query and your "clever" method is defeated. (Not to mention that the table will show up in a failed query anyway due to your "display error on failure" methodology.)

I don't mean to be negative without reason here, but your overconfidence may push new developers in a direction that they may later regret. Your meaning was good but please do a little more research first next time.

mobeamer said...

>> Why would you use addslashes after mysql_real_escape_string?

It's a personal choice, you don't have to do this if you feel its not appropriate. At the time of posting there was an issue with the mysql_real_escape_string, so I advised using both. It does not leave extra slashes in my database. (That might be a version difference though)

>> you're directly advocating security by obscurity, which is a *horrible* idea and completely the wrong path to tell someone to go.

I assume that is directed at the table naming convention. While I agree that it is security through obscurity. It's not a horrible idea. This trick will stop 80% of your hackers. If your hacker is a professional and your reading up security detail on blogs, your in for a bad night any way you cut it.

This is my 80/20 rule. I rule out 80% of the risk and I have to live with the other 20.

I do appreciate the comment though as you do have good points for those who are new to the security aspect of this.

Post a Comment