String formatting - mysql insert

[Billy Mays]

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