[Tutor] preventing SQL injection

[johnf]

On Friday 11 January 2008 11:19:43 am you wrote:
> johnf wrote:
> > I spoke to soon.  Where can I find the DB-API for postgres?  Because the
> > only way I can get this to work is using ('%s') and it does not work with
> > (%s).
>
> What module are you using to connect to postgres? That module should
> implement DB-API as documented here:
> http://www.python.org/dev/peps/pep-0249/
>
> The module itself should have a paramstyle attribute that shows what
> kind of parameter passing it expects:
> In [5]: import psycopg2
> In [6]: psycopg2.paramstyle
> Out[6]: 'pyformat'
>
> The meaning of the paramstyle is documented (somewhat) in PEP 249.
>
> > BTW where I'm doing my testing is with a SELECT statement.
> >
> > below does not work
> > mySQL= "Select fieldname from tableName where str_field = %s" % (myVar,)
> >
> > but this works
> > mySQL= "Select fieldname from tableName where str_field = '%s' " %
> > (myVar,)
>
> Can you post a small, complete program containing both the working and
> non-working variants and show the complete output of the program?
>
> Kent

Sure:
localTableName = 'arcust'
cursor.KeyField = 'pkid'
localSchemaName = 'public'

mysql="""
SELECT substring((SELECT substring(pg_get_expr(d.adbin, d.adrelid) for 128) 
FROM pg_attrdef d 
WHERE d.adrelid = a.attrelid  AND d.adnum = a.attnum  AND a.atthasdef) 
FROM 'nextval[^'']*''([^'']*)') 
FROM pg_attribute a 
LEFT JOIN pg_class c ON c.oid = a.attrelid 
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum AND 
a.atthasdef 
LEFT JOIN pg_namespace n ON c.relnamespace = n.oid WHERE (c.relname = '%s') 
AND a.attname = '%s' and n.nspname='%s' AND NOT a.attisdropped AND a.attnum > 
0 AND pg_get_expr(d.adbin, d.adrelid) LIKE 'nextval%%' 
""" %(localTableName, cursor.KeyField,localSchemaName)

tempCursor.execute(mysql)

without the single qoutes I get 
column "arcust" does not exist

What the statement wants to see is something like

c.relname = 'arcust'

and not

c.relname = arcust

- 
John Fabiani