CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
andreimarinescu
andreimarinescu

Reputation: 3641

Escaping strings in ActiveRecord where using prepared statements isn't possible

I'm writing a complex ActiveRecord query that passes an ActivRecord::Relation in a pipeline of filtering functions. One of them has to add a sequence of "OR" linked conditions. As ActiveRecord::Relation does not provide the #or method I'm writing an SQL string to pass into #where. This string is generated dynamically, so I can't really use ? for values. I know from previous questions that using prepared statements is the best practice method of creating queries, but I can't find a way to apply it here.

Is there a way of escaping the strings, or converting to using prepared statements?

Edit: I'm doing something like this (the method receives items as an ActiveRecord::Relation object.

where_string = '';
filters.each do |field,value|
  if(Item.column_names.include? field)
    where_string += "OR" if where_string!=''
    where_string += " #{field} LIKE '%#{value.to_s}%'"
   end
return items.where(where_string)

Upvotes: 0

Views: 250

Answers (2)

Benno
Benno

Reputation: 99

Here's how I would do it:

def filters_to_query(filters)
  filters.inject(['']) do |query, field, value|
    if Item.column_names.includes? field
      query[0] << ' OR ' unless query[0].blank?
      query[0] << "#{field} LIKE ?"
      query << value
    end
  end
end

Item.where(filters_to_query filters)

Upvotes: 1

user2801
user2801

Reputation: 314

Try using the below 

 query_array = []
 value_array = []
 filters.each do |field,value|
   if(Item.column_names.include? field)
     query_array << "#{field} LIKE ?"
     value_array << "%#{value.to_s}%"
   end
 end
 value_array.insert(0, query_array.join(' OR '))
 return Item.where(value_array)

Upvotes: 1

Related Questions