Explanation of this SQL sanitization code

Question

I got this from for a login form tutorial:

function sanitize($securitystring) {
        $securitystring = @trim($str);
        if(get_magic_quotes_gpc()) {
            $securitystring = stripslashes($str);
        }
        return mysql_real_escape_string($securitystring);
    }

Could some one explain exactly what this does? I know that the 'clean' var is called up afterwards to sanitize the fields; I.e. $email = sanitize($_POST['email']);

Answer 1

trim() gets rid of all whitespace, and if magic quotes is on, the backslash is removed from any escaped quotes with stripslashes(). mysql_real_escape_string() readies a string to be used in a mysql query safely.

here are the docs for the functions used: http://php.net/manual/en/function.trim.php, http://php.net/manual/en/function.get-magic-quotes-gpc.php, http://php.net/manual/en/function.stripslashes.php, http://php.net/manual/en/function.mysql-real-escape-string.php

Answer 2

Basically, if you have magic quotes switched on, special characters in POST/SESSION data will automatically be escaped (same as applying addslashes() to the string). The MySQL escape functions are better than PHP's addslashes() (although I can't remember the exact reasons why).

What your code does is check if the php.ini file has magic quotes turned on, if so the slashes are stripped from the data and then it is re-sanitised using the MySQL function. If magic quotes is not on, there is no need to strip slashes so the data is just sanitised with the MySQL function and returned.

Answer 3

mysql_real_escape_string is used to escape characters in the string to add backslashes to characters such as ', which prevents an attacker from embedding additional SQL statements into the string. If the string is not escaped, additional SQL can be appended. For example, something along the lines of this might be executed:

SELECT * FROM tbl WHERE col = 'test' ; DELETE * FROM tbl ; SELECT 'owned'

magic_quotes does escaping of its own, although if I remember correctly its use is now discouraged. Besides, the MySQL function will do all the escaping you need to prevent SQL injection attacks.

Answer 4

First of all, this code is wrong.
It has wrong meaning and wrong name.

No SQL data preparation code does any cleaning or sanitization. It does merely escaping. And this escaping must be unconditional. and escaping shouldn't be mixed with anything else.

So, it must be three separated functions, not one.

1. Getting rid of magic quotes. Must be done separately at the data input.
2. trim if you wish. It's just text beautifier, no critical function it does.
3. mysql_real_escape_string() to prepare data for the SQL query.

So, the only mysql related function here is mysql_real_escape_string(). Though it makes no data "clean", but merely escape delimiters. Therefore, this function must be used only with data what considered as a string and enclosed in quotes. So, this is a good example:

$num=6;
$string='name';
$num=mysql_real_escape_string($num);
$string=mysql_real_escape_string($string);
$query="SELECT * FROM table WHERE name='$name' AND num='$num'";

while this example is wrong:

$num=6;
$string='name';
$num=mysql_real_escape_string($num);
$string=mysql_real_escape_string($string);
$query2="SELECT * FROM table WHERE name='$name' AND num=$num";

Even though $query2 would not throw a syntax error, this is wrong data preparation and mysql_real_escape_string would help nothing here. So, this function can be used only to escape data that treated as a string. though it can be done to any data type, there is some exceptions, such as LIMIT parameters, which cannot be treat as a strings.

Answer 5

Some (old) servers have magic_quotes enabled. That means that all external input is altered to (supposedly) escape it in order to be injected in a MySQL query. So O'Brian becomes O\'Brian. This was an early design decision by the PHP team that proved wrong:

• You don't always need to inject input into database queries
• Not all DB engines use back slashes as escape char
• Escaping single quotes with backs slashes is not enough, even for MySQL
• Your server security relies on a PHP setting that can be disabled

So it's way better to code without magic_quotes. The problem comes with redistributable code: you cannot know if the server will have magic_quotes enabled or disabled. So you can use get_magic_quotes_gpc() to detect it they're on and, if so, use stripslashes() to (try to) recover the original input.