CODEWITHSUNDEEP
sqlruby-on-rails-3.1
user1585163
user1585163

Reputation: 21

Rails 3.1 where clause adds quotes to sql IN clause

I spent a whole day on this.
I store permissions in one field of a postgresql db. Here the value is "roseburg,kfc,kcpl" I want to split that and make the query select * from engines where plant_id in ('roseburg', 'kfc', 'kcpl') 

def fnc_user_permissions <br/>
  temp_var = current_user.permissions.split(",")

  sql_perms = String.new
  # "perm1", "perm2", "perm3"

  temp_var.each do |pm| 

   sql_perms += (sql_perms.blank? ? pm :  "', '" + pm)        

  end      

  @engines = Engine.where("plant_id in (:plant_id)", {:plant_id => sql_perms })
  @engines = @engines.order("name")          

end

"Engine.where" adds a second, "single quote" so instead of getting

select * from engines where plant_id in ('roseburg', 'kfc', 'kcpl')

I get

select * from engines where plant_id in ('roseburg'', ''kfc'', ''kcpl')

If I edit the each loop to this

sql_perms += (sql_perms.blank? ? pm :  ", " + pm)

I get 

select * from engines where plant_id in ('roseburg, kfc, kcpl')

I finally had to do this:

      temp_var = current_user.permissions.split(",")

  sql_perms = String.new
  # "perm1", "perm2", "perm3"

  temp_var.each do |pm| 

   sql_perms += (sql_perms.blank? ? pm :  "', '" + pm)        

  @engines = Engine.find_by_sql("select * from engines where plant_id in ('#{sql_perms}') ")

It works but I would rather use the .where code Any ideas on how to make .where stop adding a second quote that I don't want? It is so Microsoft!.

Teedub

Upvotes: 0

Views: 419

Answers (2)

katzmopolitan
katzmopolitan

Reputation: 1391

Or, an SQL-injection safe version of this would be

Engine.where("plant_id in (?)", temp_var)

Upvotes: 1

chifung7
chifung7

Reputation: 2621

You should use '?' to bind values to sql statement. Try

Engine.where("plant_id in (#{ Array.new(temp_var.size, '?').join(',') })", *temp_var)

Upvotes: 0

Related Questions