FAQ
Hi,

I get some problem when i like to set the table name dynamic.
I'm appreciate for any help.

Christian

### works ####
newcur.execute ( """ INSERT INTO events (id1,id2) VALUES (%s,%s);
""" , (rs[1],rs[2]))

### works not
newcur.execute ( """ INSERT INTO %s_events (id1,id2) VALUES (%s,
%s); """ , (table_name,rs[1],rs[2]))

### works but is not really perfect: None from rs list result in
"None" instead of NULL.
newcur.execute ( """ INSERT INTO %s_events (id1,id2) VALUES
('%s','%s'); """ % (table_name,rs[1],rs[2]))

Search Discussions

  • Chris Angelico at Jul 14, 2011 at 3:21 pm

    On Fri, Jul 15, 2011 at 1:00 AM, Christian wrote:
    Hi,

    I get some problem ?when i like to set the table name dynamic.
    I'm appreciate for any help.

    ### works but is not really perfect: None from rs list result in
    "None" instead of NULL.
    newcur.execute ( ?""" INSERT INTO %s_events (id1,id2) ? VALUES
    ('%s','%s'); """ ?% ?(table_name,rs[1],rs[2]))
    I'll start with the easy one. This one is wrong for several reasons;
    firstly, it converts everything to strings (which is why a None comes
    out as 'None'), but secondly and more seriously, it cannot handle
    apostrophes or backslashes in your strings. SQL engines such as MySQL
    need strings to be properly escaped, and the execute() function will
    do that for you - but the % interpolation won't.
    ### works not
    newcur.execute ( ?""" INSERT INTO %s_events (id1,id2) ? VALUES ?(%s,
    %s); """ , (table_name,rs[1],rs[2]))
    What's happening here is that the table name is being sent in
    apostrophes. Just as it puts quotes around your data, it also puts
    quotes around the table name - which you don't want. You're getting
    something like INSERT INTO 'foobar'_events, which MySQL doesn't like.

    I recommend a hybrid:
    newcur.execute ( ?""" INSERT INTO {0}_events (id1,id2) ? VALUES
    (%s,%s); """.format(table_name), (,rs[1],rs[2]))

    Note that I'm using the format() method rather than the % operator,
    specifically because it uses a different notation - {0} - and will
    leave the %s markers alone.

    This assumes that your table name is clean. If it comes from your own
    code, that's probably safe; but if it comes from user-supplied data,
    you WILL need to sanitize it (I recommend whitelisting valid
    characters eg letters and numbers, and keeping only those - and then
    imposing a length limit too) before giving it to .execute().

    As far as I know, MySQL doesn't have facilities for dynamic table
    names, so your best bet is to make the SQL statement itself dynamic,
    as per this example.

    Hope that helps!

    Chris Angelico
  • Billy Mays at Jul 14, 2011 at 3:31 pm

    On 07/14/2011 11:00 AM, Christian wrote:
    Hi,

    I get some problem when i like to set the table name dynamic.
    I'm appreciate for any help.

    Christian

    ### works ####
    newcur.execute ( """ INSERT INTO events (id1,id2) VALUES (%s,%s);
    """ , (rs[1],rs[2]))

    ### works not
    newcur.execute ( """ INSERT INTO %s_events (id1,id2) VALUES (%s,
    %s); """ , (table_name,rs[1],rs[2]))

    ### works but is not really perfect: None from rs list result in
    "None" instead of NULL.
    newcur.execute ( """ INSERT INTO %s_events (id1,id2) VALUES
    ('%s','%s'); """ % (table_name,rs[1],rs[2]))
    You shouldn't use The bottom form at all since that is how injection
    attacks occur.

    The reason the second version doesn't work is because the the execute
    command escapes all of the arguments before replacing them. Example:

    sql = """SELECT * FROM table WHERE col = %s;"""
    cur.execute(sql, ('name',))
    # The actual sql statement that gets executed is:
    # SELECT * FROM table WHERE col = 'name';
    # Notice the single quotes.

    --
    Bill
  • Christian at Jul 14, 2011 at 6:10 pm

    On 14 Jul., 17:31, Billy Mays wrote:
    On 07/14/2011 11:00 AM, Christian wrote:








    Hi,
    I get some problem ?when i like to set the table name dynamic.
    I'm appreciate for any help.
    Christian
    ### works ####
    newcur.execute ( ?""" INSERT INTO events (id1,id2) ? VALUES ?(%s,%s);
    """ , (rs[1],rs[2]))
    ### works not
    newcur.execute ( ?""" INSERT INTO %s_events (id1,id2) ? VALUES ?(%s,
    %s); """ , (table_name,rs[1],rs[2]))
    ### works but is not really perfect: None from rs list result in
    "None" instead of NULL.
    newcur.execute ( ?""" INSERT INTO %s_events (id1,id2) ? VALUES
    ('%s','%s'); """ ?% ?(table_name,rs[1],rs[2]))
    You shouldn't use The bottom form at all since that is how injection
    attacks occur.

    The reason the second version doesn't work is because the the execute
    command escapes all of the arguments before replacing them. ?Example:

    sql = """SELECT * FROM table WHERE col = %s;"""
    cur.execute(sql, ('name',))
    # The actual sql statement that gets executed is:
    # SELECT * FROM table WHERE col = 'name';
    # Notice the single quotes.

    --
    Bill
    thanks you guys!

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedJul 14, '11 at 3:00p
activeJul 14, '11 at 6:10p
posts4
users3
websitepython.org

People

Translate

site design / logo © 2022 Grokbase