<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Multiple Statements</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqli.quickstart.stored-procedures.html">Stored Procedures</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqli.quickstart.transactions.html">API support for transactions</a></div>
<div class="up"><a href="mysqli.quickstart.html">Quick start guide</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqli.quickstart.multiple-statement" class="section">
<h2 class="title">Multiple Statements</h2>
<p class="para">
MySQL optionally allows having multiple statements in one statement string.
Sending multiple statements at once reduces client-server
round trips but requires special handling.
</p>
<p class="para">
Multiple statements or multi queries must be executed
with <span class="function"><a href="mysqli.multi-query.html" class="function">mysqli_multi_query()</a></span>. The individual statements
of the statement string are separated by semicolon.
Then, all result sets returned by the executed statements must be fetched.
</p>
<p class="para">
The MySQL server allows having statements that do return result sets and
statements that do not return result sets in one multiple statement.
</p>
<p class="para">
<div class="example" id="example-1641">
<p><strong>Example #1 Multiple Statements</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"example.com"</span><span style="color: #007700">, </span><span style="color: #DD0000">"user"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if (</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">connect_errno</span><span style="color: #007700">) {<br /> echo </span><span style="color: #DD0000">"Failed to connect to MySQL: (" </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">connect_errno </span><span style="color: #007700">. </span><span style="color: #DD0000">") " </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">connect_error</span><span style="color: #007700">;<br />}<br /><br />if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP TABLE IF EXISTS test"</span><span style="color: #007700">) || !</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE TABLE test(id INT)"</span><span style="color: #007700">)) {<br /> echo </span><span style="color: #DD0000">"Table creation failed: (" </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno </span><span style="color: #007700">. </span><span style="color: #DD0000">") " </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">;<br />}<br /><br /></span><span style="color: #0000BB">$sql </span><span style="color: #007700">= </span><span style="color: #DD0000">"SELECT COUNT(*) AS _num FROM test; "</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$sql</span><span style="color: #007700">.= </span><span style="color: #DD0000">"INSERT INTO test(id) VALUES (1); "</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$sql</span><span style="color: #007700">.= </span><span style="color: #DD0000">"SELECT COUNT(*) AS _num FROM test; "</span><span style="color: #007700">;<br /><br />if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">multi_query</span><span style="color: #007700">(</span><span style="color: #0000BB">$sql</span><span style="color: #007700">)) {<br /> echo </span><span style="color: #DD0000">"Multi query failed: (" </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno </span><span style="color: #007700">. </span><span style="color: #DD0000">") " </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">;<br />}<br /><br />do {<br /> if (</span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">store_result</span><span style="color: #007700">()) {<br /> </span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_all</span><span style="color: #007700">(</span><span style="color: #0000BB">MYSQLI_ASSOC</span><span style="color: #007700">));<br /> </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">free</span><span style="color: #007700">();<br /> }<br />} while (</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">more_results</span><span style="color: #007700">() && </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">next_result</span><span style="color: #007700">());<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
<div class="example-contents"><p>The above example will output:</p></div>
<div class="example-contents screen">
<div class="cdata"><pre>
array(1) {
[0]=>
array(1) {
["_num"]=>
string(1) "0"
}
}
array(1) {
[0]=>
array(1) {
["_num"]=>
string(1) "1"
}
}
</pre></div>
</div>
</div>
</p>
<p class="para">
<em class="emphasis">Security considerations</em>
</p>
<p class="para">
The API functions <span class="function"><a href="mysqli.query.html" class="function">mysqli_query()</a></span> and
<span class="function"><a href="mysqli.real-query.html" class="function">mysqli_real_query()</a></span> 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 likeliness of accidental SQL injection
attacks. An attacker may try to add statements such as
<em>; DROP DATABASE mysql</em> or <em>; SELECT SLEEP(999)</em>.
If the attacker succeeds in adding SQL to the statement string but
<em>mysqli_multi_query</em> is not used, the server will not
execute the second, injected and malicious SQL statement.
</p>
<p class="para">
<div class="example" id="example-1642">
<p><strong>Example #2 SQL Injection</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"example.com"</span><span style="color: #007700">, </span><span style="color: #DD0000">"user"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT 1; DROP TABLE mysql.user"</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$res</span><span style="color: #007700">) {<br /> echo </span><span style="color: #DD0000">"Error executing query: (" </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno </span><span style="color: #007700">. </span><span style="color: #DD0000">") " </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">;<br />}<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
<div class="example-contents"><p>The above example will output:</p></div>
<div class="example-contents screen">
<div class="cdata"><pre>
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
</pre></div>
</div>
</div>
</p>
<p class="para">
<em class="emphasis">Prepared statements</em>
</p>
<p class="para">
Use of the multiple statement with prepared statements is not supported.
</p>
<p class="para">
<em class="emphasis">See also</em>
</p>
<p class="para">
<ul class="simplelist">
<li class="member"> <span class="methodname"><a href="mysqli.query.html" class="methodname">mysqli::query()</a></span></li>
<li class="member"> <span class="methodname"><a href="mysqli.multi-query.html" class="methodname">mysqli::multi_query()</a></span></li>
<li class="member"> <span class="methodname"><strong>mysqli_result::next-result()</strong></span></li>
<li class="member"> <span class="methodname"><strong>mysqli_result::more-results()</strong></span></li>
</ul>
</p>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqli.quickstart.stored-procedures.html">Stored Procedures</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqli.quickstart.transactions.html">API support for transactions</a></div>
<div class="up"><a href="mysqli.quickstart.html">Quick start guide</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
