CoderZone.org
Pages: 1 « previous     next »
  Print  
Author Topic: mysql_real_unescape_string ?  (Read 10428 times) Bookmark and Share
cuberat
Newbie
*
Posts: 40


View Profile
« on: Apr 02, 2011, 01:09:38 pm »

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?
Logged
Max
Jr. Member
*****
Posts: 75



View Profile WWW
« Reply #1 on: Apr 06, 2011, 07:17:29 pm »

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?
Logged
Keith
Newbie
*
Posts: 11


View Profile
« Reply #2 on: Apr 07, 2011, 01:01:40 pm »

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.
Logged
cuberat
Newbie
*
Posts: 40


View Profile
« Reply #3 on: Apr 07, 2011, 02:15:46 pm »

Thanks for the ideas ...

Ran phpinfo() through the web

Quote
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:  
Highlight Mode: (PHP)
  1. $sQuery = 'SELECT `name`,`service_provider_id` AS \'id\' FROM `service_provider` ';
  2.        $sQuery.=' ORDER BY `name`';
  3.        $this->db->query($sQuery);
  4.        $aResult=array();
  5.        while ($rResult = $this->db->fetch_assoc())
  6.                $aResult[]=str_replace('\\','',$rResult);
  7.        $this->db->free_result();
  8.        return $aResult;
  9.  
 

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

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

Code:  
Highlight Mode: (MySQL)
  1. mysql> select service_provider_id,name from service_provider;
  2. +---------------------+------------------------------+
  3. | service_provider_id | name                         |
  4. +---------------------+------------------------------+
  5. |                   3 | Amy\'s SP                   |
  6. |                   1 | default                      |
  7. +---------------------+------------------------------+
  8. 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.
Logged
Keith
Newbie
*
Posts: 11


View Profile
« Reply #4 on: Apr 07, 2011, 07:59:01 pm »

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.
Logged
cuberat
Newbie
*
Posts: 40


View Profile
« Reply #5 on: Apr 08, 2011, 06:29:47 am »

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:  
Highlight Mode: (MySQL)
  1. UPDATE `service_provider` SET `name`=REPLACE(`name`,"\\",""),`contact_information`=REPLACE(`contact_information`,"\\","");
 

Thanks for the ideas, they really helped.
Logged
Tags:
Pages: 1
  Print  
 
Jump to: