'Where' and 'IN' in ruby on rails to avoid SQL injection

Question

I am changing an existing query to avoid SQL injection.The query goes like this

People.select('DISTINCT people_id')
      .where(person_id: id)
      .where("position_id IN (#{approval_id.join(', ')})")
      .where('ended_on IS NULL or ended_on > ?', Date.today)

where approval_id is array with value [1, 2, 3, 4]

when I am changing the query line 3

.where("position_id IN (#{approval_id.join(', ')})") to
.where("position_id IN ?", approval_id)

It is not working. what is going wrong? as approval_id is an array I can pass it directly to an IN.

Answer 1

Pass in an array and Rails will convert it to an in query.

People
  .select('DISTINCT people_id')
  .where(
    person_id: id,
    position_id: approval_id, # approval_ids?
  )
  .where("ended_on is null or ended_on > ?", Date.today)

nil will be converted to is null and you can use and and or to keep this entirely within ActiveRecord.

People
  .select('DISTINCT people_id')
  .where(
    person_id: id,
    position_id: approval_id, # approval_ids?
  )
  .and(
    People
      .where(ended_on: nil)
      .or(People.where(ended_on > ?", Date.today)
  )

Though this is arguably more complicated in this query, it's useful to know for others.