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 ! ::::
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
