On Wed, Jun 3, 2015 at 12:25 AM, Ron Piggott <ron.piggott@xxxxxxxxxxxxxxxxxx > wrote: > On 02/06/15 23:20, Aziz Saleh wrote: > > On Tue, Jun 2, 2015 at 11:08 PM, Ron Piggott < > ron.piggott@xxxxxxxxxxxxxxxxxx> wrote: > >> >> On 02/06/15 22:58, Aziz Saleh wrote: >> >> >> >> On Tue, Jun 2, 2015 at 10:50 PM, Ron Piggott < >> ron.piggott@xxxxxxxxxxxxxxxxxx> 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 > It is possible, but sometimes with the clutter you don't notice a syntax issue. This seems to work fine: $query =<<<EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(REPLACE(REPLACE(`COLUMN_NAME`,'\\\\','\\'),"\'","'"),'\"','"') EOF;
