Zend_Db related - assigned to Bill

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

The following code produces the same error:

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

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?

There are bugs reported as recently as March 15 in the PDO category that are now CLOSED (for example: http://bugs.php.net/bug.php?id=40822). 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.

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

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.

    $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);

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

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

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.

Removing myself as assignee for this issue.

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.

Please categorize/fix as needed.

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

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?

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

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

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

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.

I believe the issue is resolved as of PHP 5.2.7RC2

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

Has this been resolved in PHP?

-Ralph

this has been fixed in PHP 5.2.8

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)

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

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.

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

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:

 

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.

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

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

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

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

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

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.

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