Something More Elegant

[Victor Subervi]

On Sat, Jan 9, 2010 at 8:39 AM, Tim Chase <python.list at tim.thechases.com>wrote:

> Victor Subervi wrote:
>
>> Hi;
>> The following code works fine. I would like you to suggest something more
>> simple and elegant:
>>
>>      sql = 'select p.ID from %sPackages p join %sCategoriesPackages c
>> where
>> c.CategoryID=%s;' % (store, store, categoryID)
>>      cursor.execute(sql)
>>      tmp = [itm[0] for itm in cursor]
>>      packageIDs = []
>>      for t in tmp:
>>        if t not in packageIDs:
>>          packageIDs.append(t)
>>
>
> You mean like
>
>  sql = "select distinct p.ID from ..." % (...)
>

Oh, that's good!


>  #             ^^^^^^^^
>  cursor.execute(sql)
>  package_ids = [row[0] for row in cursor.fetchall()]
>
> It would also help if you didn't pass the categoryID as a string-formatted
> value, but as a proper parameter, something like
>
>  sql = "... where c.categoryid=?" % (store, store)
>  cursor.execute(sql, (category_id,))
>

I now have the following:

      sql = 'select distinct p.ID from %sPackages p join
%sCategoriesPackages c where c.CategoryID=?;' % (store, store)
      cursor.execute(sql, (categoryID,))
      packageIDs = [itm[0] for itm in cursor]

It threw this error:

 /var/www/html/angrynates.com/christians/cart/display.py
  141     print '</td></tr></table>\n'
  142   cursor.close()
  143   bottom()
  144
  145 display()
display = <function display>
 /var/www/html/angrynates.com/christians/cart/display.py in display()
  109       categoryID = cursor.fetchone()[0]
  110       sql = 'select distinct p.ID from %sPackages p join
%sCategoriesPackages c where c.CategoryID=?;' % (store, store)
  111       cursor.execute(sql, (categoryID,))
  112       packageIDs = [itm[0] for itm in cursor]
  113       for pid in packageIDs:
global cursor = <MySQLdb.cursors.Cursor object>, cursor.execute = <bound
method Cursor.execute of <MySQLdb.cursors.Cursor object>>, sql = 'select
distinct p.ID from productsPackages p join productsCategoriesPackages c
where c.CategoryID=?;', categoryID = 1L
 /usr/lib64/python2.4/site-packages/MySQLdb/cursors.py in
execute(self=<MySQLdb.cursors.Cursor object>, query='select distinct p.ID
from productsPackages p join productsCategoriesPackages c where
c.CategoryID=?;', args=(1L,))
  146         query = query.encode(charset)
  147         if args is not None:
  148             query = query % db.literal(args)
  149         try:
  150             r = self._query(query)
query = 'select distinct p.ID from productsPackages p join
productsCategoriesPackages c where c.CategoryID=?;', db = <weakproxy at
0x2b79db9dc470 to Connection>, db.literal = <bound method Connection.literal
of <_mysql.connection open to 'localhost' at 142be8b0>>, args = (1L,)

TypeError: not all arguments converted during string formatting
      args = ('not all arguments converted during string formatting',)


> This helps prevent SQL-injection attacks (assuming you have full control
> over the value of "store"...otherwise, as you've been advised, if the remote
> user has control over the value in "store", you're asking to be exploited).
>


They have control over it. I pass it in the url. Please advise.


> You'd have to check the place-holder character for your particular
> back-end:
>
>  >>> import <your database engine> as db
>  >>> print db.paramstyle
>
> Printed "format". What's that mean? I use MySQLdb
TIA,
beno
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100109/f7c7f13e/attachment-0001.html>