What is the safest way to do database queries in Zend Framework?

9 Responses to What is the safest way to do database queries in Zend Framework?

1. Roman Nestertsov says:

   February 12, 2008 at 6:14 pm

   I prefer to use following thing:
   $sql = $db->quoteInto(“SELECT * FROM Table1 WHERE id = ?”, $target_id);

   Btw, source where you can read about quoting in Zend_Db: http://framework.zend.com/manual/en/zend.db.html#zend.db.adapter.quoting

   Reply
2. Wenbert says:

   February 12, 2008 at 6:58 pm

   Thanks Roman, appreciate the reply.
   just to clarify,
   $db = Zend_Registry::get(‘dbAdapter’);
   $sql = $db->quoteInto(”SELECT * FROM Table1 WHERE id = ?”, $target_id);
   is that correct?
   what about if i had 2 conditions in the WHERE part of the query? how would i do that using the quoteInto()?

   Reply
3. Roman Nestertsov says:

   February 12, 2008 at 8:28 pm

   If you need several conditions to quote you can use following schema:
   $sql = ‘SELECT * FROM Table1 WHERE id = ‘.$db->quote($target_id).’ AND param2 = ‘.$db->quote($param2);

   Read the link that i wrote above, all this things are described there

   Reply
4. Wenbert says:

   February 12, 2008 at 11:08 pm

   Awesome. Thanks a lot Roman

   Reply
5. Erik says:

   February 12, 2008 at 11:55 pm

   You can also build the query using Zend_Db_Select

   $select = $db->select()->
   from(‘Table1′, ‘*’)->
   where(‘id = ?’, $id)->
   where(‘url = ?’, $url);

   $row = $db->fetchRow($select);

   Reply
6. Wenbert says:

   February 13, 2008 at 12:06 am

   Ah nice!
   I have read about the query builder, but somehow, i never got the chance to use it because i couldn’t understand it on the ZF manual.

   It is time-consuming for me to make a long query and convert it using the query builder. I have always been comfortable with the more traditional sql queries – sadly i grew up with PhpMyAdmin and sometimes, i do queries with it – then copy-paste-edit the query.

   so a more complete example of the query builder would be something like this?

   $db = Zend_Registry::get(’dbAdapter’);

   $select = $db->select()->
   from(’Table1′, ‘*’)->
   where(’id = ?’, $id)->
   where(’url = ?’, $url);

   $row = $db->fetchRow($select);

   Thanks for all of your responses guys. Really appreciate all of them.

   Reply
7. Erik says:

   February 13, 2008 at 1:28 am

   > sadly i grew up with PhpMyAdmin and sometimes,
   > i do queries with it – then copy-paste-edit
   > the query.

   I do the same thing sometimes, only recently have I tried to convert my SQL queries to the query builder.

   > so a more complete example of the query
   > builder would be something like this?

   Yeah, that works fine in my tests.

   Reply
8. Roman Nestertsov says:

   February 13, 2008 at 2:03 am

   Query builder is a good solution if you a planning to use different databases. In this case Zend_Db class takes care about different notations in the SQL syntax depending on choosing database.

   Reply
9. Wenbert says:

   February 13, 2008 at 10:01 am

   > I do the same thing sometimes, only recently
   > have I tried to convert my SQL queries to the
   > query builder.

   hehe it’s good know i’m not alone

   > Zend_Db class takes care about different
   > notations in the SQL syntax depending on
   > choosing database

   What about if my queries had “variables” and “conditions” in the SELECT part.
   Like: @ekini:=COALESC(t.field1,t.field2)
   Would that be possible in the query? (I’m not sure if i got that right, and I’m not if it only is possible in MySQL.)

   I’m going to read more on the query builder to find out what limitations do I get compared to not using it.

   And I’m assuming that the query builder is slower? And is just safer than making queries yourself in case you need to switch databases?

   Reply