[DB-SIG] comparing with lists via cx_Oracle

[M.-A. Lemburg]

On 2007-01-09 21:03, Harald Armin Massa wrote:
>> If the problem is not the parameter style, it may be the
>> sequence --> string conversion. In this case, use can use
>>          ",".join([1,2,3,4,5])
>> instead of "[1,2,3,4,5]".
> 
> There MUST NOT be a conversion to a string.
> 
> It has to be a "sequence" or whatever that would be within Oracles SQL.

The standard SQL notation for value lists in SQL is

	x in (value1, value2, ...)

so this should work pretty much with every SQL database.

If you have numbers as sequence elements, the Python tuple
representation works just fine. For strings, things are harder,
but then I wouldn't recommend using IN predicates with strings
anyway - it's better to map the strings to integers via an
extra table (if possible).

Note that you can also write:

	(x == value1) or (x == value2) ...

and then the valueN references using binding parameters.
That way you avoid having to deal with escaping - you still
have to construct the SQL depending on the number of values
you have.

If you have lots of sequence members, it's probably easier
to create a temporary table, fill it with the sequence members
using INSERT and then do a sub-select:

	select y from z where x in (select value from temptable)

If you have string members in your sequence, there's also
another method you could try: several databases have functions
for doing substring-search. All you'd have to do is join all
the members using e.g. ',' (provided none of the members
has this character) and the do something like:

	select y from z where index(values, ',' & x & ',') > 0

You can then replace values with a binding parameter to
avoid the escaping.

> Just imagine not having numeric IDs, but Strings:
> 
> ["isK9","kloN","kJni"]
> 
> as ID.
> 
> I went down that ", ".join(whatever) way; even to the ", ".join("'%s'"
> % (a,) for a in sequence)
> 
> way (those many "' are single quotes quoted with double quotes), and it works.
> 
> BUT ... it forces me to construct the SQL query via Python String
> functions, and to do all the necessary escaping of parameters in
> Python. That is considered BAD, because then I have to deal with all
> the escaping challenges - that's the entrence door of most PHP
> vulnerabilities.

It's not all that bad if you know which backend you're dealing
with :-)

The SQL injection in PHP often has to do with MySQL being
used as backend which supports a gazillion and one ways to do
escaping. This makes it hard to write a catch-all escaping function.

> Parameters should be escaped via the database adapter. .... So I am
> looking for the way to pass sequence parameters into Oracles throat :)

See above: there are other ways this can be done using
portable SQL.

BTW, sequences in DB-talk are objects which you can use as
auto-increment integer source, e.g. for creating ids of
new rows.

Hope this helps,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jan 09 2007)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::