I'm looking for a SQL injection safe technique to insert a large number of rows (~2000 rows) at once using PHP and MySQLi.
I have an array that contains all the values it must contain. Currently I'm doing this:
prepare($query); $stmt ->bind_param("s", $one); $stmt->execute(); $stmt->close(); } ?>
I tried call_user_func_array() but it resulted in stack overflow.
What is a faster way to do this (like inserting them all at once?) but still prevent SQL injection (like prepared statements) and stack overflow?
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Try again, I don't understand why your original code doesn't work after a slight modification:
$query = "INSERT INTO table (link) VALUES (?)"; $stmt = $mysqli->prepare($query); $stmt->bind_param("s", $one); foreach ($array as $one) { $stmt->execute(); } $stmt->close();By putting your inserts into a transaction you should be able to speed things up a lot. You can also move prepare and bind statements outside the loop.
$array = array("array", "with", "about", "2000", "values"); $query = "INSERT INTO table (link) VALUES (?)"; $stmt = $mysqli->prepare($query); $stmt ->bind_param("s", $one); $mysqli->query("START TRANSACTION"); foreach ($array as $one) { $stmt->execute(); } $stmt->close(); $mysqli->query("COMMIT");I tested this code on my web server for 10,000 iterations.
No transaction:
226 seconds.Transaction time:2 seconds.Orbe two orders of magnitude faster, at least for this test.