Constructing sql in ruby on rails

Question

The following SQL I am trying to run is returning sql_string of "SELECT id FROM people WHERE id IN ("16")":

@ids = ["1", "6"]

sql_string = <<-SQL
  SELECT id
  FROM people
  WHERE id IN ("#{@ids}")
SQL

Can someone please help modify the above query so it will create the sql_string of "SELECT id FROM people WHERE id IN (1, 6)"

Answer 1

Just throwing @ids in the query will concatenate the array and give you "16". You'll want to run @ids.join(',') to comma separate them. Plus you need to wrap the expression part of the string in #{}. Otherwise it will treat it as literal.

@ids = ["1", "6"]

sql_string = <<-SQL
  SELECT id
  FROM people
  WHERE id IN (#{@ids.join(',')})
SQL

P.S. There are very few valid reasons for manually writing a whole SQL query in Rails. You should look into using ActiveRecord to do something like People.find_all_by_id(@ids) instead.

Answer 2

With the code fragment in one of the answers above, "@ids" is not sanitised. This is fine if your code 'knows' that "@ids" contains only valid integer IDs, but very dangerous if any ID came in from user input or a URL. See:

• http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M001831

...for a possible solution. This is a protected method so we have to call via 'send' to demonstrate its use at the console:

>> ActiveRecord::Base.send(:sanitize_sql_for_conditions, { :id => [1,6] }, :people)
=> "people.\"id\" IN (1,6)"

...i.e. insert the above result after the SQL WHERE keyword. As the previous answer says, unless you have a really complex case which can't be built up using standard Rails calls (which is indeed the case for Coderama but may not be for future readers), you should always try to avoid writing SQL by hand.

Bearing this in mind, an alternative way to build up complex queries is the "ez_where" plugin which is worth a look if anyone reading is thinking of resorting to SQL:

• http://github.com/ezmobius/ez-where