This is a simple function that sanitizes the data before sending it to MySQL. First it removes whitespaces from the beginning and ending of the string. If magic_quotes_gpc is enabled and the data has been already escaped we will apply stripslashes() to the data. This way the data won’t be escaped twice when mysql_real_escape_string() is called.

function sanitize($data)
{
// remove whitespaces (not a must though)
$data = trim($data); 

// apply stripslashes if magic_quotes_gpc is enabled
if(get_magic_quotes_gpc())
{
$data = stripslashes($data);
}

// a mySQL connection is required before using this function
$data = mysql_real_escape_string($data);

return $data;
}

The function mysql_real_escape_string() escapes special characters in a string for use in a SQL Statement. Unlike the deprecated function mysql_escape_string(), which doesn’t take a connection argument and does not respect the current charset setting, mysql_real_escape_string() calls MySQL library’s function mysql_real_escape_string, which prepends backslashes() to the following characters: \x00, \n, \r, \, ‘, ” and \x1a. It’s strongly recommended to use this function before sending any query to the mySQL database.

Example of unsecured script:

session_start();

$username = $_POST['username'];
$password = $_POST['password'];

$sql_query = "SELECT * FROM `members` WHERE username='".$username."'
AND password='".$password."'";

$sql = mysql_query($sql_query);

if(mysql_num_rows($sql))
{
// login OK
$_SESSION['username'] = $username;
}
else
{
$login_error = true;
}

The data sent is not sanitized. Therefore, the user could type anything he wants in the login form. Example:

$_POST['username'] = 'john';
$_POST['password'] = "' OR ''='";

The query sent to the database would be:

SELECT * FROM `members` WHERE username='john' and password='' OR ''=''

This way anyone could login without typing a valid password. The query selects all rows from `members` and the verification with mysql_num_rows() won’t work in this case.

Example of secured script:

session_start();

$username = sanitize($_POST['username']);
$password = sanitize($_POST['password']);

$query = sprintf("SELECT * FROM `members` WHERE username='%s' AND password='%s'",
 $username, $password);

$sql = mysql_query($query);

if(mysql_num_rows($sql))
{
// login OK
$_SESSION['username'] = $username;
}
else
{
$login_error = true;
}

The %s from the sprintf() function indicates that the argument is treated as and presented as a string.

If an attack is made such as the one from the previous example the query sent will be:

SELECT * FROM `members` WHERE username='john' AND password='\' OR \'\'=\''

and will return an empty result set.

Happy coding!

Do you wish to receive the latest updates as soon as they are posted? Get our RSS Feed or Subscribe to the Newsletter!