Some More MySQL

[MRAB]

Victor Subervi wrote:
> On Thu, May 27, 2010 at 2:54 PM, MRAB <python at mrabarnett.plus.com 
> <mailto:python at mrabarnett.plus.com>> wrote:
> 
>     Victor Subervi wrote:
> 
>         Hi;
>         But what about this?
> 
>          sql = "select pic%d from %s where ID='%%s';" % (pic, store)
>          cursor.execute(sql % id)
> 
>         If I try and rewrite the last line like this:
> 
>          cursor.execute(sql, id)
> 
>         it doesn't work. What do?
> 
>         How about this one:
> 
>                cursor.execute("insert into categories (Store, Category,
>         Parent) values('%s', '%s', Null)", (store, cat))
> 
>         For some reason it puts single quotes around my variables! This
>         doesn't happen if I change that comma for a percent sign! What do?
> 
>         How about this one:
> 
>              sql = 'select * from options%s where ID=%%s',
>         (opTable[0].upper() + opTable[1:])
>         #      cursor.execute(sql, id)
>              cursor.execute('select * from options%s where ID=%s' %
>         (opTable[0].upper() + opTable[1:], id))
> 
>         The last one works, but if I comment it out and uncomment the
>         middle line, it doesn't. Same here:
> 
>                sql = "update options%s set PriceDiff='%%s' where
>         Field='%%s' and ID=%%s and Store='%%s'" % (opTable[0].upper() +
>         opTable[1:])
>         #        cursor.execute(sql, (value, opName, id, store))
>                cursor.execute('update options%s set PriceDiff="%s" where
>         Field="%s" and ID=%s and Store="%s"' % (opTable[0].upper() +
>         opTable[1:], value, opName, id, store))
> 
>     As has already been explained, when working with SQL in Python there are
>     2 forms of placeholder:
> 
>     1. Python's %s placeholder, replaced by Python's % operator.
> 
>     2. SQL's %s placeholder, replaced by the .execute method.
> 
>     SQL might not let you use its %s placeholder for table or column names,
>     but they are normally hidden from the user and fixed by the application.
> 
>     For user-supplied values there's the risk of SQL-injection attacks.
>     There are 2 ways of approaching that:
> 
>     1. The hard way: check the values and add any necessary quoting or
>     escaping before using Python's % operator, then pass the fully-formed
>     SQL statement to result to .execute.
> 
>     2. The easy way: pass the SQL statement to .execute with a %s for each
>     value and let the method substitute the values itself (it'll add
>     whatever quoting or escaping is necessary).
> 
> 
> Ok, so you're telling me I'm trying to do it the hard way. That's 
> because I still don't have my head wrapped around the easy way. I was 
> able to follow what Kushal Kumaran supplied; however I must still be 
> lost on how that applies to the above examples. Could you illustrate 
> with the first and let me try and figure out the rest?
> 
First build the SQL statement with placeholder(s) for the values:

     sql = "select pic%d from %s where ID=%%s;" % (pic, store)

Then execute the SQL statement, passing the value(s) so that .execute
performs the substitution itself:

     cursor.execute(sql, id)

Placeholders which are handled by .execute shouldn't be wrapped in
quotes, even is the value is a string, because .execute will handle that
(and any other details) itself.