ZF-1398: Solution for running non-prepared SQL statements

Issue Type: Improvement Created: 2007-05-16T14:42:14.000+0000 Last Updated: 2012-05-06T13:02:40.000+0000 Status: Closed Fix version(s): Reporter: Bill Karwin (bkarwin) Assignee: Adam Lundrigan (adamlundrigan) Tags: - Zend_Db

  • state:need-feedback
  • zf-caretaker-adamlundrigan

Related issues: - ZF-1590

Attachments: - db.patch


The default implementation of the query() method in Zend_Db_Adapter_Abstract performs a prepare() on every SQL statement.

Some SQL statements are incompatible with prepare(). This varies by RDBMS brand and client API design, but there are usually some such statements.

For example, MySQL does not support preparing DDL or SHOW statements.

This is a feature request to add some method to Zend_Db_Adapter_Abstract to execute a SQL statement without performing a prepare(). In the case of PDO adapters, this is the exec() method of the underlying PDO object. But other database extensions have different methods to achieve this goal.


Posted by Bill Karwin (bkarwin) on 2007-05-18T15:26:59.000+0000

Non-prepared statements are also important for MySQL because the MySQL Query Cache feature cannot cache prepared statements.

Posted by julien PAULI (doctorrock83) on 2007-05-30T06:13:14.000+0000

Yeah, just read that please :…

I don't know if that bug has already been reported, but i met it on RC1.0 under a linux server. The query was

<pre class="highlight">

using PDO under Mysql(4.1) and Linux throws an exception 'SQLSTATE[HY000]: General error: 2030 This command is not supported in
the prepared statement protocol yet' in {file}

The only way to fix it was to modify the Zend/Db/Adapter/PdoAbstract.php by adding 

There is no way to change that behavior other than modify it manually in the framework source. And as said here : there is no was to prevent adapter from using prepared statements...

Posted by Darby Felton (darby) on 2007-12-10T10:24:26.000+0000

Related to Bill's comment:…

Posted by Wil Sinclair (wil) on 2008-03-25T20:44:00.000+0000

Please categorize/fix as needed.

Posted by Till Klampaeckel (till) on 2008-11-01T07:12:08.000+0000

Of course I only found this issue after opening another one. Can someone mark ZF-4767 as duplicate. Apologies for the extra work.

Also, what's the status of this issue? I'd like to see it fixed soon. :-)

Posted by Till Klampaeckel (till) on 2008-11-01T10:28:16.000+0000

Adding the link, to stress the importance of the issue:…

Posted by Ralph Schindler (ralph) on 2009-01-09T13:47:53.000+0000

I will evaluate this with the next 2 weeks.

Posted by Till Klampaeckel (till) on 2009-02-26T11:19:40.000+0000

This is still a blocker for me.

Below is some code which I started using in some of my projects to use unprepared queries and to utilize the query cache to a certain extend. The __call() is a way to debug this and to find methods I didn't cover yet.

This is not final but a work in progress and an idea. I could extend it, etc. if I get some feedback here.

<pre class="highlight">
 * @see Zend_Db_Statement
require_once 'Zend/Db/Statement.php';

