How Can I Securely Insert Multiple Rows into MySQL Using PDO Prepared Statements?

MySQL Multiple Row Inserts with PDO Prepared Statements

Prepared statements offer enhanced security compared to static queries. In MySQL, inserting multiple rows of values with a single query can also benefit from this security advantage.

Implementing Multiple Row Inserts with Prepared Statements

To insert multiple rows using a prepared statement in PDO:

1. Create the Values Sequence: Determine the number of columns in the table and create a string of placeholders ('?,') for each column.
2. Construct the Query: Construct the SQL INSERT statement with the VALUES clause repeating the placeholder sequence for each row.
3. Prepare the Statement: Prepare the SQL statement using $db->prepare.
4. Merge Row Values: Combine the values for all rows into a single merged array.
5. Execute the Statement: Execute the prepared statement using $stmt->execute with the merged array as the parameter.

Example Code:

// Placeholder sequence for a single row
$values = str_repeat('?,', count($row) - 1) . '?';
// Construct the query
$sql = "INSERT INTO table (columnA, columnB) VALUES " .
    str_repeat("($values),", count($rows) - 1) . "($values)";
// Prepare the statement
$stmt = $db->prepare($sql);
// Merge row values
$values = array_merge(...$rows);
// Execute the statement
$stmt->execute($values);

This approach ensures security by constructing the query dynamically but with constant placeholders and column names. It is compatible with both MySQLi and PDO in various PHP versions.

The above is the detailed content of How Can I Securely Insert Multiple Rows into MySQL Using PDO Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!