Minimal Sanitation Techniques

[Roswell]

Whenever you input data into the database, it must be sanitized. This is simply not an option. Not doing so leaves room for various exploits and attacks.

Now, there are various ways to sanitize variables. Some are more complicated and require the use of regex's, and others are more simple (we'll be focusing on these more simple techniques).

Sanitation -- Making Code Safe
Consider this bit of code for a moment:

Code: Select all

<?php
//We're assuming that the database is already
//setup and that a form has $_POST'd to
//this file already

$username = $_POST["username"];
$password = $_POST["password"];
$password = md5($password);

$_query="SELECT `id` FROM `users` WHERE `username`='" . $username . "' AND `password`='" . $password . "'";
$_query=mysql_query($_query);
if(mysql_num_rows($_query) == 0)
{
    echo "Username or Password Incorrect!";
}
?>

I'm sure some of us have written a login form like this, right? So what's wrong with it, you ask? Well. Many things.

Firstly, let's assume that I know the administrator username is "admin" (even if you don't, it's usually a safe guess). That means I could just enter this for my username and password:

Username: admin'--
Password: (empty)

This would cause my SQL query to be parsed as

Code: Select all

SELECT `id` FROM `users` WHERE `username`='admin'--' AND `password`=''

If any of you are familiar with MySQL, you'll know

Code: Select all

--

starts a comment. That means everything after -- will be ignored. This, in turn, means the server will see our query as

Code: Select all

SELECT `id` FROM `users` WHERE `username`='admin'

Now, if we go back to our original code, we're looking to see if there's a match. As there is a username named "admin" in our hypothetical database, the code will think we've authenticated successfully, logging me in.

So how do I fix that?
The first function we're going to look at is mysql_real_escape_string(). This little function escapes a string so it's safe for an SQL query. It'll turn our data that we submitted earlier into:

Username: admin\'\-\-
Password:: (empty)

Meaning our special little comment will be interpreted as a string, causing our query to not return any results. You can implement this in one of two ways:

Method 1: Implementing Only For the Variables You Need
You may recall this code from our sample above:

Code: Select all

$username = $_POST["username"];
$password = $_POST["password"];
$password = md5($password);   

As our password is hashed and is more or less immune from an inject attack, you could simply use the function on our username, doing this instead:

Code: Select all

$username = mysql_real_escape_string($_POST["username"]);   

Method 2 -- All Or Nothing
Alternatively, we can just sanitize all of our post variables, which will require a bit of rewriting of our code. We're going to take this little bit of code

Code: Select all

$username = mysql_real_escape_string($_POST["username"]);
$password = $_POST["password"];
$password = md5($password);

$_query="SELECT `id` FROM `users` WHERE `username`='" . $username . "' AND `password`='" . $password . "'";   

and rewrite it as this (note you may need to review the foreach() function to understand this

Code: Select all

$data=array();
foreach($_POST as $key => $value)
{
    $data[$key] = mysql_real_escape_string($value);
}

$_query="SELECT `id` FROM `users` WHERE `username`='" . $data['username'] . "' AND `password`='MD5(" . $data['password'] . ")'";   

As for which one is better, it really depends on the amount of data you're handling. If you have more than one or two form fields, it's probably best to use the second foreach() method.

This will take care of many SQL attacks. You may also want to implement regexs to match your data (ie, if you know a field should only be alphanumeric, make sure it is) -- hell, here's a regex for you to do that.

/^([a-zA-Z0-9_-]+)$/

On top of that, you may want to look into the strip_tags() function.

[super3boy]

Great tutorial. Sanitation is something that php programmers have to be familiar with. It only takes a few minutes to do, and without it a script is useless because it can be easily hacked.