Issues

ZF-2059: ":0'" in a String Results in Exception

Description

Today I ran into the following Zend_DB exception when running some routine select queries on a MySQL database:

SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

It seems the culprit was a three-character :0' sequence in a string in the WHERE clause. That's a column, zero, and apostrophe. If you have other characters between the zero and apostrophe, it will still generate the exception, but the column and zero need to be together. You should be able to reproduce this bug if you pass one of the two following to a fetchCol or fetchAll query.

$sql = 'SELECT something FROM somewhere WHERE somethingElse = ' . $db->quote(":0'");

or simply:

$sql = 'SELECT something FROM somewhere WHERE somethingElse = ':0\'';

As this is valid sql syntax, it seems something goes awry in how Zend_DB parses the string internally.

Comments

Assigned to Bill

SVN r6685 includes a unit test for this case.

Strange. On my laptop (PHP 5.1.4 on WinXP), I get no error from this test (pdo_mysql).

But when I run on the test server (PHP 5.2.4 on Linux), I get the expected exception:


3) testQuoteStringColonZeroApostrophe(Zend_Db_Adapter_Pdo_MysqlTest)
Zend_Db_Statement_Exception: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound
/var/local/zf/library/Zend/Db/Statement.php:279
/var/local/zf/library/Zend/Db/Adapter/Abstract.php:396
/var/local/zf/library/Zend/Db/Adapter/Pdo/Abstract.php:206
/var/local/zf/library/Zend/Db/Adapter/Abstract.php:603

When running on the test server, I also get many other test failures and errors that do not show up on my laptop.

The test server configuration runs the following adapters: * mysqli * pdo_mysql * pdo_pgsql * pdo_sqlite

Maybe something changed in pdo_mysql that is breaking this for us, or is there a better suspect?

The pdo_mysql quoting mechanism does not appear to be to blame for this error; I'm still looking for exactly where the problem is rooted.

Not certain if it is relevant, but I originally came across this issue on Zend Core 2.0 (PHP 5.2.3, pdo_mysql), running on Mac and Linux.

I was just playing around with the same string, and to my surprise it didn't produce the exception at first. Then, after a few different column names and switching to a different table, SQLSTATE[HY093] crept out again, and more perplexingly, the original query, which had been working fine, started producing the same message as well.

To further complicate things, my Mac ZC setup occasionally segfaults and then reverts to outputting the exception when running queries with the suspect string. I can't really establish a pattern to all this yet.

My apologies for the anecdotal observations. I will check out the above svn rev and test in a little more structured way when I get a chance this weekend!

Your comments, however anecdotal, are helpful; no need to apologize. :)

I just committed another revision to the unit tests in SVN r6687.

On PHP 5.2.4 (Linux) I get inconsistent results:


 Zend_Db_Adapter_MysqliTest
 .
 Zend_Db_Adapter_Pdo_MysqlTest
 E
 Zend_Db_Statement_Pdo_MysqlTest
 .
 Zend_Db_Adapter_Pdo_PgsqlTest
 .
 Zend_Db_Adapter_Pdo_SqliteTest
 .

There was 1 error:

1) testZF2059(Zend_Db_Adapter_Pdo_MysqlTest)
Zend_Db_Statement_Exception: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound
/home/darby/framework/trunk/library/Zend/Db/Statement.php:279
/home/darby/framework/trunk/library/Zend/Db/Adapter/Abstract.php:396
/home/darby/framework/trunk/library/Zend/Db/Adapter/Pdo/Abstract.php:206
/home/darby/framework/trunk/library/Zend/Db/Adapter/Abstract.php:603

As you can see, the test only fails for the pdo_mysql adapter test, where it succeeds for both pdo_pgsql and pdo_sqlite. But the test passes when run for the pdo_mysql statement suite. Strange, indeed.

Again, on PHP 5.1.4 (WinXP), all the tests succeed (only testing pdo_mysql, though). I would much like to hear a report from you after you have had a chance to check it out.

This issue should have been fixed for the 1.5 release.

Please categorize/fix as needed.

A similar case has been reported with positional parameter placeholder ('?') and an escaped quote character. This appears to be a fault in PDO. See ZF-1343.

Should be fixed by the patch i attached to ZF-3025

Just did a check and this issue is indeed fixed by the patch i attached to ZF-3025

I think I should explain the bug (which is just a small typo) a little bit.

The problem is in Zend_Db_Statement::_stripQuoted , which should remove quoted parts of the SQL statement. The removing of parts in the 'identifier quotes' works fine, but the removing of parts in 'quotes' doesn't work correctly if there is an escaped quote. (was going to write a more detailed explanation, but if you look at the code and the patch it should be self explaining)

btw. the issue ZF-1343 is similar, but seems to be a PDO bug and isn't fixed by my patch

Sorry.. I was wrong... my patch doesn't fix this problem. Started to get inconsistent results.

This doesn't appear to have been fixed in 1.5.0. Please update if this is incorrect.

Reassigning as Ralph is the maintainer of Zend_Db

is there a PDO issue on bugs.php.net that confirms that this is a PDO and not a Zend_Db bug?

See http://bugs.php.net/bug.php?id=44251

This issue was reported against PDO in PHP 5.2.5. It is reproducible using PHP without Zend Framework. Actually the test case involves named parameter placeholders, not positional parameter placeholders. So it's more like the defects reported in ZF-2059 and ZF-3025. But I would assume the same defect in PDO applies to both types of parameter placeholders.

A comment in PHP bug 44251 claims that a fix is finally in CVS as of 2008-10-10, committed in branches for PHP 5.2, 5.3, and HEAD. This means it could be fixed in the recent PHP 5.2.8 release. Why don't you try testing it?

I am inclined to close this issue if it is indeed an issue with php itself, and has found resolution in a PHP CVS. Can someone confirm?

This is an issue with PHP that has been fixed as of 10/10/2008: http://bugs.php.net/bug.php?id=44251. Closing as 'Not an issue' since it is a PDO issue and not a Zend Framework issue.

,Wil