Sphinx PHP API EscapeString() function doesn't work for SphinxQL?

Question

I found the following function in the Sphinx PHP API code:

function sphinxapi_EscapeString($string)
{
    $from = ['\\', '(', ')', '|', '-', '!', '@', '~', '"', '&', '/', '^', '$', '=', '<'];
    $to   = ['\\\\', '\(', '\)', '\|', '\-', '\!', '\@', '\~', '\"', '\&', '\/', '\^', '\$', '\=', '\<'];

    return str_replace($from, $to, $string);
}

However, it doesn't seem to work properly because when I use strings with certain characters in them in queries Sphinx throws exceptions.

An example is the quote character ". EscapeString() puts a backslash \ in front of it, but Sphinx throws an exception saying:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 index my_index: syntax error, unexpected $end near ''' in ..

If I add two more backslashes, making it \\\", then no error is thrown.

What's the deal here? Why isn't EscapeString() working?

Answer 1

You havent shared your exact code, but I wonder if you JUST calling this function, need to escape as per SQL rules too.

EscapeString ONLY escapes the query to escape the Extended Syntax charactors.

Thats all that is required in the API, as the Query/AddQuery function takes the query directly.

But in SphinxQL the query string is inside a SQL statement, so the string needs 'SQL String' escaping before being embedded in the statement (whether or not you ALSO escape like EscapeString does). PDO can do it automatically if you use prepared statements, otherwise use the PDO quote function.

(A Query like SELECT ... MATCH('one \" ') isnt escaped propelly, as the slash is 'swallowed' by the SQL parser, not making it through to the Full text Query parser)