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
