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'

One more example with zend select

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

Invalid bind-variable position ':x'

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.

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

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

Attached Patch to fix this

Please evaluate and categorize as necessary.

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

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"

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

Resolved in trunk r9727

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.

Is this still an issue?

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

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

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

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