PDO - inline SQL or Stored Procedure

Question

I was using http://www.yiiframework.com/doc/guide/1.1/en/database.dao

Data Access Objects (DAO) uses PDO.

Was unsure which was best

Option One - Inline Code

    $alias_name =Utils::checkEnteredData($alias_name);
    $connection = Yii::app()->db;       
    $sql_images = $connection->createCommand("SELECT ig.id, 
        ig.is_flickr,                     
        ig.main_image,
        ig.author_id,
        ig.description,
        ig.latitude, 
        ig.longitude,
        ig.is_youtube, 
        ig.author_id,       
        ig.flickr_youtube_id, 
        ig.thumbnail_image_medium, 
        ig.thumbnail_image, 
        ig.alias_title,
                    ig.image_hits, 
                    ig.title, 
                    ig.created_on,
        a.title,
        a.alias_title as album_alias_title,
        igo.realname, 
        igo.username,
                    igo.location from image_gallery ig 
                    JOIN album a ON ig.album_id = a.id 
                    LEFT JOIN image_gallery_owner igo ON ig.author_id = igo.id  
                    WHERE ig.approved =:approved AND ig.visible=:visible AND ig.alias_title =:alias_title LIMIT 1");
    $sql_images->bindValues(array(':alias_title'=>$alias_name, ':approved'=>'Yes', ':visible'=>'1'));               
    $image = $sql_images->queryAll();

Option 2 Stored Procedure:

  public function getImageFromAliasTitle($alias_title)  
        {     
            $alias_title =Utils::checkEnteredData($alias_title);  
            $connection = Yii::app()->db;   
            $command = $connection->createCommand("CALL getSingleImage(:alias_title)");   
            $command->bindParam(":alias_title",$alias_title,PDO::PARAM_STR);  
            $image = $command->queryAll();  
            return $image;  
        }

score 0 · Accepted Answer · edited May 23 '17 at 12:18

It depends, but I'd still recommend on using stored procedure for your case. The major reasons being(Taken from a previous answer):

Stored procedure have the of benefits of no need to grant users direct table access.
Since each non-parametrized query is a new, different query to SQL Server, it has to go through all the steps of determining the execution plan, for each query (thus wasting time - and also wasting plan cache space, since storing the execution plan into plan cache doesn't really help in the end, since that particular query will probably not be executed again)
Non-parametrized queries are at risk of SQL injection attack and should be avoided at all costs.

This is an interesting read too.

PDO - inline SQL or Stored Procedure

1 Answers1