PDO 支持多个查询(PDO_MYSQL、PDO_MYSQLND) [英] PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

问题描述

我知道 PDO 不支持在一个语句中执行多个查询.我一直在 Google 上搜索，发现很少有帖子讨论 PDO_MYSQL 和 PDO_MYSQLND.

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 是一个更危险的应用比任何其他传统MySQL 应用程序.传统 MySQL只允许一个 SQL 查询.在PDO_MySQL 没有这样的限制，但你有被注射的风险多个查询.

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.

来自:使用 PDO 和 Zend 框架防止 SQL 注入(2010 年 6 月；Julian)

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

似乎 PDO_MYSQL 和 PDO_MYSQLND 确实提供了对多个查询的支持，但我无法找到有关它们的更多信息.这些项目停止了吗?现在有没有办法使用 PDO 运行多个查询.

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.

推荐答案

据我所知，PDO_MYSQLND 在 PHP 5.3 中取代了 PDO_MYSQL.令人困惑的部分是名称仍然是 PDO_MYSQL.所以现在ND是MySQL+PDO的默认驱动.

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
• 模拟准备好的语句.确保 PDO::ATTR_EMULATE_PREPARES 设置为 1(默认).或者，您可以避免使用准备好的语句并直接使用 $pdo->exec.

• 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.

使用 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);

使用语句

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

---

注意事项:

当使用模拟准备好的语句时，请确保您在 DSN(从 5.3.6 开始可用).否则如果使用了一些奇怪的编码，SQL注入的可能性很小.

---

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.

这篇关于PDO 支持多个查询(PDO_MYSQL、PDO_MYSQLND)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！