Createcommand Does Not Work With Like Concat

Yii 1.1 General Discussion
1

Hi guys,

I have the following working code:




$sql = "SELECT * FROM ingredients

WHERE '". mysql_escape_string($recipe_ingredient) . "' LIKE CONCAT('%',name,'%')";


$ingredients= Yii::app()->db->createCommand($sql)->queryAll();

However, when I switch out mysql_escape_string with the yii bind params, it stops working. The result is empty. No errors display.




$sql = "SELECT * FROM ingredients

WHERE ':recipe_ingredient' LIKE CONCAT('%',name,'%')";


$ingredients= Yii::app()->db->createCommand($sql)->bindValue('recipe_ingredient', $recipe_ingredient)->queryAll();

Did I do anything wrong ?

2

Sorry divert to you nguyendh,

See this link

3

Identifiers like column- and tablenames cannot be bound to prepared statements by design. You might want to take a look at CDbConnection::quoteColumnName(), but the only really safe way to use dynamic column names is to create a whitelist of allowed names and use it to filter input.

4

Use this query:




// fill $columns array with column name.

// you can get the column's name from model:

$columns = Ingredients::model()->attributeNames();


// or set the array manually:

// $columns[] = 'id';

// $columns[] = 'name';

// so on


if(in_array($recipe_ingredient, $columns))

{

   $result = Yii::app()->db->createCommand()

                       ->from('ingredients')

                       ->where(array('like', $recipe_ingredient, '%name%'))

                       ->queryAll();

   $this->render('viewFile', array('result'=>$result));

}

else

   throw new CHttpException(404,'ERROR: Bad Request!');