MySQL 带有可变大小变量列表的准备语句

Question

如何在 PHP 中编写每次使用不同数量参数的准备好的 MySQL 语句？ 此类查询的一个示例是：

SELECT `age`, `name` FROM `people` WHERE id IN (12, 45, 65, 33)

IN 子句每次运行时都会有不同数量的 id。

我脑子里有两种可能的解决方案，但想看看是否有更好的方法。

可能的解决方案1 让语句接受100个变量，并用保证不在表中的虚拟值填充其余的变量； 对超过 100 个值进行多次调用。

可能的解决方案2 不要使用准备好的语句； 构建并运行查询，严格检查可能的注入攻击。

Answer 1

我可以想到几个解决方案。

一种解决方案可能是创建临时表。 将 in 子句中包含的每个参数插入到表中。 然后对临时表进行简单的连接。

另一种方法可能是做这样的事情。

$dbh=new PDO($dbConnect, $dbUser, $dbPass);
$parms=array(12, 45, 65, 33);
$parmcount=count($parms);   // = 4
$inclause=implode(',',array_fill(0,$parmcount,'?')); // = ?,?,?,?
$sql='SELECT age, name FROM people WHERE id IN (%s)';
$preparesql=sprintf($sql,$inclause);  // = example statement used in the question
$st=$dbh->prepare($preparesql);
$st->execute($parms);

我怀疑，但没有证据，第一个解决方案可能更适合较大的列表，而后者适用于较小的列表。

---

为了让 @orrd 高兴，这里有一个简洁的版本。

$dbh=new PDO($dbConnect, $dbUser, $dbPass);
$parms=array(12, 45, 65, 33);
$st=$dbh->prepare(sprintf('SELECT age, name FROM people WHERE id IN (%s)',
                          implode(',',array_fill(0,count($parms),'?'))));
$st->execute($parms);

---

Answer 2

还有 FIND_IN_SET 函数< /a> 其第二个参数是逗号分隔值的字符串：

SELECT age, name FROM people WHERE FIND_IN_SET(id, '12,45,65,33')

Answer 3

不错的 sql 包装器支持绑定到数组值。
IE

$sql = "... WHERE id IN (?)";
$values = array(1, 2, 3, 4);
$result = $dbw -> prepare ($sql, $values) -> execute ();

Answer 4

请把#2 从桌子上去掉。 准备好的语句是您应该考虑保护自己免受 SQL 注入的唯一方法。

然而，您可以做的是生成一组动态的绑定变量。 即，如果您需要 7 个（或 103 个），则不要制作 100 个。

Answer 5

我的答案来自： http://bugs.php.net/bug.php? id=43568。
这是我的问题的 mysqli 解决方案。 现在我可以动态地使用任意数量的参数。 它们将与我在数组中的编号相同，或者在本例中，我传递了最后一个查询中的 id（它找到了 email = '[email protected]') 到动态查询，以获取有关每个 id 的所有信息，无论我最终需要多少个。

<?php $NumofIds = 2; //this is the number of ids I got from the last query
    $parameters=implode(',',array_fill(0,$NumofIds,'?')); 
    // = ?,? the same number of ?'s as ids we are looking for<br />
    $paramtype=implode('',array_fill(0,$NumofIds,'i')); // = ii<br/>
    //make the array to build the bind_param function<br/>
    $idAr[] = $paramtype; //'ii' or how ever many ?'s we have<br/>
    while($statement->fetch()){ //this is my last query i am getting the id out of<br/>
        $idAr[] = $id;  
    }

    //now this array looks like this array:<br/>
    //$idAr = array('ii', 128, 237);

    $query = "SELECT id,studentid,book_title,date FROM contracts WHERE studentid IN ($parameters)";
    $statement = $db->prepare($query);
    //build the bind_param function
    call_user_func_array (array($statement, "bind_param"), $idAr);
    //here is what we used to do before making it dynamic
    //statement->bind_param($paramtype,$v1,$v2);
    $statement->execute();
?>

Answer 6

如果您仅在 IN 子句中使用整数值，则无需使用 SQL 参数即可动态构建查询。

function convertToInt(&$value, $key)
{
    $value = intval($value);
}

$ids = array('12', '45', '65', '33');
array_walk($ids, 'convertToInt');
$sql = 'SELECT age, name FROM people WHERE id IN (' . implode(', ', $ids) . ')';
// $sql will contain  SELECT age, name FROM people WHERE id IN (12, 45, 65, 33)

但毫无疑问，解决方案这里是解决这个问题的更通用的方法。

Answer 7

我今天遇到了类似的问题，我找到了这个主题。 查看答案并在谷歌上搜索，我找到了一个很好的解决方案。

虽然，我的问题有点复杂。 因为我有固定的绑定值和动态的。

这是 mysqli 解决方案。

$params = array()
$all_ids = $this->get_all_ids();

for($i = 0; $i <= sizeof($all_ids) - 1; $i++){
    array_push($params, $all_ids[$i]['id']);
}

$clause = implode(',', array_fill(0, count($params), '?')); // output ?, ?, ?
$total_i = implode('', array_fill(0, count($params), 'i')); // output iiii

$types = "ss" . $total_i; // will reproduce : ssiiii ..etc

// %% it's necessary because of sprintf function
$query = $db->prepare(sprintf("SELECT * 
                                FROM clients    
                                WHERE name LIKE CONCAT('%%', ?, '%%') 
                                AND IFNULL(description, '') LIKE CONCAT('%%', ?, '%%')
                                AND id IN (%s)", $clause));

$thearray = array($name, $description);
$merge    = array_merge($thearray, $params); // output: "John", "Cool guy!", 1, 2, 3, 4

// We need  to pass variables instead of values by reference
// So we need a function to that
call_user_func_array('mysqli_stmt_bind_param', array_merge (array($query, $types), $this->makeValuesReferenced($merge))); 

以及函数makeValuesreferenced：

public function makeValuesReferenced($arr){
    $refs = array();
    foreach($arr as $key => $value)
        $refs[$key] = &$arr[$key];
    return $refs;
}

获取此“知识”的链接：https://bugs.php.net/bug.php?id=49946，PHP 将一个数组附加到另​​一个数组（不是 array_push 或 +），[PHP]: 错误 -> sprintf() 中的参数太少；、https://www.php.net/manual/en/mysqli-stmt.bind-param.php#89171，PHP 5.3.1 的引用传递问题