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)
--
Posted via http://www.ruby-forum.com/.
On Monday, August 07, 2006, at 12:41 PM, Matt Stone wrote:>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) > > > > >-- >Posted via http://www.ruby-forum.com/. >_______________________________________________ >Rails mailing list >Rails@lists.rubyonrails.org >http://lists.rubyonrails.org/mailman/listinfo/railsI''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 -- Posted with http://DevLists.com. Sign up and save your mailbox.
Kevin, Thanks - that''s exactly what I was looking for. Seems much cleaner & less code. rgds, - matt. Kevin Olbrich wrote:> On Monday, August 07, 2006, at 12:41 PM, Matt Stone 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 >>Rails@lists.rubyonrails.org >>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-- Posted via http://www.ruby-forum.com/.