class Zend_Db_Statement_Mysqli_Unprepared extends Zend_Db_Statement
    protected $_fetchMode, $_result;

    public function __construct($result, $fetchMode)
        $this->_result    = $result;
        $this->_fetchMode = $fetchMode;

    public function fetchAll($fetchMode = null)
        $data = array();
        while ($row = $this->fetch($fetchMode)) {
            array_push($data, $row);
        return $data;

    public function fetchColumn($col = 0)
        $row = $this->fetch(Zend_Db::FETCH_NUM);
        if (isset($row[$col])) {
            return $row[$col];
        return false; // FIXME

    public function fetch($fetchMode = null)
        if ($fetchMode === null) {
            $fetchMode = $this->_fetchMode;

        switch ($fetchMode) {
            case Zend_Db::FETCH_BOTH:
                $fetchMode = MYSQLI_BOTH;

            case Zend_Db::FETCH_ASSOC:
                $fetchMode = MYSQLI_ASSOC;

            case Zend_Db::FETCH_NUM:
                $fetchMode = MYSQLI_NUM;

        return $this->_result->fetch_array($fetchMode);

    public function __call($method, $args)

Here is a patch to Zend_Db_Adapter_Abstract:

<pre class="highlight">
Index: library/Zend/Db/Adapter/Abstract.php
--- library/Zend/Db/Adapter/Abstract.php    (revision 12330)
+++ library/Zend/Db/Adapter/Abstract.php    (working copy)
@@ -132,6 +132,12 @@
+     * @var boolean Defines whether the adapter uses prepared statemtents.
+     *              The default is 'true' to avoid a BC-break.
+     */
+    protected $_usePreparedStatements = true;
+    /**
      * Constructor.
      * $config is an array of key/value pairs or an instance of Zend_Config
@@ -406,7 +412,7 @@
      * @param  mixed  $sql  The SQL statement with placeholders.
      *                      May be a string or Zend_Db_Select.
      * @param  mixed  $bind An array of data to bind to the placeholders.
-     * @return Zend_Db_Statement_Interface
+     * @return mixed  Zend_Db_Statement_Interface or query result.
     public function query($sql, $bind = array())
@@ -425,13 +431,26 @@
             $bind = array($bind);
-        // prepare and execute the statement with profiling
-        $stmt = $this->prepare($sql);
-        $stmt->execute($bind);
+        if ($this->_usePreparedStatements === true) {
+            // prepare and execute the statement with profiling
+            $stmt = $this->prepare($sql);
+            $stmt->execute($bind);
-        // return the results embedded in the prepared statement object
-        $stmt->setFetchMode($this->_fetchMode);
-        return $stmt;
+            // return the results embedded in the prepared statement object
+            $stmt->setFetchMode($this->_fetchMode);
+            return $stmt;
+        }
+        if (!empty($bind)) {
+            throw new Zend_Db_Exception('You can\'t disable prepared statements, yet supply parameters to bind.');
+        }
+        $fetchClass = get_class($this) . '_Fetch';
+        Zend_Loader::loadClass($fetchClass);
+        return new $fetchClass(
+            $this->getConnection()->query($sql),
+            $this->_fetchMode
+        );
@@ -1022,6 +1041,24 @@
+     * Enable or disable prepared statements here.
+     *
+     * @param bool $use 'true' or 'false'.
+     *
+     * @return self
+     * @uses   self::$_usePreparedStatements
+     */
+    public function usePrepared($use)
+    {
+        if ($use === true) {
+            $this->_usePreparedStatements = $use;
+            return $this;
+        }
+        $this->_usePreparedStatements = false;
+        return $this;
+    }
+    /**
      * Abstract Methods

And here is some code demonstrating usage:

<pre class="highlight">
set_include_path('.:' . dirname(__FILE__) . '/trunk/library');

require_once 'Zend/Loader.php';
require_once 'Zend/Db.php';
require_once 'Zend/Db/Select.php';

try {
    $db = Zend_Db::factory(
            'username' => 'root',
            'password' => '',
            'host'     => 'localhost',
            'dbname'   => 'mysql'

    $select = $db->select();
    $select->from('user', array('Host', 'User'));



    $select = $db->select();
    $select->from('user', array('Host', 'User'));


} catch (Exception $e) {

I briefly ran tests and it didn't break anything, but no guarantees. ;-) I also cloned the tests for the Mysqli driver and ran them with usePrepared() and it worked. This is just an idea.

The usePrepared() call is more or less an idea to change the behaviour on run time. I also envision a config option which you can pass in with a Zend_Config object or an array.

I think by default this should be disabled but that would break BC so I guess I could live with it the other way around too. Haven't made up my mind up though, I just needed to get rid off prepared queries.

Posted by Benjamin Eberlei (beberlei) on 2009-02-26T14:26:08.000+0000

The fix looks good, by default i would enable the preparing of statements and allow to pass a config value to disable it.

Posted by Ralph Schindler (ralph) on 2009-07-17T08:48:06.000+0000

Why is there the requirement that non-preparable queries must go through query?

Why not add a method called execute() ? OR- why not simply use the Statement? or the getConnection()?

Posted by Josh Butts (jimbojsb) on 2009-08-17T11:16:20.000+0000

I agree that this should be possible to do, but don't necessary agree with the API implementation outlined in the patch above

Posted by Pieter Kokx (kokx) on 2010-02-18T11:21:39.000+0000

I attached a patch for this issue. It does only contain a fix for PDO adapters yet, other adapters will fail because there is an abstract function missing.

Posted by Adam Lundrigan (adamlundrigan) on 2011-11-17T02:08:06.000+0000

This issue is specifically mentioned in the Zend\Db RFC for ZFv2. However, is this something which should be fixed in ZFv1 as well?

Posted by Adam Lundrigan (adamlundrigan) on 2012-05-06T13:02:40.000+0000

This won't be fixed in ZFv1

Have you found an issue?

See the Overview section for more details.


© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.