Protecting against SQL injection using MySQL with PHP

03 March 2006

If you are running a dynamic website coded in PHP, chances are you’ll be using MySQL for storing content or information. However, it is easy to overlook potential security problems, especially if you don’t have much experience.

Example

For instance, you may have a login script for a secure page of your site:

<?php
  # Database connection code here

  $result = mysql_query('select * from users where user="'.$_POST['username'].'" and pass="'.$_POST['password'].'"');

  if(mysql_num_rows($result)==0):
    # Username or password incorrect
    exit;
  endif;

  # Send user protected page
?>

So for instance, if somebody sent " or ""=" for the username and the password, the SQL query sent to the database would read: select * from users where user="" or ""="" and pass="" or ""="", which would allow access to the protected page without a valid username or password.  This method is called SQL Injection.

Escaping

To prevent this from happening, the data provided by the user need to be ‘escaped’ - this means putting backslashes in front of quotes, backslashes and other special characters.

This means that the MySQL engine will recognise that the quotes are part of the string, which prevents SQL injection.

PHP has a built in function that is intended for escaping strings, called addslashes.

For instance, passing the form data from our example through addslashes would result in select * from users where user="\" or \"\"=\"" and pass="\" or \"\"=\"" being passed to the database, which can be correctly interpreted by MySQL.

Magic Quotes

PHP has a feature called ‘Magic Quotes’, which automatically escapes get, post and cookie data, as if addslashes had been called on them.  The idea of this is to prevent scripts written by inexperienced coders being vulnerable to SQL injection.

However, there are several problems with this feature:

• Addslashes doesn’t escape data exactly right for MySQL databases. (The MySQL function msql_real_escape_string should really be used instead)
• Magic quotes can give programmers a false sense of security, and makes scripts completely vulnerable if the option is turned off.
• It has the irritating side effect that form inputs used in other parts of scripts have slashes added to them, which can be very puzzling for beginners, and adds extra coding to remove them again.

Because of these reasons, magic quotes are turned off by default in PHP 5, although they are on by default in PHP 4.

Best Practice

To keep your code portable and protected against vulnerabilities whether Magic Quotes is enabled or not, it is best to use a function similar to the one below:

<?php
  function proper_escape($datastring) {
     # Strip slashes if data has already been escaped by magic quotes
     if(get_magic_quotes_gpc()):
       $datastring = stripslashes($datastring);
     endif;

     # Escape string properly & return
     return mysql_real_escape_string($datastring);
   }
?>

Call this when sending input data to the MySQL database like: proper_escape($_POST['username']).

Links

• Magic Quotes reference at php.net.
• Reference for mysql_real_escape_string at php.net.