[Tutor] preventing SQL injection
jfabiani at yolo.com
Fri Jan 11 20:33:00 CET 2008
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:
> The module itself should have a paramstyle attribute that shows what
> kind of parameter passing it expects:
> In : import psycopg2
> In : psycopg2.paramstyle
> Out: '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?
localTableName = 'arcust'
cursor.KeyField = 'pkid'
localSchemaName = 'public'
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 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
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)
without the single qoutes I get
column "arcust" does not exist
What the statement wants to see is something like
c.relname = 'arcust'
c.relname = arcust
More information about the Tutor