Apostrophe and MySQL Update

[xProteuSx]

I am having trouble, because I am trying to enter a string, such as this into a database field:

 

$string = "There's trouble ahead because they're silly.";

 

Ofcourse, MySQL craps out because of the apostrophes.

 

So I did this:

 

$string = mysql_real_escape_string($string);

 

This is entered into the database, however it is entered as this:

 

"There\'s trouble ahead because they\'re silly."

 

I was wondering how I can enter apostrophes, without entering a backslash, because now when I pull the text from the DB and display it on a page, I get a backslash in front of all apostrophes.

 

HELP!  Please!

 

Is the only way around this to add slashes, then use stripslashes() when displaying text??

[litebearer]

Not sure if it is the ONLY way, but stripslashes will work. Is there a valid reason you don't want to use it?

[xProteuSx]

My reason for not using it is that I would have to go through my code, some 40+ pages, and thousands of lines of code to mysql_real_escape_string() and stripslashes() all pertinent data.  Its a lot of work, plus inevitable I will miss some, and have to fix these issues as they come up ...

 

Long story short, I want everything to work 100% right off the bat.

[litebearer]

I can sympathize with that; however, using mysql_real_escape_string should ALWAYS be used. As to 100% off the bat, sometimes means hardwork. Not said to demean, rather stating the realities of life. Too often we (ME) write lines and lines of code before we test.  And yes, it can suck big time.

[PFMaBiSmAd]

The symptoms you stated are not possible. For the $string variable with that content, you will not end up with any \ characters stored in your database after using mysql_real_escape_string once on the data. You must have something else going on in your actual code OR the $string variable is actually coming from external data (get, post, cookie) and magic_quotes_gpc is ON and is escaping the value once before it reaches your code.

 

You need to use mysql_real_escape_string on string data being put into your query statements to both properly escape data (the escaping that magic_quotes_gpc does, does not take into account different character sets, which is why it has been completely removed in the latest php version), and to prevent sql injection. I also hope you are validating/casting external numerical data being put into your queries to prevent sql injection through it as well. If you are not already using mysql_real_escape_string on string data and validating/casting numerical data (or using prepared statements), you will need to modify your code to do so, assuming you don't want database errors to occur for legitimate visitors or hackers dumping the content of your database tables.

 

If your 40+ pages are largely the same, but for different content or different data value, you need to re-factor/simplify what you are doing and make your pages data driven (i.e. have one or just a few pages where the data defines what your code does on the one or just a few pages.)

[xProteuSx]

PFMaBiSmAd,

 

You are absolutely correct, the data is coming from a form, method=get.  That having been said, there is definitely a '\' character in the database field.  Now, you mentioned something about magic_quotes_gpc ... what is that?  I don't know if its on, but by the sound of it, it is.  What's that all about?

[PFMaBiSmAd]

The php.net documentation has a section on what the magic_quotes settings do, didn't do, and why php.net tried to get the language to do something that the programmer should have been doing only when he needed it to be done - http://us.php.net/magic_quotes

 

If you cannot turn off magic_quotes_gpc or want your code to be universal, see this link - http://www.phpfreaks.com/forums/index.php?topic=357226.msg1688577#msg1688577

 

 

[PFMaBiSmAd]

Further to the above, if your external data contains arrays, you would need to write a function to unescape the data and use array_walk_recursive to process all nested levels of the external data.

 

Edit: Specifically -

 

<?php

function unescape_deep(&$item, $key){
	$item = stripslashes($item);
}

if(get_magic_quotes_gpc()){
array_walk_recursive($_GET,'unescape_deep');
array_walk_recursive($_POST,'unescape_deep');
array_walk_recursive($_COOKIE,'unescape_deep');
}

 

[chriscloyd]

when you echo your info back from the database you can use

<?php
//act like this is the string from your db
$string = "Hi I\'m Chris, please don\'t kill me!";
echo stripslashes($string);
?>

 

[PFMaBiSmAd]

You cannot unconditionally use stripslashes on data as that would prevent you from actually using the \ character in the data.

 

You must fix what the magic_quote settings do so that your data is stored and retrieved correctly (if you double escape the data and the extra \ characters get stored in your database, you cannot correctly perform any searches on that data should the magic_quotes setting get altered after the data has been stored.)

[xProteuSx]

chriscloyd,

 

You're right.  This is what I've had to do.  However, I am going to implement PFMaBiSmAd's fix, which seems more logical.  However, this will affect my current data, as there are now several entries that contain the \ character.  I will edit these by hand, as there are not that many.

 

I have turned off get_magic_quotes_gpc() using an entry in my .htaccess file:

 

php_flag magic_quotes_gpc Off

 

So far it works on my XAMMP install.  Hope I don't run into any BS on the actual site server.

 

Thanks for the help.