Parameterizing MySQL queries IN clause

Question

I am using Zend's convenience methods for DB calls fetchAll. This, as the knowledgeable ones should know is a function that allows parameterization of queries. For instance, the query could be:

$query = "select * from user where email = ?"
$results = $this->_db->fetchAll($query, $email);

And this is how a parameterization can be achieved.

My query however, is this:

 $query = "select * from user where email in ("noLuck@hotmail.com", "hotmailsucks@gmail.com","gmailrocks@hotmail.com");

How can I parameterize the above query, because those emails are user inputs so I am not going to simply have them in the raw query and trying the following failed:

$query = "select * from user where email in ? ";
$this->_db->fetchAll($query, $commaSeparatedEmailList);

where $commaSeparatedEmailList = "(".implode("," , $emails).")";

Any ideas ?

score 2 · Accepted Answer · answered May 02 '13 at 00:09

Unfortunately ZF is a little inconsistent about how it handles this. Some of the classes do work with array parameters, but unfortunately fetchAll() on the adapter class isn't one of them. There are two (slightly messy) options I'm aware of:

Switch to building the query with Zend_Db_Select instead (which does handle it correctly):

$select = $db->select();
$select->from('user')
       ->where('email IN (?)', $email);

$db->fetchAll($select);

or stick with fetchAll and use quoteInto:

$db->fetchAll("SELECT * FROM user WHERE ".$db->quoteInto('email IN (?)', $email));

Parameterizing MySQL queries IN clause

1 Answers1