CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4mysql2
Hisatake Ishibashi
Hisatake Ishibashi

Reputation: 155

How can I sort ActiveRecord Objects by id array

My Env.

ruby 2.0.0-p195
rails (4.0.0.rc1)
activerecord (4.0.0.rc1)

I want to sort ActiveRecord Objects by id array. I tried with order by field.

ids = [1,4,2,3]
Foo.where(id: ids).order('FIELD(id, ?)', ids)

However it's failed.

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?,?}), ö, ÷, ï, ñ' at line 1:

Then I try 

ids = [1,4,2,3]
Foo.where(id: ids).order('FIELD(id, #{ids.join(","))')

it's of course success. However I'm afraid that it may have SQL Injection risk because Array ids are generated from session value.

Is there any better and secure ways?

Thanks in advance.

Upvotes: 1

Views: 4333

Answers (2)

andyroberts
andyroberts

Reputation: 3518

I tried this answer using the FIELD method on Rails6 but was encountering errors. However, I discovered that all one has to do is wrap the sql in Arel.sql().

# Make sure it's a known-safe values.
user_ids = [3, 2, 1]
# Before 
users = User.where(id: user_ids).order("FIELD(id, 2, 3, 1)")
# With warning.

# After 
users = User.where(id: user_ids).order(Arel.sql("FIELD(id, 2, 3, 1)"))
# No warning

[1] https://medium.com/@mitsun.chieh/activerecord-relation-with-raw-sql-argument-returns-a-warning-exception-raising-8999f1b9898a

Upvotes: 0

basgys
basgys

Reputation: 4400

You are right it is a security hole.

I see two possibility:

Convert to numbers

ids = [1,4,2,3].map(&:to_i).compact
Foo.where(id: ids).order("FIELD(id, #{ids.join(',')})")

I think this should be secure because you ensure that values are numbers and then you remove nil entries. nil entries would be non-numbers entries.

Escape string

escaped_ids = ActiveRecord::Base::sanitize(ids.join(","))
Foo.where(id: ids).order("FIELD(id, #{escaped_ids})")

You just escape the content of your string... Nothing special.

Upvotes: 9

Related Questions