ZF-1343: "Invalid parameter number: no parameters were bound" when using ' and ? in where clause

Issue Type: Bug Created: 2007-05-04T17:47:26.000+0000 Last Updated: 2009-11-03T16:44:57.000+0000 Status: Resolved Fix version(s): Reporter: Chris van der Wel (kritz) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

Related issues: - ZF-5790



<pre class="highlight">
class Users extends Zend_Db_Table_Abstract {
    protected $_name = 'users';

$db = Zend_Db::factory('PDO_MYSQL', $options);

$username = "chr?i's";

$table = new Users();
$where = $table->getAdapter()->quoteInto('username = ?',$username);
$rows = $table->fetchAll($where);

results in an exception:

Fatal error: Uncaught exception 'Zend_Db_Adapter_Exception' with message 'SQLSTATE[HY093]: Invalid parameter number: no parameters were bound'

When using "chr?is" or "chr'is" for $username everything is fine. It doesn't matter where the ? and ' are in the $username variable, just when they are both in $username they are causing problems.


Posted by Thomas Weidner (thomas) on 2007-05-06T09:06:45.000+0000

Zend_Db related - assigned to Bill

Posted by Chris van der Wel (kritz) on 2007-05-06T10:05:02.000+0000

It looks like it's a bug in PDO itself and not the Zend Framework.

The following code produces the same error:

<pre class="highlight">
$sth = $dbh->prepare("SELECT * FROM `users` WHERE username = 'ch?\'ris';");

Posted by Chris van der Wel (kritz) on 2007-05-06T14:05:03.000+0000

Does anybody from Zend know if any bugs in the PDO extension will be fixed? The last release was more than a year ago, but there are several bug reports. Does it make any sense to report a new bug?

Posted by Bill Karwin (bkarwin) on 2007-05-06T21:22:37.000+0000

There are bugs reported as recently as March 15 in the PDO category that are now CLOSED (for example: So clearly some work continues on PDO.

This bug seems related to ZF-347. PDO is not very intelligent about noticing when parameter placeholders inside string literals. Not surprising -- to support that, PDO would have to implement a complete client-side SQL parser and that's probably beyond its scope.

I'm going to mark this issue POSTPONED with respect to working on it for Zend Framework.

Posted by Bill Karwin (bkarwin) on 2007-07-20T20:23:57.000+0000

Confirmed, a test shows that this bug occurs when using PDO directly, not using Zend_Db at all.

Posted by Julian Davchev (jmut) on 2008-03-18T09:16:11.000+0000

I think we should reopen this ticket.

I know this is old story but it totally bugs me that I am not able to use PDO_Mysql because of's just so much faster. From your conclusion I get the idea that it's impossible to use this value using PDO_Mysql, well this is not the case. I am in process to finding what exactly the problem is...but it come to Select object etc etc... and will need more time. Thats why I decided to post get some feedback from someone more familiar with issue.

I looked in 6 scenarios with two values - one reported in ticket and one that I have problem with (seems it behaves differently - see scenario 2)

Well obviously to reproduce all cases should comment/uncomment all values and/or all scenarious code. Hope it is not messy and clear. Scenario 1 is the one that works for either values.

    $username = "chr?i's";  // value 1
    //$username = "';?%>'"; // value 2

    $table = new Table_User();

    //scenario 1 ... works for value 1 and 2
    //$oRow = $table->getAdapter()->fetchRow("SELECT * FROM `user` WHERE U_user_name = ?",$username);

    //scenario 2 ... works for value 1 doesn't work for value 2       - initially reported in ticket
    //$sql_username = $table->getAdapter()->quoteInto('U_user_name = ?',$username);
    //$oRow = $table->getAdapter()->fetchRow("SELECT * FROM user WHERE $sql_username");

    //scenario 3 ... doesn't work for value 1 and 2
    //$oRow = $table->fetchRow("U_user_name = ?",$username);

    //scenario 4 ... doesn't work for value 1 and 2
    //$where = $table->getAdapter()->quoteInto('U_user_name = ?',$username);
    //$oRow = $table->fetchAll($where);

    //scenario 5 ... doesn't work for value 1 and 2
    //$oRow = $table->fetchRow("SELECT * FROM user WHERE U_user_name = ?",$username);

    //scenario 6 ... doesn't work for value 1 and 2       -  one Chris van der Wel tested with.
    //$sth = $table->getAdapter()->prepare("SELECT * FROM `users` WHERE U_user_name = '{$username}';");


Posted by Julian Davchev (jmut) on 2008-03-18T09:30:17.000+0000

My bad, "initially reported in ticket" is scenario 4, not as I mentioned scenario 2

Posted by Julian Davchev (jmut) on 2008-03-18T09:49:19.000+0000

Can scratch scenario 3 and 5... second param is order by there, hence not relevant...don't know why got confused.

Posted by Willie Alberty (willie) on 2008-03-18T10:53:54.000+0000

I am experiencing this same issue in a production environment. I acknowledge the fact that there is a bug in PDO which is causing this behavior. However, I am not using PDO directly. If I were, I would be forced to live with it.

Since I am using Zend_Db, there is an opportunity to work around the bug until it can be fixed in PDO. Isn't that part of the purpose of an abstraction layer -- to normalize behavior and work around bugs in the underlying implementations?

Unless there is truly nothing that can be done (i.e, there is no possible way to code around it in PHP, which I did not read in the comment stream), this issue should be reopened. Telling our customers to "not use ' and ? characters in the same database column" is simply impractical.

Posted by Bill Karwin (bkarwin) on 2008-03-18T11:40:52.000+0000

Removing myself as assignee for this issue.

Posted by Bill Karwin (bkarwin) on 2008-03-18T11:57:24.000+0000

Users requested reopening this issue.

Changing component, since it is not specific to Zend_Db_Table. Any query to a PDO adapter can exhibit this issue.

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

Please categorize/fix as needed.

Posted by Wil Sinclair (wil) on 2008-03-25T22:06:47.000+0000

Resetting 'fix version priority' and 'fix version' to be re-evaluated for next release.

Posted by Tim Steiner (spam38) on 2008-08-05T09:15:44.000+0000

It would appear that if you use double quotes instead of single quotes around the string, the problem goes away

ie: SELECT * FROM users WHERE username = "chr?i\'s"; instead of SELECT * FROM users WHERE username = 'chr?i\'s';

What are the consequences of having the _quote method use double quotes instead of single quotes?

Posted by Bill Karwin (bkarwin) on 2008-08-05T09:22:06.000+0000

@Tim: Double-quotes used for string delimiters is non-standard SQL. Double-quotes are supposed to delimit identifiers (e.g. table and column names). SIngle quotes are the only standard SQL string delimiter.

Posted by Ralph Schindler (ralph) on 2008-08-05T09:29:31.000+0000

Bill, do you have any insight for me as per fixing the issue? I am inclined to mark this as not-an-issue since its a PDO thing. is that not the right course of action?


Posted by Bill Karwin (bkarwin) on 2008-08-05T11:05:32.000+0000

Yes, in my opinion this is a non-issue for ZF. The fault lies in PDO code, not in ZF. I had closed this issue as a non-issue on 2007-06-20, but users requested that it be re-opened.

There are two workarounds:

  1. Avoid using a PDO adapter if you use string literals in SQL expressions containing both an escaped quote and a question mark (or named parameter like :param). This is not satisfying some people who say that PDO_MySQL is faster than MySQLi. However, I'm not sure what the real difference in performance is, or whether it's a dealbreaker for a typical web app. This deserves some measurement, not anecdotal evidence or artificial benchmarks.

  2. Parameterize the SQL string literal, without using interpolation as a "fake parameter" mechanism like quoteInto(). For example compare the following two solutions, the first using a parameter and the second using quoteInto():

     // True parameter solution works:
     $string = "O'Reilly?";
     $sql = "SELECT * FROM foo WHERE c = ?";
     $stmt = $db->prepare($sql);
     // Fake parameter solution with interpolation throws exception:
     $string = "O'Reilly?";
     $sql = $db->quoteInto("SELECT * FROM foo WHERE c = ?", $string);
     $stmt = $db->prepare($sql);
     $stmt->execute();  // PDO throws exception here

It's still difficult to use params in Zend_Db_Table methods, since those all use interpolation rather than true parameters. You might want to architect some methods to Zend_Db_Table to support true parameters. In fact, a community member just posted on the ZF mailing list today with such an idea. But I think his solution is clumsy.

One other suggestion: have someone with privileges on actually fix the fault in PDO itself. I have emailed Wez Furlong about this issue twice in the past, but unfortunately I got no reply.

Posted by James Hartford (jameshartford) on 2008-08-21T10:22:30.000+0000

This is also an issue when using Zend_Db_Select as follows:

$db = new Zend_Db::factory(...);

$select = $db->select()->from("users")->where("firstname=?", "test's ?"); $rows = $db->fetchAll($select);

In this case you should be able to detect in Zend_Db_Adapter_Abstract->query() that $sql is an instance of Zend_Db_Select and reset the $sql and $bind values appropriately to use a true parameter solution. Unless this is done, using Zend_Db_Select gives the same result as the fake parameter solution that Bill has shown above.

Posted by Tim Steiner (spam38) on 2008-10-30T14:31:31.000+0000

I believe the issue is resolved as of PHP 5.2.7RC2

Posted by Wil Sinclair (wil) on 2008-12-19T15:08:08.000+0000

Ralph, please evaluate this issue and determine the best course of action.

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

Has this been resolved in PHP?


Posted by Serban Ghita (serbanghita) on 2009-01-21T23:30:33.000+0000

this has been fixed in PHP 5.2.8

take a look at:

Fixed bugs #44251, #41125 (PDO + quote() + prepare() can result in segfault). (tsteiner at nerdclub dot net)

Posted by Alexander Steshenko (lcf) on 2009-02-26T09:19:22.000+0000

Nope. Nothing has changed in 5.2.8. The bug is still there.

Posted by Johannes Schill (johannes) on 2009-02-27T02:21:32.000+0000

I had similar (if not the same?) problem with PHP 5.2.3, but yesterday's release - 5.2.9 - solved it here if anyone is interested.

Posted by Alexander Steshenko (lcf) on 2009-02-27T03:06:00.000+0000

Yes. I've just upgraded to the yesterday's 5.2.9 release - problem is solved.

Posted by Niko Kivelä (tovrleaf) on 2009-03-16T01:44:02.000+0000

I experiences this same bug and what I found interesting was that using PDOStatement doesn't crash if you have used single ' before using both ' and ? as:

<pre class="literal"> 

This gives us dirty quick workaround solution (in theory) to add logic to query when input contains both ' and ? for users with PHP version < 5.2.9 to use something like this:

when ```` crashes.

Posted by Snakehit (snakehit) on 2009-03-18T03:55:41.000+0000

I have also upgraded to latest version of PHP 5.2.9 and the problem is solved.

Posted by Artur Bodera (joust) on 2009-04-27T04:29:48.000+0000

This sucks when you can't use the newest PHP version.

My workaround for the problem (only for Mysql, may have other negative effects)

<pre class="highlight">
$db->getConnection()->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, false);

Posted by Artur Bodera (joust) on 2009-04-27T07:36:08.000+0000

Unfortunatelly PDO sucks even more, and the same error happend when using :varName binding, ie:

<pre class="highlight">
SELECT * FROM table  WHERE name = '}elhF\'?:u|h>\'?n\'?'

This query, even with my woarkound above, will throw SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters. That's because it sees :u as a parameter.

Posted by Ralph Schindler (ralph) on 2009-05-18T15:41:01.000+0000

Test case commited, this appears to be not an issue in php 5.2.8

Have you found an issue?

See the Overview section for more details.


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

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