Re: Removing slashes from the database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On 03/06/15 09:37, Aziz Saleh wrote:
On Wed, Jun 3, 2015 at 12:25 AM, Ron Piggott <ron.piggott@xxxxxxxxxxxxxxxxxx <mailto: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
    <mailto: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
        <mailto: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;
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



[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux