Prevent SQL injection for array ( xxx IN (y1, y2, y3, ...) )

I'm using raw SQL code in Ruby on Rails. For preventing SQL injections I use e.g.

... WHERE name = :name

However, now I have an array of strings, which are user-set filters for retrieving data from the database. I need to escape this as well, but am not sure how to approach it. The current (vulnerable) code is (where filter[:values] is the array of user-set filters)

AND answer_value IN (#{filter[:values].join(',')}))

I was thinking of creating a string_array like this, for the amount of present filters:

escaped_filters = [
  "filter_1",
  "filter_2",
  "filter_3"
]

have the code use that:

AND answer_value IN (#{escaped_filters.join(',')}))

and somehow get those strings replaced by the actual filters. I'm not getting further than this, but could use an honest opinion of what is the actual right approach for this. Even if this might work, is it actually escaping injections?

Thanks!

Solution 1:

If you really did need to create a SQL string the correct way is to create a number of placeholders equal to the number of elements in the array:

where(
  "answer_value IN (#{ Array.new(filter[:values].length, '?').join(',') })",
  *filter[:values]
)

This is safe from a SQL injection as the user input is not used to create the SQL string and the bound parameters are escaped by the db adapter.

However you most likely don't even need it since ActiveRecord will automatically turn arrays into WHERE IN (?, ?, ?, ...) when you use the high level query interface:

where(
  answer_value: filter[:values]
)