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

Issue Type:
Bug
Created:
2012-11-06T12:40:18.000+0000
Last Updated:
2013-04-05T16:07:11.000+0000
Status:
Closed
Fix version(s):
    Reporter:
    Milan Krupa (samius05@gmail.com)
    Assignee:
    Ralph Schindler (ralph)
    Tags:
    • Zend_Db
    Related issues:
    Attachments:

    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

    Posted by Rob Allen (rob) on 2012-11-06T19:53:31.000+0000

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

    ?

    Posted by Milan Krupa (samius05@gmail.com) on 2012-11-07T09:22:08.000+0000

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

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

    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/…

    Posted by Ralph Schindler (ralph) on 2013-04-05T16:07:11.000+0000

    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