Zend Framework

Invalid bind-variable position when colon used in quoted parameter (using Mysqli)

Details

  • Type: Patch Patch
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 1.5.1
  • Fix Version/s: None
  • Component/s: Zend_Db

Description

$db->query("SELECT `foo`.`bar` FROM `xxx` WHERE (zzz = 'a\'b+c:d')");

This query was build using Db_Select
single quote was properly escaped and passed to fetch method where it failed

The message is: 'Invalid bind-variable position ':d'

From what I can tell the :d was mistaken for named parameter
it only happens if the single quote was present in the same parameter
after removing it the query is processed correctly
$db->query("SELECT `foo`.`bar` FROM `xxx` WHERE (zzz = 'ab+c:d')");

using Mysqli adapter

Issue Links

This issue is related to:
ZF-5063 Segmentaion fault on preg_replace in Zend_Db_Statement Major Resolved
ZF-7911 Zend_Db_Statement::_stripQuoted seems not to be complete Major Resolved
ZF-2059 ":0'" in a String Results in Exception Critical Closed

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Karol Grecki added a comment -

Another example

$db->query("REPLACE INTO foo (aaa) VALUES (('a\'s aa '),('rv:1.8.1.11'))");

escaped single quote in first parameter confuses the code
'Zend_Db_Statement_Exception' with message 'Invalid bind-variable position ':1'

Show
Karol Grecki added a comment - Another example 
$db->query("REPLACE INTO foo (aaa) VALUES (('a\'s aa '),('rv:1.8.1.11'))");
escaped single quote in first parameter confuses the code 'Zend_Db_Statement_Exception' with message 'Invalid bind-variable position ':1'
Hide
Permalink
Karol Grecki added a comment -

One more example with zend select

$db->select()->from('foo', 'bar')->where('xxx = ?', "as'as:x")->query();

Invalid bind-variable position ':x'

Show
Karol Grecki added a comment - One more example with zend select 
$db->select()->from('foo', 'bar')->where('xxx = ?', "as'as:x")->query();
Invalid bind-variable position ':x'
Hide
Permalink
Ota Mares added a comment -

The same happens when a qurestion mark is used as value. 

$db->select()->from('foo', 'bar')->where('xxx = ?', "__'?__")->query();

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

I think this is not fixable because of the nature of pdo querys. A workaround is to use parametrized querys instead of the zend_db quoting features.

Show
Ota Mares added a comment - The same happens when a qurestion mark is used as value. 
$db->select()->from('foo', 'bar')->where('xxx = ?', "__'?__")->query();
Result: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound I think this is not fixable because of the nature of pdo querys. A workaround is to use parametrized querys instead of the zend_db quoting features.
Hide
Permalink
Karol Grecki added a comment -

The same query works fine with plain mysqli, I believe the problem lies in ZF code where placeholders are parsed

Show
Karol Grecki added a comment - The same query works fine with plain mysqli, I believe the problem lies in ZF code where placeholders are parsed
Hide
Permalink
Sven Herrmann added a comment -

Problem is caused by a typo in DB/Statement.php

Attached Patch to fix this

Show
Sven Herrmann added a comment - Problem is caused by a typo in DB/Statement.php Attached Patch to fix this
Hide
Permalink
Wil Sinclair added a comment -

Please evaluate and categorize as necessary.

Show
Wil Sinclair added a comment - Please evaluate and categorize as necessary.
Hide
Permalink
Karol Grecki added a comment -

Sven's patch seems to fix the problem, can it make its way into trunk please?

Show
Karol Grecki added a comment - Sven's patch seems to fix the problem, can it make its way into trunk please?
Hide
Permalink
Karol Grecki added a comment -

I found another case when this happens and attached patch doesn't help this time.

