Can't Reference a Database Bind Parameter Multiple Times

The CMS Questions
1

I noticed that Kirby’s database class can’t reference the same bind parameter multiple times in a single query.

$query
   ->where('match (name) against (:name_param) > 0')
   ->order('match (name) against (:name_param) desc');

Doing this returns an empty collection. No errors, just an empty collection - even though the query returned by ->debug() executes correctly when copy/pasted into a SQL client.

I don’t know much about PDO - is this expected behavior?

2

I don’t really understand what the match function inside the order statement is supposed to do, shouldn’t it just order by name?

$query
   ->where('match (name) against (:name_param) > 0')
   ->order('name desc');
3

match...against does a full-text search. It’s ordering by how relevant the search engine thinks the row is to the query.

That’s not really the point though. This query exhibits the same zero-results behavior:

$query->select('name', ':param', ':param')->bindings([...])->all();

If :param is only referenced once in the query, results are as expected with a name column, a param column, and a result for every record in the table. (Every resulting object has the same value in the param field of course - whatever the binding was.)

As soon as the param is mentioned a second time anywhere in the query (a second time in the select statement like this example, again the the where or order-by clauses, etc.) an empty collection is returned with no errors.

4

Hm, maybe @lukasbestle has an idea?

5

@Jayshua Which database engine and version as well as which PHP version are you using?

6

MySQL version 8.0.18, PHP version 7.4.

7

Thanks for the information and sorry for the delay. I will try to get back to you in the next few days.

8

I’ve done some research and it is indeed a limitation of PDO or actually MySQL:

MySQL doesn’t support named parameters (:name_param), so PDO replaces the named parameters with ? characters and passes the bound parameter values as a simple ordered list. This is why the same parameter name cannot be used twice as the value would need to be transmitted to the database twice:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.
– PHP: PDO::prepare - Manual

There are two workarounds for this:

  • Supply the parameter value twice with different and unique names.
  • Use $database->connection()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); to make PDO emulate the named parameters. PDO will then escape the parameter values locally and bake them right into the query instead of passing them as a raw list to the database. As far as I read, PDO’s emulation should be pretty good by now, but of course it’s going to be less reliable than native prepared statements.