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.

There was a problem

You must be a member of this account.

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