mysql insert with tuple

[Chris Rebert]

On Wed, Nov 21, 2012 at 9:19 AM, Christian <mining.facts at gmail.com> wrote:
> Hi ,
>
> my purpose is a generic insert via  tuple , because the number of fields and can differ. But  I'm stucking .
>
> ilist=['hello',None,7,None,None]
>
> #This version works, but all varchar fields are in extra '' enclosed.
> con.execute(""" INSERT INTO {} VALUES %r; """.format(table) , (tuple(ilist),))

A. "%r" is not a valid SQL/MySQLdb parameter specification (nor part
of Python's format() mini-language).
B. You don't need to enclose `ilist` in a singleton tuple. (Or convert
it to a tuple, for that matter.)

This should work:
# assuming `table` isn't obtained from untrusted input...
paramspec = ",".join(["%s"] * len(ilist))
con.execute("""INSERT INTO {} VALUES {};""".format(table, paramspec) , ilist)

But really, I would recommend instead using a more abstract database
library (e.g. SQLAlchemy, SQLObject, etc.), which safely and
conveniently constructs the SQL strings for you.

> #This produce (1054, "Unknown column 'None' in 'field list'"),
> #but without None values it works.
> con.execute(""" INSERT INTO {} VALUES %r; """.format(table) % (tuple(ilist),))

This is an SQL injection (http://en.wikipedia.org/wiki/SQL_injection )
waiting to happen!

Regards,
Chris

P.S. Where's my mining fact? ;-)