[DB-SIG] any 2.0-compliant packages for postgresql?

Stuart Bishop zen@shangri-la.dropbear.id.au
Thu, 28 Jun 2001 11:44:17 +1000 (EST)

[attributions removed since I'd probably get them mixed up :-)]

> > > > Q: psycopg correctly put strings obtained by the Date and Time
> > > > constructors inside quotes  ('') as requested by the api. it should do
> > > > the same (and maybe escape) normal strings? if that's the case don't we
> > > > need a String constructor? how is the module supposed to know when a
> > > > string is real string and need quoting and when i am passing it just as
> > > > convenience but it really is a number? example...

It knows it is a real string that needs quoting because it was passed
a string. Failing to quote the string properly can lead to major security
problems and threads on Bugtraq:

curs.execute("INSERT INTO test VALUES '(%a)s')", mydict)

Now - if mydict was initialized from an untrusted source (eg. parameters
passed from a web form), it malicious user could initialise mydict['a']
to something like '''
    ');delete * from test;insert into test values '0wn3d

Failing to escape string types in the driver requires programmers
to continually call some sort of quote function (such as could be
done automatically by a String class like you mention). Enforcing
this would be an annoyance since (I would assume) in the majority of
cases, if I have a string object I need to pass to a RDBMS it is to
be inserted into a VARCHAR field. If an string should not be quoted
as a string by the python driver, it should be passed as some other
type. This is already the way Date and Binary types are handled.
The only legitimate use I can think of for passing a non-string
as a string to the driver is if you need to pass a number where
its precsion exceeds Python's ability to represent. The databases
I have experience with will cast this back into a number anyway
so this is handled for you (and if there are databases that don't do
this, we need Number class to handle extended precision or wait
for Python to support extended precision floats).

> > curs.execute("CREATE TABLE test (a text)")
> > curs.execute("INSERT INTO test VALUES (%(a)s)", {'a':'some text...'})
> >
> > the method call fails miserably because the generated SQL is:
> >
> >     INSERT INTO test VALUES (some text...)
> >
> > without quotes. footnote 5 requires argument binding to prodive quotes
> > and escape sequences but how can the module know is the given string
> > will be a string in the db too and requires quoting or it is something
> > different (let's say a user-defined type in psogresql) that does not
> > require it?

Stuart Bishop <zen@shangri-la.dropbear.id.au>