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]

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]

Pardon?

% instead of %s?

It doesn't work... :(

On 26 sep, 12:15, Wojtek Walczak <[e-mail] 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]

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]

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]

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]

On Fri, 26 Sep 2008 11:00:59 -0500
"Michael Mabin" <d3******@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 <da***@druid.ne t | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
[phone] (DoD#0082) (eNTP) | what's for dinner.

[Paul Boddie]

On 26 Sep, 12:15, Wojtek Walczak <[e-mail] 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]

On Fri, 26 Sep 2008 14:04:35 -0500
"Michael Mabin" <d3******@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 <da***@druid.ne t | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
[phone] (DoD#0082) (eNTP) | what's for dinner.

[Tino Wildenhain]

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.