Correct way to bind parameters using MySQL "IN" syntax in Yii2?

Question

Ok, I'm using Yii2 and I'm familiar with preparing/binding data when using mysql queries, such as:

$sql = $this->db->createCommand("UPDATE some_table SET something='foo' WHERE some_id=:some_id");
$sql->bindValue(':some_id', $some_id);

But what about when the value may contain multiple values, such as when using the MySQL syntax IN?

For example:

$sql = $this->db->createCommand("UPDATE some_table SET something='foo' WHERE some_id IN (:parents)");
$sql->bindValue(':parents', $parents);

Now as I understand the above would only work well if the $parents var only had one value; but if it had multiple values such as 1,2,3 then you would end up with something like '1,2,3' when you really want '1','2','3' OR 1,2,3.

What is the correct way to do this?

@saty instead of giving advice on topics you don't know about, do read the manual before you type. — N.B.
– N.B., Commented May 2, 2015 at 10:05
possible duplicate of Can I bind an array to an IN() condition? — N.B.
– N.B., Commented May 2, 2015 at 10:07

Rjazhenka · Accepted Answer · 2016-05-25 12:32:54Z

9

I've found this solution

$params = [];
$sql = \Yii::$app->db->getQueryBuilder()->buildCondition(['IN', 'some_id', $ids], $params);
//$sql = some_id NOT IN (:qp0, :qp1, :qp2)
//$params = [':qp0'=>1, ':qp1'=>2, ':qp2'=>3]
$this->db->createCommand("UPDATE some_table SET something='foo' WHERE $sql", $params);

answered May 25, 2016 at 12:32

Rjazhenka

1,5181 gold badge11 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oleg Over a year ago

Deprecated since 2.0.14. Is not used, will be dropped in 2.1.0. yiiframework.com/doc/api/2.0/…

laszlovl · Accepted Answer · 2015-05-05 21:10:27Z

8

You can just use Yii's QueryBuilder functions and everything will be handled automatically. Try this:

$params = [];
$sql = \Yii::$app->db->getQueryBuilder()->update('some_table', ['something' => 'foo'], ['some_id' => [1, 2, 3]], $params);

The result:

string(78) "UPDATE `some_table` SET `something`=:qp0 WHERE `some_id` IN (:qp1, :qp2, :qp3)"
array(4) { [":qp0"]=> string(3) "foo" [":qp1"]=> int(1) [":qp2"]=> int(2) [":qp3"]=> int(3) }

answered May 5, 2015 at 21:10

laszlovl

4912 silver badges6 bronze badges

2 Comments

Rjazhenka Over a year ago

What if I need to implement criteria like some_id NOT IN (1, 2, 3) AND some_field = 2

Anton Rybalko Over a year ago

@Rjazhenka something like ['and', ['not', ['some_id' => [1, 2, 3]]], ['some_field' => 2]]. See specification yiiframework.com/doc-2.0/yii-db-query.html#where()-detail

Community · Accepted Answer · 2017-05-23 10:31:00Z

4

Yii2's DB functions are based on PDO. According to the manual of bindValue there's no support of value from Array type. (Third parameter - data_type).

The solution is to create a string prior to the query, which fits to your IN clause and bind it as a string.

Something like:

$parents = "1,2,3";
/*
Or in case you already have an array of the desirable ids:
$parents_array = array(1,2,3);
$parents = implode(",",$parents_array);
*/

$sql = $this->db->createCommand("UPDATE some_table SET something='foo' WHERE some_id IN (:parents)");
$sql->bindValue(':parents', $parents);

Edit

It seems that the placeholder being replaced by the imploded array as a one string value '1,2,3' instead of '1','2','3' (since it's a single placeholder).

In order to solve this issue, i'll suggest using the multiple ? placeholders. So instead of having IN (:parents) you would have IN (?, ?, ?, ? ,....), and since we already have an arranged array - we can use count($array) to know how much placeholders we need to put.

//$parents = array(1,2,3);
$placeholders = str_repeat('?,', count($parents) - 1). '?';
$sql = $this->db->createCommand("UPDATE some_table SET something='foo' WHERE some_id IN (".$placeholders.")");

foreach($parents as $i => $parent){
 $sql->bindValue($i+1, $parent);  
}

Please notice the passed value of the first parameter of bindValue; The reason it's $i+1 and not $i mentioned in the manual:

For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.

For further information and alternative solutions, look at the following answer: https://stackoverflow.com/a/920523/998096

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered May 2, 2015 at 9:27

Ofir Baruch

10.3k2 gold badges29 silver badges39 bronze badges

6 Comments

Brett Over a year ago

Wouldn't that just end up with the value being tried as '1,2,3'?

Abdul Rehman Over a year ago

@Brett it will be 1,2,3 not '1,2,3' and the query will work

Brett Over a year ago

@Bsienn Doesn't binding with bindValue cause the value to be quoted though?

Brett Over a year ago

@OfirBaruch Well, I guess this would solve the issue with trusted data, but it wouldn't really be advisable to use this method if using user provided data within the IN; I guess I was more looking for a more robust solution and not just one for this particular situation.

Brett Over a year ago

Well I just tested and I was right, using this method ended up with '1,2,3' and didn't work correctly.

|

Alliswell · Accepted Answer · 2018-01-22 11:07:49Z

1

If data is TRUSTED, this one solution works pretty good:

$db = Yii::$app->db;
$ids = "'" . implode("','", $ids_array) . "'";
$result = $db->createCommand("
    UPDATE some_table SET something='foo' WHERE some_id IN ($ids)
")->queryColumn();

answered Jan 22, 2018 at 11:07

Alliswell

1,59320 silver badges35 bronze badges

2 Comments

Nebulosar Over a year ago

I agree to just use the raw data if you just recovered it from a trusted location.

rob006 Over a year ago

It will not work if data contains ' or `` at the end of string. I would not use this for anything except integers.

Brett · Accepted Answer · 2015-05-03 09:15:57Z

0

I ended up doing it like this:

$parents_safe = '';
$parents_sep = explode(',', $parents);

foreach ($parents_sep as $parent) {
    $parents_safe .= $this->db->quoteValue($parent) . ',';
}

$parents_safe = rtrim($parents_safe, ',');

Where $this->db is an instance of Yii::$app->db.

answered May 3, 2015 at 9:15

Brett

20.3k58 gold badges170 silver badges307 bronze badges

Comments

Modder · Accepted Answer · 2019-03-20 08:45:34Z

0

If someone needs a solution for Yii1:

$db = \Yii::app()->db;

$inCondition = $db
    ->getCommandBuilder()
    ->createInCondition('some_table', 'some_id', $parents);

$command = $db
    ->createCommand("UPDATE some_table SET something = :foo WHERE $inCondition");

$command->bindValue(':foo', 'foo');
$command->execute();

answered Mar 20, 2019 at 8:45

Modder

92311 silver badges21 bronze badges

Comments

shyyawn · Accepted Answer · 2015-05-03 08:55:17Z

-5

Just do

$parents = implode("','", $parrent_array);
$sql->bindValue(':parents', $parents);

Should probably do the trick.

answered May 3, 2015 at 8:55

shyyawn

12 bronze badges

2 Comments

Brett Over a year ago

For that to work it would rely on the values being like '1','2','3'.

Radon8472 Over a year ago

Thats insecure imagine somebody passes e.g. "don't panik" in $parant_array, this would crash your query and opens the door for sql injections

Collectives™ on Stack Overflow

Correct way to bind parameters using MySQL "IN" syntax in Yii2?

7 Answers 7

1 Comment

2 Comments

6 Comments

2 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

2 Comments

6 Comments

2 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related