When saving ' to database, i get \' in a text box

Question

I am learning PDO after the many people telling me to do so. However in updating one of my scripts, PDO is causing me a problem that I'm not sure how to fix.

My problem is a user will input the title to the website. Say its "Smith's Inventory".

Since the whole PDO switch, it is saved in the db as "Smith\'s Inventory". Which is output in various places on my website. Such as the header, the html title, and the settings text box. If you click save again with \', then you get \\', and so on.

I realize why this is done, but how can it be fixed?

Here is the instert code:

foreach ($_POST as $key => $value)
{
    $sql = $dbh->prepare("UPDATE settings set value=? where variable=?");
    $sql->bindParam(1, $value);
    $sql->bindParam(2, $key);
    $sql->execute();
}
echo '<h2><font  color=green>Saved</font></h2>';

Please show the code that you are using to insert data into the database. You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL. — eggyal, Jan 15 '13 at 16:18
@bobwienholt — register_globals gives them a run for their money. — Quentin, Jan 15 '13 at 16:20

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

3

Looks like you are double escaping the data.

The most likely reasons for this are:

Your PHP install has magic quotes enabled — best to turn them off
You are using something like mysql_real_escape_string and prepared statements with placeholders — use only the latter

edited May 23 '17 at 12:12

Community

1
1

answered Jan 15 '13 at 16:18

Quentin

914,110
126
1,211
1,335

score 2 · Answer 2 · answered Jan 15 '13 at 16:23

I've had this problem before, it was due to PHP magic quotes. PHP automatically inserts a slash to escape 'risky' characters in order to prevent sql injection.

You need to either disabled magic quotes on your php install or use the stripstashes function just before you output it.

http://php.net/manual/en/security.magicquotes.disabling.php
http://php.net/manual/en/function.stripslashes.php

You can read about magic quotes here:

http://www.tizag.com/phpT/php-magic-quotes.php

score 1 · Accepted Answer · answered Jan 15 '13 at 16:17

1

You can use stripslashes on the PHP side.

<?php
$str = "Is your name O\'reilly?";

// Outputs: Is your name O'reilly?
echo stripslashes($str);
?>

answered Jan 15 '13 at 16:17

Kermit

33,827
13
85
121

3

It is better to solve the actual issue instead of trying a work around – Shakti Singh Jan 15 '13 at 16:18
Well after doing a little research, there is not a universal way to turn off magic quoted. Would stripslashes need to be put before the insert into mysql or after the fetch from mysql? – alexander7567 Jan 15 '13 at 16:38
@alexander7567 After the fetch. `stripslashes` would wrap the variable coming out of your query. – Kermit Jan 15 '13 at 16:40
Well since this is going to be used in many different enviroments, this seemed like the best choice. However, I am still open for suggestions! Thanks! – alexander7567 Jan 15 '13 at 16:47

When saving ' to database, i get \' in a text box

3 Answers3