Multiple mysql INSERT statements in one query php

Multiple mysql INSERT statements in one query php

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Concatenating SQL Statements: The mysql_* Legacy Trap

Concatenating multiple INSERT statements into a single string and executing them via one function call is technically possible in legacy PHP environments, but it represents an outdated approach that modern frameworks and best practices have long moved away from. While the specific code snippet provided in your source material demonstrates string concatenation followed by a single execution call, understanding why this pattern is problematic requires looking under the hood at how database drivers handle queries and where security vulnerabilities lurk.

The code example mysql_query($string1) executes the entire concatenated string as one atomic operation to the MySQL server. From a pure syntax perspective, if the individual statements are valid SQL and there are no resource limits hit, the database will process them sequentially. However, relying on this behavior creates significant friction when dealing with transactions, error handling, and most critically, security.

The Security Criticality of String Concatenation

The primary reason developers should avoid constructing SQL strings by concatenating variables is the risk of SQL injection. When you build a query string like $string1 .= "INSERT INTO users VALUES ('" . $name . "', ...)", you are manually managing quotes and escaping logic. If $name contains a single quote character, your constructed string breaks, leading to syntax errors or, worse, unexpected query structures if not escaped perfectly.

Even if you feel confident in your mysqli_real_escape_string() or PDO prepared statements for individual lines, doing this inside a loop that builds a massive string introduces complexity. The moment you stop using parameterized queries and start treating SQL as a string to be manipulated, you invite bugs. Modern PHP drivers, such as PDO or the legacy mysqli, support multiple-statement execution specifically designed to handle batches without requiring manual string concatenation.

Using Multiple Statements in Legacy Drivers

If you are forced to work with the old mysql_* extension (which was deprecated and removed in PHP 7), you might still need to handle batch inserts. The mysql_query() function accepts a second parameter, MYSQLI_USE_MULTI, but wait—mysql_query doesn't exist in that context anymore because the entire extension is gone.

In the active mysqli or PDO extensions, handling multiple statements requires explicit configuration. For instance, with mysqli, you must enable multi-statement mode before executing the batch:

$mysqli->multi_query("INSERT INTO table1 VALUES (1, 'a'); INSERT INTO table1 VALUES (2, 'b');");

However, this is only safe if every statement in the string uses parameterized placeholders (? or named parameters). You cannot safely concatenate user input directly into a multi-statement string. The mysql_query() function shown in your source material is the specific legacy function that lacks proper support for multi-statement safety features found in its successors, making the approach inherently dangerous for anything other than static, hardcoded data.

The Real Solution: Batch Inserts and Transactions

Instead of concatenating SQL statements, the industry standard for inserting multiple rows efficiently is to use a single INSERT statement with multiple value sets. This approach reduces network overhead and avoids the need for multi-statement execution flags entirely.

$values = [
    ['col1' => 'val1', 'col2' => 'val2'],
    ['col1' => 'val3', 'col2' => 'val4']
];

$sql = "INSERT INTO table_name (col1, col2) VALUES (?, ?), (?, ?)";
$stmt = $pdo->prepare($sql);

foreach ($values as $row) {
    $stmt->execute([$row['col1'], $row['col2'], $row['col1'], $row['col2']]); 
    // Note: You need to duplicate the placeholders for each row in the VALUES clause
}

Actually, a cleaner implementation constructs the values list dynamically while maintaining parameter safety:

$columns = ['id', 'name', 'email'];
$values = [];

foreach ($rows as $row) {
    $values[] = '(' . implode(', ', array_map(fn($k) => '?', $columns)) . ')';
}
$sql = "INSERT INTO users (" . implode(', ', $columns) . ") VALUES " . implode(', ', $values);

$stmt = $pdo->prepare($sql);
// Then execute with a single flat array of all values needed

This method executes as a single query, ensuring atomicity if wrapped in a transaction, and completely eliminates the SQL injection surface area associated with string concatenation.

Conclusion

While concatenating INSERT statements into one query string is syntactically valid for simple, static scripts using legacy functions, it is a pattern that should be avoided in any production environment. The shift towards prepared statements, batch value insertion within a single query definition, and modern drivers like PDO has made the concatenated string approach obsolete. If you encounter this pattern in legacy codebases, refactoring to parameterized queries or using native batch insert capabilities is the necessary step for security and maintainability.

Our network