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.

Answer 1

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.