Ulf Wendel

PDO_MYSQLND: Mighty multiple-queries and PDOStatement::nextRowset()

2008/07/04 by admin

MySQLs multiple-queries feature makes it possible to run two or more SQL commands with one function call: SQL injections made easy. The PDO design is said to be optimized for security. For security reasons PDO has gone the stony road of prepared statements. If security matters, why does PDO_MYSQL explicitly enable multiple-queries? If portability matters, why do you use a MySQL specific feature?

Mighty multiple-queries feature: performance

Multi-statement is yet another example of how optimization works: trade in one feature (security) for another (performance). Sending several SQL command with one function call saves communication round-trips. You need only one round-trip for executing n > 1 SQL statements. This can be a mighty feature if you need to tune MySQL to the extreme. The price you pay is that you have to check carefully what SQL commands you send.

Even if some do not like to hear this: it is the duty of the application programmer to send only proper SQL! The language spoken between applications and most database servers is the Structured Query Language (SQL). SQL has been been around in the world for some 10 years before PHP was born in 1995.

Feel free to approach a scandinavian beauty in a language which she does not speak, say "Plattdeutsch". But be aware that the compliments you make in "Plattdeutsch" may be misunderstood by her. She might give you a slap in your face instead of a romantic midsummer evening. You have three options to get out of the dilemma:

  • learn her language
  • choose another communication channel
  • choose a girl from north germany which understands "Plattdeutsch"

If you don’t want to talk proper SQL to your database server you have the same options.

The security risk

SQL injection attacks aim to insert malicious SQL fragements into SQL commands you send to the database server. SQL injection on the application layer is almost impossible if you never build your queries dynamically. An attacker cannot change a constant string. 


$db->exec("DROP TABLE IF EXISTS test");

To change a constant string you either need to hack PHP or you need to sniff on network connection. The best MySQL sniffer dog is the MySQL Proxy. Of course, true hackers don’t need the comfort of Joe, the MySQL Proxy, but use Wireshark (ethereal) to go deeper.

Dynamically build SQL commands have a higher risk of getting tainted. Kristian has teached me ever since that a web application is no different from any other application. An application is a state machine. The state machine must not switch from state A to state B if the data passed to B is not sanitized, if need be. And he continued his lesson: all outside data is tainted.


$sql = "DROP TABLE IF EXISTS test; 
  CREATE TABLE test(id INT, label CHAR(1))";
[...]
$db->exec($sql);

The above code smells – a bit. Are you 100% sure that $sql never contains any harmful data? Are you still using the handy but stinky Register Globals feature. Is there any chance that $sql has been created by extract($_GET, EXTR_OVERWRITE) or $sql = $_GET['sql'].

Are multiple-queries more insecure than unquoted parameters?

I am boring you. You know that you must escape input parameter before you put them into a query string. 


$sql = sprintf('INSERT INTO test(label) VALUES (%s)', 
  $db->quote($_GET['label']));
$db->exec($sql);

By the way. The PDO documentation says that you are strongly recommended to use PDO::prepare() to prepare SQL statements with bound parameters instead of using PDO::quote() to interpolate user input into a SQL statement. Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query., from the PDO::quote() documentation.

This is only half of the story. With emulated prepared statements there is no difference between prepare() and the above! The PDO SQL parser does the very same. Prepared Statements are not supported by all databases. They are not more portable. Two different code fragments doing the very same are immune to SQL injection to the same degree. The PHP manual speaks about server-side prepared statements. It leaves out emulated prepared statements. However, no doubt that prepare() is a convenience feature which helps to ensure that the use of quote() will not be forgotten.

Back to multiple-queries. To insert malicious SQL at a place where a parameter is expected can be a bit tricky. You need to ensure that the resulting SQL command is still valid and the database server executes it. Its impossible for you to insert a second SQL command as the server will execute only one command. If the multiple statement execution feature is enabled and you manage to insert malicious SQL in a way that the query string is still valid you can insert and execute an arbitrary number of malicious SQL commands!


$malicious = '2); DROP DATABASE mysql; SET @a = (1';

// Uuups someone has turned his brain off 
// Use prepare() it is brain-enabled by default
$sql = sprintf('INSERT INTO test(label) VALUES (%s)', 
  $_GET['label']);

$db->exec($sql);

Preconditions: emulated prepared statements, unexperienced programmer. Work around: use ext/mysqli @ mysqlnd (default: multiple-queries disabled) or disable the prepared statement emulation of PDO using $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0). Server-side prepared statements do not support the multiple-queries feature.

BC break for PDO_MYSQLND: disable multiple-queries by default

Can we introduce a BC break and disable the multiple statement feature by default? I have no clue how it crept into PDO_MYSQL. By its nature this feature is a security risk. As PDO tries to optimize for security, I am +1 disabling multiple statements by default. For BC reasons we can introduce a flag to enable the feature for those who know how to write SQL.

Using PDO::nextRowset() with multiple-queries

Some queries generate more than one result set. In case of MySQL, stored procedures and the multiple statement feature can return more than one result set. Those result sets can be fetched using PDO::nextRowset(). The posting PDO_MYSQLND: Calling Stored Procedures works fine with mysqlnd shows how you call stored procedures with PDO_MYSQL[ND]. Note that bugs exist in PDO_MYSQL which may cause issues. PDO_MYSQLND does not have those problems. The second use case of PDO::nextRowset() are multiple-queries.

As explained, the multiple statement feature can be used to execute a sequence of SQL commands with one API call. PDO::exec() can be used for all queries which do not generate a result set such as CREATE, INSERT, UPDATE or DELETE. As none of the statements generates a result set nothing needs to be fetched. See above for a usage example.

Of course SELECT does generate a result set. And you can send more than one SELECT statement at once to the MySQL Server using PDO::query() or PDO::prepare(). You must not use PDO::exec() for any statement returning data. If you do, you will get errors. PDO::exec() is not made for fetching results.


$stmt = $db->query('SELECT 1; SELECT 2; SELECT 3');
do {
  var_dump($db->fetchAll(PDO::FETCH_ASSOC));
} while ($db->nextRowset());

To iterate over all result sets you use the same syntax which you use for calling stored procedures. The example works fine with PDO_MYSQLND.

PDOs API does not support all MySQL features

However, the PDO API does not support mixing statements which return result sets and statements which do not return data. MySQL allows you mix all kinds of queries in one call when using the multiple statement feature. Using ext/mysqli or the C-API you can execute such a query and handle its results properly. With PDO you cannot!


$stmt = $db->query('DROP TABLE IF EXISTS test;
  CREATE TABLE test(id INT); 
  INSERT INTO test(id) VALUES (1); 
  SELECT * FROM test');
do {
  var_dump($db->fetchAll(PDO::FETCH_ASSOC));
} while ($db->nextRowset());

This example code will bail at you.

We could patch PDO_MYSQLND to run the example code and print out exactly one result set: the result set from the SELECT query. But its impossible to return the number of affected rows from the INSERT to you. The PDO API does not support it. The MySQL flagship extension ext/mysqli gives you access to both the SELECT results and the number of affected rows from the INSERT. Given that, I am not convinced that we should support multiple-queries sequences like the above in PDO_MYSQLND.

Only ext/mysqli gives you access to all MySQL server features. If your application makes use of an userland (PHP based) database abstraction, make sure that the database abstraction does use ext/mysqli for accessing MySQL. Don’t restrict your database abstraction layer to the features which are accessible through PDO.

Comments are closed.