Issues

ZF-12460: Invalid bind-variable name(mysqli)

Description

If there is an escaped newline ({{\\n}}), followed by datetime (and probably by anything containing colon) in the query, Zend_Db_Statement throws exception Invalid bind-variable name.

It is caused by {{Zend_Db_Statement::stripQuoted}}, which adjusts sql for example to


SELECT * FROM  WHERE (data LIKE 'foo\nbar2012-01-01 9:45:12')

instead of


SELECT * FROM  WHERE (data LIKE ) AND (datetime > )

Example of failing code:


$sql    = "SELECT * FROM `example` WHERE (data LIKE 'foo\\nbar') AND (datetime > '2012-01-01 9:45:12')";
$result = $db->fetchAll($sql);

And this is the example table:


CREATE TABLE `example` (
 `id` int(11),
 `data` text,
 `datetime` datetime
);

Comments

Does this work when you do:


$result = $db->fetchAll("SELECT * FROM `example` WHERE (`data` LIKE ?) AND (`datetime` > ?)", 'foo\\nbar', '2012-01-01 9:45:12');

?

Thanks, it works, and it could be a quick solution. actually, it should be:


$result = $db->fetchAll("SELECT * FROM `example` WHERE (`data` LIKE ?) AND (`datetime` > ?)", array('foo\\nbar', '2012-01-01 9:45:12'));

Just for the record, using param binding via {{Zend_Db_Select}} does NOT work again:


$select = $db->select()
             ->from('example')
             ->where('data LIKE ?', 'foo\\nbar')
             ->where('datetime > ?', '2012-01-01 9:45:12');
$db->fetchAll($select);

It's probably regression to ZF-3025.

I have the same problem on ZF 1.12.2. The following code will throw an error "Invalid bind-variable name ':2'"

$sQuery = $oDb->quote(serialize(array('a' => 1, 'b' => 2)));
$sQuery = 'INSERT INTO test VALUES('. $sQuery .')';

$oDb->query($sQuery);

Please see last comment: http://framework.zend.com/issues/browse/…

This issue has been closed on Jira and moved to GitHub for issue tracking. To continue following the resolution of this issues, please visit: https://github.com/zendframework/zf1/issues/30