how to replace and string in a "SELECT ... IN ()"

[bcurtu]

Hi,

I have a BIIIIIG problem with the next query:

cursor.execute( """
SELECT titem.object_id , titem.tag_id
FROM tagging_taggedi tem titem
WHERE titem.object_id IN (%s)
""",( eid_list))

eid_list is suppossed to be a list of ids = [1,5,9]

How can I make it work?

Thanks

[Wojtek Walczak]

Re: how to replace and string in a "SELECT ... IN ()"

On Fri, 26 Sep 2008 02:32:50 -0700 (PDT), bcurtu wrote:

I have a BIIIIIG problem with the next query:
>
cursor.execute( """
SELECT titem.object_id , titem.tag_id
FROM tagging_taggedi tem titem
WHERE titem.object_id IN (%s)
""",( eid_list))

^
It should rather be '%'.
HTH.

--
Regards,
Wojtek Walczak,

http://tosh.pl/gminick/

[bcurtu]

Re: how to replace and string in a "SELECT ... IN ()"

Pardon?

% instead of %s?

It doesn't work... :(

On 26 sep, 12:15, Wojtek Walczak <gmin...@bzt.bz twrote:

On Fri, 26 Sep 2008 02:32:50 -0700 (PDT), bcurtu wrote:

I have a BIIIIIG problem with the next query:

>

        cursor.execute( """
                    SELECT titem.object_id , titem.tag_id
                    FROM tagging_taggedi tem titem
                    WHERE titem.object_id IN (%s)
                """,( eid_list))

>
                     ^
It should rather be '%'.
HTH.
>
--
Regards,
Wojtek Walczak,http://tosh.pl/gminick/

[Bruno Desthuilliers]

Re: how to replace and string in a &quot;SELECT ... IN ()&quot;

Wojtek Walczak a écrit :

On Fri, 26 Sep 2008 02:32:50 -0700 (PDT), bcurtu wrote:
>

>I have a BIIIIIG problem with the next query:
>>
> cursor.execute( """
> SELECT titem.object_id , titem.tag_id
> FROM tagging_taggedi tem titem
> WHERE titem.object_id IN (%s)
> """,( eid_list))

^
It should rather be '%'.

Please avoid such clueless advices and read the doc of the python db-api.

[Bruno Desthuilliers]

Re: how to replace and string in a &quot;SELECT ... IN ()&quot;

bcurtu a écrit :

Hi,
>
I have a BIIIIIG problem with the next query:
>
cursor.execute( """
SELECT titem.object_id , titem.tag_id
FROM tagging_taggedi tem titem
WHERE titem.object_id IN (%s)
""",( eid_list))
>
eid_list is suppossed to be a list of ids = [1,5,9]
>
How can I make it work?

You have to build your sql statement in three stages:

# stage 0: the template
sql_template = """
SELECT titem.object_id , titem.tag_id
FROM tagging_taggedi tem titem
WHERE titem.object_id IN (%s)
"""

# stage 1: build correct place_holders string for the actual number
# of items in eid_list
place_holders = ", " .join("%s" for x in xrange(len(eid_ list)))

# stage 2 : build the effective sql statement
sql = sql_template % place_holders

# ok, let's go:
cursor.execute( sql_template, eid_list)


NB : you can of course make it in a single statement, but readability
will suffer:

cursor.execute(
"""
SELECT titem.object_id , titem.tag_id
FROM tagging_taggedi tem titem
WHERE titem.object_id IN (%s)
""" % ", " .join("%s" for x in xrange(len(eid_ list))),
eid_list
)


HTH

[MRAB]

Re: how to replace and string in a &quot;SELECT ... IN ()&quot;

On Sep 26, 12:23 pm, Tino Wildenhain <t...@wildenhai n.dewrote:

Hi,
>
>
>
Bruno Desthuilliers wrote:

bcurtu a écrit :

Hi,

>

I have a BIIIIIG problem with the next query:

>

        cursor.execute( """
                    SELECT titem.object_id , titem.tag_id
                    FROM tagging_taggedi tem titem
                    WHERE titem.object_id IN (%s)
                """,( eid_list))

