Description

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')");

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">
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"
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 <http://framework.zend.com/issues/browse/ZF-5063> 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.