Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

ActiveRecord problem with sanitizing array

#2818

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:
@@@ruby
["... 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:
@@@sql
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:
@@@ruby

    # 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) }
      else
        statement
      end
    end

Reported by bterkuile · June 19th, 2009 @ 02:32 PM

State: stale
Milestone: 3.x
Assigned to: nobody
Importance: none

Activity

  1. Jeremy Kemper
    Jeremy Kemper
    • Milestone changed from 2.x to 3.x

    May 4th, 2010 @ 06:48 PM

  2. Rohit Arondekar
    Rohit Arondekar
    • State changed from new to stale
    • Importance changed from to

    Marking ticket as stale. If this is still an issue please leave a comment with suggested changes, creating a patch with tests, rebasing an existing patch or just confirming the issue on a latest release or master/branches.

    October 9th, 2010 @ 04:03 AM

  3. Ryan Bigg
    Ryan Bigg
    • Tag cleared.

    Automatic cleanup of spam.

    October 11th, 2010 @ 10:53 AM

  4. Jeff Kreeftmeijer