Zend_Db quoteInto() with multiple arguments

Posted on Thursday, May 21st, 2009 under ,

Zend Framework has some weird “features” so to say, which pop out from time to time and get the programmers really annoyed. The latest thing quoteInto() method of Zend_Db_Adapter_Abstract class. This is supposed to quote input variables prior to entering them into a SQL command to avoid SQL injection attacks. Sounds cool, doesn’t it? Well…not quite. Because, because it can only quote one argument.

You can’t write:

// note: I usually extend my models from the Zend_Db_Table_Abstract class
// this code should be executed in a model of a such model
$select = new Zend_Db_Select($this->_db);
$select->from($this->_name);
$select->where($this->_db->quoteInto('(a = ? AND b = ?) OR (c != ?)', $a, $b, $c);

…as this won’t work. Of course, there’s a really lame approach, to concatenate the output from multiple quoteInto() calls. Like such:

$select->where(
    $this->_db->quoteInto('(a =? AND ', $a) .
    $this->_db->quoteInfo('b = >)', $b) . 
    $this->_db->quoteInfo(' OR ( c != ?)', $c)
);

As I said, extremely lame. What’s to do then. Well, when I’ve hit this roadblock, I’ve opted for prepared statements. Of course, they brake the OOP encapsulation of SQL queries but if you don’t need to alter the query later it’s okay. So:

$stmt = $this->_db->prepare( 'SELECT * FROM ' . $this->_name . ' WHERE (a = ? AND b = ?) OR (c != ?)');
$stmt->execute(array($a, $b, $c));

This also works when you need to write custom sql queries:

$stmt = $this->_db->prepare('INSERT INTO ' . $this->_name . ' (first, second) VALUES (?, ?) ON DUPLICATE KEY UPDATE first = first + 1');
$stmt->execute(array($first, $second));

A decent workaround to the quoteInto() problem…

Related posts

6 Responses to “Zend_Db quoteInto() with multiple arguments”

  2. • Tudor •
    September 9, 2009

    You link doesn’t work…

  3. • Ben •
    September 24, 2009

    Looks like this is what he meant to link: http://codeaid.net/search/quoteinto

  4. • Tudor •
    September 24, 2009

    Yes, it seems so. But a better idea would be this:

    $values = array('value1', 'value2', 3, 'whatever');
 
$sql = 'INSERT INTO table (col1, col2, col3, col4) VALUES( ?, ?, ?, ?)';
 
foreach($values as $value) {
    $sql = $adapter->quoteInto($sql, $value);
}

    Not tested, but it should work!

  5. • Alex •
    April 30, 2010 
    $where = "(group_id = ? AND format_id = ? AND color_id = ?)";
$values = array($groupId, $formatId, $colorId);
 
foreach($values as $value) 
	$where = $this->_db->quoteInto($where, $value , '', 1);

    tested and works!

  6. • Tudor •
    April 30, 2010

    Nice! But I’m quite sure that it wasn’t working one year ago when the article was written :)

Leave a Reply

«
»