>

eid_list is suppossed to be a list of ids = [1,5,9]

>

How can I make it work?

>

You have to build your sql statement in three stages:

>

# stage 0: the template
sql_template = """
    SELECT titem.object_id , titem.tag_id
    FROM tagging_taggedi tem titem
    WHERE titem.object_id IN (%s)
"""

>

# stage 1: build correct place_holders string for the actual number
# of items in eid_list
place_holders = ", " .join("%s" for x in xrange(len(eid_ list)))

>
Hm. either ", ".join(["%s"]*len(eid_list))
or ", ".join("%s" for x in eid_list)
>
should produce the same, wouldn't it? :-)
>

[snip]
Or:

place_holders = ("%s," * len(eid_list))[ : -1]

:-)

[D'Arcy J.M. Cain]

Re: how to replace and string in a &quot;SELECT ... IN ()&quot;

On Fri, 26 Sep 2008 11:00:59 -0500
"Michael Mabin" <d3vvnull@gmail .comwrote:

So we can drop a table in an in clause? How is this a use case. Cartoons
are funny but actual proof that this example using an in-clause provides an
exploit would be more helpful I think.

I'm not sure what proof you require. If you program such that users
can enter arbitrary stings into your database it is obvious that the
exploit in that cartoon can be used against you. And the point is that
it has nothing to do with IN clauses. It can be any SQL. Go read that
cartoon carefully. It says nothing about IN clauses. Consider;

"UPDATE student SET name = '%s' WHERE student_id = %s" % (name, id);

Now set name to "Robert'; DROP TABLE student;" and see what happens if
you feed that to your SQL database. Hell, just put "';" in the string
for fun.

--
D'Arcy J.M. Cain <darcy@druid.ne t | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

[Paul Boddie]

Re: how to replace and string in a &quot;SELECT ... IN ()&quot;

On 26 Sep, 12:15, Wojtek Walczak <gmin...@bzt.bz twrote:

On Fri, 26 Sep 2008 02:32:50 -0700 (PDT), bcurtu wrote:

I have a BIIIIIG problem with the next query:

>

cursor.execute( """
SELECT titem.object_id , titem.tag_id
FROM tagging_taggedi tem titem
WHERE titem.object_id IN (%s)
""",( eid_list))

>
^
It should rather be '%'.

You're telling the inquirer to do string substitution which can be
dangerous if eid_list is built, say, from a collection of strings
taken from an untrusted source.

Sadly, SQL parameter substitution, which is done using the syntax
employed by the inquirer above (along with the unfortunate "%s"
placeholder syntax), does not really deal with sequences of values
very well. What needs to be done here, if everything should happen
relatively safely, is that the query string should be made to contain
the appropriate number of placeholders between the brackets, with
commas separating them as demanded by the syntax of SQL. Then, the
values should be correctly taken from eid_list by the execute method,
although for portability between different database modules, whose
authors seem to have differing views on what kind of object can be
given containing the parameters, I'd recommend converting eid_list to
a tuple.

Bruno and Tino thrash out some kind of working solution, I think.

Paul

[D'Arcy J.M. Cain]

Re: how to replace and string in a &quot;SELECT ... IN ()&quot;

On Fri, 26 Sep 2008 14:04:35 -0500
"Michael Mabin" <d3vvnull@gmail .comwrote:

Doesn't it depend on where and why you intend to execute the code?
Obviously some SQL is more at risk for exploit when the input is from the
screen on a web page than if you were running parameterized code in a
controlled batch environment. Or if you were writing code generators (which
is what I happen to do) which won't be run by the general public.
>
Incidentally, couldn't input field edits prevent such exploits prior to
interpolation?

I encourage my competitors to program that way.

--
D'Arcy J.M. Cain <darcy@druid.ne t | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

[Tino Wildenhain]

Re: how to replace and string in a &quot;SELECT ... IN ()&quot;

Hi,

Michael Mabin wrote:

If the inputs are edited prior to the construction of the string and
these fields are used for more than one update then it's not an exploit.
It's simply a matter not repeating yourself when coding.

In python we do not fear that.

In this particular case too, we're talking about a list of integers that
gets inserted into a string. If the list is validated prior to its

Its a list, if it indeed has integers in it is uncertain. It is so very
easy to check that that it doesnt even make sense to write such lengthy
emails about how bad you want to avoid it. Just do it. And even more so
if you are telling others how to do things make sure they do not so easy
shoot themselfes in their feet.

insertion into an SQL statement then there is no exploit. If I write a
batch program (not a web program) that retrieves this list of integers
from other sources and validates the data prior to using it in an SQL
statement, that should be sufficient.

This might be well true but if you have a look at your original
contribution you see that all these your asumtions are just not in.

As far as wrong and right is concerned. I think it's more about doing
what is appropriate according to the circumstances. As a rule you

If its easy to do, why not just doing it correctly (or robust) in all
circumstances to just avoid overlooking a case?

should only code what is appropriate for the circumstances. If it's
appropriate to code more simply without introducing unnecessary
complexity you should do so.

But you did not tell us about your asumtations about the circumstances.

I work in the data warehousing ETL world, where we have to perform field
edits or transformations to load source data into databases. If I'm

Thats wrong. You do not "edit" fields. You have a validating type path
and _always_ the database is most authoritative about what it accepts.
Any other concept is just wrong and outright dangerous. There are
many examples of how this works out (just check bugtraq)

already performing edits on these fields and if these fields are going
to be used for more updates downstream, it's wasteful to perform them
again when I build the SQL insert with the list and execute it.

I still don't know what you mean by "edit" ;) If you mean filter out
special chars with for example replace("bad stuff","good stuff") check
your idea again, this is not going to work. (google for default permit)

Finally, whatever happened to the practice of granting appropriate
privileges to IDs that perform database operations? Shouldn't the
person acting in the capacity of DBA ensure that the user updating or
retrieving data from the database does not have DROP, ALTER, or CREATE
privileges on that database?

This of course is another layer which should be added - but you would
not need to - you edited the fields, right? ;)

Sorry, it was not meant to put you to the wall but you insist so much
on your still dangerous solution while top posting the hell out of
this thread I just could not ignore it ;)

