My Zend Framework 'quoting' mess.

Question

I've got a probably very simple issue to which I can't find a satisfactory (subjectively seen) answer in the Zend Framework manual or elsewhere...

There are so many ways how I can hand over my php variables to my sql queries that I lost the overview and probably I lack some understanding about quoting in general.

Prepared Statements

$sql =  "SELECT this, that
        FROM table
        WHERE id = ? AND restriction = ?";

$stmt = $this->_db->query($sql, array($myId, $myValue)); 
$result = $stmt->fetchAll();

I understand that with this solution I don't need to quote anything because the db handles this for me.

Querying Zend_Db_Table and _Row objects over the API

$users = new Users();

a) $users->fetchRow('userID = ' . $userID);  
b) $users->fetchRow('userID = ' . $users->getAdapter()->quote($userID, 'INTEGER'));  
c) $users->fetchRow('userID = ?', $userID);  
d) $users->fetchRow('userID = ?', $users->getAdapter()->quote($userID, 'INTEGER'));

Questions

I understand that a) is not ok because it's not quoted at all. But what about the other versions, what's the best? Is c) being treated like a statement and automatically quoted or do I need to use d) when I use the ? identifier?

Answer 1

Disclaimer: This information is valid as of the original posting date of this answer. ZF changes often, this information may become outdated with future releases, however, this will remain unchanged for archival purposes.

If you pass a string to the fetchRow() method of a subclass of Zend_Db_Table_Abstract (which you are doing), it will be treated as a where part of a Zend_Db_Table_Select instance.

In other words, internally, Zend_Db_Table does this:

if (!($where instanceof Zend_Db_Table_Select)) {
    $select = $this->select();

    if ($where !== null) {
        $this->_where($select, $where);
    }

So...:

a) $users->fetchRow('userID = ' . $userID);  

Is not quoted at all.

b) $users->fetchRow('userID = ' . $users->getAdapter()->quote($userID, 'INTEGER'));  

Is manually quoted as an integer.

c) $users->fetchRow('userID = ?', $userID);  

Is automatically quoted by Zend_Db_Adapter_*::quoteInto()

d) $users->fetchRow('userID = ?', $users->getAdapter()->quote($userID, 'INTEGER'));

Is actually double quoted, once by you, and once via the automatic quoting.

As far as "best" is concerned, I would recommend option C. The framework will automatically call quoteInto on the parameterized value.

Keep in mind: You could always pass an instance of Zend_Db_Table_Select or Zend_Db_Select to the fetchRow() method instead...

Again, in a subclass of Zend_Db_Table_Abstract, that would look like this:

$this->fetchRow($this->select()->where('userID = ?', $userID));

The plus of doing this, is that you can construct much more complex queries, as you have control over much, much more than just the WHERE clause of the SQL query. In theory, you could easily do:

$select = $this->select()->where('userID = ?', $userID)
                         ->join(array('sat' => 'superAwesomeTable'), array('sat.user_id = userID', array('superAwesomeColumn'));

$this->fetchRow($select);

Note: If passed an instance of Zend_Db_Select, the fetchRow() method acts exactly like fetchAll() except it internally calls the limit() method of the select object, with a parameter of 1.

Answer 2

I got used to

$where = $this->getAdapter()->quoteInto('name = ?', $name);
$this->fetchRow($where);