Some More MySQL

[MRAB]

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