DatabaseException on select query with extended characters as first symbols in a match value

[sergeypayu]

I get Foolz\SphinxQL\Exception\DatabaseException: [1064] index kbindex: syntax error, unexpected ')' near '\()' [ SELECT * FROM kbindex WHERE MATCH('(\\()')] error when trying to make a select query if I use special symbols as first character in match value. Example:

$query = SphinxQL::create($this->conn)->select()->from('kbindex')->match('*','(test');
$result = $query->execute();

I didn't find any way to gracefully escape such values. I can bypass the problem by manually constructing a match query like this:

private function createSelectQuery($query, $index)
{
        $sq = SphinxQL::create($this->conn);
        $query = $this->conn->escape($sq->escapeMatch($query));
        return $sq->query('SELECT * FROM '.$index.' WHERE MATCH('.$query.')');
}

But I hope you can fix it inside your code.

[Necklaces]

I get this as well, weirdest part is when input is nothing but a space it throws the same error..

Edit: looks like it might have something to do with the MATCH part of the query builder, usually the query looks something like this:

SELECT * FROM table WHERE MATCH('(@(column) searchterm)');

But when given only a space it just looks like this

SELECT * FROM table WHERE MATCH((@(column)  )');

Which is a syntax error in SphinxQL afaik.

With just a parenthesis it correctly escapes it but it's still a syntax error:

SELECT * FROM table WHERE MATCH((@column) \\))');

My suggestion is to just enclose the search-term part with double quotes, like this:

SELECT * FROM table WHERE MATCH((@column) "\\)")');

This has fixed all of the failed queries I've tested, but I'm no expert so I'm not sure if it's the most ideal solution.

[oohnoitz]

This fell off my radar a few times already and it's a bit of a difficult solve.

@sergeypayu could you try the following as part of your query? I wonder if we have some odd escaping issue with our current list of characters/mapping.

->setFullEscapeChars(['\\', '(', ')', '|', '-', '!', '@', '%', '~', '"', '&', '/', '^', '$', '='])
->setHalfEscapeChars(['\\', '(', ')', '!', '@', '%', '~', '&', '/', '^', '$', '='])

@Necklaces could you also try the same? Also, let me try to address some of the errors you are having...

SELECT * FROM table WHERE MATCH((@(column)  )');
SELECT * FROM table WHERE MATCH((@column) \\))');

This does seem to be the correct behavior.

SELECT * FROM table WHERE MATCH((@column) "\\)")');

From what I can tell, this one actually works because it's performing an exact match and assumes everything in the double quotes to be what it should be searching in your documents/index.

[oohnoitz]

Also, I don't have searchd configured to run in my local development environment so I can't confirm some of these suspicions I have at the moment.

[Necklaces]

Well, I tried doing this:

$sq = (new SphinxQL($this->sphinx))->select('id')->from('index_table');
# Redacted some parts of the code that just adds to the query builder, like ->where()
$sq = $sq->setFullEscapeChars(['\\', '(', ')', '|', '-', '!', '@', '%', '~', '"', '&', '/', '^', '$', '='])
->setHalfEscapeChars(['\\', '(', ')', '!', '@', '%', '~', '&', '/', '^', '$', '=']);

$sphinxql = $sphinxql->match(['column1', 'column2'], $search_term);

$ids = array_column($sphinxql->execute()->fetchAllAssoc(), 'id');

But I still get the same error when putting ')' into search term:

index index_table: syntax error, unexpected ')' near ' \))' [ 
SELECT id FROM index_table 
WHERE MATCH('(@(column1,column2) \\))')

Same goes for space, and seemingly all of these characters when alone in the searchterm.
In contrast, searching for "TEST!" (rather than just "!") works.

[arnisjuraga]

The same problem here. If the term starts with ( or - - it generates fatal error syntax error, unexpected...

What I did as a dirty workaround, is just to remove all special characters from the beginning of string:

$search_term = ltrim($search_term, '\()-!@%~"&/^$=');
if(!$search_term) {
  return [];
}

Any better ideas?

[MikhailGuschin]

Hello.
One more problem example. I tried to search expressions === | *===* in index. SphinxQL build query SELECT * FROM rt_cohorts_groups WHERE MATCH('(@(cohort_name,group_name) \\(\\=\\=\\= | *\\=\\=\\=*\\))') and after execute get the error: index rt_cohorts_groups: syntax error, unexpected '|' near ' \(\=\=\= | *\=\=\=*\))'.

[ghost]

Having this issue too. Only when it's the only char. Can search for /whatever but not for / or a space etc.

It also seems you cannot exclude words anymore with the - char.

[ghost]

I just did this and it seems to work. Searching for / or a space as the only char doesn't crash anymore, plus Boolean search now works:

$query->match($column, $searchString, true);

try {
    return $query->execute()->fetchAllAssoc() ?? [];
} catch (\Foolz\SphinxQL\Exception\DatabaseException $e) {
    return [];
}

Added the true param to the match() method and then wrapped the execution in a try/catch just outputting an empty array on error.