Sphinx: PDO exception with certain characters

Question

I'm trying to get the Sphinx search server working with PDO, but it triggers a syntax error when using the MATCH() function in specific scenarios.

Ex.:

In my code I'm splitting the search query by space and then concatenate it using the | (OR) operator. If someone types test > 3, in the match function it would become (test | > | 3). This combination triggers a: Syntax error or access violation: 1064 main_idx: syntax error, unexpected '|' near ' > | 3'. I don't think it's an escape problem because the > character is not on the escape list and even if you try to escape it, it doesn't work. Is this a bug in the version of Sphinx i'm using? Or am I doing something wrong?

I'm using Sphinx version 2.2.11. It's actually a docker instance provided by this image: jamesrwhite/sphinx-alpine:2.2.11 The PHP version is 7.2.

This is my non-working code:

$searchQuery = "SELECT * FROM main_idx WHERE MATCH(:search)";
$dbh = new PDO('mysql:host=127.0.0.1;port=9306', 'root', 'root');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare($searchQuery);
$stmt->bindValue('search', 'test | > | 3');
$stmt->execute();

Same code works perfectly fine if I'm using the MySQLi extension. It also works fine with PDO and Sphinx version 2.2.6. Something must've changed between 2.2.6 and 2.2.11. Anyone encountered this issue?

MATCH(), should contain a comma separated list of column name(s) to search in and AGAINST() should contain what you are searching for in the column name(s) listed in your MATCH() — Stephanie Temple
@StephanieTemple Sorry, but this is a question about Sphinx using SphinxQL. You are referring to MySQL syntax there which is not the same. — azertys

Manticore Search Manticore Search · Accepted Answer · 2019-01-17T04:14:46

This behaviour is caused by this bug http://sphinxsearch.com/bugs/view.php?id=2305 and this fix https://github.com/sphinxsearch/sphinx/commit/d9923f76c7724fa8d05a3d328e26a664799841b7. In the previous revision ' > | ' was supported. We at Manticore Search (fork of Sphinx) will check if the fix was correct and will make a better fix if that's not. Thanks for pointing this out.

Meanwhile you can use 2.2.8 from http://sphinxsearch.com/downloads/archive/ or build manually from the latest revision which supports the syntax (https://github.com/sphinxsearch/sphinx/commit/f33fa667fbfd2031ff072354ade4b050649fbd4e)

[UPDATE] The fix is proper. It was wrong to not show the error about that in the previous versions as long as you DON'T have the spec. character (>) in your charset_table. To workaround this you can add > to your charset_table and then escape it in the search query, e.g.:

mysql> select * from idx_min where match('test | \\> | a');
+------+---------+----------+-------+------+
| id   | doc     | group_id | color | size |
+------+---------+----------+-------+------+
|    7 | dog > < |        5 | red   |    3 |
+------+---------+----------+-------+------+
1 row in set (0.00 sec)

mysql> select * from idx_min where match('test | \\< | a');
+------+---------+----------+-------+------+
| id   | doc     | group_id | color | size |
+------+---------+----------+-------+------+
|    7 | dog > < |        5 | red   |    3 |
+------+---------+----------+-------+------+
1 row in set (0.00 sec)

or

$stmt->bindValue('search', 'test | \\< | a');

in PDO.

There's still a little bug found though which is that if non-spec character is not in charset_table it doesn't generate an error. E.g.

mysql> select * from idx_min where match('test | j | a');
Empty set (0.00 sec)

works fine even though j is not in charset_table. I've filed a bug in our bug tracker https://github.com/manticoresoftware/manticoresearch/issues/156 Thanks again for helping to point this out.

Sphinx: PDO exception with certain characters

2 Answers