Some More MySQL

[Victor Subervi]

On Thu, May 27, 2010 at 2:54 PM, MRAB <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?
TIA,
beno
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100527/ef671748/attachment.html>