CODEWITHSUNDEEP
phpmysqlpdoinnodbsql-injection
TryHarder
TryHarder

Reputation: 2777

Is there a safer way to do this using PDO while avoiding unnecessary queries?

I'm using code similar to the one below to insert into a mysql DB (using PDO.) The aim was to insert data using only one query. Unfortunately it defeats the purpose of using PDO because it doesn't make use of bindValue or param. I want to write the code so that it is safer but still avoids unnecessary queries. Can anyone recommend how to do this?

NOTE1: The number of questions in the set can change each time the program is accessed (ie $totalQ can be different each time it is run).

try 
{
    for ($i=0; $i<$totalQ; $i++)
    { 
        $stqid[$i][0]=$lastInsertValue;     //instance         PDO::PARAM_INT
        $stqid[$i][1]=$jqid[$i][0];          //question number  PDO::PARAM_INT
        $stqid[$i][2]=$jqid[$i][5];          //result           PDO::PARAM_INT
        $stqid[$i][3]=$jqid[$i][3];          //question start   PDO::PARAM_STR 
        $stqid[$i][4]=$jqid[$i][4];          //question finish  PDO::PARAM_STR 
    }

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

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

    $count = $dbh->exec("INSERT INTO results(instance, qid, result, start, finish) VALUES  ".implode (', ', $values));  
    $dbh = null;
}

NOTE2: Commas in the time format for start and finish can cause errors with the implode statement. I've just added them so you can see what I'm trying to achieve.

Any help would be much appreciated. Thanks.

EDIT: While I chose chris' answer, I'm very grateful to Alix Axel for his advice. It helped a lot, thank you!

Upvotes: 1

Views: 89

Answers (2)

Alix Axel
Alix Axel

Reputation: 154553

How about something like this:

try 
{
    for ($i=0; $i<$totalQ; $i++)
    { 
        $stqid[$i][0]=$lastInsertValue;     //instance         PDO::PARAM_INT
        $stqid[$i][1]=$jaid[$i][0];          //question number  PDO::PARAM_INT
        $stqid[$i][2]=$jaid[$i][5];          //result           PDO::PARAM_INT
        $stqid[$i][3]=$jqid[$i][3];          //question start   PDO::PARAM_STR 
        $stqid[$i][4]=$jqid[$i][4];          //question finish  PDO::PARAM_STR 
    }

    $values = null;

    foreach ($stqid as $rowValues) 
    {
        $values .= vsprintf('(%s, %s, %s, %s, %s) ', array_map(array($dbh, 'quote'), $rowValues));
    }

    $count = $dbh->exec('INSERT INTO results (instance, qid, result, start, finish) VALUES ' . rtrim($values) . ';');  
    $dbh = null;
}

Upvotes: 2

goat
goat

Reputation: 31813

Untested. Still uses prepared statements.

$numColumns = 5; //or $numColumns = count($stqid[0]);
$rowPlaceholder = join(', ', array_fill(0, $numColumns, '?'));
$rowPlaceholders = array_fill(0, $totalQ, "($rowPlaceholder)");
echo $sql = "INSERT INTO results(instance, qid, result, start, finish) VALUES " . join(", \n", $rowPlaceholders);
$flat = call_user_func_array('array_merge', $stqid);
$stmt = $dbh->prepare($sql);
$stmt->execute($flat);

Upvotes: 2

Related Questions