How Can I Efficiently Insert Multiple Rows into MySQLi While Preventing SQL Injection?

Efficient Insertion of Multiple Rows in MySQLi with SQL Injection Mitigation

Inserting a large number of rows into a MySQL database with PHP and MySQLi requires a technique that is both secure and efficient. Avoiding SQL injections while handling such data volume can be challenging.

Solution

To resolve this issue, consider using a transaction to group the insertion operations. This approach involves moving the prepare and bind operations outside the loop, as shown below:

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

Benefits of Transaction

• Increased Speed: Executing SQL queries within a transaction significantly reduces the overhead and improves performance. Our tests with 10,000 iterations showed a two-fold speed increase when using a transaction.
• SQL Injection Mitigation: Transactions group a series of operations into a single atomic unit, ensuring that all or none of the rows are inserted. This prevents partial insertions and protects against SQL injection attempts.

Implementation

Remember to execute the START TRANSACTION and COMMIT queries outside the PHP loop to ensure the entire operation is treated as a single transaction. This approach combines the security of prepared statements with the efficiency of bulk insertions while mitigating stack overflows.

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