The following code produces the same error:

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

http://bugs.php.net/bug.php?id=40822

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.

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.

$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}';");
//$sth->execute();

var_dump($oRow);

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.

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

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?

-ralph

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);
$stmt->execute(array($string));
print_r($stmt->fetchAll());

// 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
print_r($stmt->fetchAll());

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.

$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.

-Ralph

take a look at: http://www.php.net/ChangeLog-5.php

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

$select->from('foo')->where('bar = ?', "test'ing")->where('bar = ?', "b'?iz");

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:

$select->from('foo')->where("id <> ?", "'")->where('bar = ?', "b'?iz");

when

 $select->from('foo')->where('bar = ?', "b'?iz");

crashes.

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

$db->getConnection()->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, false);

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.