我知道PDO不支持在一条语句中执行多个查询。我一直在谷歌上搜索,发现很少有关于PDO_MYSQL和PDO_MYSQLND的帖子。
PDO_MySQL是一个比任何其他传统MySQL应用程序都更危险的应用程序。传统的MySQL只允许一个SQL查询。在PDO_MySQL中没有这样的限制,但是您有被注入多个查询的风险。
似乎PDO_MYSQL和PDO_MYSQLND确实提供了对多个查询的支持,但我找不到有关它们的更多信息。这些项目停止了吗?现在有没有办法使用PDO运行多个查询。
发布于 2011-06-24 06:01:47
据我所知,在PHP5.3中,PDO_MYSQLND
取代了PDO_MYSQL
。令人困惑的部分是名称仍然是PDO_MYSQL
。因此,ND现在是MySQL+PDO的默认驱动程序。
总的来说,要一次执行多个查询,您需要:
PHP 5.3+
PDO::ATTR_EMULATE_PREPARES
设置为1
(默认值)。或者,您可以避免使用预准备语句,而直接使用$pdo->exec
。使用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开始提供)中设置了正确的编码(反映实际数据编码)。否则为there can be a slight possibility for SQL injection if some odd encoding is used。
发布于 2015-03-05 07:58:21
在摆弄了半天之后,我发现PDO有一个bug ...
--
//This would run as expected:
$pdo->exec("valid-stmt1; valid-stmt2;");
--
//This would error out, as expected:
$pdo->exec("non-sense; valid-stmt1;");
--
//Here is the bug:
$pdo->exec("valid-stmt1; non-sense; valid-stmt3;");
它将执行"valid-stmt1;"
,在"non-sense;"
上停止,并且永远不会抛出错误。将不会运行"valid-stmt3;"
,返回true并谎称一切运行正常。
我希望它在"non-sense;"
上出错,但它没有。
这是我找到这个信息的地方:Invalid PDO query does not return an error
这里有个bug:https://bugs.php.net/bug.php?id=61613
所以,我试着用mysqli来做这件事,并没有找到任何关于它如何工作的可靠答案,所以我想把它留给那些想要使用它的人。
try{
// db connection
$mysqli = new mysqli("host", "user" , "password", "database");
if($mysqli->connect_errno){
throw new Exception("Connection Failed: [".$mysqli->connect_errno. "] : ".$mysqli->connect_error );
exit();
}
// read file.
// This file has multiple sql statements.
$file_sql = file_get_contents("filename.sql");
if($file_sql == "null" || empty($file_sql) || strlen($file_sql) <= 0){
throw new Exception("File is empty. I wont run it..");
}
//run the sql file contents through the mysqli's multi_query function.
// here is where it gets complicated...
// if the first query has errors, here is where you get it.
$sqlFileResult = $mysqli->multi_query($file_sql);
// this returns false only if there are errros on first sql statement, it doesn't care about the rest of the sql statements.
$sqlCount = 1;
if( $sqlFileResult == false ){
throw new Exception("File: '".$fullpath."' , Query#[".$sqlCount."], [".$mysqli->errno."]: '".$mysqli->error."' }");
}
// so handle the errors on the subsequent statements like this.
// while I have more results. This will start from the second sql statement. The first statement errors are thrown above on the $mysqli->multi_query("SQL"); line
while($mysqli->more_results()){
$sqlCount++;
// load the next result set into mysqli's active buffer. if this fails the $mysqli->error, $mysqli->errno will have appropriate error info.
if($mysqli->next_result() == false){
throw new Exception("File: '".$fullpath."' , Query#[".$sqlCount."], Error No: [".$mysqli->errno."]: '".$mysqli->error."' }");
}
}
}
catch(Exception $e){
echo $e->getMessage(). " <pre>".$e->getTraceAsString()."</pre>";
}
发布于 2015-05-07 00:26:48
一种快速下手的方法:
function exec_sql_from_file($path, PDO $pdo) {
if (! preg_match_all("/('(\\\\.|.)*?'|[^;])+/s", file_get_contents($path), $m))
return;
foreach ($m[0] as $sql) {
if (strlen(trim($sql)))
$pdo->exec($sql);
}
}
在合理的SQL语句终结点进行拆分。没有错误检查,没有注入保护。在使用之前先了解你的用法。就我个人而言,我使用它来播种用于集成测试的原始迁移文件。
https://stackoverflow.com/questions/6346674
复制相似问题