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!

Tags:

Related Posts

Comment via Facebook

18 Replies to "PHP: Sanitize Data to Prevent SQL Injection Attacks"

  2. Brian Recchia
    July 18, 2009 at 2:41 AM
    Reply

    I’m going to have to use this. Thanks! But I have to be a smartass and point out the fact that that when sanitized, the code would let john login if his password actually was ' OR ''='. ;)

    1. Gabriel
      July 18, 2009 at 10:22 AM
      Reply

      @Brian Recchia, are you sure? I’ve just checked this and there are no results returned for the query SELECT * FROM `members` WHERE username='john' AND password='\' OR \'\'=\''

    2. Dave
      April 6, 2010 at 7:33 AM
      Reply

      Don’t you have any protection on your registration? Are those values not being escaped before being entered into the database?

  3. James Jeffery
    October 17, 2009 at 4:19 AM
    Reply

    Was on the hunt for a quick copy and paste sanatize function.

    Whilst I like yours, there is one major issue. It is no reusable.

    What if the user was to use PDO? Or a database other than MySQL?

    Just a heads up :)

  4. Techie Talks
    November 21, 2009 at 8:44 AM
    Reply

    Its a good thing you have this. Before I used to have that unsecured coding also and that bothered me a lot when I remember those sites that I have created in the past!

  5. Alex Monthy
    January 18, 2010 at 2:45 AM
    Reply

    This is my sanitize function in an application where I only expect user input without quotes, double dashes etc. What do you think of it?

    sanitize( &$_GET );
    sanitize( &$_POST );

    function sanitize( &$some) {
    foreach( $some as $key => $value ) {
    $value = str_replace( '--', '', $value );
    $value = str_replace( '/*', '', $value );
    $value = str_replace( '"', '', $value );
    $value = str_replace( "'", '', $value );
    $value = ereg_replace( '[\( ]+0x', '', $value );
    if ($value != $some[$key]) {
    $some[$key] = $value;
    }
    }
    }

  6. Kevin
    May 26, 2010 at 2:01 AM
    Reply

    This is actually the wrong approach as the only definitively safe method is to use prepared queries. They take a bit more hassle, but really there’s no excuse for doing otherwise.

    1. Gabriel C.
      May 26, 2010 at 8:17 AM
      Reply

      Yes, they are recommended to use and indeed they take a bit more hassle, but I disagree that my approach is wrong. It depends which option is more convenient to the programmer.

  7. bind
    June 7, 2010 at 4:27 AM
    Reply

    the arguement for the verification of the username should be a case insensitive string comparison.

  8. Samuel
    August 6, 2010 at 3:48 PM
    Reply

    Hello, I use md5 for my user name/passwords logon

    eg:

    $my_user = md5($_POST['txt_user']);
    $my_pass = md5($_POST['txt_pass']);

    $valid_login_q = "select * from users where md5(user_name) = '$my_user' and md5(user_passw) = '$my_pass'

    The users table is already indexed by a function based index for md5(user_name), do guys think that’s a good idea? I have been used before discovering other methods of “sanitization”.

    See ya..

    1. Tomas
      December 18, 2010 at 5:00 AM
      Reply

      $valid_login_q = “select * from users where md5(user_name) = ‘$my_user’ and md5(user_passw) = ‘$my_pass’

      I think you really mean:
      $valid_login_q = “select * from users where md5(user_name) = ‘$my_user’ and user_passw = ‘$my_pass’

      Your password should already be stored in the database as md5 hashes, not plain text.

  9. saras
    February 2, 2011 at 12:50 AM
    Reply

    Well… yes MySQL stores md5 password, but user inputs not md5 so code sgould convert it to md5 first to compare with database entry.

  10. John
    February 20, 2011 at 3:09 AM
    Reply

    Dont you think it should be addslashes() instead of stripslashes() ?

    1. Gabriel C.
      February 20, 2011 at 3:23 AM
      Reply

      No, because I already use mysql_real_escape_string() which is used to escape special characters in a string for use in an SQL statement . The role of stripslashes() here is to remove any existing slashes that may result if get_magic_quotes_gpc() is enabled. This way, the string won’t be retrieved with multiple slashes in the end. Hope that makes sense!

      1. John
        February 20, 2011 at 4:13 AM
        Reply

        Thanks for clearing the confusion. That makes a lot of sense!!

  11. Alex
    March 7, 2011 at 11:54 PM
    Reply

    Do NOT use this as protection in any real-life application, it is a very unsecure and wrong aproach.

    It may work for strings, but if you have a numeric value escaping strings does not make any change.. example:

    …&id=5 union select @@version, null, null

    so you don’t need the tick to perform a sql injection, you can even do where statements using the concat and char function of mysql concat(char(34), char(32)…)…

    So, i recommend to use a prebuilt library (framework or orm like propel) or use prepared statements, since they are not vulnerable to sql-injection.

  12. webi
    August 15, 2012 at 9:35 PM
    Reply

    This adding and stripping slashes is confusing me.

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.