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 <ron.piggott@actsministries.org <mailto:ron.piggott@actsministries.org>> wrote:

    On 02/06/15 22:58, Aziz Saleh wrote:

    On Tue, Jun 2, 2015 at 10:50 PM, Ron Piggott
    <ron.piggott@actsministries.org
    <mailto:ron.piggott@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