Confusion about mysql_real_escape_string and strip_slashes

Question

I have users entering their name, as in: O'riley.

Before I enter this data into the MySQL DB, I run mysql_real_escape_string.

Problem is, when I then select this data for display and use later, it comes out as: O\'riley.

Obviously, this is the intended operation. What I'm wondering is if there's someway to be sure I can store it in the DB (still safely escaping possible malicious code), so that I don't have to use strip_slashes() on the output EVERY time I call the data throughout the entire web app? Or, am I missing something here?

Thanks.

UPDATE Please refer to the comments in Deceze's answer.

Can you show the magic quotes snippet from your `php.ini`? – eggyal May 30 '12 at 08:36 — eggyal, May 30 '12 at 08:36

score 10 · Answer 1 · answered Feb 15 '12 at 00:52

10

No, it's not the intended operation to store the string as "O\'riley"; it should only be escaped in the query, but not stored this way. I'll guess that PHP puts in the backslash through Magic Quotes, and you escape it again to make it stick.

Disable Magic Quotes.

answered Feb 15 '12 at 00:52

deceze

510,633
85
743
889

This is odd. It's definitely OFF in `php.ini` - showing the same in WHM. However with `phpinfo()` it shows it as ON. I've edited the PHP.ini directly in: `Configuration File (php.ini) Path: /usr/local/lib` - but still it shows as ON. This didn't used to be the case, any ideas?? – Shackrock Feb 15 '12 at 01:42
There may be different .inis used for CLI, Apache and other scenarios. Check `phpinfo()` which ini it's using. – deceze Feb 15 '12 at 01:50
Seems to be the same one in that directory... `Loaded Configuration File /usr/local/lib/php.ini ` I have verified that this is indeed OFF in there. I set it off in the HTACCESS, and it appears off now in php info (local) but ON (master value)... it's very odd. Further, it still doesn't seem to fix the issue... and they are being inserted into the DB with a slash. – Shackrock Feb 15 '12 at 01:57
Then do some debugging. At which point is the slash added? Is it already in the POST data? What does the query look like exactly? Did you try following the instructions on PHP.net about dynamically removing magic quote slashes? – deceze Feb 15 '12 at 02:19
coming back to this, because I still never got a fix. The slash is already in the POSTed data - yes. However, STILL I can see with phpinfo that magic quotes if off. – Shackrock Apr 14 '12 at 12:43
Still with the issue now. I can also confirm that `if (get_magic_quotes_gpc())` returns TRUE. Any ideas? – Shackrock May 29 '12 at 23:41
1

Well, did you try the code snippet at http://php.net/manual/en/security.magicquotes.disabling.php? – deceze May 29 '12 at 23:45
there is somewhere in your code `set_magic_quote_runtime(true)` ? – Luca Rainone Jun 05 '12 at 06:48

score 4 · Answer 2 · answered Jun 03 '12 at 04:18

4

I personally always turn off magic quotes because it is doing something I haven't told it to do. If you dont have the ability to turn it off, consider including this code at the top of all of your pages.

if (get_magic_quotes_gpc()) {
    function strip_array($var) {
        return is_array($var)? array_map("strip_array", $var):stripslashes($var);
    }

    $_POST = strip_array($_POST);
    $_SESSION = strip_array($_SESSION);
    $_GET = strip_array($_GET);
}

answered Jun 03 '12 at 04:18

Michael Blood

1,257
10
11

To expand on @VIPINJAIN comment, if you do something like this, then later want to secure a post for mysql, there will be additional slashes involved. For example, running `` You will have the quote escape and a backslash escaped. Pulling from the database will have an extra backslash in it. – teynon Jan 19 '13 at 20:55

score 4 · Answer 3 · edited May 23 '17 at 12:02

If you have magic quotes disabled, and in the case that get_magic_quotes_gpc returned 1 and you did something like @Michael shows and this still occurs, magic quotes runtime may be enabled.

Magic quotes runtime will add slashes to strings when doing database queries and even when writing to files. To disable this in the currently executing script, do:

if(function_exists('get_magic_quotes_runtime') && get_magic_quotes_runtime())
{
    set_magic_quotes_runtime(false);
}

You can also disable this configuration option through php.ini as well, if you can do so. However, if you can't disable it through php.ini permanently, you may need to add @ before get_magic_quotes_runtime and set_magic_quotes_runtime as PHP may through E_DEPRECATED errors if error_reporting is set to log such errors (and this will get fairly annoying).

score 4 · Answer 4 · answered Jun 05 '12 at 01:53

Alright, here's what you need to do. First, based on the comments, from other questions:

ISOLATE

You need to determine what is causing the problem. Is it the server? Is it some hidden code somewhere? What? Some programmers may include code like this on a configuration page:

<?php if (isset($_POST)) 
    foreach ($_POST as $key => $value) $_POST[$key] = addslashes($value); 
?>

So here are some checks to see if it is the server. This is about as solid a way of checking as possible. Create a NEW page. Leave it entirely blank and add this code:

<?php print_r($_POST); ?>
<form action="" method="POST">
<input type="text" name="test" value="O'riley" />
<input type="submit" name="submit" value="submit" />
</form>

