Model::find('col IN ()') with bind

Question

Model::find('col IN ()') with bind

Karolis Mačiulskis Sep '14

Created Sep '14	Last Reply May '17	Replies 17	Views 9416	Votes 0

Karolis Mačiulskis 26.3k

Sep '14

Hello,

I have strange issue. I'll start from example:

$collection = Model::find(array(
                'id IN (:ids:)',
                'bind' => array('ids' => '15,17,18')
            ));
var_dump(count($collection));

int 1

$collection = Model::find(array(
                'id IN (15,17,18)'
            ));
var_dump(count($collection));

int 3

$collection = Model::find(array(
                'id IN (:ids:)'
                'bind' => array('ids' => array(15,17,18))
            ));
var_dump(count($collection));

Notice: Array to string conversion in...........
int 0

How to properly bind WHERE col IN () arguments?

Phalcon
98.9k

Accepted
answer

edited Sep '14
Sep '14

Check this:

https://forum.phalcon.io/discussion/2159/phql-binding-array-with-the-in-operator

You can't bind an array as a PDO parameter, making this:

$collection = Model::find(array(
                'id IN (:ids:)'
                'bind' => array('ids' => array(15,17,18))
));

Means this:

$collection = Model::find(array(
                'id IN ("15")'
));

You have to create a placeholder for each parameter:

$collection = Model::find(array(
                'id IN (?0, ?1, ?2)'
                'bind' => array(15,17,18)
));

Karolis Mačiulskis · Answer 1 · 2014-09-17T03:52:47-07:00

Has phalcon plans to make easier to bind a lot of params (auto count arguments)? Binding idea is to prevent sql injection, but if i parse my array manualy, then I don't need to bind.

Phalcon · Answer 2 · 2014-09-17T03:55:59-07:00

No, we don't have plans to add it, but if someone wants to implement it in Phalcon 2 it would be great.

Update: it's supported in Phalcon 2.0.4: https://blog.phalcon.io/post/phalcon-2-0-4-released

Karolis Mačiulskis · Answer 3 · 2014-09-17T05:27:33-07:00

So I created a function to make possible to pass arrays in bind.

class Model extends \Phalcon\Mvc\Model
{
    public static function find($parameters = NULL)
    {
        return parent::find(self::_prepareBinds($parameters));
    }

    /*
     * FROM:
     * 0 => string 'id IN (:ids:)' (length=13)
     * 'bind' => 
     *   array (size=1)
     *     'ids' => 
     *       array (size=4)
     *         0 => int 15
     *         1 => int 17
     *         2 => int 18
     *         3 => int 19
     * 'for_update' => boolean true
     *
     * TO:
     * 0 => &string 'id IN (?0, ?1, ?2, ?3)' (length=22)
     * 'bind' => 
     *   array (size=4)
     *     0 => int 15
     *     1 => int 17
     *     2 => int 18
     *     3 => int 19
     * 'for_update' => boolean true
     */
    private static function _prepareBinds($parameters = NULL)
    {
        // binding is allowed only in array
        if (!is_array($parameters))
            return $parameters;

        // getting conditions from 0 or conditions parameter
        if (!empty($parameters['conditions']))
            $conditions =& $parameters['conditions'];
        elseif (!empty($parameters[0]))
            $conditions =& $parameters[0];
        else
            $conditions = '';

        // finding largest already set placeholder to avoid conflicts
        if (preg_match('/.*\?(\d+)/', $conditions, $matches))
            $i = $matches[1] + 1;
        else
            $i = 0;

        /*
         * check if exists bind and replace all arrays to ?0 ?1 etc
         */
        if (!empty($parameters['bind']))
            foreach ($parameters['bind'] as $key => $binded)
                if (is_array($binded))
                {
                    $placeholders = array();
                    $binds = array();
                    foreach ($binded as $bind)
                    {
                        $placeholders[] = '?'.$i;
                        $parameters['bind'][$i] = $bind;
                        $i++;
                    }
                    unset($parameters['bind'][$key]);
                    $conditions = str_replace(':'.$key.':', implode(', ', $placeholders), $conditions);
                }
        return $parameters;
    }
}

class Entity extends Model
{

}

class EntityController extends \Phalcon\Mvc\Controller
{
    public function indexAction($checks)
    {
        $entityCollection = Entity::find(array(
                'id IN (:ids:) AND entity_id IN (?99, :entities:)',
                'bind' => array('ids' => array(15,17,18,19), 99 => 1, 'entities' => array(1,5,9,7)),
            ));
    }
}

Result:

array (size=3)
  0 => string 'id IN (:ids:) AND entity_id IN (?99, :entities:)' (length=48)
  'bind' => 
    array (size=3)
      'ids' => 
        array (size=4)
          0 => int 15
          1 => int 17
          2 => int 18
          3 => int 19
      99 => int 1
      'entities' => 
        array (size=4)
          0 => int 1
          1 => int 5
          2 => int 9
          3 => int 7
  'for_update' => boolean true

array (size=3)
  0 => &string 'id IN (?100, ?101, ?102, ?103) AND entity_id IN (?99, ?104, ?105, ?106, ?107)' (length=77)
  'bind' => 
    array (size=9)
      99 => int 1
      100 => int 15
      101 => int 17
      102 => int 18
      103 => int 19
      104 => int 1
      105 => int 5
      106 => int 9
      107 => int 7
  'for_update' => boolean true

dexx086 · Answer 4 · 2014-09-21T14:57:09-07:00

DestinyMKas: thanks for sharing this code, I was missing it very much!

Btw: what's the requirements for this code to be implemented in Phalcon core? Passing an array for a bind parameter cannot be valid, I guess never, so a solution such this to make it work, would be big help. And on the other hand, without it, it's quite cumbersome to pass an array for an IN operator...

