Mysql PDO search with slash (/) fails

Hello, has anyone run into this problem:

If I search a database using mysql and PDO (prepared statements and the whole thing) and the search term has a slash in it, the search will fail?

Example, searching for “ac/dc” fails immediately, while searching for either “ac” or “dc” brings up results.

So the slash must be causing a problem. Ironic really because that is the whole point of PDO, not having to deal with escaping strings, etc.

Any ideas?

It’s not going to be a problem to do with escaping strings seeing as the forward slash character is never (afaik) used as an escape character.

Please post the query/queries you’re using here, along with a few sample rows of data — the problem will most likely lie here somewhere.

ok, couple of examples:

select id, title from table where title like '%%ac/dc%%';

This works fine in a straight sql search, but no matches when run through PDO.

sample data: title =
AC/DC: Live at Donington
AC/DC: No Bull

Another interesting thing I just found out (unrelated to above) is that this search will also fail when trying to do FULLTEXT searching.

I have 2 different searches, one FULLTEXT and another using traditional LIKE (as above) when the words have less than 4 characters because they are not indexed by FULLTEXT.

I’m not sure why you’ve doubled the % characters in the query. Although it probably won’t affect anything (as a % in a LIKE query signifies zero or more characters), it’s worth removing one from each site.

I’m also unsure as to exactly how you are binding the value to the query seeing as you didn’t post that part of the code.

I just got the following code to work successfully on my dev machine (i.e. it returned the two rows as expected). PHP 5.3.5 and MySQL 5.1.40.


/*

CREATE TABLE albums
( id INT PRIMARY KEY AUTO_INCREMENT
, title VARCHAR(99) NOT NULL
);

INSERT INTO albums
  ( title )
VALUES
  ( 'AC/DC: Live at Donington' ),
  ( 'AC/DC: No Bull' );

*/

$db = new PDO('mysql:server=localhost;dbname=test', 'username', 'password');

$keywords = 'ac/dc';

$query = $db->prepare('SELECT id, title FROM albums WHERE title LIKE :keywords');
$query->bindValue('keywords', '%' . $keywords . '%');
$query->execute();

echo '<pre>';

print_r($query->fetchAll());

Does this help at all?

The double %%: to be honest, this was something my brother suggested (he read it somewhere) and when we tried it we seemed to get better matches than using single. I was also under the impression that a single % meant one or more characters, but the double %% really made a difference. I haven’t had time to investigate why this would be.

So you’re saying it worked fine for you with PDO?

Sorry I didn’t post the code earlier but it’s pretty convoluted, it was something like this:

$query = $dbh->prepare("SELECT * FROM table WHERE title LIKE :title");											
$data2match = array(':title'=>"%%$keyword%%");
$query->execute($data2match);

$data2match is an array because there are more variables that go in the code.

BTW
Thanks a million for taking the time to try it, that was really great and it does help a lot because it means it’s not PDO, but maybe something I’m doing wrong. It seems that my short (LIKE) search wasn’t being called and it was going to the FULLTEXT search which will not return any hits for “ac/dc” which I’m still investigating, but the term, using a LIKE search with PDO does work, I just modified the code and tried again.

Thanks a lot.

Now if anyone has any idea why a FULLTEXT search would fail for “ac/dc” (I’m sure it’s the slash causing problems) I’d be even more grateful :smiley:

Could you show me your fulltext search query?

Something like this:

select * from table where match(title) against('ac/dc' in boolean mode)

It doesn’t matter if I put the term in double quotes (as a strict boolean would have) or if I change boolean mode to WITH QUERY EXPANSION. Never finds anything.