Sequel adds single quotes to .where

Question

I'm using Sequel with a Sinatra API and Postgres. I have a basic dataset called 'results':

results = Receipt.order(Sequel.desc(:date))

I'm trying to filter it with a hash that comes in from a JSON object like this:

filters = {item: 'mortgage',
           method: 'check'}

def get_filtered_results(results, filters)
    filters.each do |key, value|
        results = results.where('? = ?', key, value)
    end
    return results
end

The sql query that's being generated by these '.where's is:

SELECT * FROM "receipts" WHERE ('item' = 'mortgage') and ('method' = 'check') ORDER BY "date" DESC LIMIT 50 OFFSET 0;

The quotes around 'item' and 'method' are causing the query not to return any results - when I run them manually without the quotes they return the expected records. I tried passing the filter in as a hash rather than a prepared statement, same result. Is there any way to construct the query without the quotes?

Edit: When I construct the statement in pry - so

Receipt.order(Sequel.desc(:date)).where(:item => 'mortgage payment').where(:method => 'check')

I get the expected results.

Answer 1

The problem is that the values that will be replaced in the placeholders are treated as strings and not as field names. You will have to interpolate the hash key in the query. However this makes the query vulnerable to attacks, so it's a good idea you create an array of allowed filters:

filters = {item: 'mortgage',
           method: 'check'}

def get_filtered_results(results, filters)
  allowed_filters = [:item, :method]

  filters.slice(*allowed_filters).each do |key, value|
    results = results.where("#{key} = ?", value)
  end
  return results
end

The slice() method will create a new Hash containing only the keys that are in the allowed_filters array.