|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #77015 Change behaviour of query() to match that of exec() for better performance
Submitted: 2018-10-15 17:20 UTC Modified: -
From: love at sickpeople dot se Assigned:
Status: Open Package: PDO MySQL
PHP Version: 7.2.11 OS:
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2018-10-15 17:20 UTC] love at sickpeople dot se

 - query() should send a QUERY like exec() does, instead of sending a PREPARE + EXECUTE + CLOSE

 - It improves performance in a very commonly used method, by avoiding unnecessary round trips

 - It is a BC break but only a minor one (you get a different error) and one you can easily fix (by switching "query" to "prepare", literaly)


For comparison

 1) query() sends a PREPARE + EXECUTE + CLOSE

 2) exec() sends a QUERY

 3) prepare() and execute() in concert sends a PREPARE + EXECUTE + CLOSE

The general usage of query() is to send a query without parameters and get the result. Query() doesn't work with parameters.

So changing query() to send a QUERY like exec() will avoid two unnecessary round trips. Hence this bug report.


However query() returns a PDOStatement which exec() doesn't. You can actually do this

$stmt = $pdo->query("select 1");


And execute the prepared query multiple times, just like prepare() and execute(). I.e. PREPARE + EXECUTE + EXECUTE + EXECUTE + CLOSE.

But calling execute() on the statement from query() is odd

 1) The manual for query() says (

    "For a query that you need to issue multiple times, you will realize better performance if you prepare a PDOStatement object using PDO::prepare() and issue the statement with multiple calls to PDOStatement::execute()."

    It seems like it's undocumented that you can do the exact same thing with query(), at least with Mysqlnd.

 2) You *cannot* use parameters to execute(), even if you have parameters in the query


$stmt = $pdo->query("select ?");


    This throws a PDOException with 2031 error (


So this will be a BC break because

 1) Calling execute() on the PDOStatement from query() should instead throw an exception, stating that the query is not prepared/has ended/there are no query to execute or similar

    The fix is to simply change $pdo->query(..) to $pdo->prepare(..).

 2) Using parameters in query() will lead to a different error

    Currently if you do $pdo->query("select ?") you will get a PDOException with 2031 error (same as earlier), because PREPARE is used.

    If query() is changed to use QUERY, you will get an PDOException with syntax error instead. Just like with $pdo->exec("select ?")

I've tested with PHP 7.2.11, Mysqlnd "5.0.12-dev - 20150407" (same version number as in master) and MySQL 5.7.23.

Test script:
$host = '';
$db = '';
$user = '';
$pass = '';

$options = [
    PDO::ATTR_EMULATE_PREPARES => false, /* required */

$pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass, $options);

/* Extracts below are from MySQL general log

$pdo->exec('set @foo=1');

2018-10-15T16:57:44.219983Z	 5126 Query	set @foo=1

$pdo->prepare('set @foo=2')->execute();

2018-10-15T16:57:44.220117Z	 5126 Prepare	set @foo=2
2018-10-15T16:57:44.220192Z	 5126 Execute	set @foo=2
2018-10-15T16:57:44.220283Z	 5126 Close stmt	

$pdo->query ('set @foo=3');

2018-10-15T16:57:44.220311Z	 5126 Prepare	set @foo=3
2018-10-15T16:57:44.220353Z	 5126 Execute	set @foo=3
2018-10-15T16:57:44.220431Z	 5126 Close stmt	

$stmt = $pdo->query("select 1");


2018-10-15T16:57:44.220523Z	 5126 Prepare	select 1
2018-10-15T16:57:44.220566Z	 5126 Execute	select 1
2018-10-15T16:57:44.220706Z	 5126 Execute	select 1
2018-10-15T16:57:44.220801Z	 5126 Execute	select 1
2018-10-15T16:57:44.220868Z	 5126 Execute	select 1
2018-10-15T16:57:44.221214Z	 5126 Close stmt	

$stmt = $pdo->query("select ?");

$stmt->execute([1]); // throws PDOException with 2031


Add a Patch

Pull Requests

Add a Pull Request

PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Tue Mar 09 05:01:23 2021 UTC