How to bind array parameters in YII framework?

Question

I have below case of php:

$inputs = "1,2,3,4,5";
$sql = "SELECT * FROM obj WHERE id IN(:input)";

I used yii provide db function:

$commond = Yii::app()->db->createCommand($sql);
$commond->bindValue(":input", $inputs , PDO::PARAM_STR);

but the query result in-correct,so if this how can i do ?

Answer 1

Having come across this problem a few times in my projects I have come-up with the following Yii work-around using CDbCriteria which is a little hacky, but gives the security of param count matching.

When applied to your example my code would be:

$inputs = array(1,2,3,4,5);
$criteria = new CDbCriteria();
$criteria->addInCondition('id',$inputs);

$sql = 'SELECT * FROM obj WHERE '.$criteria->condition;
$command = Yii::app()->db->createCommand($sql);
$results = $command->queryAll(true, $criteria->params);

UPDATE

There is actually a much cleaner way to do this built into Yii:

$results = Yii::app()->db->createCommand()
   ->select()
   ->from('obj')
   ->where(['in', 'id', $inputs])
   ->queryAll();

See Docs

Answer 2

for now use it like this

$command = Yii::app()->db->createCommand()
    ->select()
    ->from('tableName')
    ->where(array('in', 'id', explode(',', $inputs)));

I ll try to get back with $command->bindValue() method.

Answer 3

Using Yii's method chaining in CDbCommand to build your query (as in Uday Sawant's answer) is generally a good choice. If having to construct the query piecemeal is not ideal, a good alternative is to flatten your array of parameters so you don't bypass SQL injection protection, like so:

$sql = "SELECT * FROM obj WHERE id IN (:id_array) AND other_field = :other_value";
$args = array(
  'id_array' => array(1, 2, 3, 4, 5),
  'other_value' => 12,
);

// Flatten array arguments into multiple parameters,
// replacing with parameter lists in the SQL
$newArgs = array();
$replace = array();
foreach($args as $oldKey => $input) {
  if(!is_array($input)) {
    $newArgs[$oldKey] = $args[$oldKey];
    continue;
  }

  $replace[':'.$oldKey] = array();
  foreach($input as $i => $value) {
    $replace[':'.$oldKey][] = ':'.$oldKey.$i;
    $newArgs[$oldKey.$i] = $value;
  }
  $replace[':'.$oldKey] = implode(', ', $replace[':'.$oldKey]);
}
$sql = strtr($sql, $replace);

$query = Yii::app()->db->createCommand($sql);
$query->params = $newArgs;
$query->queryAll();

In this example, the final sql and arguments are:

SELECT * FROM obj WHERE id IN (:id_array0, :id_array1, :id_array2, :id_array3, :id_array4) AND other_field = :other_value
array(
  'id_array0' => 1,
  'id_array1' => 2,
  'id_array2' => 3,
  'id_array3' => 4,
  'id_array4' => 5,
  'other_value' => 12,
)

In projects where using raw SQL is the preferred standard, the biggest benefit is you can bundle this up as a utility function and reuse it for any query. It's a shame Yii doesn't automatically expand array arguments this way, but you can also add this support yourself to projects which directly use PDO.