How do I eliminate the \\ from MySQL returned data

PHP
1

Hello,

We use mysql_real_escape_string() before inserting any data into our MySQL db for those data that the user can enter the data into a text field.

But when we retrieve data from MySQL db, then any character which was escaped with a back slash before being inserted into MySQL has that back slash added to the back of all bad characters. For example a line comes out this way:

this is a test\" line showing how mysql add\’ back slashes\"

So my question is how does one display the results of a MySQL SELECT without displaying the \ which was inserted due to usage of mysql_real_escape_string()?

Regards,

2

that shouldn’t happen…

could you post pieces of code? where you insert and where you fetch?

3

One does either get rid of deprecated magic_quotes_gpc ini setting or clear data once it came to the script if it was spoiled with this setting.
Using get_magic_quotes_gpc() to check

mysql_real_escape_string() must be applied to any data that goes to the query. Not only data that the user can enter into a text field or even data that weren’t entered by user at all

4

Hello,

1st, why should mysql_real_escape_string() be applied to every data before it is inserted into MySQL? I mean for example if the user chose a data from a drop down list or a Radio button then there is NO chance that a bad char may have been introduced.

2nd, I do not quite follow the 1st part of what you have written!

regards,

5

Magic quotes will be automatically escaping your data. Its an annoying ‘feature’ that has been removed in PHP6.

At the moment however, you’ll have to reverse the affects of this before adding the data. Something like:

function escape($data)
{
   if (get_magic_quotes_gpc())
   {
      $data = stripslashes($data);
   }

   $data = mysql_real_escape_string($data);

   return $data;
}
6

Of course there is. A user could easily edit the html to add a malicious value to the dropdown or radio button, then send the form.

7

Jaanboy

function escape($data)

that’s wrong approach. these functions shouldn’t be used together.

WorldNews

I mean for example if the user chose a data from a drop down list

Because this function has nothing to do with user’s input. It is not for user input, but for SQL query. It is part of proper SQL syntax.
As I said above, this function should be used even if there was no user input at all.

8

Hi,

Are you saying that any data before it is inserted into MySQL should be
sent to this function which will then both do th emysql_real_escape_string()
as well as remove the \ from before chars like "?

Regards,

9

Now I am confused.
So u are saying not to use the function call that the other gent had provided.
Ok, so then how do you make sure that when data is retrieved from
MySQL chars like " or ’ do not have a \ before them?

10

Jaanboy, but would be the purpose of such a malicious act since they will
be just breaking MySQL insertion of their own data!

11

At first you must understand that these symbols came not from mysql, but from the browser.
So, you have to eliminate it not then it coming from database but then it coming from the browser.
You can use a code like in this:

<?php
if(get_magic_quotes_gpc())
{
    function stripslashes_deep($value)
    {
        $value = is_array($value) ?
        array_map('stripslashes_deep', $value):
        stripslashes($value);
        return $value;
    }
    $_POST=array_map('stripslashes_deep', $_POST);
    $_GET=array_map('stripslashes_deep', $_GET);
    $_COOKIE=array_map('stripslashes_deep', $_COOKIE);
    $_REQUEST=array_map('stripslashes_deep', $_REQUEST);
}
?>

place this code at the very top of your scripts, or even in the configuration file

That’s all, your problem solved.

12

Or, you could just disable magic quotes and get on with things.
After all, it won’t even exist with PHP 6.0 so it’s useful to get used to things now.

13

the malicious attack could be something as simple as
“DROP DATABASE;”

this would delete your whole database

14

A very good page about this is SQL Injection Attacks by Example

15

One note 2 WorldNews
At the moment you have already spoiled data in your database.
If there’s just test data and can be deleted - just delete it.
I there is something important, it must be cleaned out.

16

so to KISS,

mysql_real_escape_string() will insert a SQL native backslash to string containing ’ or ", that this SQL native backslash will not exist/visible outside the database?

it is the magic quotes that adds a backslash to string with ’ or " even if the string comes right out of database is plain ’ " like O’connor ?

Example:–

first, string O’connor is stored into database with backslash by mysql_real_escape_string() – so it’s O’/connor inside database

then database feeds string as O’connor not O’/connor before processed by PHP.

finally, magic quotes adds a blackslash to O’connor and now become O’/connor, which is out put to browser.

am i right?

17

i think it is an important knowledge to keep in mind, since we now must mysql_real_escape_string() again for even the datas retrieved from TABLE 1 which we will insert into TABLE 2.

because datas retrieved from TABLE 1 is just a plain ’ or " like O’connor, even they were escaped before by mysql_real_escape_string()

18

now i can tell you how i hate magic quotes!

19

am i right?

Quite contrary :).
there are 2 kinds of magic quotes - magic_quotes_gpc and magic_quotes_runtime
second one will act as you described, adding a backslash to the data retrieved from the database. But it almost always turned off by default. Can be turned off manually, using set_magic_quotes_runtime(FALSE) to be sure.

But we were talking of magic_quotes_gpc, which adds these garbage backslashes to the data that coming from the outside. So, corrected Example:–

  1. first, string O’connor were posted from the form in the user’s browser.

  2. magic quotes adds a blackslash to O’connor and now become O\'connor

  3. mysql_real_escape_string() adds another slash for the each special character, and our poor fellow become O\\\'connor

  4. Once this string goes to the database, one pair of slashes get stripped off, so, O\'connor again. And forever.

To make it right way we have to either eliminate item 2 by setting magic_quotes_gpc directive to off, or disable it’s mean trick by using stripslashes_deep code I posted above.

See also: http://php.net/manual/en/security.magicquotes.php

20

craqgerbil, we are talking about someone tampering with data from a drop down list to add a ’ which would cause a MySQL error for that submission, something that would affect them only.

So how do you go from creating a browser warning that MySQL could not add your data, due to you having added a ’ to an item from the drop down list to being able to issue a command to the MySQL such as “Drop database”???