Binding a PHP array for SQL insert-PHP Chinese Network Q&A

Trying to bind an array (first array binding) to prevent SQL injection

This is valid code:

;

if (isset($_POST['checkbox_selected'])) { $valuesArr = array(); foreach ($_POST['checkbox_selected'] as $key => $value) { //Retrieve the Array ID to find the row number of the CSVOption column $findrow = array_search_partial($attributeid, $value); //When the form is submitted, the attribute value is assigned to the attribute id $attribute = $value; $csv = $csvcolumn[$findrow]; $valuesArr[] = "('$userid', '$feed_id', '$attribute', '$csv')"; } $sql = "INSERT INTO map (user_id, feed_id, attribute_id, csvcolumn) values "; $sql .= implode(',', $valuesArr); mysqli_query($conn,$sql); }

I can't bind the array, tried:

$sql = "INSERT INTO map (user_id, feed_id, attribute_id, csvcolumn) VALUES (?, ?, ? ,?)"; $stmt = $conn->prepare($sql); $stmt->bind_param('iiii', implode(',', $valuesArr)); $stmt->execute(); echo implode(',', $valuesArr) //('1', '1', '13', '9') //This is the array inserted into SQL //(user_id, feed_id, attribute_id, csvcolumn) //Here is the value assigned in the first statement

if (isset($_POST['checkbox_selected'])) { $sql = "INSERT INTO map (user_id, feed_id, attribute_id, csvcolumn) VALUES (?, ?, ?, ?);"; // prepare only has to happen once $stmt = mysqli_prepare($conn,$sql); mysqli_begin_transaction($conn); try { foreach ($_POST['checkbox_selected'] as $key => $value) { $findrow = array_search_partial($attributeid, $value); $attribute = $value; $csv = $csvcolumn[$findrow]; $stmt->bindParam('iiii', $userid, $feed_id, $attribute, $csv); $stmt->execute(); } mysqli_commit($conn); } catch(mysqli_sql_exception $e) { mysqli_rollback($conn); // immediately roll back changes throw $e; // re-throw exception } }

reply all (1)

P粉3846792662023-08-09 13:15:28 1 floor

You have two problems:

You are not using the correct bind syntax.

You are trying to insert multiple rows in a prepared statement

The only slight benefit is that when you try to wrap multiple VALUES() in a query, it will be wrapped by an implicit transaction. But other aspects of this approach are disadvantageous. Explicitly opening the transaction that wraps the binding/execution loop, you can get the same benefits [error rollback, IO batching], while also taking advantage of the benefits of prepared statements [single simple query parsing, parameterization, etc.]