This project is archived and is in readonly mode.

#2818 ✓stale

ActiveRecord problem with sanitizing array

Reported by bterkuile | June 19th, 2009 @ 02:32 PM | in 3.x

I try to create an ActiveRecord extension to be able to do smarter date selections. These selections can have the form:
@@@ruby MyModel.all(:conditions=>{:date_month => [2,6,8]})
MyModel.all(:conditions=>{:date_month => 3..7})

where date is a DateTime field. This causes problems. The array will be translated in the form:
["... IN (?)", [2,6,8]]
And for the range type it idealy should become:
@@@ruby ["... BETWEEN ? AND ?", 3, 7]
This way is incompatible with the ar-extensions since in this case a Result Struct is generated that can only contain one value. This value should be passed with a * for the Range and just as one variable, to delever the sql arrays as described above. I did not see a clean way to improve that in that module without dirty tricks that may be specific for this one case.

A solution is to create a completed SQL statement without question marks, this would look like: @@@ruby ["... BETWEEN 3 AND 7"]
In the method sanitize_sql_array in the file: activerecor-2.3.2/lib/active_record/base.rb (in this case starting from line 2337) this will result in:
@@@ruby statement, *values = ary #=> statement = "... BETWEEN 3 AND 7", values = []

This should not be a problem, but now the special case. Since sqlite does not have a month function I use the strftime function, which would look like:
CAST(STRFTIME("%m", "table"."date") AS INTEGER) BETWEEN 3 AND 7

This will fail in the else statement because of the % function. A solution would be:

    # Accepts an array of conditions.  The array has each value
    # sanitized and interpolated into the SQL statement.
    #   ["name='%s' and group_id='%s'", "foo'bar", 4]  returns  "name='foo''bar' and group_id='4'"
    def sanitize_sql_array(ary)
      statement, *values = ary
      if values.first.is_a?(Hash) and statement =~ /:\w+/
        replace_named_bind_variables(statement, values.first)
      elsif statement.include?('?')
        replace_bind_variables(statement, values)
      elsif values.any?
        statement % values.collect { |value| connection.quote_string(value.to_s) }

Comments and changes to this ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>