Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Bug in Zend and PDO. (solved)
 
ShopGuy
Guru
 
Total Posts:  462
Joined:  2008-09-07
 

I have come across a problem that I am sure affects more than just me. It is hard to trace, but I have figured out a couple things. I have searched google for hours trying to find the solution. I have found a lot of issues similar to mine with different character combinations (usually apostrophe, colon, and question mark)

I am getting the following error for perfectly valid queries:

Invalid parameter numberno parameters were bound

The problem is, the query is perfectly correct. In fact, I printed the query prior to the exception being thrown in Zend/Db/Statement/Pdo to make sure.

I did notice something though. Every query that caused this problem (most did not), had an apostrophe in it. So, for example the following will produce the error:

... where name 'men\'s clothing'; 

(please note, the above is properly escaped and properly quoted even if the formatted makes it appear as if it is not)

Even more perplexing though is that the error does not happen all the time. However, when it does happen there is always an apostrophe in the where clause of a select statement. Keep in mind, running the statement directly on the mysql server produces no errors.

So, my guess is that there is a bug in Zend or Pdo. My question is, How do I fix this issue?

I am running Mysql 5.1, PHP 5.2.6, and apache 1.3

 
Magento Community Magento Community
Magento Community
Magento Community
 
SeL_
Magento Team
 
Avatar
Total Posts:  1140
Joined:  2007-10-10
Paris, France
 

Hi,

Is that called from Magento code ? Which version ? v1.1.8 ?
Can you tell us what feature is implicated ?

I haven’t noticed that problem even if in french there are a lot of single quotes.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ShopGuy
Guru
 
Total Posts:  462
Joined:  2008-09-07
 

I am running Magento 1.1.6.

The code is being ran in a custom module. However, all the code is standard magento code. Here is the query:

Skip to next post. The below is irrelevant

$select $this->_getReadAdapter()->select()
                    ->
from($this->getMainTable())
                    ->
where($this->getMainTable().'.name = ?'$object->getName())
                ->
where($this->getMainTable().'.parent_id = ?', (int)$object->getParentId());

I am still testing. However, changing the code to not use paramters seems to fix the problem so far:

$select $this->_getReadAdapter()->select()
                    ->
from($this->getMainTable())
                    ->
where($this->getMainTable().'.name = "' $this->_getReadAdapter()->quote($object->getName()) . '"')
                ->
where($this->getMainTable().'.parent_id = ?', (int)$object->getParentId());

The above query is ran many, many times (3000+) in one program session and randomly gives the error. Without running the query many, many times it does not error. That is one reason why it has been really hard to figure out what is going on. Using quote() instead of the quoteInto() function that where(column, value) typically uses seems to fix it.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ShopGuy
Guru
 
Total Posts:  462
Joined:  2008-09-07
 

Okay, after much testing it has been determined that the above does not fix the problem. It was purely coincidence that the problem seemed to go away and both of the above cases make use of the same underlying process to generate queries.

After much debugging, it has been determined that the problem function is

Zend/Db/Statement.php: _parseParameters()

It will turn the following query into:

SELECT `_category`.* FROM `_categoryWHERE (_category.name 'men\'s apparel') AND (_category.parent_id = 2) AND (_category.category_id != '24')

And return this as parameter parts:

Array
(
    
[0] => SELECT .* FROM  WHERE (_category.name s apparel124')
)

Of course, the above is not valid. I am still trying to determine a fix.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ShopGuy
Guru
 
Total Posts:  462
Joined:  2008-09-07
 

-- snip—

 
Magento Community Magento Community
Magento Community
Magento Community
 
ShopGuy
Guru
 
Total Posts:  462
Joined:  2008-09-07
 

Solution

In the following file:

lib/Zend/Db/Statement.php

Replace the following:

protected function _stripQuoted($sql)
    
{
        
// get the character for delimited id quotes,
        // this is usually " but in MySQL is `
        
$d $this->_adapter->quoteIdentifier('a');
        
$d $d[0];

        
// get the value used as an escaped delimited id quote,
        // e.g. \" or "" or \`
        
$de $this->_adapter->quoteIdentifier($d);
        
$de substr($de12);
        
$de str_replace('\\''\\\\'$de);

        
// get the character for value quoting
        // this should be '
        
$q $this->_adapter->quote('a');
        
$q $q[0];

        
// get the value used as an escaped quote,
        // e.g. \' or ''
        
$qe $this->_adapter->quote($q);
        
$qe substr($q12);
        
$qe str_replace('\\''\\\\'$qe);

        
// get a version of the SQL statement with all quoted
        // values and delimited identifiers stripped out
        // remove "foo\"bar"
        
$sql preg_replace("/$d($de|[^$d])*$d/"''$sql);
        
// remove 'foo\'bar'
        
if (!empty($q)) {
            $sql 
preg_replace("/$q($qe|[^$q])*$q/"''$sql);
        
}

        
return $sql;
    
}

WITH

protected function _stripQuoted($sql)
    
{
        
// get the character for delimited id quotes,
        // this is usually " but in MySQL is `
        
$d $this->_adapter->quoteIdentifier('a');
        
$d $d[0];
          
        
// get the value used as an escaped delimited id quote,
        // e.g. \" or "" or \`
        
$de $this->_adapter->quoteIdentifier($d);
        
$de substr($de12);
        
$de str_replace('\\''\\\\'$de);
          
        
// get the character for value quoting
        // this should be '
        
$q $this->_adapter->quote('a');
        
$q $q[0];
        
        
// get the value used as an escaped quote,
        // e.g. \' or ''
        
$qe $this->_adapter->quote($q);
        
$qe substr($qe12);
        
$qe str_replace('\\''\\\\'$qe);

        
// get a version of the SQL statement with all quoted
        // values and delimited identifiers stripped out
        // remove "foo\"bar"
        
$sql preg_replace("/$q($qe|\\\\{2}|[^$q])*$q/"''$sql);
        
// remove 'foo\'bar'
        
if (!empty($q)) {
            $sql 
preg_replace("/$q($qe|[^$q])*$q/"''$sql);
        
}

        
return $sql;
    
}

In the original function the bad lines of code are the following:

$qe substr($q12);
...
$sql preg_replace("/$d($de|[^$d])*$d/"''$sql);
        
// remove 'foo\'bar'
        
if (!empty($q)) {
            $sql 
preg_replace("/$q($qe|[^$q])*$q/"''$sql);
        
}

The worst line is $qe = substr($q, 1, 2); it should be $qe = substr($qe, 1, 2);

These changes are reflected in the latest Zend Framework release.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ShopGuy
Guru
 
Total Posts:  462
Joined:  2008-09-07
 

Bug report created for the errors in _stripQuoted that was corrected in the latest zend framework release: http://www.magentocommerce.com/bug-tracking/issue?issue=4492

Still not 100% sure if my problem has been 100% fixed. I will need to test it more later.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ShopGuy
Guru
 
Total Posts:  462
Joined:  2008-09-07
 

Upgraded from PHP 5.2.6 to PHP 5.2.8. This took care of the PDO issue. All valid queries I have thrown at it are now being executed

 
Magento Community Magento Community
Magento Community
Magento Community
 
SeL_
Magento Team
 
Avatar
Total Posts:  1140
Joined:  2007-10-10
Paris, France
 

Hi ShopGuy,

This issue has been fixed in v1.2.0.
Please let us know if everything is fine now.
Thanks.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top