Re: Removing slashes from the database

On 02/06/15 23:20, Aziz Saleh wrote:
> On Tue, Jun 2, 2015 at 11:08 PM, Ron Piggott 
> <[email protected] 
> <mailto:[email protected]>> wrote:
>
>
>     On 02/06/15 22:58, Aziz Saleh wrote:
>>
>>
>>     On Tue, Jun 2, 2015 at 10:50 PM, Ron Piggott
>>     <[email protected]
>>     <mailto:[email protected]>> wrote:
>>
>>
>>         I am working through the process of removing \'s from the
>>         database. I am trying to get this query using a variable
>>         starting with "<<<"
>>
>>         $query1  =<<<EOF
>>         UPDATE `TABLE_NAME` SET `COLUMN_NAME` =
>>         REPLACE(REPLACE(REPLACE(`COLUMN_NAME`,'\\\'','\''),'\\\"','"'),'\\\\','\\');
>>         EOF;
>>
>>         But when I go to execute the query I am getting the error:
>>
>>         |#1064 - You have an error in your SQL syntax; check the
>>         manual that corresponds to your MariaDB server version for
>>         the right syntax to use near '\''),'\\"','"'),'\\','\')' at
>>         line 1 |
>>
>>         Could someone help me know what \ and ' should be part of
>>         this query so it will execute correctly --- only removing \'s
>>         from the database table text columns?
>>
>>         Thank you.
>>
>>         Ron
>>
>>
>>     When you say remove, as replace all occurrences with an empty
>>     string, or replace with a different character?
>     I want \" to become just "
>     I want \' to become just '
>     I also want however \ was escaped to become just \
>
>     (I am trying to revert the text back to what it was originally
>     before mysql_escape_string was applied)
>
>     I hope this helps elaborate.
>
>     Ron
>
>
> For simplicity sake, do each one in its own query and see which one 
> breaks if any:
>
>
> $query1  =<<<EOF
> UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(`COLUMN_NAME`,'\"','"')
> EOF;
> $query2  =<<<EOF
> UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(`COLUMN_NAME`,"\'","'")
> EOF;
> $query3  =<<<EOF
> UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(`COLUMN_NAME`,'\\\\','\\')
> EOF;
>
> However, personally, I do not recommend this sort of action. Your data 
> should be escaped in the DB. Your MySQL driver should be handling the 
> escape/un-escape when setting/retrieving the data.
A friend pointed out to me today: In the earlier versions of PHP there 
was a setting called 'magic_quotes_gpc'.  When enabled slashes were 
added  by default. This setting has since been depreciated as of PHP 5.3 
and was removed completely in PHP 5.4.  I am using PHP 5.6.

Thank you for the suggestion of running 3 separate commands. 
Individually these execute successfully.  Is it even possible to do a 
"REPLACE" in the fashion I have noted?

Ron