How to insert array into mysql using PDO and bindParam?

Question

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

I replaced the following

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

with

$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).

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

Chris Forrence · Accepted Answer · 2014-03-04 16:49:45Z

4

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

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.

I mean:

This simple query:

SELECT * FROM A WHERE val="$param"

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

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.

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, ...)";

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

}

This will work.

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.

Your statement should looks like:

$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.

edited Mar 4, 2014 at 16:49

Chris Forrence

10.1k11 gold badges50 silver badges66 bronze badges

answered Mar 25, 2012 at 5:26

grifos

3,4111 gold badge18 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TryHarder Over a year ago

Thank you for updating your answer. It is much easier to understand now :)

TryHarder Over a year ago

Is there a way to do this when the number of values to be inserted is different each time?

grifos Over a year ago

Yeah you just have to generate your query string according to your needs and bind the params matching those of the query you generated

Collectives™ on Stack Overflow

How to insert array into mysql using PDO and bindParam?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related