MySQL / PHP problem with " and '

Question

Hi

I made a simple news system with comments using PHP and MySQL, and it worked great on my local Apache server, both on my Fedora 10 machine, and my Windows 7 one. Now I have a problem though, I uploaded it to a web host and it keeps returning all the ' and " as \' and \".

I believe this is either the web host who by automatic adds them for security reasons or that the MySQL is the wrong collation, but I have not been able to resolve it yet, testing multiple MySQL collations.

Here is a example of a query:

mysql_query("INSERT INTO news (title, poster, text, time) VALUES ('$newstitle', '1', '$newstext', '$time')") or die(mysql_error());

$time is time(); $newstitle and $newstext are parsed from $_POST and both are ran through mysql_real_escape_string() before I run the query (I guess this might be the problem, but since it is a part of the security I just don't want to remove it, and since it did not cause problems on my local servers)

As a final note: On my local apache servers I had latin1_swedish_ci which did not work on the web hosts server.

EDIT:

They look like this in the database:

\'\'\'\"\"\"

While on my local ones they didn't have the extra backslash, so it must be the PHP adding it. Is there any way to solve this except adding a function that removes extra backslashes?

Answer 1

Edit: As mentioned several times below, and in comments, the correct way to go about this is to disable magic quotes. Had I fully read and comprehended the question before replying, then that would probably have been better ;)

---

mysql_real_escape_string() will be doing what it says, and escaping the relevant characters within the string. Escaping quotes is done by prefixing them with the escape character (a back-slash in PHP, and many other languages). When you pull the data back from the database, do something like run stripslashes() (see the PHP doc) on the content, to remove the slashes from things like quotes.

Edit: As Gumbo mentioned, it could also be due to Magic Quotes being enabled on the server. You should disable these as per his answer.

Answer 2

These additional backslashes are probably Magic Quotes. Try to disable them or remove them before processing the data.

Answer 3

Your host might have Magic Quotes enabled.

Answer 4

Look at magic_quotes_gpc. You can turn this off via .htaccess and use mysql_real_escape_string().

Answer 5

No, you don't want to run stripslashes(). Rather, set up your server properly.

First, turn off magic_quotes. This means that any data going into your script will have nothing escaped.

Then run mysql_real_escape_string() on the data, that'll add the slashes for the query only, and will store the data in the database without the slashes.

When you load the data again, you'll be good to go and there won't be any backslashes around the quotes.

Answer 6

Take a look at your magic_quotes settings, and compare them on your servers. Your home servers probably have magic_quotes disabled (as they should be, since Magic Quotes is deprecated in current versions of PHP), and your web host likely has them enabled (which is sadly common). You can use:

<?php echo phpinfo(); ?>

for a quick comparison.

If magic_quotes is new to you, you may want to look into PHP security a bit more before you deploy anything you care about.

Questions containing "magic_quotes"

Answer 7

The real solution is to use prepared statements in your insert queries.

Here's a site with an insert example right at the top.

Prepared Statements in PHP

The benefit is that it's a best practice, and the side-effect is that you no longer have to worry about cleansing the input against SQL injection attacks.