PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

Question

I do know that PDO does not support multiple queries getting executed in one statement. I've been Googleing and found few posts talking about PDO_MYSQL and PDO_MYSQLND.

PDO_MySQL is a more dangerous application than any other traditional MySQL applications. Traditional MySQL allows only a single SQL query. In PDO_MySQL there is no such limitation, but you risk to be injected with multiple queries.

From: Protection against SQL Injection using PDO and Zend Framework (June 2010; by Julian)

It seems like PDO_MYSQL and PDO_MYSQLND do provide support for multiple queries, but I am not able to find more information about them. Were these projects discontinued? Is there any way now to run multiple queries using PDO.

Answer 1

As I know, PDO_MYSQLND replaced PDO_MYSQL in PHP 5.3. Confusing part is that name is still PDO_MYSQL. So now ND is default driver for MySQL+PDO.

Overall, to execute multiple queries at once you need:

• PHP 5.3+
• mysqlnd
• Emulated prepared statements. Make sure PDO::ATTR_EMULATE_PREPARES is set to 1 (default). Alternatively you can avoid using prepared statements and use $pdo->exec directly.

Using exec

$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');

// works regardless of statements emulation
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

$sql = "
DELETE FROM car; 
INSERT INTO car(name, type) VALUES ('car1', 'coupe'); 
INSERT INTO car(name, type) VALUES ('car2', 'coupe');
";

$db->exec($sql);

Using statements

$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');

// works not with the following set to 0. You can comment this line as 1 is default
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

$sql = "
DELETE FROM car; 
INSERT INTO car(name, type) VALUES ('car1', 'coupe'); 
INSERT INTO car(name, type) VALUES ('car2', 'coupe');
";

$stmt = $db->prepare($sql);
$stmt->execute();

---

A note:

When using emulated prepared statements, make sure you have set proper encoding (that reflects actual data encoding) in DSN (available since 5.3.6). Otherwise there can be a slight possibility for SQL injection if some odd encoding is used.