Scope and Field as Parameter in Rails

I've added a scope to a Rails model that allows for searching based on a specified parameter field using a range. Here is what it looks like:

scope :upcoming, lambda { |field| 
  where("to_char(#{field}, 'DDD') BETWEEN :alpha AND :omega", 
    alpha: Time.now.advance(days: 4).strftime('%j'),
    omega: Time.now.advance(days: 8).strftime('%j'),
  )
}

Event.upcoming(:registration) # Query all events with registration shortly.
Event.upcoming(:completion)   # Query all events with completion shortly.

The a开发者_运维百科bove works fine, however in creating I read the Ruby on Rails Guides and found the following:

Putting the variable directly into the conditions string will pass the variable to the database as-is. This means that it will be an unescaped variable directly from a user who may have malicious intent. If you do this, you put your entire database at risk because once a user finds out he or she can exploit your database they can do just about anything to it. Never ever put your arguments directly inside the conditions string.

Although the scope is currently never called with a user parameter, I am curious if a way exists of setting the field without using the interpolation in order to better conform with the above recommendation. I've tried using another named parameter, however this will escape the field with quotes (and thus cause it to fail). Any ideas?

I would recommend validating the field parameter against the model's attributes, essentially using the model as a whitelist for values that are allowed to be passed. Something like this:

scope :upcoming, lambda { |field|    
  if column_names.include? field.to_s
    where("to_char(#{field}, 'DDD') BETWEEN :alpha AND :omega", 
      alpha: Time.now.advance(days: 4).strftime('%j'),
      omega: Time.now.advance(days: 8).strftime('%j'),
    )
  else
    # throw some error or return nil
  end
}

Okay, reading all the way to the end might help(thanks rubyprince). It looks like you are doing a between query on a field that is storing a date in Oracle. The problem is that to_char is looking for a variable, not a string. And the act of escaping a variable in rails turns it into a string. So, in this particular case you might convert :alpha and :omega into the format of the value stored in field. That way you can escape field in a straightforward manner. Of course there is the issue with Oracle treating dates as Time. I'm guessing that is why you converted to day-of-year for the compare. If you are using the Oracle Enhanced Adaptor you can set

self.emulate_dates_by_column_name = true

to make sure that the field is treated like a date. Then use the to_date function(which takes a string) with :alpha and :omega

scope :upcoming, lambda { |field| 
  where(":field BETWEEN to_date(:alpha,'yyyy/mm/dd') AND to_date(:omega,'yyyy/mm/dd')",
    field: field,
    alpha: Time.now.advance(days: 4).strftime('%Y/%m/%d'),
    omega: Time.now.advance(days: 8).strftime('%Y/%m/%d'),
  )
}

I have no way of testing this so I might be off in the weeds here.

Validating user input as per Jordan is always a good idea.