What are some alternative methods to executing multiple SQL statements in PHP other than mysqli::multi_query?

Using mysqli::multi_query can be risky as it allows for potential SQL injection vulnerabilities if the statements are not properly sanitized. To execute multiple SQL statements in PHP safely, you can use prepared statements with parameter binding. This method ensures that user input is properly escaped and prevents SQL injection attacks. 

// Establish a connection to the database
$mysqli = new mysqli("localhost", "username", "password", "database");

// Define the SQL statements to be executed
$sql1 = "INSERT INTO table1 (column1, column2) VALUES (?, ?)";
$sql2 = "UPDATE table2 SET column1 = ? WHERE id = ?";

// Prepare the statements
$stmt1 = $mysqli->prepare($sql1);
$stmt2 = $mysqli->prepare($sql2);

// Bind parameters
$stmt1->bind_param("ss", $value1, $value2);
$stmt2->bind_param("si", $value3, $id);

// Set parameter values
$value1 = "value1";
$value2 = "value2";
$value3 = "value3";
$id = 1;

// Execute the statements
$stmt1->execute();
$stmt2->execute();

// Close the statements and the connection
$stmt1->close();
$stmt2->close();
$mysqli->close();

Keywords

PHP SQL PDO prepared statements transactions

Related Questions