3

I'm running a dynamic MySQL query from Python (using MySQLDb) which includes an "in list" clause that contains string values. The function which executes this gets an array of the values. I could make that array into a tuple or any other kind of collection if it helps.

What's the "best" way to insert this list? Keep in mind that single quotes and commas etc are needed. Here's an ugly, but safe manual approach:

inList = ""
for stringValue in someArray:
    if inList != "" : inList += ","
    inList += "'%s'" % stringValue
querystr = "SELECT * FROM some_tbl WHERE some_column IN( %s );" % (inList)

Alternatively, here's another option. It's shorter, but relies on the array to string representation remaining exactly the same in the future:

inList = str(someArray).replace("[", "").replace("]", "")   
querystr = "SELECT * FROM some_tbl WHERE some_column IN( %s );" % (inList)

EDIT

I think my Python terminology was wrong when I wrote this. I should be saying "list" not "array".

BuvinJ
  • 10,221
  • 5
  • 83
  • 96
  • 1
    exactly how is that safe? it's a wide open sql injection vulnerability. – Marc B Jan 13 '16 at 15:24
  • Please explain. Does that not assume someone can define the array as they like? How else does one generally build dynamic sql statements? – BuvinJ Jan 13 '16 at 15:27
  • it's not the building dynamic sql that's the problem, it's building it in an UNSAFE manner. – Marc B Jan 13 '16 at 15:29
  • By "safe" I meant "reliably functional", btw... – BuvinJ Jan 13 '16 at 15:29
  • well, consider doing a list of names: `Miles O'Brien`, which then becomes `where name in (Miles O'Brien)`. oops. syntax error, query dead, "totally reliable, dude". – Marc B Jan 13 '16 at 15:30
  • Please tell me the "safe" way then. – BuvinJ Jan 13 '16 at 15:30
  • go read up and learn about sql injection attacks. until you're aware of the problem, you shouldn't be working with database (and dynamic query building). – Marc B Jan 13 '16 at 15:30
  • Thank you for your concern. Answering the question wold be more productive than simply being insulting. – BuvinJ Jan 13 '16 at 15:33

1 Answers1

5

There is really no good way to make a dynamic query of this kind safe. You should switch to a parametrized query, in which case the solution is:

placeholder = '%s'
param_subs = ','.join((placeholder,) * len(param_list))
sql = 'SELECT col1, col2, . . . FROM Table WHERE Column IN ( %s );' % param_subs
cursor.execute(sql, param_list)

(This assumes you're using MySQL Connector which, unfortunately, uses %s as a placeholder. Other Python libraries tend to use ? as the placeholder.)

xmedeko
  • 7,336
  • 6
  • 55
  • 85
Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • That looks like a good approach. Just to be clear, by "safe" are you referring to the prevention of bad chars, or sql injection (as Mark B pointed out), or something else? I'm regretting using that "safe" term in my question... – BuvinJ Jan 13 '16 at 15:40
  • I'm referring to both because both arise from the same issue -- we cannot know at the time of writing the code the possible universe of values that will be supplied in the future as parameters. – Larry Lustig Jan 13 '16 at 15:44
  • Also `itertools.repeat` may be used instead of `(placeholder,) * len(...)`. – xmedeko Sep 22 '16 at 07:04