SphinxQL doesn't work with prepared statements?

Question

I found several threads on the Sphinx forum discussing this issue:

http://sphinxsearch.com/forum/view.html?id=5974

My function for creating a DB connection:

function createSphinxPdo()
{
    try {
        $options = [
            \PDO::ATTR_EMULATE_PREPARES => true,
            \PDO::ATTR_ERRMODE          => \PDO::ERRMODE_EXCEPTION
        ];
        $db      = new \PDO("mysql:host=127.0.0.1;port=3308;charset=utf8mb4", null, null, $options);
    } catch (\PDOException $e) {
        throw $e;
    }

    return $db;
}

This works fine:

$sphinxDb = createSphinxPdo();

$stmt = $sphinxDb->prepare('
SELECT *, WEIGHT() AS w FROM my_index
WHERE MATCH(\'@column "word"/1\')
');

try {
    $success = $stmt->execute();
} catch (\PDOException $e) {
    throw $e;
}

$results = $stmt->fetchAll(\PDO::FETCH_ASSOC);

echo '$results<pre>' . PHP_EOL;
print_r($results);
echo PHP_EOL . '</pre>';

No results returned:

$stmt = $sphinxDb->prepare('
SELECT *, WEIGHT() AS w FROM my_index
WHERE MATCH(\'@column ":word"/1\')
');

try {
    $success = $stmt->execute([':word' => 'word']);
} catch (\PDOException $e) {
    throw $e;
}

$results = $stmt->fetchAll(\PDO::FETCH_ASSOC);

echo '$results<pre>' . PHP_EOL;
print_r($results);
echo PHP_EOL . '</pre>';

When I bind values to a prepared statement, no results are returned.

score 2 · Accepted Answer · answered Mar 23 '15 at 15:00

Ah, you can't 'bind' in the middle of other strings. Because its expected that the prepared statements will take care of quoting.

You can do:

$stmt = $sphinxDb->prepare('
SELECT *, WEIGHT() AS w FROM my_index
WHERE MATCH(:query)
');
....
$success = $stmt->execute([':query' => '@column "word"/1']);

Ie the parameter should be the whole extended query.

To tie with your other question, you COULD use EscapeString directly on the value of the array, eg

$success = $stmt->execute([':query' => EscapeString($query)]);

if don't want to allow any of the extended query syntax. EscapeString will take care of the query syntax, pdo/mysql-client will take care of the SphixnQL quoting.

SphinxQL doesn't work with prepared statements?

1 Answers1

Linked