String formatting - mysql insert

[Chris Angelico]

On Fri, Jul 15, 2011 at 1:00 AM, Christian <ozric at web.de> 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