Protecting Script using SQL injection For MySQL with PHP

If you are running a dynamic website coded in PHP, chances are you'll be using
MySQL for storing content or information.

MySQL is very well suited for running anything from small personal websites to large corporate
systems, as it is both simple to use and scalable.  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
  MySQL_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']).