PHP SQL Injection, Protection already set?

[eddie]

Hello,

While i have been working on a few PHP scripts i have come across something and i cannot figure out why it is doing it, hopefully you may be able to tell me.

When a form is submitted i have always used the following code to protect the mysql queries from sql injection and to strip any tags that have been entered:

$data=mysql_real_escape_string(strip_tags($_POST['data']);

The data variable is then passed through a mysql query and then i echo out a message like this.

echo "The data you entered, ".$data.", was successfully added to the database.";


The problem is this... When the data is echoed out, if the data contained a ' character then there is 3 backslahes before it.

For example is a user entered: Eddie's code
The message would say: The data you entered, Eddie\\\'s code, was successfully added to the database.

I think that the ' character is being protected by a backslash and then both those characters are being protected so therefore it results in 3 backlashes and a ' character.

When i remove the SQL protection (mysql_real_escape_string(strip_tags($_POST['data'])) then the data is passed through the query correctly without trouble...

After all of that what i really want to know is, do mysql queries now protect themselves form SQL injection?

I don't know which verison PHP the server i have my script hosted on it, but i would just like to know why this is and whether it is safe to remove all the SQL protection as the mysql quries execute the code with no trouble even when a ' character is used.

Sorry for the essay haha,

Thanks everyone for you time in helping me

Eddie


##Edit##
When a user submits a textfield with a ' characters in it, is that characters escaped using a backslash?

[SteveMC]

It's probably due to the PHP settings on the server (in the php.ini file), - if the 'Magic Quotes' settings are enabled, then PHP automatically tries to 'make safe' any input from forms.

The PHP developers now recommend that the Magic Quotes options are disabled, not least because it can encourage programmers to be lazy, and assume their inputs are safe (not in your case though!).

There's more info on this at the PHP manual (including ways to work out what the current setting is, and how to work around it).

Stephen

[eddie]

Thanks Steve!!

That explains it I've been having to add stripslashes($_POST['data']) to everything that have been submited by the POST method in a form! Just to get a variable to equal what was actually submitted by the form haha.

They have removed the option to change the magic quotes on/off in the newer PHP versions which is good, but on the other hand, when the company we host with upgrade their version of PHP we will need to change our scripts

I have read the manual for disabling it and i don't quite understand how to do this. They have just wrote some text. Do you know how to do it?

[SteveMC]

I have read the manual for disabling it and i don't quite understand how to do this. They have just wrote some text. Do you know how to do it?

There doesn't look to be a 100% surefire way to sort things out, but one of the examples posted on the page I linked to should be enough for your form data:

<?php
if (get_magic_quotes_gpc()) {
    function stripslashes_gpc(&$value)
    {
        $value = stripslashes($value);
    }
    array_walk_recursive($_GET, 'stripslashes_gpc');
    array_walk_recursive($_POST, 'stripslashes_gpc');
    array_walk_recursive($_COOKIE, 'stripslashes_gpc');
    array_walk_recursive($_REQUEST, 'stripslashes_gpc');
}
?>

If you put that in a file that gets included in every PHP page (such as a config file, or library) then it'll sort out the form values. When you move to a server without magicquotes, the above code will leave the $_ variables alone, but for now it will run the stripslashes function on each element of the four input arrays.

You'll still need to use the escape functions before storing data in the database though (if it's mySQL, then use mysql_real_escape_string() rather than just 'addslashes' - which of course you are doing already ).

Stephen

[eddie]

Ah right, include that into every script, got it

Cheers for all your help Stephen.

[SteveMC]

One thing to be aware of though, the code above works fine if all your form inputs are stored in the main arrays as follows:

<input type="text" name="input1" />

which when submitted, creates PHP values as:

$_POST['input1']

...but if you're doing anything clever with form arrays like this:

<input type="text" name="myform[input1]" />

which get turned into:

$_POST['myform']['input1'];

...then it won't remove the slashes from the value of those inputs. It should be possible to adapt the code to recurse its way through if there are nested arrays if required.

Stephen