From: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
---|---|
To: | "W(dot) Matthew Wilson" <matt(at)tplus1(dot)com> |
Cc: | "psycopg(at)postgresql(dot)org" <psycopg(at)postgresql(dot)org> |
Subject: | Re: Is passing a list as a bound variable safe from SQL injection? |
Date: | 2013-10-02 16:17:11 |
Message-ID: | CA+mi_8bHN9cK=WUUj9QQhYm-cXJhbiUJ9+DEzXa3YDphvTqwzg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
On Wed, Oct 2, 2013 at 4:48 PM, W. Matthew Wilson <matt(at)tplus1(dot)com> wrote:
> Here's the python code:
>
> cursor.execute(textwrap.dedent("""
> select *
> from bundles
> where bundle_id = any(%(list_of_bundle_ids))
> """), {'list_of_bundle_ids': [2,3,4,5,6,7]})
>
> Is there more of a risk of SQL injection here by passing in a list of
> integers list this?
Don't see any risk here.
> Also, is there some simple way I can pass in a list of strings that could be
> converted to integers, like this:
>
> ["2", "33", "444"]
>
> When I tried passing a list containing a string, I got an "operator does not
> exist" error.
You are creating avoidable problems to the postgres parser. Just use
map(int, my_list) in python and you are on the safe side.
> Finally, I feel like recently I read a blog post that described how using "=
> any(array[...])" was much slower than using some other approach.
>
> But now I can't find that blog post, and I don't remember the other
> approach. Any ideas?
What other approach? Not using IN: the IN operator is converted to "=
any(array)" by the postgres parser:
=# explain select * from x where id in (1,2,3,4,5);
QUERY PLAN
------------------------------------------------------------
Seq Scan on x (cost=0.00..6.58 rows=5 width=51)
Filter: (id = ANY ('{1,2,3,4,5}'::integer[]))
It is true that this can be very inefficient for long lists, I've
experimented it myself several times, but it's not something you can
change at driver level: creating a temp table to join on can be faster
even for not very long lists.
-- Daniele
From | Date | Subject | |
---|---|---|---|
Next Message | W. Matthew Wilson | 2013-10-02 16:31:50 | Re: Is passing a list as a bound variable safe from SQL injection? |
Previous Message | W. Matthew Wilson | 2013-10-02 15:48:13 | Is passing a list as a bound variable safe from SQL injection? |