[Tutor] preventing SQL injection

[Kent Johnson]

johnf wrote:
> On Friday 11 January 2008 11:45:36 am you wrote:
> Let's start over!

Thank you.

> import psycopg2
> 
> conn = psycopg2.connect("host='192.168.1.201' dbname='aName' user ='UserName' 
> password ='******'")
> tempCursor= conn.cursor()
> custnum = 'ABC123'
> mysql ="Select ccustno from public.arcust where ccustno =%s" % (custnum,)
> tempCursor.execute(mysql)
> 
> The above statement will return an error from postgres
> column "ABC123" does not exist

Right, you are not calling execute correctly. The whole point is *not* 
to use string interpolation to put the parameters into the sql. The 
params are passed to execute() as a separate argument. Try this:

mysql ="Select ccustno from public.arcust where ccustno =%s"
tempCursor.execute(mysql, (custnum,))

If that doesn't work then you probably need a different paramstyle. I 
think ? works with psycopg2 but it says it wants pyformat style which 
would be something like

mysql ="Select ccustno from public.arcust where ccustno =%(custnum)s"
tempCursor.execute(mysql, dict(custnum=custnum))

Kent

> 
> However, if change the statement to:
> 
> mysql ="Select ccustno from public.arcust where ccustno ='%s' " % (custnum,)
> 
> it works.
> 
> Because the select statement wants to see 
> ccustno = 'ABC123'
> 
> not
> ccustno=ABC123
> 
> 
>