[Tutor] preventing SQL injection

Kent Johnson kent37 at tds.net
Fri Jan 11 20:45:36 CET 2008


johnf wrote:
> On Friday 11 January 2008 11:19:43 am you wrote:

>> Can you post a small, complete program containing both the working and
>> non-working variants and show the complete output of the program?

Jeez, you might want to try to get this to work with something simple!

This is still not a complete program - where are the imports? There are 
at least four different DB-API compliant modules that talk to Postgres. 
Am I supposed to guess which one you are using?

Where does tempCursor come from? You just show the working version. 
Should I guess what the failing version looks like?

I'm trying to help but you aren't giving me much to work with and I'm 
getting tired of guessing. Show me the code!

> 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)

       ^
       ^
------^-----

Are you leaving that % in when you take out the quotes? Don't.

Kent
> 
> 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
> 



More information about the Tutor mailing list