PDO PHP bindValue doesn't work

Question

I know this has been asked 1000 times, but for some reason I continue to bang my head agains the wall..

This works:

$sql = 'SELECT a.eventCode, a.eventTime, a.teamCode, a.playerCode, b.lastName, b.firstName, b.number, a.xCoord, a.yCoord, a.id ';
$sql = $sql . 'FROM events a, players b ';
$sql = $sql . 'WHERE a.regGUID in ( ' . $regGUID . ' ) and ';
$sql = $sql . 'a.playerCode=b.playerCode and a.gameCode = "' . $game . '" order by a.eventTime desc, a.actionCode asc'; 
$stmt = $db->prepare($sql);
$results = $stmt->execute();

This Doesn't:

$sql = 'SELECT a.eventCode, a.eventTime, a.teamCode, a.playerCode, b.lastName, b.firstName, b.number, a.xCoord, a.yCoord, a.id ';
$sql = $sql . 'FROM events a, players b ';
$sql = $sql . 'WHERE a.regGUID in ( :regGUID ) and ';
$sql = $sql . 'a.playerCode=b.playerCode and a.gameCode = :game order by a.eventTime desc, a.actionCode asc'; 
$stmt = $db->prepare($sql);
$stmt->bindValue(':regGUID', $regGUID, PDO::PARAM_STR);
$stmt->bindValue(':game', $game, PDO::PARAM_STR);
$results = $stmt->execute();

What am I missing? Thanks

Answer 1

The problem is here:

$sql = $sql . 'WHERE a.regGUID in ( :regGUID ) and ';
$stmt->bindValue(':regGUID', $regGUID, PDO::PARAM_STR);

I assume $regGUID is a comma-separated list of quoted strings.

Each query parameter accepts only a single scalar value. Not lists of values.

So you have two choices:

1. Continue to interpolate the $regGUID string, even if you use parameters for other scalar values. But you still want to be careful to avoid SQL injection, so you must form the $regGUID string correctly. You can't just call PDO::quote() on the whole string, that would make it a single quoted string containing UUIDs and commas. You have to make sure each UUID string is escaped and quoted individually, then implode the list together and interpolate it into the IN clause.

   $regGUIDs = explode(',', $regGUID);
   $regGUIDs = array_map(function ($g) { return $db->quote($g); }, $regGUIDs);
   $regGUID = implode(',', $regGUIDs);
   $sql = $sql . 'WHERE a.regGUID in (' . $regGUID . ') and ';
   

2. explode() the $regGUID into an array, and add one query parameter for each element in the array. Interpolate the dynamic list of query parameter placeholders.

   $regGUIDs = explode(',', $regGUID);
   $params = array_fill(1, count($regGUIDs), '?');
   $sql = $sql . ' WHERE a.regGUID in ( ' . implode(',', $params) . ' ) and ';
   

You could bindValue() in a loop for the array, but keep in mind that other parameters should also be bound by position, not by name. PDO has bugs that make it not happy when you try to mix the two different styles of parameters in the same query.

Instead of using bindValue() I just pass an array of parameter values to PDOStatement::execute(), which is much easier.

$paramValues = $regGUIDs;
$paramValues[] = $game;
$results = $stmt->execute($paramValues);

Answer 2

This indeed has been asked 1000 times.

Prepared statements can only accept scalar values, not arbitrary parts of the SQL query.

You have to form IN() statement using as many placeholders, as many items you have to put in and then bind them one by one.

To ease this task one can use some helper function.

Say, using SafeMysql library this code could be written as

$sql  = 'SELECT * FROM events a, players b WHERE regGUID in (?a) and';
$sql .= ' a.playerCode=b.playerCode and a.gameCode = ?s';
$sql .= ' order by a.eventTime desc, a.actionCode asc'; 
$results = $db->getAll($sql,$regGUID,$game);

Note that $regGUID should be an array, not string and $results already contain all the requested data, without any further processing.