sql escaping module

[Frank Millman]

David Bear wrote:
> Being new to pgdb, I'm finding there are lot of things I don't understand
> when I read the PEP and the sparse documentation on pgdb.
>
> I was hoping there would be a module that would properly escape longer text
> strings to prevent sql injection -- and other things just make sure the
> python string object ends up being a properly type for postgresql. I've
> bought 3 books on postgresql and none of th code samples demonstrate this.
>
> web searchs for 'python sql escape  string' yeild way too many results.
>
> Any pointers would be greatly appreciated.
>

I think I know where David is coming from, as I also battled to
understand this. I think that I have now 'got it', so I would like to
offer my explanation.

I used to think that each DB-API module transformed the 'string +
parameters' into a valid SQL command before passing it to the db.
However, this is not what is happening.

Every modern database provides an API to allow applications to interact
with the database programmatically. Typically these are intended for C
programs, but other languages may be supported. The authors of the
various DB-API modules provide a python wrapper around this to allow
use from a python program.

Each of the API's includes the capability of passing commands in the
form of 'string + parameters' directly into the database. This means
that the data values are never embedded into the SQL command at all,
and therefore there is no possibility of injection attacks.

The various API's use different syntaxes for passing the parameters. It
would have been nice if the DB-API had specified one method, and left
it to the author of each module to transform this into the form
required by the underlying API. Unfortunately the DB-API allows a
choice of 'paramstyles'. There may be technical reasons for this, but
it does make supporting multiple databases awkward.

Frank Millman