sql escaping module

[Tim Roberts]

David Bear <david.bear at asu.edu> 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.

All of the Python database modules will do this protection for you.
Example:

   db = psycopg2.connect(database='dbname')
   c = db.cursor()
   c.execute( "INSERT INTO table1 VALUES (%s,%s,%s);", (var1, var2, var3) )

Note that I have used a "comma", not the Python % operator, and I have not
done any quoting in the query.  By doing that, I am instructing the
database module to do whatever protective quoting may be required for the
values I have passed, and substitute the quoted values into the string.

As long as you use that scheme, you should be safe from injection.  It's
only when people try to do it themselves that they get in trouble, as in:

   c.execute( "INSERT INTO table1 VALUES ('%s','%s','%s');" % (var1, var2,
var3) )   # THIS IS WRONG

-- 
- Tim Roberts, timr at probo.com
  Providenza & Boekelheide, Inc.