PHP - Manual: Multiple Statements

2024-11-23

Multiple Statements

MySQL optionally allows having multiple statements in one statement string, but it requires special handling.

Multiple statements or multi queries must be executed with mysqli::multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.

The MySQL server allows having statements that do return result sets and statements that do not return result sets in one multiple statement.

示例 #1 Multiple Statements


<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");

$sql = "SELECT COUNT(*) AS _num FROM test;
        INSERT INTO test(id) VALUES (1); 
        SELECT COUNT(*) AS _num FROM test; ";

$mysqli->multi_query($sql);

do {
    if ($result = $mysqli->store_result()) {
        var_dump($result->fetch_all(MYSQLI_ASSOC));
        $result->free();
    }
} while ($mysqli->next_result());

以上例程会输出：

array(1) {
  [0]=>
  array(1) {
    ["_num"]=>
    string(1) "0"
  }
}
array(1) {
  [0]=>
  array(1) {
    ["_num"]=>
    string(1) "1"
  }
}

Security considerations

The API functions mysqli::query() and mysqli::real_query() do not set a connection flag necessary for activating multi queries in the server. An extra API call is used for multiple statements to reduce the damage of accidental SQL injection attacks. An attacker may try to add statements such as ; DROP DATABASE mysql or ; SELECT SLEEP(999). If the attacker succeeds in adding SQL to the statement string but mysqli::multi_query is not used, the server will not execute the injected and malicious SQL statement.

示例 #2 SQL Injection


<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
$result = $mysqli->query("SELECT 1; DROP TABLE mysql.user");
if (!$result) {
    echo "Error executing query: (" . $mysqli->errno . ") " . $mysqli->error;
}
?>

以上例程会输出：

Error executing query: (1064) You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax 
to use near 'DROP TABLE mysql.user' at line 1

Prepared statements

Use of the multiple statement with prepared statements is not supported.

See also

add a note

User Contributed Notes 1 note

down

velthuijsen ¶

3 years ago


Suggested improvement(s) to example 1.

reasons:
Multi_query only returns a non false response if a data/result set is returned and only checks for the first query entered. Switching the first SELECT query with the INSERT query will result in a premature exit of the example with the message "Multi query failed: (0)".
The example assumes that once the first query doesn't fail that the other queries have succeeded as well. Or rather it just exits without reporting that one of the queries after the first query failed seeing that if a query fails next_result returns false.

The changes in the example comes after the creation of the string $sql.

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

$sql = "SELECT COUNT(*) AS _num FROM test; ";
$sql.= "INSERT INTO test(id) VALUES (1); ";
$sql.= "SELECT COUNT(*) AS _num FROM test; ";

// changes to example 1 start here

// don't bother checking the result from multi_query since it will return false
// if the first query does not return data even if the query itself succeeds.
$mysqli->multi_query($sql);

do // while (true); // exit only on error or when there are no more queries to process
{
    // check if query currently being processed hasn't failed
    if (0 !== $mysqli->errno)
    {
        echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
        break;
    }

    // store and possibly process result of the query,
    // both store_result & use_result will return false 
    // for queries that do not return results (INSERT for example)
    if(false !== ($res = $mysqli->store_result() )
    {
        var_dump($res->fetch_all(MYSQLI_ASSOC));
            $res->free();
    }

    // exit loop if there ar no more queries to process
    if (false === ($mysqli->more_results() )
    {
        break;
    }

    // get result of the next query to process
    // don't bother to check for success/failure of the result
    // since at the start of the loop there is an error check &
    // report block.
    $mysqli->next_result()

} while (true); // exit only on error or when there are no more queries to process
?>

Note that the normal while ($mysqli->more_results() && $mysqli->next_result() has been replaced by two checks and  while (true);
This is due to the 'problem' that next_result will return false if the query in question failed.
So one either needs to do one last check after the while loop to check if there was an error or one has to split up the different actions.
The changes in the example do the splitting.

add a note

官方地址：https://www.php.net/manual/en/mysqli.quickstart.multiple-statement.php

有任何技术问题请点击这里网站运营推广招聘

IT PHP 编程语言 Linux 开发编程科技 Elasticsearch HTML/CSS/XML 网络 JAVA NoSQL 数据库面试 C/C++ Golang Git 算法操作系统正则表达式 Redis 互联网 MySql JavaScript 运维软件国际架构设计 Mac OS TCP/IP Excel Vim Windows Socket Oracle VR MongoDB Python 运营 MemCache 商业硬件电子娱乐设计 nginx 摄影游戏 WordPress HTTP 团建数码电器

php7 安装fileinfo扩展 preg_split — 通过一个正则表达式分隔字符串 php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题 [PHP] inet_pton/inet_ntop IP地址转换函数 array_multisort() 多字段排序 PHPMailer设置utf8 PHPMailer字符集CharSet ADORecordSet对象 [鸟哥]PHP_INT_MIN 和 -9223372036854775808 PHP 8.1 adodb手册 opcache预加载 Composer的Packagist资源 mysql面试题支持php7的性能采集扩展 php扩展开发 PHP7 mcrypt替代方案 Packagist / Composer 中国全量镜像 PHP json解析（json_decode）页面工具如何在Composer中为GitLab实例设置访问令牌 PHP mkdir()写出来的权限与mode值不符合

略微加速

PHP官方手册 - 互联网笔记

Multiple Statements

User Contributed Notes 1 note