How can I sort ActiveRecord Objects by id array

Question

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.

What is stored in a? I think you might be looking for Foo.where(id: ids).order('id ASC'). See all about querying and ordering in the Rails Guides on the ActiveRecord Query Interface. — Thomas Klemm
– Thomas Klemm, Commented Jun 2, 2013 at 8:40
Thank you for your comment. "a" is mistake for "ids". I edited the mistakes. then I want to do is not "ORDER BY id ASC" but "ORDER BY FIELD(id, 1, 4, 2, 3)". — Hisatake Ishibashi
– Hisatake Ishibashi, Commented Jun 2, 2013 at 8:44

Mitsun Chieh · Accepted Answer · 2016-04-27 07:52:07Z

9

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.

edited Apr 27, 2016 at 7:52

Mitsun Chieh

415 bronze badges

answered Jun 2, 2013 at 9:21

basgys

4,40331 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

andyroberts · Accepted Answer · 2021-03-24 10:36:27Z

0

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

answered Mar 24, 2021 at 10:36

andyroberts

3,5182 gold badges40 silver badges40 bronze badges

Collectives™ on Stack Overflow

How can I sort ActiveRecord Objects by id array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related