Search code examples
mysqljsonpdomysql-json

How to use PDO bindParam inside MySQL JSON_CONTAINS?

I'm trying to get the following to run:

$driverID = 123; 

$query = 'SELECT
*
FROM cars
WHERE JSON_CONTAINS(`data`, \'{"drivers": [{"driverID": ":driverID"}]}\');';

$statement = $db->prepare($query);
$statement->bindParam(':driverID', $driverID);
$result = $statement->execute();

However, it always results in an empty match. Removing the parameter and inputting the value directly works fine.

I suspect there's an issue with the quotes but I can't quite figure it out - what I would need to modify to get it to work?

Solution

  • You can never put a parameter placeholder inside string delimiters.

    WRONG:

    SELECT * FROM MyTable WHERE mycolumn = ':param'

    RIGHT:

    SELECT * FROM MyTable WHERE mycolumn = :param

    A good workaround is to build the JSON string in your application code and then bind the whole JSON string to the parameter. 

    $driverID = 123; 
$obj = ['drivers' => [['driverId' => $driverID]]];
$json = json_encode($obj);

$query = 'SELECT
*
FROM cars
WHERE JSON_CONTAINS(`data`, :json);';

$statement = $db->prepare($query);
$result = $statement->execute(['json'=>$json]);

    Using json_encode() is a good way to ensure that the data is in valid JSON format. It protects you from making accidental syntax mistakes.

    See a very similar case (though for PostgreSQL) here: Rails ActiveRecord: How to use bind variables with double quotes on jsonb