How Can I Efficiently Insert 2000 Rows into MySQL While Preventing SQL Injection and Stack Overflow?

Efficient Bulk Insertion in mysqli: Preventing SQL Injection and Stack Overflows

Inserting large volumes of data into a MySQL database securely and performantly is crucial for many applications. This question explores techniques to insert 2000 rows effectively while ensuring SQL injection protection.

The provided code snippet iterates through an array of values, preparing and executing a separate query for each insertion. While secure against SQL injections, this approach can be inefficient and may result in stack overflows.

Enhanced Solution with Transactions:

A more optimal solution involves utilizing MySQL transactions. By grouping the insertions within a single transaction, the database can optimize the process and significantly reduce the execution time.

$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");

Performance Comparison:

This technique provides a substantial speed improvement, as demonstrated by benchmark testing:

• Without transaction: 226 seconds
• With transaction: 2 seconds

This represents an impressive two order of magnitude speed increase for the test case.

By leveraging transactions, you can efficiently insert data in bulk while maintaining SQL injection security and avoiding stack overflows.

The above is the detailed content of How Can I Efficiently Insert 2000 Rows into MySQL While Preventing SQL Injection and Stack Overflow?. For more information, please follow other related articles on the PHP Chinese website!