CODEWITHSUNDEEP
phpmysqliforeachprepared-statementsql-insert
Meitie
Meitie

Reputation: 75

How to change my PHP foreach looped SQL-Insert into a prepared-statement SQL loop?

I have a looped query to do inserts into the MySQL database it works perfectly to do what I need it to do as in it takes all the users inputs in array and then loops them and inputs each into their own row in the database.

$sql_insert_race_history = "INSERT INTO inf_race_history 
                                    (`inf_id`,`race_history`, `results`) 
                            VALUES ";

if ($vracehistory != '') {
    foreach ($vracehistory as $kay => $value) {
        // $sql .= '' | $sql = $sql . '';
        $sql_insert_race_history .= "('$inserted_id','{$value}','{$results[$kay]}'),";
    }
} else {
    $vracehistory = '';
}
// remove last `,` into query;
$sql_insert_race_history = rtrim($sql_insert_race_history, ',');
$countRow = count($_POST['racehist']);
//INSERT INTO THE DATABASE VIA QUERY
$results_racehistory = mysqli_query($vconn, $sql_insert_race_history);

This code works and inserts everything as i need it However i have been told that it is vulnerable to SQL injections attacks, so i have been trying to prevent that by using prepared statements every version I try only so far loops the dont work and it only uploads the very last item in the array

$stmtrace = $conn->prepare("INSERT INTO inf_race_history 
                                   (`inf_id`,`race_history`, `results`) 
                            VALUES (?,?,?)");
if ($vracehistory != '') {
    foreach ($vracehistory as $kay => $value) {
        $stmtrace->bind_param("sss", $inserted_id,$value,$results[$kay]);
    }
} else {
    $vracehistory = '';
}
// remove last `,` into query;
$sql_insert_race_history = rtrim($stmtrace, ',');
$countRow = count($_POST['racehist']);
//INSERT INTO THE DATABASE VIA QUERY
$stmtrace->execute();

I think it may have something to do with changing it from .= in the foreach loop to just ->bind_param as maybe that is taking away the opportunity to loop it ? tho im not too sure and also how would i echo that i try to echo $stmtrace tho it says method _tostring is not implemented

Upvotes: 0

Views: 1065

Answers (3)

Kabir Safi
Kabir Safi

Reputation: 87

foreach ($vracehistory as $kay => $value) {
    $stmtrace->bind_param("sss", $inserted_id, $value, $results[$kay]);
    $stmtrace->execute();
}

Upvotes: 3

Kezufru
Kezufru

Reputation: 123

bind the params outside the foreach loop, and assign and execute the query when you assign the variables inside the foreach loop. For example 

$stmtrace->bind_param("sss", $insertId, $insertValue, $insertKey);
foreach ($vracehistory as $kay => $value) {
    $insertId = inserted_id;
    $insertValue = $value;
    $insertKey = $kay;
    $stmtrace->execute();
}

Another note, if you bind an integer, the value of the bind_param method should be 'i'.

Upvotes: 0

venoel
venoel

Reputation: 458

You should place execute() inside loop.

Upvotes: 0

Related Questions