bind_param 错误 - 类型定义字符串中的元素数与绑定变量数不匹配 [英] bind_param error - Number of elements in type definition string doesn't match number of bind variables

问题描述

我有一个准备用于 SQL 查询的数组，因此请按照以下步骤操作以使其尽可能安全.尝试以下操作:

I have an array that I am preparing for a SQL query, so following the steps to make it secure as possible. Attempting the following:

首先我对数组进行内爆 - 我希望字符串结果显示为 'string1'、'string2'、'string3' 等等:

First I implode the array - I want the string result to come out as 'string1','string2','string3' and so on:

$in = "'" . implode("','", array_fill(0, count($finalArray), '?')) . "'";

我提出查询:

$query = <<<SQL
UPDATE products SET `Status` = 'Reserved' WHERE `SerialNumber` in ($in);
SQL;
$query = <<<SQL

并准备语句变量:

$statement = $mysqli->prepare($query);

然后我尝试了一个带有 str_repeat 的 bind_param，这就是出错的地方:

Then I attempt a bind_param with str_repeat, and this is where things go wrong:

$statement->bind_param(str_repeat('\'s\',', count($finalArray)), ...$finalArray);

我得到:

mysqli_stmt::bind_param(): 类型定义字符串中的元素数与绑定变量数不匹配

mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

有谁知道我为什么会收到这个问题以及如何解决它?

Does anyone know why I am getting this and how I can resolve it?

推荐答案

查看占位符的动态创建:

Looking at your dynamic creating of your placeholders:

$in = "'" . implode("','", array_fill(0, count($finalArray), '?')) . "'";

所以似乎用 ' 引号创建它们.占位符不需要引号.

So seem to have creating them with ' quotations. Placeholders do not need quotations.

$in = implode(',', array_fill(0, count($finalArray), '?'));

$query = "UPDATE products SET Status = 'Reserved' WHERE SerialNumber IN ($in)";
$statement = $mysqli->prepare($query);

然后，在分配类型时，您也不需要引用它们:

Then, in assigning types, you don't need them quoted also:

$statement->bind_param(str_repeat('s', count($finalArray)), $finalArray);

旁注:请注意，您还必须通过 call_user_func_array() 动态调用 bind_param，因为您将使用数组.这部分详细讨论.

Sidenote: Take note that you'll also having to dynamically call bind_param thru call_user_func_array() since you're going to use an array. This part discusses it thoroughly.

虽然我建议/更喜欢使用 PDO 的 ->execute():

Though I'd suggest/prefer to use PDO's ->execute():

$pdo = new PDO('mysql:host=localhost;dbname=DATABASE NAME', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$in = implode(',', array_fill(0, count($finalArray), '?'));
$query = "UPDATE products SET Status = 'Reserved' WHERE SerialNumber IN ($in)";
$statement = $pdo->prepare($query);
$statement->execute($finalArray);

另一种使用Reflection的方法:

$in = implode(',', array_fill(0, count($finalArray), '?'));
$type = str_repeat('s', count($finalArray));
$query = "UPDATE products SET Status = 'Reserved' WHERE SerialNumber IN ($in)";
$statement = $mysqli->prepare($query);

$ref = new ReflectionClass('mysqli_stmt');
$method = $ref->getMethod('bind_param');
array_unshift($finalArray, $type); // prepend the 'sss' inside
$method->invokeArgs($statement, $finalArray);

$statement->execute();

这篇关于bind_param 错误 - 类型定义字符串中的元素数与绑定变量数不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！