Save it and load it in the browser. Hit submit. See if the slash is added still.

If it is still adding it, you need to troubleshoot your server / configuration. If it's a server you're paying for, you need to tell them to fix it or shove it. If it's your own server, then you're going to need to do some messing / googling to figure out how to turn off magic quotes.

If there are no quotes showing up, then there is definitely some code adding slashes to your post variables. It might not be as obvious as the code above, however. You need to run a search on your code for "addslashes", "mysql_real_escape_string", and possibly "str_replace". If you find one of these, you need to disable it. But be advised this may break other parts of your site that are assuming this action is taking place. You're other option for storing it in the database is to do a function similar to the code above, but instead, run stripslashes on it. Then you can run mysql_real_escape_string on it later. (A bit more unneeded overhead.)

score 3 · Answer 5 · answered May 30 '12 at 01:03

3

Use

var_dump(get_magic_quotes_gpc());

somewhere near the actual use of the posted data to see if the Magic Quotes are really off.

Also some weird frameworks may add said quotes for you so grep your code for addslashes and other escape_string-like functions.

answered May 30 '12 at 01:03

sanmai

29,083
12
64
76

ok... so this returns false.... Give me an hour to run back through now. This is blowing my mind! – Shackrock May 30 '12 at 11:40
give some more precise answer – Vipin Jain Jun 04 '12 at 06:50

score 2 · Answer 6 · edited Jun 05 '12 at 01:22

2

I would honestly suggest the use of PDO.

PDO employs the use of prepare and execute statements, which in turn adds security and removes some extra headache.

$pdo = new PDO();
$stm = $pdo->prepare('INSERT... (LastName) VALUES (:LastName)');
$stm->execute(array(':LastName' => "O'Rily"));

$stm->fetchAssoc(PDO::FETCH_ASSOC);

You no longer will need to worry about removing the escaping slashes as well as securing basic sql injection tactics.

edited Jun 05 '12 at 01:22

user229044

232,980
40
330
338

answered Jun 04 '12 at 16:12

Mike Mackintosh

13,917
6
60
87

I'd love to, but this is an old code base. It would be crazy to try and go replace everything. – Shackrock Jun 04 '12 at 19:41
An idea you can do is to semi-override the exisitng MySQL functions such as... `$result = my_mysql_query($mysql_resource, 'INSERT INTO ...'); $row = my_mysql_fetch_assoc($result); function my_mysql_query($mysql_resource, $query){ return mysql_query($mysql_resource, mysql_real_escape_string($query, $mysql_resource)); } function my_mysql_fetch_assoc($resource){ $result = mysql_fetch_assoc($resource); return array_map("strip_slashes", $result); }` – Mike Mackintosh Jun 05 '12 at 16:11

score 0 · Answer 7 · answered Jun 05 '12 at 04:09

0

It's possible magic quotes are turned on in your httpd.conf or in a virtual host declaration.

The exact location and file will depend on OS and distro. I'd check Apache configuration files for the php_flag setting.

answered Jun 05 '12 at 04:09

crempp

248
1
7

`php_flag magic_quotes_gpc On` might also be set in a .htaccess file. – Eric Jun 05 '12 at 08:18

score -1 · Answer 8 · edited Jun 05 '12 at 08:52

Just use clean the data before inserting in to database,

function check_input($value)
{
    if( get_magic_quotes_gpc() )
    {
        $value = stripslashes( $value );
    }

    //check if this function exists
    if( function_exists( "mysql_real_escape_string" ) )
    {
        $value = mysql_real_escape_string( $value );
    }

    //for PHP version < 4.3.0 use addslashes
    else
    {
        $value = addslashes( $value );
    }

    return $value;
}


$cleandata = check_input($value);

$sql = "INSERT INTO `table_name` SET `field_name`='".$cleandata."'";

While fetching the data and show into it use stripslashes($val)

score -1 · Accepted Answer · answered Jun 07 '12 at 00:52

Thank you everyone for the answers. I will award the +50 out, but I wanted to tell my real solution here, all which people did help with...

I was performing mysql_real_escape_string on all of the data AS SOON as it posted (before any processing). So, a slash was added to escape the ' character that was submitted. This, we know is normal.

However, there was no reason that the backslash \ should show up in the DB entry, right? The escape was there to be sure the ' was entered.

Turns out, AFTER escaping, I would then save the variable to be reloaded to the page in the session, in case the user had an error that PHP found while validating all of the form fields. In this case, the user's input (formerly O'riley was now printed to their screen as O\'riley. Then, the user didn't catch this - so they would often just fix their error that PHP caught during validation (unrelated to the name field), and thus the O\'riley would land in the database because mysql_real_escape_string would escape the characters.

Lesson: When processing a form, FIRST save data for form-refill use. SECOND validate form fields. THIRD escape the data for processing into the database.

Or better yet, use PDO and avoid this =).

Comments welcome. THANKS ALL!

There is one critical fault with your approach. Whatever "data" doesn't needs to be escaped, but SQL strings only. For all other data types escaping is useless and thus makes you wide open to injection. — Your Common Sense, May 09 '13 at 10:07

Confusion about mysql_real_escape_string and strip_slashes

9 Answers9