On Thu, Oct 27, 2005 at 11:56:20AM +0100, Paul Moore wrote:
> ... and you generally shouldn't paste escaped values into SQL
> statements, but rather use bind variables in any case.

   Nice theory. Hard in practice. Python DB API drivers are poor at quoting
different data types. I am working on converting SQLObject to use
parametrized queries:

   http://svn.colorstudy.com/home/phd/SQLObject/paramstyles/

   and stumbled upon a major troubles in drivers. Low-level drives do a
poor job converting different types to SQL. For example, PySQLite1 does not
convert boolean values - it actually does

   query_string % parameters

in the code, and SQLite barfs on 'True'/'False' constants.

   psycopg1 does not quote datetime and mxDateTime instances, so

cursor.execute("INSERT VALUES (?,?,?)", 'Yes', datetime.now(), True)

   is executed as

INSERT VALUES ('Yes', 2005-10-10 12:00:01, .t.)

   and Postgres of course aborts such a broken query. Date instances are
even more funny:

INSERT VALUES ('Yes', 2005-10-10, .t.)

   and Postgres report the error: "You are trying to insert an integer into
a DATE column; use cast."
   Yes, 2005-10-10 is an integer, 1985.

Oleg.
--
     Oleg Broytmann            http://phd.pp.ru/            phd@phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.