A multi insert query with escaped quote and backslash (it's fine with just a quote):

REPLACE INTO foo VALUES 
('rv:1.8.1.2) \'\\'), 
('rv:1.8.1.7) ')

I put debug code around $sql = $this->_stripQuoted($sql); in Zend_Db_Statement::_parseParameters($sql) and it looks like that function fails to remove those quoted elements, instead the whole first row of values is gone and the second one is intact so later it triggers an exception because it contains ":1"

Show
Karol Grecki added a comment - I found another case when this happens and attached patch doesn't help this time. A multi insert query with escaped quote and backslash (it's fine with just a quote): 
REPLACE INTO foo VALUES 
('rv:1.8.1.2) \'\\'), 
('rv:1.8.1.7) ')
I put debug code around $sql = $this->_stripQuoted($sql); in Zend_Db_Statement::_parseParameters($sql) and it looks like that function fails to remove those quoted elements, instead the whole first row of values is gone and the second one is intact so later it triggers an exception because it contains ":1"
Hide
Permalink
Karol Grecki added a comment -

Ok, the problem is the ending
- escaped backlash was preventing this regex "/'(
'|[^'])*'/" from matching ending quote
I modified the expression to ignore matching backslashes : "/'(\\'|
{2}|[^'])*'/"





Index: library/Zend/Db/Statement.php

===================================================================

— .	(revision 9404)

+++ .	(working copy)

@@ -187,7 +187,7 @@

         $sql = preg_replace("/$d($de|[^$d])*$d/", '', $sql);

         // remove 'foo\'bar'

         if (!empty($q)) {

-            $sql = preg_replace("/$q($qe|[^$q])*$q/", '', $sql);

+            $sql = preg_replace("/$q($qe|\\\\{2}|[^$q])*$q/", '', $sql);

         }



         return $sql;









Maybe someone can verify and commit it
 

    

    

        Show
        

            
    
    
    
                

    Karol Grecki
 added a comment  -                Ok, the problem is the ending 
  - escaped backlash was preventing this regex "/'(
'|[^'])*'/" from matching ending quote
I modified the expression to ignore matching backslashes : "/'(\\'|
{2}|[^'])*'/"







Index: library/Zend/Db/Statement.php
===================================================================
— .	(revision 9404)
+++ .	(working copy)
@@ -187,7 +187,7 @@
         $sql = preg_replace("/$d($de|[^$d])*$d/", '', $sql);
         // remove 'foo\'bar'
         if (!empty($q)) {
-            $sql = preg_replace("/$q($qe|[^$q])*$q/", '', $sql);
+            $sql = preg_replace("/$q($qe|\\\\{2}|[^$q])*$q/", '', $sql);
         }

         return $sql;







Maybe someone can verify and commit it         

    



                     

    

        Hide
        

            Permalink
                                

        

    
    
    
                

    Simon Mundy
 added a comment  -   

        
Resolved in trunk r9727
 

    

    

        Show
        

            
    
    
    
                

    Simon Mundy
 added a comment  -                Resolved in trunk r9727         

    



                     

    

        Hide
        

            Permalink
                                

        

    
    
    
                

    Vincent de Lau
 added a comment  -   

        
It seems that this issue is not fixed properly. The patch proposed by Karol Grecki was not applied correctly. Instead of the second preg_replace, the first one was replaced.



I've added a patch to http://framework.zend.com/issues/browse/ZF-5063 that will put back (a modified version of) the original replacement.
 

    

    

        Show
        

            
    
    
    
                

    Vincent de Lau
 added a comment  -                It seems that this issue is not fixed properly. The patch proposed by Karol Grecki was not applied correctly. Instead of the second preg_replace, the first one was replaced.

I've added a patch to http://framework.zend.com/issues/browse/ZF-5063 that will put back (a modified version of) the original replacement.         

    



                     

    

        Hide
        

            Permalink
                                

        

    
    
    
                

    Adam Lundrigan
 added a comment  -   

        
Is this still an issue?
 

    

    

        Show
        

            
    
    
    
                

    Adam Lundrigan
 added a comment  -                Is this still an issue?         

    



                 



                

            

        


        

            

                
                    
People



                Vote
    
                (1)
    



            Watch
    
        
            (5)
    


Dates
    
    
  • 
            
    
            
    Created:
    
            
    
                        
                         
        
    
            
    
            
    Updated:
    
            
    
                        
                         
        
    
            
    
            
    Resolved:
    
            
    
                        
                         
        
    
        
    • 




                

            

        

        
            
                
            
        

    

















        Atlassian JIRA
        (v4.4.3#663-r165197)
        | Report a problem
    

            

                    Powered by a free Atlassian JIRA open source license for Zend Framework. Try JIRA - bug tracking software for your team.