PDO PHP bindValue not working

Question

I know this has been set 1000 times, but for some reason I keep banging my head on the wall.

It 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(); 

Is not:

 $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 list of quoted strings separated by commas.

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

So, you have two options:

• Continue to interpolate the string $ regGUID, even if you use parameters for other scalar values. But you still want to be careful to avoid SQL injection, so you must correctly form the $ regGUID string. You cannot just call PDO :: quote () for the entire string, which would make it a single quote containing UUIDs and commas. You need to make sure that each UUID string is escaped and quoted separately, then combine the list and integrate 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 '; 

• explode() $ regGUID into the array and add one query parameter for each element of the array. Interpolate a 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 can bind Value () in a loop for an array, but keep in mind that other parameters should also be bound by position, not by name. PDO has errors that make it unhappy when you try to mix two different styles of parameters in one query.

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

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

Answer 2

This has indeed been set 1000 times.

Prepared statements can only take scalar values , not arbitrary parts of a SQL query.

You must form the IN () operator using as many placeholders as possible, since you have to insert many elements and then link them one by one.

To facilitate this task, you can use some helper function.

Say, using the SafeMysql library, this code can 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 a string, and $results already contain all the requested data without further processing.

Answer 3

What is the content of $regGUID ? Since you are using the in clause, I suspect the list is separated by commas.

Binding a variable to a parameter is not like replacing this string with a query; this is how to tell MySQL how to use your actual PHP variables. Therefore, if you bind a string such as '1,2,3' to the query parameter, it remains one string and is not reinterpreted as a list of numbers.

Therefore, if $regGUID is something like "'AAA1', 'BBB2'" , your first request becomes

 ... WHERE a.regGUID in ( 'AAA1', 'BBB2' ) ... 

but your second request is more like

 ... WHERE a.regGUID in ( '\'AAA1\', \'BBB2\'' ) ... 

which coincides with the fact that

 ... WHERE a.regGUID = '\'AAA1\', \'BBB2\'' ... 

Answer 4

As with others, you can only attach one scalar value to a placeholder. So this means that you really need a placeholder for every value in your IN expression. I usually do something like the following. It should be noted that I never use bindValue , so if it has rules that things should be links like Mysqli, then you may need to change the following:

 $regGUIDPlaceholders = array(); // prepare the placeholders // assume regGUID is an array - if its a string then explode on whatever to make it an array foreach($regGUID as $k => $v) { $placeholder = ':regGUID' . $k; $regGUIDPlaceholders[$key] = $value; } // prepare the IN statememnt $in = sprintf('IN (%s)', implode(',', array_keys($regGUIDPlaceholders))); $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 '; // USE the IN statement dynamically prepared above $sql = $sql . 'WHERE a.regGUID '. $in . ' and '; $sql = $sql . 'a.playerCode=b.playerCode and a.gameCode = :game order by a.eventTime desc, a.actionCode asc'; $stmt = $db->prepare($sql); // bind each GUID to its placeholder foreach($regGUIDPlaceholders as $placeholder => $value) { $stmt->bindValue($placeholder, $value, PDO::PARAM_STR); } $stmt->bindValue(':game', $game, PDO::PARAM_STR); $results = $stmt->execute();