mysql_real_escape_string() leaving slashes in MySQL

Question

I just moved to a new hosting company and now whenever a string gets escaped using:

mysql_real_escape_string($str);

the slashes remain in the database. This is the first time I've ever seen this happen so none of my scripts use

stripslashes()

anymore.

This is on a CentOS 4.5 64bit running php 5.2.6 as fastcgi on a lighttpd 1.4 server. I've ensured that all magic_quotes options are off and the mysql client api is 5.0.51a.

I have the same issue on all 6 of my webservers.

Any help would be appreciated.

Thanks.

Edit:

Magic Quotes isn't on. Please don't recommend turning it off. THIS IS NOT THE ISSUE.

Mammoth

If you echo out SQL statements, do they have the correct slashing? – Bowsprit

Yes. It's that MySQL leaves the slashes in the string when inserted as if I called addslashes(). – Mammoth

stackoverflow.com/editing-help – Victoria

Use PDO then all your escaping and injection woes will be gone. – Mongolia

Eric, did you ever get a solution for this? I also just switch hosts and am seeing the same problem. Have of course varified magic_quotes is off. – Hakan

Notice: mysql()*is deprecated as of PHP 5.5.0, it is not recommended for writing new code as it will be removed in the future. Instead, use mysqli or PDO What hosting company are you using? This problem might be from their end. – Preordain

Answer 1

The host that you've moved probably has magic_quotes_runtime turned on. You can turn it off with set_magic_quotes_runtime(0).

Please turn off magic_quotes_runtime, and then change your code to use bind variables, rather than using the string escaping.

Pines

The actual "right" part of it is the part, that suggests the use of bind variables instead of string concatenation/escaping, thus avoiding all the troubles of escaping, quoting, potential security holes (hello, sql injections) and so on – Laskowski

Can you use bind variables in mysql? This is the first I've ever heard of such an approach instead of escaped values. – Alcmene

$sql = "UPDATE..."; $stmt = $db->prepare($sql); $stmt->bind_param('ss',$bio,$name); $stmt->execute(); – Lassitude

Answer 2

I can think of a number of things that could cause this. But it depends how you are invoking SQL queries. If you moved to use parameterized queries like with PDO, then escaping is unnecessary which means the call to mysql_real_escape_string is adding the extra slashes.

If you are using mysql_query etc. then there must be some code somewhere like addslashes which is doing this. This could either be before the data is going into the database, or after.

Also you say you have disabled magic quotes... if you haven't already, just do a hard check in the code with something like this:

echo htmlentities($_GET['value']); // or $_POST, whichever is appropriate

Make sure there are no slashes in that value, then also check this:

echo "Magic quotes is " . (get_magic_quotes_gpc() ? "ON" : "OFF");

I know you've said multiple times it isn't magic quotes, but for us guys trying to help we need to be sure you have checked the actual PHP output rather than just changing the config (which might not have worked).

Mongolia

Answer 3

it sounds as though you have magic quotes turned on. Turning it off isn't too hard: just create a file in your root directory called .htaccess and put this line in it:

php_flag magic_quotes off

If that's not possible for whatever reason, or you want to change your application to be able to handle magic quotes, use this technique:

Instead of accessing the request variables directly, use a function instead. That function can then check if magic quotes is on or off and strip out slashes accordingly. Simply running stripslashes() over everything won't work, because you'll get rid of slashes which you actually want.

function getVar($key) {
    if (get_magic_quotes_gpc()) {
        return stripslashes($_POST[$key]);
    } else {
        return $_POST[$key];
    }
}

$x = getVar('x');

Now that you've got that, all your incoming variables are ready to be escaped again and mysql_real_escape_string() won't stuff them up.

Side

Note that you may not always be able to disable magic_quotes in a .htaccess file. If the value is set with php_admin_flag in php.ini, it can't be overridden later. – Kaspar

Answer 4

the slashes remain in the database.

It means that your data gets double escaped.

There are 2 possible reasons:

1. magic quotes are on, despite of your feeling. Double-check it

2. There is some code in your application, that just mimic magic quotes behaviour, escaping all input.
   This is very common misconception to have a general escaping function to "protect" all the incoming data. While it does no good at all, it also responsible for the cases like this.
   Of so - just find that function and wipe it out.

Crepe

Answer 5

You must probably have magic quotes turned on. Figuring out exactly how to turn it off can be quite a headache in PHP. While you can turn off magic quotes with set_magic_quotes_runtime(0), it isn't enough -- Magic quotes has already altered the input data at this point, so you must undo the change. Try with this snippet: http://talks.php.net/show/php-best-practices/26

Or better yet -- Disable magic quotes in php.ini, and any .htaccess files it may be set in.

Kaspar

Answer 6

I am not sure if I understand the issue correctly but I had a very same problem. No matter what I did the slashes were there when the string got escaped. Since I needed the inserted value to be in the exact same format as it was entered I used

htmlentities($inserted_value)

this will leave all inserted quote marks unescaped but harmless.

Kurtzig

Answer 7

What might be the problem (it was with us) that you use mysql_real_escape_string() multiple times on the same var. When you use it multiple times, it will add the slashes.

Bothersome

Answer 8

Function below will correctly remove slashes before inserting into the database. I know you said magic quotes isn't on but something is adding slashes so try the following page and see the output. It'll help figure out where. Call with page.php?var=something-with'data_that;will`be|escaped

You will most likely see number three outputting more slashes than needed.

*Change the db details too.

<?php

$db = mysql_connect('host', 'user', 'pass');

$var = $_REQUEST['var'];
echo "1: $var :1<br />";
echo "2: ".stripslashes($var)." :2<br />";
echo "3: ".mysql_real_escape_string($var)." :3<br />";
echo "4: ".quote_smart($var)." :4<br />";


function quote_smart($value)
{
    // Stripslashes is gpc on
    if (get_magic_quotes_gpc())
    {
        $value = stripslashes($value);
    }
    // Quote if not a number or a numeric string
    if ( !is_numeric($value) )
    {
        $value = mysql_real_escape_string($value);
    }
    return $value;
}

?>

Klemens

Answer 9

mysql_real_escape_string($str); is supposed to do exactly that. it is meant to add backslashes to special characters especially when you want to pass the query to mysql. Take note that it also takes into account the character set of mysql.

For safer coding practices it would be good to edit your code and use stripslashes() to read out the data and remove the slashes.

Songsongbird

Wouldn't running stripslashes() on a non-escaped string remove good slashes too? – Mammoth

The slashes are only to escape the string that is being inserted, they're not supposed to end up in the database. You should never have to run stripslashes() on data you pulled from the database. – Euphonium

slashes would also be escaped from \ to \\ when using mysql_real_escape_string($str); if you use stripslashes() in the same text then it would remove any extra slashes and you end up with 1 slash. – Songsongbird