MySQL escaped strings problem

Question

In some PHP code, I have an mysql_real_escape_string()'d term, like foo\'s. I search that in my database (where it is also stored as foo\'s) like so:

mysql_query("SELECT * FROM coupons WHERE retailerName LIKE '%" . $searchTerm . "%'");

The query should look like this without variables:

SELECT * FROM coupons WHERE retailerName LIKE '%foo\'s%'

If I search f, fo, or foo, then the search works. But if I search foo's then the search doesn't work (keep in mind that the actual query takes an escaped string, so everything should match up).

Perhaps your data is using curly apostrophes, and that's why it's not matching? — Paul Schreiber
– Paul Schreiber, Commented Mar 8, 2011 at 6:12
More code and more info, please. What does the line with mysql_real_escape_string() look like? Could you show us the error message from mysql_error()? — nitro2k01
– nitro2k01, Commented Mar 8, 2011 at 6:16
Are you getting a error, or no data? What is the value of the retailerName row you are trying to find. foos or foo's — The Scrum Meister
– The Scrum Meister, Commented Mar 8, 2011 at 6:28

GregA100k · Accepted Answer · 2011-03-08 06:47:42Z

Perhaps the interface from you program to mysql (JDBC or similar) is adding extra escape characters to your string. If the same mechanism is not what put the data into the database, try doing an insert to see how the data gets stored.

Mysql can handle the query through it's own interface

mysql> describe test_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col1  | varchar(20) | YES  |     | NULL    |       |
| col2  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into test_table (col1, col2) values ('col1value', 'foo\'s');
Query OK, 1 row affected (0.04 sec)

mysql> select * from test_table where col2 like '%foo\'s%';
+-----------+-------+
| col1      | col2  |
+-----------+-------+
| col1value | foo's |
+-----------+-------+
1 row in set (0.00 sec)

binaryLV · Accepted Answer · 2011-03-08 07:18:20Z

If it's stored as foo\'s in DB, then there are 2 options - either you are double-escaping (i.e., using mysql_real_escape_string() twice), or you are escaping values that "something" (e.g., magic quotes) has already slashed.

Check if you have magic_quotes_gpc enabled.

Here's PHP5.3 code for stripping "magic quotes" automatically (can be used in config file). For older PHP, callback function would look differently, but you should get the idea from this.

// remove slashes, if they are being automatically added
if ( get_magic_quotes_gpc () ) {
    $stripslashes = function($value) use(&$stripslashes) {
        if ( is_array($value) ) {
            return array_map($stripslashes, $value);
        }
        return stripslashes($value);
    };
    $_GET    = array_map($stripslashes, $_GET);
    $_POST   = array_map($stripslashes, $_POST);
    $_COOKIE = array_map($stripslashes, $_COOKIE);
    unset($stripslashes);
}

Collectives™ on Stack Overflow

MySQL escaped strings problem

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related