如何使用PDO和bindParam将数组插入mysql? [英] How to insert array into mysql using PDO and bindParam?

问题描述

我正在使用以下代码.该代码有效，但我想对其进行更改，使其使用bindparam

I'm using the following code. The code works, but I want to change it so that it uses bindparam

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
$stqid=array();

    for ($i=0; $i<$array_count; $i++){
    $stqid[$i][0]=$lastInsertValue;
    $stqid[$i][1]=$qid[$i][0];
    $stqid[$i][2]=$qid[$i][1];
    }

$values = array();
    foreach ($stqid as $rowValues) {
        foreach ($rowValues as $key => $rowValue) {
        $rowValues[$key] = $rowValues[$key];  
        }

    $values[] = "(" . implode(', ', $rowValues) . ")";
    }

$count = $dbh->exec("INSERT INTO qresults(instance, qid, result) VALUES  ".implode (', ', $values)); 
$dbh = null;
}
catch(PDOException $e){
    echo $e->getMessage();
}

我替换了以下内容

$count = $dbh->exec("INSERT INTO qresults(instance, qid, result) VALUES  ".implode (', ', $values)); 

使用

$sql = "INSERT INTO qresults (instance, qid, result) VALUES (:an_array)";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':an_array', implode(',', $values),PDO::PARAM_STR);
$stmt->execute();

但是插入不再起作用(尽管我没有收到任何错误消息).

but the insert doesn't work anymore (I didn't get any error messages though).

问题:我在做什么错?如何重写代码以使用bindParam?

QUESTION: What am I doing wrong? How can I rewrite the code to use bindParam?

推荐答案

您正在尝试创建一条语句并绑定一个参数.

You're trying to create a statement and bind a param.

语句很棒，因为它可能使任何类型的SQL注入无效.它通过删除仅被视为字符串的查询的概念来实现. SQL查询被视为带有参数列表的字符串，而关联的数据则作为绑定变量. 因此，查询不仅是文本，而且是文本+数据.

Statement are great because it potentially nullify any kind of SQL injection. And it does it by removing the concept of a query being only seen as a string. The SQL query is seen as a string with a parameter list and an the associated data as binded variables. So the query is not only text, but text + data.

我的意思是:

这个简单的查询:

SELECT * FROM A WHERE val="$param"

这是不安全的，因为查询仅被视为字符串.而且，如果未选中$ param，则它是一个SQLi漏洞.

It is not safe because the query is only viewed as a string. And if $param is not checked, it is a SQLi hole.

但是在创建语句时，您的查询将变为:

But when create a statement, your query becomes:

SELECT * FROM A WHERE val=:param

然后使用bindparam指定值a:param.这意味着该值未附加到查询字符串中，但查询已被解析并提供了数据.

Then you use bindparam to specify the value a :param. Which mean the value is not appended to the query string, but the query is already parsed and the data is provided.

在您的情况下，您将内爆数组绑定到param:array(我假设"data1"，"data2"等.).该参数只是一个值作为字符串的参数("data1，data2，data3 ...")，因此只会导致一次插入，而不会导致多次插入.

In your case, you bind to the param :array an imploded array (I assume "data1", "data2", etc..). Which is only one parameter with the value as a string ( "data1, data2, data3..." ), so it will only result in one insert and not multiple insertions.

您可以通过生成具有足以处理数组的参数的查询来更改语句的生成

You can change your statement generation by generating a query with enough parameters to handle your array

$sql = "INSERT INTO qresults (instance, qid, result) VALUES ( :val0, :val1, :val2, ...)";

然后在您的数组上循环并为每个参数调用bindparam方法.

Then loop on your array and call the bindparam method for each parameters.

$count = 0;
foreach($values as $val)
{
   $stmt->bindParam(":val$count", $val,PDO::PARAM_STR);
   $count++;

}

这将起作用.

编辑:该解决方案说明了它如何用于一维数组，但可以通过调整语句查询的生成并修改bindparam循环轻松地将其扩展到您的问题.

Edit: This solution show how it works for a one dimensional array, but can be easily extended to your problem by tweaking the statement query generation and modify the bindparam loop.

您的声明应如下所示:

$sql = "INSERT INTO qresults (instance, qid, result) VALUES (:val0, :val1, :val2) , (:val3, :val4, :val5), ...";

您只需要计算基本数组中的元素数即可.

You just have to count the number of element in your base array.

这篇关于如何使用PDO和bindParam将数组插入mysql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！