PDO PHP bindValue不起作用 [英] PDO PHP bindValue doesn't work

问题描述

我知道这已经被问过1000次了，但是由于某种原因，我继续用力砸墙.

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

这有效:

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

这不是:

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

我想念什么?谢谢

推荐答案

问题在这里:

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

我假设$ regGUID是逗号分隔的带引号的字符串列表.

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

每个查询参数仅接受一个标量值.不是列表值.

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

因此，您有两种选择:

1. 即使将参数用于其他标量值，也继续插入$ regGUID字符串.但是您仍然要小心避免SQL注入，因此必须正确形成$ regGUID字符串.您不能只对整个字符串调用PDO :: quote()，这会使它成为包含UUID和逗号的单引号字符串.您必须确保每个UUID字符串都被转义并单独引用，然后将列表内插在一起并将其插值到IN子句中.

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 ';

explode()将$ regGUID放入数组中，并为数组中的每个元素添加一个查询参数.插入查询参数占位符的动态列表.

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 ';

您可以在数组的循环中绑定bindValue()，但请记住，其他参数也应按位置而不是按名称绑定.当您尝试在同一查询中混合使用两种不同样式的参数时，PDO的错误会使其感到不满意.

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.

我没有使用bindValue()而是将参数值数组传递给PDOStatement :: execute()，这要容易得多.

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

这篇关于PDO PHP bindValue不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！