PHP: Sanitize Data to Prevent SQL Injection Attacks

Posted on January 9, 2009, under PHP 

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 = md5(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!

Comment via Facebook

comments

Leave a Reply


* = required fields

  (will not be published)


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Note: If you want to post CODE Snippets, please make them postable first!
(e.g. <br /> should be converted to &lt;br /&gt;)

POSTING RULES:

  • The comment must be relevant with the topic of the post.
  • Only comments with real email addresses will get approved. So, emails like 'abc@domain.com' will not be accepted.
  • Do not post the same message in multiple articles through the site.
  • Do not post advertisements, junk mail or pyramid schemes.
  • In case you post a link to another site, please explain briefly where the link goes as a courtesy to other users.
  • Do not post comments such as: "Thank you", "Awesome", "Nice tutorial", "Merci", etc.