Dynamically created queries

Rails
#1

Hello,

I am having difficulty dynamically building a query. I want to ensure
that I’m using Active Record protection against SQL injection attacks.

In PHP land, I would have built up the query in my logic & attempted to
clean every variable - a bit tedious really.

I want to be able to achieve something like:

events = Event.find(:all,
:conditions => [DynamicallyBuiltQuery ,
DynamicllyBuiltListOfVariables],
:order => event_datetime_start)

It would be great if someone could please enlighten me on the rails way,
or if there is a better way to do this.

rgds,

  • matt.

This is roughly how I’ve mangle rails as I would have done it in PHP

  event = Event.new(params[:event])

  strQuery = ""

  unless event.event_name.blank
    strQuery = "UPPER(event_name) LIKE UPPER(" +

event.event_name.to_s + ") "
end

  unless event.artist.blank
    strQuery += sql_and(strQuery)
    strQuery += "UPPER(artist) LIKE UPPER(" + event.artist.to_s + ")

"
end

  unless event.city.blank
    strQuery += sql_and(strQuery)
    strQuery += "UPPER(city) LIKE UPPER(" + event.city.to_s + ") "
  end

  strQuery += sql_and(strQuery)
  strQuery += "event_datetime_start >= '" +

event.event_datetime_start "’) AND "
strQuery += “event_datetime_end <= '” + event.event_datetime_end
"’) "

  events = Event.find(:all, :conditions [strQuery])

Which could produce…

events = Event.find(:all,
:conditions => [“UPPER(event_name) LIKE UPPER(event_name) AND
event_datetime_start >= ‘some-date’ AND event_datetime_end <=
‘another-date’”],
:order => event_datetime_start)

or

events = Event.find(:all,
:conditions => [“UPPER(event_name) LIKE UPPER(event_name) AND
UPPER(artist) LIKE UPPER(artist) AND event_datetime_start >=
‘some-date’ AND event_datetime_end <= ‘another-date’”],
:order => event_datetime_start)

this is what I want to produce…

events = Event.find(:all,
:conditions => [“event_name LIKE ? AND event_datetime_start >= ? AND
event_datetime_end <= ?” , event_name, some-date, another-date],
:order => event_datetime_start)

or

events = Event.find(:all,
:conditions => ["event_name LIKE ? and Artist LIKE ? AND
event_datetime_start >= ? AND event_datetime_end <= ? , event_name,
artist, some-date, another-date],
:order => event_datetime_start)

#2

On Monday, August 07, 2006, at 12:41 PM, Matt S. wrote:

events = Event.find(:all,

 end
 end

Which could produce…
:conditions => ["UPPER(event_name) LIKE UPPER(event_name) AND
:order => event_datetime_start)

–
Posted via http://www.ruby-forum.com/.

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

I’ve been experimenting with using the ez_where plugin to do this.
It works pretty well but there are still a few things I need to work
out.

Your search would look something like this.

events = Event.find_where(:all,:order => event_datetime_start) do
|events|
event_name =~ params[:event][:event_name] if
params[:event][:event_name]
artist =~ params[:event][:event.artist] if
params[:event][:event.artist]
city =~ params[:event][:event.city] if params[:event][:event.city]
event_datetime_start >= params[:event][:event_datetime_start] if
params[:event][:event_datetime_start]
event_datetime_end <= params[:event][:event_datetime_end] if
params[:event][:event_datetime_end]
end

You may be able to do without the if statements. If things like
‘params[:event][:event_datetime_end]’ evaluate to nil instead of
throwing errors because you are trying to call ‘[] on a nil’, then I
think you can omit the ‘if’ clauses and ez_where will just ignore the
condition if it evaluates to nil.

This is all from memory, so YMMV. I haven’t had any coffee yet this
morning.

_Kevin
www.sciwerks.com

#3

Kevin,

Thanks - that’s exactly what I was looking for.

Seems much cleaner & less code.

rgds,

  • matt.

Kevin O. wrote:

On Monday, August 07, 2006, at 12:41 PM, Matt S. wrote:

events = Event.find(:all,

 end
 end

Which could produce…
:conditions => ["UPPER(event_name) LIKE UPPER(event_name) AND
:order => event_datetime_start)

–
Posted via http://www.ruby-forum.com/.

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

I’ve been experimenting with using the ez_where plugin to do this.
It works pretty well but there are still a few things I need to work
out.

Your search would look something like this.

events = Event.find_where(:all,:order => event_datetime_start) do
|events|
event_name =~ params[:event][:event_name] if
params[:event][:event_name]
artist =~ params[:event][:event.artist] if
params[:event][:event.artist]
city =~ params[:event][:event.city] if params[:event][:event.city]
event_datetime_start >= params[:event][:event_datetime_start] if
params[:event][:event_datetime_start]
event_datetime_end <= params[:event][:event_datetime_end] if
params[:event][:event_datetime_end]
end

You may be able to do without the if statements. If things like
‘params[:event][:event_datetime_end]’ evaluate to nil instead of
throwing errors because you are trying to call ‘[] on a nil’, then I
think you can omit the ‘if’ clauses and ez_where will just ignore the
condition if it evaluates to nil.

This is all from memory, so YMMV. I haven’t had any coffee yet this
morning.

_Kevin
www.sciwerks.com