Removing Special Characters in SphinxSE

Question

Im using an implementation of the sphinx storage engine to search on my site, which works pretty well, however, when the search includes characters like @, the search fails with the following error:

There was a problem processing the query on the foreign data source. Data source error: search query already specified 

and php throws this error:

 Warning: mysql_query() [function.mysql-query]: Unable to save result set in /home/path/to/file.php on line 100 

Im avoiding user input using mysql_real_escape_string

Interestingly, if I copy the request and run it directly in phpmyadmin, I get no errors.

  query = '@title("cheese & cake");limit=1000filter=type=1;ranker=sph04;mode=extended;sort=extended:@weight desc;' 

Answer 1

Character smoothing in Sphinxql is a tricky question ... I'm not sure if it is fully resolved. mysql_real_escape_string will not handle special Sphinx request characters.

They provide an escape function in sphinxapi.php:

 function EscapeString ( $string ) { $from = array ( '\\', '(',')','|','-','!','@','~','"','&', '/', '^', '$', '=' ); $to = array ( '\\\\', '\(','\)','\|','\-','\!','\@','\~','\"', '\&', '\/', '\^', '\$', '\=' ); return str_replace ( $from, $to, $string ); } 

Note that this will not specifically handle SQL escape characters (for example, without replacing a single quote). In fact, I tested it, and it doesn't even work for Sphinx characters only.

You need this function:

 function EscapeSphinxQL ( $string ) { $from = array ( '\\', '(',')','|','-','!','@','~','"','&', '/', '^', '$', '=', "'", "\x00", "\n", "\r", "\x1a" ); $to = array ( '\\\\', '\\\(','\\\)','\\\|','\\\-','\\\!','\\\@','\\\~','\\\"', '\\\&', '\\\/', '\\\^', '\\\$', '\\\=', "\\'", "\\x00", "\\n", "\\r", "\\x1a" ); return str_replace ( $from, $to, $string ); } 

Note the additional backslashes on sphinx-specific characters. I think what happens is that they send your entire query through the SQL parser, which removes the backslash of “extraneous” for SQL purposes (i.e. '\ &' → '&'). Then he puts the MATCH sentence through a full-text parser, and suddenly "&" is a special character. So you need an extra backslash at the beginning.