'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.

Put () around the query param, so (?). IIRC you can also just write where(position_id: approval_id). Unrelated, but I would name the variable to reflect it's a collection, approval_ids. — Dave Newton
– Dave Newton, Commented Mar 17, 2022 at 16:15

Schwern · Accepted Answer · 2022-03-17 17:26:24Z

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.

It is bizarre that the Rails Guides do not mention this guides.rubyonrails.org/…
@DavidAldridge They do under the obscure name "subset conditions"; I linked to it. "Array conditions" refers to passing multiple, distinct bind values as an Array. It's confusing.

ddd · Accepted Answer · 2022-03-17 18:45:34Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related