PDO bindParam using an array, not working as assumed

Question

I have the following code...

$statement = $conn->prepare("insert into logtest (course, date, time, distance,
      actualstarttime, finishtime, fav, favtotalmatched, favwma, 2ndfav,
      2ndfavtotalmatched, 2ndfavwma, 3rdfav, 3rdfavtotalmatched, 3rdfavwma,
      orangeflag, greenflag, betplaced, totalracetime, numcalculations,
      secswaitedbeforemonitoring, monitorstarttime, totalmonitoringtime)
      values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");

    for($i = 0; $i < 23; $i++){
        $statement->bindParam($i + 1, $record[$i]);
    }

    $recordsprocessed = 0;

    $line = fgets($file); // Do this once before the loop to go past
                          // the column headers line

    while (!feof($file)){
        $line = fgets($file);
        $record = explode(",", $line);
        print_r($record);
        $statement->execute();
        ++$recordsprocessed;
    }

    echo "<p>Import finished.  $recordsprocessed records imported.</p>";

The code is filling the database with nulls. I can't understand why. I used a print_r to verify that the $record array does contain values.

From page http://php.net/manual/en/pdo.prepared-statements.php there is an implication that the parameters can be bound to variables before those variables contained values, so I assumed I could bind to the items of $record and then fill those values in a loop later on.

What am I doing wrong? Or does bindParam simply not work with arrays?

score 1 · Accepted Answer · answered Apr 18 '15 at 12:33

As an alternative, you could just get those values per line then just add those batches thru ->execute()

$query = 'INSERT INTO logtest 
    (course,date,time,distance,actualstarttime,finishtime,fav,favtotalmatched,favwma,2ndfav,2ndfavtotalmatched,2ndfavwma,3rdfav,3rdfavtotalmatched,3rdfavwma,
        orangeflag,greenflag,betplaced,totalracetime,numcalculations,secswaitedbeforemonitoring,monitorstarttime,totalmonitoringtime) 
        values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
';
$statement = $conn->prepare($query);

$recordsprocessed = 0;
$line = fgets($file);  // do this once before the loop to go past the column headers line
while (!feof($file)){

    $line = fgets($file);

    $record = explode(",",$line);

    $statement->execute($record);

    ++$recordsprocessed;
}

echo "<p>Import finished.  $recordsprocessed records imported.</p>";

That works... But is it sql injection safe? I assume it must be if I'm using PDO and the variables are not directly mentioned in the query string, but I am asking to be sure. — MrVimes, Apr 18 '15 at 12:36
@MrVimes absolutely, it doesn't bind each value like `->bindParam/bindValue` but this bind values as well. so to answer your question, yes its safe — Kevin, Apr 18 '15 at 12:38

PDO bindParam using an array, not working as assumed

1 Answers1