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

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.

CodePudding user response：

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.

CodePudding user response：

I'd use Arel instead of strings:

people_table = People.arel_table

People
 .select(:people_id)
 .where(person_id: id)
 .where(people_table[:position_id].in(approval_id))
 .where(
   people_table[:ended_on].eq(nil).or(
     people_table[:ended_on].gt(Date.today)
   )
 ).distinct