issue with sql construction WHERE IN ()

[arava]

hi guys... i have an issue with this constructing :

$deps        = '3,2,30,11';
$response =  $this->db->exec(
    [
        'SELECT * FROM departments_escalation_role WHERE iddep IN (:deps) AND active = :one'
    ],
    [
        array(
            ':deps' => $deps,
            ':one'  => 1,
        ),
    ]
);

var_dump($response); // it will be null

but if i`ll use the next construction all will be fine:
return $this->db->exec("SELECT * FROM departments_escalation_role WHERE iddep IN ($deps) AND active = 1");

can you try to explain what i`m doing wrong

[ved]

Hi, sorry I deleted by previous answer as it wasn't really correct.

Can you try this:

$response = $this->db->exec(
    'SELECT * FROM departments_escalation_role WHERE iddep IN (:deps) AND active = :one',

    [':deps' => $deps,':one'  => 1]
);

[Richard Goldstein]

I don’t believe this will work. Each entry in the IN () clause needs to be its own parameter.

I have a helper function that I use to construct the in clauses – I’ll send a follow up message

--
-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-framework...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en
---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to f3-framework...@googlegroups.com.
To post to this group, send email to f3-fra...@googlegroups.com.
Visit this group at https://groups.google.com/group/f3-framework.
To view this discussion on the web visit https://groups.google.com/d/msgid/f3-framework/9a3c26d8-c686-462c-8f14-15093ea00d1b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Richard Goldstein]

Here is my helper method:

 

/**
* Create the contents for an 'IN' clause and the params array associated with it
*
* @param int|string|array $key Array of values for the in clause
* @param string $prefix Prefix for the replaceable params in thw query
*
* @return array [ 'params'=>array params list, 'in_clause' => What goes between the parens
*
*          $ip = self::createInParams($key);
*          $params = array_merge([
*              // ... other params
*              ], $ip['params']);
*
*          $q = "SELECT... WHERE field in ({$ip['in_clause']}) ..."
*          $this->>exec($q, $params);
*/
public static function createInParams($key, $prefix='k')
{
    if (!is_array($key)) {
        $key = [$key];
    }
    $params = [];
    $key_fields = [];
    foreach ($key as $k => $v) {
        $id = ":{$prefix}f{$k}";
        $key_fields[] = $id;
        $params[$id] = $v;
    }
    $in = implode(', ', $key_fields);
    return ['params' => $params, 'in_clause' => $in];
}

 

So if I have an array of params like
$id = [1,2,3,4,5];

I can do:

$p = obj::createInParams($id);
$sql = “select * from table where my_field in ({$p[‘in_clause’]})”;
$r = $db->exec($sql, $p[‘params’]);

 

The comment to the method outlines how I handle other parameters via array_merge

 

From: ved via Fat-Free Framework <f3-framework+APn2wQdk8BzurMhBnm1...@googlegroups.com>
Date: Tuesday, November 27, 2018 at 7:36 AM
To: Fat-Free Framework <f3-fra...@googlegroups.com>
Subject: [f3-framework] Re: issue with sql construction WHERE IN ()

 

Hi, sorry I deleted by previous answer as it wasn't really correct.

--

[ved]

Yeah but OP had $deps setup as a string so I'm not really sure on this case why it couldn't just assign that string to the parameter. I'm thinking some escaping issue(?)

What if deps is setup as:

$deps = "'3,2,30,11'";

It's weird, but could this work?

[Richard Goldstein]

I don’t believe that will work – worth a shot but I think it breaks the notion of replaceable parameters – if this were allowed, sql injection would be possible here

 

 

From: ved via Fat-Free Framework <f3-framework+APn2wQdk8BzurMhBnm1...@googlegroups.com>

Date: Tuesday, November 27, 2018 at 8:02 AM
To: Fat-Free Framework <f3-fra...@googlegroups.com>

--

-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-framework...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en
---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to f3-framework...@googlegroups.com.
To post to this group, send email to f3-fra...@googlegroups.com.
Visit this group at https://groups.google.com/group/f3-framework.

To view this discussion on the web visit https://groups.google.com/d/msgid/f3-framework/19209e89-dcdf-43f8-9ea4-e22dcee578cd%40googlegroups.com.

[Yaroslav Beregovoy]

sorry, but this doesn`t work

will try with a "helper method"...

--

-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-framework...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en
---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to f3-framework...@googlegroups.com.
To post to this group, send email to f3-fra...@googlegroups.com.
Visit this group at https://groups.google.com/group/f3-framework.

To view this discussion on the web visit https://groups.google.com/d/msgid/f3-framework/19209e89-dcdf-43f8-9ea4-e22dcee578cd%40googlegroups.com.

[ved]

Yeah I guess you're right. I'm not at home so I can't really test it either at this moment.

Anyway, your helper function should help OP with what he wants though.

[Yaroslav Beregovoy]

i tested this already, in this case it work:

return $this->db->exec(
    [
        'SELECT * FROM departments_escalation_role WHERE iddep IN (:q1, :q2) AND active = 1'
    ],
    [
        array(

            ':q1' => 2,

            ':q2' => 30,
        ),
    ]

i will use your function and will write to U as soon as possible

On Tue, Nov 27, 2018 at 3:29 PM ved via Fat-Free Framework <f3-framework+APn2wQdk8BzurMhBnm1...@googlegroups.com> wrote:

Yeah I guess you're right. I'm not at home so I can't really test it either at this moment.

Anyway, your helper function should help OP with what he wants though.

--

-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-framework...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en
---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to f3-framework...@googlegroups.com.
To post to this group, send email to f3-fra...@googlegroups.com.
Visit this group at https://groups.google.com/group/f3-framework.

To view this discussion on the web visit https://groups.google.com/d/msgid/f3-framework/aa85a2d2-9e72-4fed-8439-8e4cd1dbaa2c%40googlegroups.com.

[Yaroslav Beregovoy]

so... i modified a little bit "helper method" :

public static function createINParams($keys, $prefix=':q') // [1,3,30,23]
{
    $i     = 0;
    $bound = [];
    foreach ($keys as $key) {
        $bound[$prefix.$i] = $key; // [':q1' => 1,':q2' => 2]
        $i++;
    }
    $prepared = implode(',', array_keys($bound));

    return ['prepared' => $prepared, 'bound' => $bound];
}

$prepared = $params['prepared'];
return $this->db->exec(
    [
        "SELECT * FROM departments_escalation_role WHERE iddep IN ($prepared) AND active = 1"
    ],
    [
        $params['bound']
    ]
);

Thanks a much... "helper method" was really helpful.... i`m grateful :-) happy coding...

On Tue, Nov 27, 2018 at 3:29 PM ved via Fat-Free Framework <f3-framework+APn2wQdk8BzurMhBnm1...@googlegroups.com> wrote:

Yeah I guess you're right. I'm not at home so I can't really test it either at this moment.

Anyway, your helper function should help OP with what he wants though.

--

-- You've received this message because you are subscribed to the Google Groups group. To post to this group, send an email to f3-fra...@googlegroups.com. To unsubscribe from this group, send an email to f3-framework...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/f3-framework?hl=en
---
You received this message because you are subscribed to the Google Groups "Fat-Free Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to f3-framework...@googlegroups.com.
To post to this group, send email to f3-fra...@googlegroups.com.
Visit this group at https://groups.google.com/group/f3-framework.

To view this discussion on the web visit https://groups.google.com/d/msgid/f3-framework/aa85a2d2-9e72-4fed-8439-8e4cd1dbaa2c%40googlegroups.com.