So, I was kind of pulling my hair out because I was running a query and the order criteria just didn’t seem to be working. I found out that using params in the order by clause of a pure SQL generated CDbCommand did not apply them. I was wondering if anyone could shine some insight onto why, and if it was meant for any reason. I allow users to choose the order on the page, so I wanted to use a param to prevent SQL attacks, but it doesn’t seem possible, or I’m doing something wrong. Here is my original code:
$sql = "SELECT concat(`player`.`first_name`,' ',`player`.`last_name`) as fullName, `player`.*, `projection`.* \n".
'FROM `player` '."\n".
'LEFT OUTER JOIN `projection` ON `player`.`id`=`projection`.`player_id` '."\n".
'WHERE `player`.`position` = :position'."\n".
'AND `player`.`active`=1'."\n".
'AND mvp_board_id = 0'."\n".
'AND fantasy_pts > 0'."\n".
'ORDER BY :order_field :order_direction';
$params = array(
':position'=>$this->position,
':order_field'=>$this->order_field,
':order_direction'=>$this->order_direction
);
$command = Yii::app()->db->createCommand($sql);
$command->params = $params;
$results = $command->queryAll();
Running this code, it was not applying the order. Even in the weblog at the bottom of the page it listed out the whole query and all of the bound parameters, so I assumed it was applying them properly. It wasn’t until I re-wrote my code to apply the order parameters directly into the query that I was getting the expected result. Here’s what worked:
$sql = "SELECT concat(`player`.`first_name`,' ',`player`.`last_name`) as fullName, `player`.*, `projection`.* \n".
'FROM `player` '."\n".
'LEFT OUTER JOIN `projection` ON `player`.`id`=`projection`.`player_id` '."\n".
'WHERE `player`.`position` = :position'."\n".
'AND `player`.`active`=1'."\n".
'AND mvp_board_id = 0'."\n".
'AND fantasy_pts > 0'."\n".
'ORDER BY '.$this->order_field.' '.$this->order_direction;
$params = array(
':position'=>$this->position,
);
$command = Yii::app()->db->createCommand($sql);
$command->params = $params;
$results = $command->queryAll();
So, any insights as to why binding parameters in the order clause isn’t allowed, or is this a bug?