Pavel · Answer 5 · 2014-09-22T00:58:26-07:00

The easier way is to use builder, that returns same objects as find():

$builder = new \Phalcon\Mvc\Model\Query\Builder();
return $builder
    ->from('App\Models\Model')
    ->inWhere('id', $ids)
    ->getQuery()->execute();

dexx086 · Answer 6 · 2014-09-22T02:28:20-07:00

PVGrad, you're right, it works by inWhere well. But I still think that the default binding could be extended with this functionality to be included in core. For IN operations (which is not so rare I think) would solve this array problem quite transparently (and because arrays are still unusable for direct parameter binding, it wouldn't raise problems for current SQL generations).

Cedric Kastner · Answer 7 · 2015-08-18T07:46:03-07:00

Be aware of the following behavior.

$collection = Model::query()->inWhere('id', array(1, 2, 3));
echo $collection->count();
// Returns: 3 (correct)

But:

$collection = Model::query()->inWhere('id', array(1, 2, 3))->limit(99);
echo $collection->count();
// Returns: 1 (wrong)

And:

$collection = Model::query()->inWhere('id', array(1, 2, 3))->andWhere('col = :val:', array('val' => '1'));
echo $collection->count();
// Returns: 1 (wrong again)

It seems, that as soon as you start using bound parameters in any part of your query, the inWhere() assignment gets messed up. My educated guess is: This is an bug while handling the binding of parameters, because both limit() and andWhere() are making use of it, as well as several other functions.

Andres Gutierrez · Answer 8 · 2015-08-18T08:10:19-07:00

Are you using Phalcon 2.0.7?

Be aware of the following behavior.
$collection = Model::query()->inWhere('id', array(1, 2, 3));
echo $collection->count();
// Returns: 3 (correct)
But:
$collection = Model::query()->inWhere('id', array(1, 2, 3))->limit(99);
echo $collection->count();
// Returns: 1 (wrong)
And:
$collection = Model::query()->inWhere('id', array(1, 2, 3))->andWhere('col = :val:', array('val' => '1'));
echo $collection->count();
// Returns: 1 (wrong again)
It seems, that as soon as you start using bound parameters in any part of your query, the inWhere() assignment gets messed up. My educated guess is: This is an bug while handling the binding of parameters, because both limit() and andWhere() are making use of it, as well as several other functions.

Cedric Kastner · Answer 9 · 2015-08-18T09:07:58-07:00

Cedric Kastner
2.1k

in reply to

Andres Gutierrez

edited Aug '15
Aug '15

Are you using Phalcon 2.0.7?

I havn't upgraded yet, but I guess this could still be useful for people using older versions.

Anton · Answer 10 · 2015-10-01T06:59:45-07:00

This variant seems to work


find([ 'conditions'=>'id IN ('.implode(',',[1,2,3]).')' ]);

No, we don't have plans to add it, but if someone wants to implement it in Phalcon 2 it would be great.

Andres Gutierrez · Answer 11 · 2015-10-01T07:41:34-07:00

This feature is natively supported by Phalcon in 2.0.4, https://blog.phalcon.io/post/phalcon-2-0-4-released

blm14 · Answer 12 · 2016-03-02T11:34:35-07:00

Hi - what is the syntax for this now? I am trying to bind an array in a find:: and I keep getting "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"

Code, $status is a model which has a relationship to MyStatus:

$result = $status->getMyStatus(array("conditions"=>"md in (:md:)","bind"=>array("md"=>array(0=>153,1=>199))));

Is it because I am not doing find:: but rather passing to a sub-model?

FaimMedia B.V. · Answer 13 · 2016-03-29T06:05:33-07:00

You can use it like this:

Status::find([
    'conditions' => 'md IN ({md:array})',
    'bind' => [
        'md' => [153, 199],
    ],
]);

Doesn't matter if you use it with find, findFirst or the queryBuilder.

Example can be find here, in the third code block.

Hi - what is the syntax for this now? I am trying to bind an array in a find:: and I keep getting "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"

Code, $status is a model which has a relationship to MyStatus:
$result = $status->getMyStatus(array("conditions"=>"md in (:md:)","bind"=>array("md"=>array(0=>153,1=>199))));
Is it because I am not doing find:: but rather passing to a sub-model?

Anton · Answer 14 · 2016-06-17T23:45:12-07:00

This feature is natively supported by Phalcon in 2.0.4, https://blog.phalcon.io/post/phalcon-2-0-4-released

Would you please provide right syntax example how to use it natively with find, not queryBuilder.

FaimMedia B.V. · Answer 15 · 2016-06-21T03:16:48-07:00

Please read my reply first.

This feature is natively supported by Phalcon in 2.0.4, https://blog.phalcon.io/post/phalcon-2-0-4-released

Would you please provide right syntax example how to use it natively with find, not queryBuilder.

blm14 · Answer 16 · 2016-06-21T09:51:06-07:00

Here is how I did it:

$myarr = array (1,5,89,14);
$result = MyModel::find(array(
        "conditions"=>"column in ({myvar:array})",
        "bind"=>array("myvar"=>$myarr)
));

So it works pretty much the normal way except that you have to enclose the bind parameter in curly braces and specify that it's an array.

Ali SadeghipourKorabaslo · Answer 17 · 2017-05-29T10:15:01-07:00

So simple solution:

$post_id_arr = array(10,15,20,22,30);

$arrStr = implode("\",\"",$post_id_arr);

$posts = MyModel::find(array("id IN (\"$arrStr\")"));

Nikolay Mihaylov · Answer 18 · 2017-05-29T12:02:38-07:00

Nikolay Mihaylov
93.7k

May '17

@Ali SadeghipourK, better use bim14 solution. Your example could lead to injection.