Mysql Query issues using LIKE and apostrophe

Question

So I have an interesting issue that I have never come across and can't seem to find much information about correcting the issue. I have massive database that has an enormous amount of data in it (10 years worth), and attempting to search through it.

Now the search stuff works fine, but recently someone brought it to my attention about a 'bug' if you will. I've tried to trouble shoot it to get the expected results, but to no avail.

This is my issue:

When someone uses an apostrophe in the search, it's not that the search faults out, But it returns no results. So for example when searching Pete's the query executes but returns nothing. Yes, I make sure the query has the mysql_real_escape_string() so that Pete's becomes Pete\'s.

Even when I try to query it using phpmysql's search feature, I get strange results. The query is intended to be like this:

SELECT * FROM  `smd_article` WHERE  `copy` LIKE  '%Pete\'s%'

But when I use the search feature in phpmyadmin, it gives me:

SELECT * FROM  `smd_article` WHERE  `copy` LIKE  '%Pete''s%'

And when I actually type out the query in the sql tab, it still returns no results. But there are some 17K records that get returned when I just use Pete and some 3k records when I do just Petes.

So I am curious as to what I am doing wrong, or missing to make it so that it can allow for the apostrophe in a query using the LIKE statement. Thoughts?

Answer 1

try by mysql_real_escape_string

LIKE '%" . mysql_real_escape_string($find) . "%' 

or

 LIKE  '%Pete%27s%'

or if you are using pdo you can do like

$search ='Pete\'s'
$stmt = $db->prepare("SELECT * FROM  `smd_article` WHERE  `copy` LIKE ?");
$stmt->bindValue(1, "%$search%", PDO::PARAM_STR);
$stmt->execute();

Answer 2

The problem is not because of the ' single quote. You escaped it correctly. (as the search function in phpmyadmin):

String: Pete's

MySQL: 'Pete''s'

So the following query will work. (I've tested it)

SELECT *
FROM `smd_article`
WHERE copy
LIKE '%Pete''s%'

You can also have a look at the IN statement:

SELECT *
FROM `smd_article`
WHERE copy
IN (
 'Pete', 'Pete''s'
)