My Zend Framework is quoting a mess

Question

I have a very simple problem to which I can not find a satisfactory (subjectively noticed) answer in the Zend Framework manual or elsewhere ...

There are so many ways I can pass my PHP variables to my sql queries that I lost in the review, and I probably lack understanding in general quoting.

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 do not need to bring anything, because db handles this for me.

API request for Zend_Db_Table and _Row objects

$ 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 in order, because it is not quoted at all. But what about other versions, which is better? C) is treated as an expression and is automatically quoted or do I need to use d) when do I use? Identifier?

Answer 1

Disclaimer:. This information is valid from the original publication date of this response. ZF changes frequently, this information may become outdated from future releases, however it will remain unchanged for archival purposes.

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

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

Not quoted at all.

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

Hand quoted as an integer.

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

Automatically quoted Zend_Db_Adapter_*::quoteInto()

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

Actually double quotes, once to you and once using automatic quoting.

As for the “best,” I would recommend option C. The structure will automatically call quoteInto on the parameterized value.

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

Again, in a subclass of Zend_Db_Table_Abstract , it will look like this:

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

The plus to this is that you can create much more complex queries, since you have control over much more than just a where clause of the SQL query. Theoretically, you can easily:

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

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

Answer 2

I am used to

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