QuoteInto to with multiple parameters

dele454 · #1 (**permalink**) 08-11-2008, 11:44 PM

hi

I need to query the database with 2 parameters from a URL :

http://mainevent.com/admin/galleries...d/1/type/venue

Code:

public function getGalleryByIdType($requestType="", $requestID="")
                {
                               if($requestType ="event'){
                               
                                  $db        = Zend_Registry::get('db');
                                   $sql       = $db->quoteInto("SELECT g.GalleryName, e.EventName
                                                   FROM Galleries  g  
                                                   LEFT JOIN EventGallery eg ON eg.GalleryID = g.GalleryID
                                                   LEFT JOIN Event e On e.EventID = eg.EventID
                                                   WHERE g.Type=? AND g.GalleryID=?" , $requestType,$requestID);
                       
                                                                                         
                                  $query     = $db->query($sql);
                               
                                  $results   = $query->fetchAll();
                                 
                                  return $results;
                            }
               
               
                }

It seems the quoteInto function only accepts a single parameter. It returns an empty array with no results. And the function doesnt support supplying parameters as an array of values. I came across this article:

Modifying Zend_Db_Adapter_Abstract::quoteInto to accept multiple question marks at Amikelive | Technology Blog

And this guy recommends modifying the function definition. I just want to know if this is the appropriate route to solving this. Thanks

Tekerson · #2 (**permalink**) 08-12-2008, 01:59 AM

The other option is to use bound parameters by passing the second parameter to query(). So you don't even need quoteInto(). Like this:

Code:

...
$sql       = 'SELECT g.GalleryName, e.EventName
                ...
                WHERE g.Type = :gType AND g.GalleryID = :gGalleryId';
                
                $query     = $db->query($sql, array(
                    'gType' => $gType,
                    'gGalleryId' => $gGalleryId,
                ));

You can have as many bound parameters as you want, unfortunately you can't use the same one multiple times in the query (unless that's changed since last I looked).

dele454 · #3 (**permalink**) 08-12-2008, 05:24 AM

Quote:

Originally Posted by Tekerson

The other option is to use bound parameters by passing the second parameter to query(). So you don't even need quoteInto(). Like this:

Code:

...
$sql       = 'SELECT g.GalleryName, e.EventName
                ...
                WHERE g.Type = :gType AND g.GalleryID = :gGalleryId';
                
                $query     = $db->query($sql, array(
                    'gType' => $gType,
                    'gGalleryId' => $gGalleryId,
                ));

You can have as many bound parameters as you want, unfortunately you can't use the same one multiple times in the query (unless that's changed since last I looked).

Thanks but then i am not quoting any of those values! Doesnt that open me to sql injection attacks?

Tekerson · #4 (**permalink**) 08-12-2008, 06:12 AM

Nope, the bound parameters are inserted by creating a prepared statement and executing it (or a facsimile of, if prepared statements are not available in your database/adapter).

serbanghita · #5 (**permalink**) 10-30-2008, 09:53 AM

Tekerson - i have a question related to what you said

PHP Code:


			
$arr = array('some_id' => (int)$some_id, 'name' => $name, 'name2' => $name2);

$this->db->update('table', $arr, 'id ='.(int)$id);

I am only doing trim() on $name and $name2, is this query safe? How should i escape the vars? Is Zend_Db doing it automagically?

Thanks!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode