This project is archived and is in readonly mode.

#4343 ✓resolved

Date values are not properly escaped in SQL output

Reported by bluecat | April 8th, 2010 @ 10:46 AM

The following problem occurs using SQLite3 as well as MySQL database!

I have following search statement:
events = CalendarEvent.find :all, :conditions => { :target_date =>
begin_date..end_date }

which produces following console output:
SELECT "calendar_events".* FROM "calendar_events" WHERE
("calendar_events"."target_date" BETWEEN 2010-03-29 AND 2010-05-03)

The result is WRONG, because i.e. sqlite treats 2010-03-29 as number of the
value 1978!

If I write following instead:
events = CalendarEvent.find :all, :conditions => ["target_date
BETWEEN ? AND ?", begin_date, end_date]

I get the right result, which is:
SELECT "calendar_events".* FROM "calendar_events" WHERE
(target_date BETWEEN '2010-03-29' AND '2010-05-02')

Is there another way to specify the needed format? I already checked,
that begin_date and end_date are truly Date values. If I use,3,29) f.i. the result is exactly the same.

Comments and changes to this ticket

  • bluecat

    bluecat April 8th, 2010 @ 10:54 AM

    • no changes were found...
  • keeran

    keeran April 11th, 2010 @ 05:22 PM

    I tried the following against Rails 3 (master)

    OrderItem.where(:updated_at =>


    OrderItem.find(:all, :conditions => {:updated_at =>}) in 2.3.4

    Both cases returned correctly escaped dates.

    Should this be covered in activerecord/test/cases/sanitize_test perhaps? What version of rails are you using?

  • Gaël Deest

    Gaël Deest April 11th, 2010 @ 07:39 PM

    I cannot reproduce on 2.3.5 either.

  • Ryan Bigg

    Ryan Bigg April 11th, 2010 @ 10:50 PM

    What types are begin_date and end_date?

  • Santiago Pastorino

    Santiago Pastorino April 12th, 2010 @ 12:30 AM

    • State changed from “new” to “invalid”

    Can't reproduce getting this output SELECT "calendar_events".* FROM "calendar_events" WHERE ("calendar_events"."target_date" BETWEEN '2010-04-11' AND '2010-04-11') on the latest master.

    Which rails version are you using? you should test with rails from git, thanks for helping.

  • carlsverre

    carlsverre April 12th, 2010 @ 02:56 AM

    I reproduced this bug using rails3-beta1 and ruby 1.8.7, but it seems to have been resolved as of rails3-beta2.

    @OP, what version of ruby/rails are you using?

    Below is the schema and test:


      create_table "tasks", :force => true do |t|     "start"
    t.datetime "created_at"
    t.datetime "updated_at"


      test "escape date properly" do

    (1..5).each do |i|
      Task.create!(:start => + i.days)
    tasks = Task.find(:all, :conditions => {:start =>})
    assert_operator tasks.length, :>=, 2

    Note: This test is not perfect. It simply takes advantage of the fact that when you execute the sql between without quoted dates Active Record returns an empty set.

  • Santiago Pastorino

    Santiago Pastorino April 12th, 2010 @ 03:07 AM

    This was an arel bug an is already fixed please try to reproduce things in the Rails master version and with the last arel

  • bluecat

    bluecat April 12th, 2010 @ 09:06 AM

    I'm using rails3.0.0-beta (presumably beta1) as stated in the release notes plus ruby 1.9.1p376.

    Will try to update to rails from git and test again, thanks!

  • bluecat

    bluecat April 12th, 2010 @ 10:03 AM

    Ok, I checked with latest beta (Rails3.0.0.beta2) Arel 0.3.3
    and now it works as expected.
    The bug is RESOLVED! (I'm not able to change status)

  • Ryan Bigg

    Ryan Bigg April 12th, 2010 @ 10:58 AM

    • State changed from “invalid” to “resolved”

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>