0

I have a list of integer ID's that correspond to rows in the database, and I need to get out the specific rows for a specific set of ID's. My query would be as follows:

SELECT id, name FROM locations WHERE ID in (1,4,5,7,8,...)

I could do this by imploding the list into a comma separated string, but this would not be safe from SQL injection. Is there a way, using prepared statements, to bind a list of integers?

If not, would a suitable workaround be to check that each item in the list is numeric, and only then proceeding with the query?

Alex Blundell
  • 2,084
  • 5
  • 22
  • 31
  • Where does your list of integers come from? – Aya May 31 '15 at 12:30
  • Through a post variable from the web. It would just end up being converted to a list of int's in Python. – Alex Blundell May 31 '15 at 12:32
  • In that case, I'd probably just use your suggested workaround, i.e. sanitize the list to ensure they're all valid integers, and implode into the query. – Aya May 31 '15 at 12:35
  • You may find http://stackoverflow.com/q/4574609/190597 helpful. It has examples of how to use string formatting to make a *parametrized* sql query. The parametrized query will help protect against sql injection. – unutbu May 31 '15 at 12:35
  • Note that your MySQL database adapter may use `%s` instead of `?` as a SQL parameter placeholder, but otherwise the duplicate applies in equal measure to MySQL as it does to SQLite. – Martijn Pieters May 31 '15 at 12:40
  • I'm not sure that this is a duplicate question.. I understand how to create the where..in.. statement, the query was really relating to the safety of doing this, and whether prepared statements could be used natively for this type of query. – Alex Blundell May 31 '15 at 12:42
  • With regards to parameterizing, it's only really necessary for strings. The net effect is that each string in the list is passed through `mysql_real_escape_string()` which, IMO, is an unnecessary overhead if you're certain that each item in your list is a valid integer. – Aya May 31 '15 at 12:48
  • @AlexBlundell: the post there *produces a parameterised query*; because the number of parameters can easily vary, it is not as likely that the database can re-use the prepared statements for such queries. It also offers an alternative; use a temporary table to hold the possible values, and use a `JOIN`. That way you can re-use the same statement. – Martijn Pieters May 31 '15 at 13:49

1 Answers1

0

As far as i know there s no way in mysql to do this. You could implode list of '?' by length of locations list, and then bind parameters as you do for normal prepared statement.

Dmitry V.
  • 336
  • 4
  • 11
  • For other rdbms, e.g. oracle, you can pass ARRAY as in parameter to stored procedure call. But tbh there is some hoops jumping involved as well. – Dmitry V. May 31 '15 at 12:34