0

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?

Nate
  • 26,164
  • 34
  • 130
  • 214

1 Answers1

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)

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • That does indeed appear to be the issue. I've found binding parameters to a prepared statement with PDO does not work with Sphinx (no search results are returned). Others posted this on the Sphinx forum as well, so it must be a bug. But modifying the Sphinx escape function as shown in this answer seems to fix the issue: http://stackoverflow.com/a/7171719/1101095 – Nate Mar 23 '15 at 13:04
  • Prepared Statements do work with sphinx just fine. I've recently started using them. I suspect using them incorrectly. I still would suggest keeping the two escape functions seperate. ie use EscapeString AND pdo:quote - as they escape different things. Also EscapeString as it stands isn't Multi-Byte safe. – barryhunter Mar 23 '15 at 14:03
  • Prepared statements work, but binding values doesn't. At least not for me. I made a new question: http://stackoverflow.com/questions/29212746/sphinxql-doesnt-work-with-prepared-statements – Nate Mar 23 '15 at 14:25