T.

[Steve Holden]

Re: how to replace and string in a &quot;SELECT ... IN ()&quot;

Michael Mabin wrote:

Tino, dude, I'm afraid I lied about my previous post being the last
word. There are some things you said here that must be addressed.

Good grief, is there no utterance so inconsequential that you will walk
away from it without yet another round of retaliation?

I believe that all people were trying to convey is:

1. There are some data patterns that cannot be directly incorporated
into SQL statements without additional processing, regardless of whether
the "intention" of the data's originator is malevolent. A good example
is a string value containing an apostrophe, which in most SQL
implementations you can escape by preceding the apostrophe with another
apostrophe.

2. SQL drivers in Python are written so that no matter what the values
of the data may be, and no matter which backend they implement, data may
safely be passed as a tuple to a parameterized statement without such
cleansing because the drivers are written to ensure "dangerous" values
are appropriately handled.

Having said all that, if you are positive none of your string data
contains apostrophes you are, of course, free to build SQL statements
yourself - though doing so will on some systems lose you the speed
advantages offered by "prepared statements". Similarly, if you are *not*
positive of the quality of your data you are free to do the escaping in
your logic rather than using parameterized queries. This could be called
"buying a dog and barking yourself".

regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/

[Lawrence D'Oliveiro]

Re: how to replace and string in a &quot;SELECT ... IN ()&quot;

In message <mailman.1547.1 222447134.3487. python-list@python.org >, D'Arcy
J.M. Cain wrote:

On Fri, 26 Sep 2008 11:00:59 -0500
"Michael Mabin" <d3vvnull@gmail .comwrote:
>

>So we can drop a table in an in clause? How is this a use case.
>Cartoons are funny but actual proof that this example using an in-clause
>provides an exploit would be more helpful I think.

>
I'm not sure what proof you require.

I would say Mr Mabin is displaying thinking characteristic of a PHP
programmer
<http://groups.google.c o.nz/group/nz.comp/msg/4c2a4d220499daf d>. :)