ActiveRecord user-supplied column name

Question

I am trying to allow the end-user of a rails app to limit results based on the value of an arbitrary column. At its simplest, I want to do something roughly equivalent to:

"SELECT * FROM products WHERE (#{params[:min_col]} >= #{params[:min]})"

without the injection vulnerability.

For example, example.com/myapp/catalog?min_col=sell_price&min=300 would return all products with sell_price greater than or equal to $3.00

I tried adding a scope like this to the model:

->(column,min) { where("? >= ?", column, min) }

and passing the uri parameters to that scope, but this yields

WHERE ('sell_price' >= '300')

which seems to just be comparing two literal strings -- this query and others like it always return every row or no rows. How do I get the desired behavior of comparing against a column specified in params?

Answer 1

To prevent sql injection, you should validate the column is a valid one

valid_cols = ["c1", "c2"]
valid_cols.include?(column) or raise "Bad query"

Then you can just use the query interface as before

Model.where("#{column} >= ?", min)