[SOLVED] mysql_real_escape_string() question

[RyanSF07]

Hello,

 

I've been using addslashes/stripslashes to insert/retrieve data from the database.

 

From what I've read online, it's better to use mysql_real_escape_string().

 

My question is this: All of the data in my database has had slashes added to it -- what steps should I take to remove them (sql query?), or what should I be aware of in trying to migrate data formatted with addslashes to data formatted with mysql_real_escape_string()?

 

thanks,

Ryan

[akitchin]

if you've been using slashes correctly, they should not make it into your database. the point of escaping is to tell MySQL that those characters are literal, with no additional meaning, such that MySQL will insert them into the table as-is. when it does so, it drops the escaping backslash because it no longer needs it. in essence, \" translates to " upon insertion. if you've got backslashes in your database, it means you've run it twice (such that you're actually escaping the backslashes as well as those characters).

 

if you do have backslashes in your database, the easiest way to eliminate them is by running an UPDATE against the column:

 

UPDATE table SET column = REPLACE(column, '\\', '')

 

keep in mind that this will remove ALL backslashes from all of that column's values, simply because it can no longer differentiate between the backslashes present in the original string and ones that were added by addslashes().

 

once that's done, you can just move the data over by passing it through mysql_real_escape_string() as you normally would with POSTed input. keep in mind that you require a database connection before using mysql_real_escape_string() because it is context-sensitive.

[RyanSF07]

thank you a.kitchin,

Yep -- they're in there. I sent "the" via a web form and it's  	\"the\" in the database. 

I've emailed my web host asking whether or not magic_quotes is on. I don't see it in the php.ini file, but don't know if they have it on by default. If on, would that cause the double backslash insert?

Here is my basic code snip for insert:
$opt1 = addslashes($text);
if($submit)
{	
$sql = "INSERT INTO $table (opt1) VALUES ('$opt1')";

 

thanks again for your help and advice,

Ryan

[akitchin]

you can use the function get_magic_quotes_gpc to check whether the setting is on or off by default, and addslashes only if it isn't on:

 

$opt1 = (get_magic_quotes_gpc() == 0) ? addslashes($text) : $text;

 

that example uses the ternary operator, so if it doesn't look familiar, have a look in the PHP manual about it. essentially that first set of parentheses contains the conditional for a simple if-else loop. the statement before the colon is what is returned if the conditional is true, otherwise it will return the latter.

 

i should note that after doing a bulk UPDATE to get rid of the slashes, you may consider adjusting your script to use mysql_real_escape_string to escape data rather than addslashes.