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 this...it'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 this...so 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.

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

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?

-ralph

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:

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.

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()}}:

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 PHP.net 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

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.