Passing array to Rails where method

Question

So I want to dynamically pass filter parameters to my where method so basically I have this

@colleges = College.where(@filter).order(@sort_by).paginate(:page => params[:page], :per_page => 20)

And the @where is just a string built with this method

def get_filter_parameters
if params[:action] == 'index'
    table = 'colleges'
    columns = College.column_names
else
    table = 'housings'
    columns = Housing.column_names
end

filters = params.except(:controller, :action, :id, :sort_by, :order, :page, :college_id)
filter_keys = columns & filters.keys

@filter = ""
first = true

if filter_keys
    filter_keys.each do |f|
        if first
            @filter << "#{table}.#{f} = '#{filters[f]}'"
            first = false
        else
            @filter << " AND #{table}.#{f} = '#{filters[f]}'"
        end
    end
else
    @filter = "1=1"
end

The problem is I don't know how good it is to drop raw SQL into a where method like that. I know normally you can do stuff like :state => 'PA', but how do I do that dynamically?

UPDATE

Okay so I am now passing a hash and have this:

if params[:action] == 'index'
    columns = College.column_names
else
    columns = Housing.column_names
end

filters = params.except(:controller, :action, :id, :sort_by, :order, :page, :college_id)
filter_keys = columns & filters.keys

@filter = {}

if filter_keys
    filter_keys.each do |f|
        @filter[f] = filters[f]
    end
end

Will that be enough to protect against injection?

Alex Wayne · Accepted Answer · 2012-10-16 03:05:13Z

4

in this code here:

College.where(:state => 'PA')

We are actually passing in a hash object. Meaning this is equivalent.

filter = { :state => 'PA' }
College.where(filter)

So you can build this hash object instead of a string:

table = "colleges"
field = "state"
value = "PA"

filter = {}
filter["#{table}.#{field}"] = value
filter["whatever"] = 'omg'

College.where(filter)

However, BE CAREFUL WITH THIS!

Depending on where this info is coming from, you be opening yourself up to SQL injection attacks by putting user provided strings into the fields names of your queries. When used properly, Rails will sanitize the values in your query. However, usually the column names are fixed by the application code and dont need to be sanitized. So you may be bypassing a layer of SQL injection protection by doing it this way.

edited Oct 16, 2012 at 3:05

answered Oct 15, 2012 at 23:26

Alex Wayne

188k52 gold badges328 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nikorablin Over a year ago

Yeah, that is what I was afraid of because these are just parameters in the url and I'm guessing someone could inject code in the URL. See I thought to check the filter key against the columns, but that doesn't protect against what the value is.

Alex Wayne Over a year ago

Rails protects the values for you when you query with the hash form. Perhaps you could loop through the columns on a model, and pull out param names that match those columns. That way any bogus field names are simply ignored.

Collectives™ on Stack Overflow

Passing array to Rails where method

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related