Createcommand Does Not Work With Like Concat

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 ?

Sorry divert to you nguyendh,

See this link

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.

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()


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


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



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