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

Issue Type: Patch Created: 2008-04-02T11:15:16.000+0000 Last Updated: 2013-03-04T10:22:09.000+0000 Status: Resolved Fix version(s): Reporter: Karol Grecki (antych) Assignee: Simon Mundy (peptolab) Tags: - Zend_Db

  • zf-caretaker-adamlundrigan

Related issues: - ZF-2059

Attachments: - statement.diff


$db->query("SELECT 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 FROM xxx WHERE (zzz = 'ab+c:d')");

using Mysqli adapter


Posted by Karol Grecki (antych) on 2008-04-03T03:28:43.000+0000

Another example

<pre class="highlight">
$db->query("REPLACE INTO foo (aaa) VALUES (('a\'s aa '),('rv:'))");

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

Posted by Karol Grecki (antych) on 2008-04-03T03:42:12.000+0000

One more example with zend select

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

Invalid bind-variable position ':x'

Posted by Ota Mares (ota) on 2008-04-03T03:57:58.000+0000

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

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.



Posted by Karol Grecki (antych) on 2008-04-03T04:33:39.000+0000

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



Posted by Sven Herrmann (sherrmann) on 2008-04-09T12:55:54.000+0000

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

Attached Patch to fix this



Posted by Wil Sinclair (wil) on 2008-04-18T13:20:27.000+0000

Please evaluate and categorize as necessary.



Posted by Karol Grecki (antych) on 2008-05-02T06:24:33.000+0000

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



Posted by Karol Grecki (antych) on 2008-05-09T04:29:12.000+0000

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):

    <pre class="highlight">
    ('rv: \'\\'), 
    ('rv: ')

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"



Posted by Karol Grecki (antych) on 2008-05-09T07:50:46.000+0000

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}|[^'])_'/"

    <pre class="highlight">
    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



Posted by Simon Mundy (peptolab) on 2008-06-19T05:22:03.000+0000

Resolved in trunk r9727



Posted by Vincent de Lau (vdelau) on 2009-09-21T08:47:14.000+0000

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 <> that will put back (a modified version of) the original replacement.



Posted by Adam Lundrigan (adamlundrigan) on 2011-10-05T14:38:38.000+0000

Is this still an issue?



Posted by Dominik Blunk (dblunk) on 2012-07-09T14:46:03.000+0000

I think this issue is still unresolved (tested with 1.12.0rc1). The following code will throw an error "Invalid bind-variable name ':2'"

    <pre class="literal">
    $data = $dbAdapter->quote(serialize(array('alpha', 'omega')));
    $sql = sprintf("insert into `import` (field_value) VALUES (%s)", $data);
    $results = $dbAdapter->query($sql);

When removing the quoted " the query works:

    <pre class="literal">
    $data = $dbAdapter->quote(serialize(array('alpha', 'omega')));
    $data = str_replace('\"', '"', $data);
    $sql = sprintf("insert into `import` (field_value) VALUES (%s)", $data);
    $results = $dbAdapter->query($sql);



Posted by Mateusz Juściński (luinnar) on 2013-03-04T10:22:09.000+0000

Please reopen this issue. I have the same problem with serialized data on ZF 1.12.2.



Have you found an issue?

See the Overview section for more details.


© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.