On Wed, Jun 3, 2015 at 12:25 AM, Ron Piggott <ron.pigg...@actsministries.org
> wrote:

>  On 02/06/15 23:20, Aziz Saleh wrote:
>
>  On Tue, Jun 2, 2015 at 11:08 PM, Ron Piggott <
> ron.pigg...@actsministries.org> wrote:
>
>>
>> On 02/06/15 22:58, Aziz Saleh wrote:
>>
>>
>>
>> On Tue, Jun 2, 2015 at 10:50 PM, Ron Piggott <
>> ron.pigg...@actsministries.org> 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;

Reply via email to