[cuberat]

I have some strings that I used mysql_real_escape_string on prior to putting them in the database and now ... needless to say ... they're coming out with backslashes  ...

I resorted to the following:

SELECT REPLACE ( `field`,"\","" ) AS `field` FROM `table`;

am I missing a better solution?

[Max]

You could use stripslashes() on the affected fields. I think using 'SELECT REPLACE...' would remove all slashes, including any that are supposed to be there.

I have some strings that I used mysql_real_escape_string on prior to putting them in the database and now ... needless to say ... they're coming out with backslashes  ...

I resorted to the following:

SELECT REPLACE ( `field`,"\","" ) AS `field` FROM `table`;

am I missing a better solution?

[Keith]

You shouldn't have to do that. Let's see some code.

Prior to calling mysql_real_escape_string(), I am guessing either 1) magic_quotes is enabled  and not being sanitized, or 2) you are manually adding slashes and are unaware of it.

[cuberat]

Thanks for the ideas ...

Ran phpinfo() through the web

magic_quotes_gpc   Off   Off
magic_quotes_runtime   Off   Off
magic_quotes_sybase   Off   Off

       

Then I checked the code to see exactly what I have.  Right now there are at least two approaches I'm using, until I find a better solution:

Code:  select

Highlight Mode: (PHP)
$sQuery = 'SELECT `name`,`service_provider_id` AS \'id\' FROM `service_provider` ';
        $sQuery.=' ORDER BY `name`';
        $this->db->query($sQuery);
        $aResult=array();
        while ($rResult = $this->db->fetch_assoc())
                $aResult[]=str_replace('\\','',$rResult);
        $this->db->free_result();
        return $aResult;
 
 

Code:  select

Highlight Mode: (PHP)
        $sCols.='`route`.`name` AS \'name\',REPLACE(`service_provider`.`name`,\'\\\\\',\'\') AS  \'service_provider_name\','';
 
 

As you can see - the backslashes are in the database ...

Code:  select

Highlight Mode: (MySQL)
mysql> select service_provider_id,name from service_provider;
+---------------------+------------------------------+
| service_provider_id | name                         |
+---------------------+------------------------------+
|                   3 | Amy\'s SP                   | 
|                   1 | default                      | 
+---------------------+------------------------------+
2 rows in set (0.00 sec)
 

 
I'm stumped, and I'm pretty sure there is a simple solution that I just can't find.

[Keith]

Are you responsible for the INSERT queries as well? It'd help to see the code that's actually sending the data to the database.

[cuberat]

I found the root cause of the problem.

The data is inserted into the database through Perl scripts, which my code calls.  I had an extraneous addslashes call in with the escapeshellargs (duh), which was causing the backslashes to be added in to the database.

All the 'unescape' code was workarounds and I removed it.

I'm pretty ashamed of myself.

This is the SQL I used to clean up the database.

Code:  select

Highlight Mode: (MySQL)
UPDATE `service_provider` SET `name`=REPLACE(`name`,"\\",""),`contact_information`=REPLACE(`contact_information`,"\\","");
 

Thanks for the ideas, they really helped.