Re: [PHP-DB] Removing slashes from the database

On 03/06/15 09:37, Aziz Saleh wrote:
> On Wed, Jun 3, 2015 at 12:25 AM, Ron Piggott 
> <[email protected] 
> <mailto:[email protected]>> wrote:
>
>     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
>
>
> 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;
I am still having something weird happening which I don't understand.

When I do print_r( $query ); the output is

UPDATE `donation_paypal_code` SET `option` = 
REPLACE(REPLACE(REPLACE(`option`,'\\','\'),"\'","'"),'\"','"');

and I receive the database error

(
     [0] => 42000
     [1] => 1064
     [2] => 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
)

I have confirmed my script has \\\\ & \\

When I "search" using phpMyAdmin \ turns into \\\\ and \\ turns into 
\\\\\\\\   Is this what I should be using in order to get PHP to submit 
what I want into the database?

Thanks, Ron