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

Please help.
TIA,
beno

TIA,
beno
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100527/b4611a70/attachment-0001.html>

Search Discussions

  • MRAB at May 27, 2010 at 6:54 pm

    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).
  • Victor Subervi at May 27, 2010 at 7:36 pm

    On Thu, May 27, 2010 at 2:54 PM, MRAB 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>
  • MRAB at May 27, 2010 at 10:22 pm

    Victor Subervi wrote:
    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?
    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.
  • Victor Subervi at May 28, 2010 at 12:16 pm

    On Fri, May 28, 2010 at 2:17 AM, Dennis Lee Bieber wrote:

    On Thu, 27 May 2010 23:22:24 +0100, MRAB <python at mrabarnett.plus.com>
    declaimed the following in gmane.comp.python.general:
    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.
    Even more internal details -- the MySQLdb placeholder is %s because
    the adapter, internally, converts ALL parameters to strings, applies
    escapes to them, and THEN wraps them with quotes before using Python
    string interpolation to make the query that gets submitted to the
    server.

    This is why you can not use, say %d as a placeholder for a numeric
    parameter... MySQLdb will convert that numeric to a string, and then
    Python will choke when it tries to use a %d formatter and is given a
    string value.


    All of your and MRAB's comments were very helpful. However, I don't see how
    these two problems are addressed:

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

    TIA,
    beno
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: <http://mail.python.org/pipermail/python-list/attachments/20100528/09543d88/attachment.html>
  • Kushal Kumaran at May 28, 2010 at 12:58 pm

    On Fri, May 28, 2010 at 5:46 PM, Victor Subervi wrote:
    On Fri, May 28, 2010 at 2:17 AM, Dennis Lee Bieber wrote:

    On Thu, 27 May 2010 23:22:24 +0100, MRAB <python at mrabarnett.plus.com>
    declaimed the following in gmane.comp.python.general:
    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.
    ? ? ? ?Even more internal details -- the MySQLdb placeholder is %s because
    the adapter, internally, converts ALL parameters to strings, applies
    escapes to them, and THEN wraps them with quotes before using Python
    string interpolation to make the query that gets submitted to the
    server.

    ? ? ? ?This is why you can not use, say %d as a placeholder for a numeric
    parameter... MySQLdb will convert that numeric to a string, and then
    Python will choke when it tries to use a %d formatter and is given a
    string value.
    All of your and MRAB's comments were very helpful. However, I don't see how
    these two problems are addressed:

    ????? 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 second argument to cursor.execute needs to be a tuple. Change the
    call to this:

    cursor.execute(sql, (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))
    Lose the quotes around the %s. You are replying to a post that
    describes why this is important.

    --
    regards,
    kushal
  • Victor Subervi at May 28, 2010 at 3:39 pm
    I still have this code:

    sql = 'select * from options%s where ID=%%s', (opTable[0].upper() +
    opTable[1:])
    cursor.execute(sql, (id,))

    which throws this error:

    /var/www/html/angrynates.com/cart/enterOptionsPrices2.py
    70 print 'All options prices have been successfully updated.'
    71 print '</body>\n</html>'
    72
    73 enterOptionsPrices2()
    74
    enterOptionsPrices2 = <function enterOptionsPrices2>
    /var/www/html/angrynates.com/cart/enterOptionsPrices2.py in
    enterOptionsPrices2()
    58 id = form.getfirst('%sID' % option)
    59 sql = 'select * from options%s where ID=%%s',
    (opTable[0].upper() + opTable[1:])
    60 cursor.execute(sql, (id,))
    61 # cursor.execute('select * from options%s where ID=%s' %
    (opTable[0].upper() + opTable[1:], id))
    62 if cursor.fetchone() is not None:
    cursor = <MySQLdb.cursors.Cursor object>, cursor.execute = <bound method
    Cursor.execute of <MySQLdb.cursors.Cursor object>>, sql = ('select * from
    options%s where ID=%%s', 'Sizes'), id = '0.00'
    /usr/lib64/python2.4/site-packages/MySQLdb/cursors.py in
    execute(self=<MySQLdb.cursors.Cursor object>, query=('select * from
    options%s where ID=%%s', 'Sizes'), args=('0.00',))
    144 db = self._get_db()
    145 charset = db.character_set_name()
    146 query = query.encode(charset)
    147 if args is not None:
    148 query = query % db.literal(args)
    query = ('select * from options%s where ID=%%s', 'Sizes'), query.encode
    undefined, charset = 'latin1'

    AttributeError: 'tuple' object has no attribute 'encode'
    args = ("'tuple' object has no attribute 'encode'",)


    Please advise.
    TIA
    beno
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: <http://mail.python.org/pipermail/python-list/attachments/20100528/4c5d49cf/attachment.html>
  • MRAB at May 28, 2010 at 4:39 pm

    Victor Subervi wrote:
    I still have this code:

    sql = 'select * from options%s where ID=%%s', (opTable[0].upper()
    + opTable[1:])
    cursor.execute(sql, (id,))

    which throws this error:

    /var/www/html/angrynates.com/cart/enterOptionsPrices2.py
    <http://angrynates.com/cart/enterOptionsPrices2.py>
    70 print 'All options prices have been successfully updated.'
    71 print '</body>\n</html>'
    72
    73 enterOptionsPrices2()
    74
    enterOptionsPrices2 = <function enterOptionsPrices2>
    /var/www/html/angrynates.com/cart/enterOptionsPrices2.py
    <http://angrynates.com/cart/enterOptionsPrices2.py> in enterOptionsPrices2()
    58 id = form.getfirst('%sID' % option)
    59 sql = 'select * from options%s where ID=%%s',
    (opTable[0].upper() + opTable[1:])
    60 cursor.execute(sql, (id,))
    61 # cursor.execute('select * from options%s where ID=%s' %
    (opTable[0].upper() + opTable[1:], id))
    62 if cursor.fetchone() is not None:
    cursor = <MySQLdb.cursors.Cursor object>, cursor.execute = <bound method
    Cursor.execute of <MySQLdb.cursors.Cursor object>>, sql = ('select *
    from options%s where ID=%%s', 'Sizes'), id = '0.00'
    /usr/lib64/python2.4/site-packages/MySQLdb/cursors.py in
    execute(self=<MySQLdb.cursors.Cursor object>, query=('select * from
    options%s where ID=%%s', 'Sizes'), args=('0.00',))
    144 db = self._get_db()
    145 charset = db.character_set_name()
    146 query = query.encode(charset)
    147 if args is not None:
    148 query = query % db.literal(args)
    query = ('select * from options%s where ID=%%s', 'Sizes'), query.encode
    undefined, charset = 'latin1'

    AttributeError: 'tuple' object has no attribute 'encode'
    args = ("'tuple' object has no attribute 'encode'",)


    Please advise.
    On line 59 you're making a tuple.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedMay 27, '10 at 6:16p
activeMay 28, '10 at 4:39p
posts8
users3
websitepython.org

People

Translate

site design / logo © 2022 Grokbase