# Dynamically created queries

**URL:** <https://www.ruby-forum.com/t/dynamically-created-queries/101681>\
**Category:** Rails\
**Created:** [August 7, 2006, 12:41pm UTC](https://www.ruby-forum.com/t/dynamically-created-queries/101681 "2006-08-07T12:41:21Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![matthew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ed655f/32.png) [@matthew](https://www.ruby-forum.com/u/matthew)\
**Post date:** [August 7, 2006, 12:41pm UTC](https://www.ruby-forum.com/t/dynamically-created-queries/101681/1 "2006-08-07T12:41:21Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![matthew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ed655f/32.png) [@matthew](https://www.ruby-forum.com/u/matthew)\
**Post date:** [August 7, 2006, 1:24pm UTC](https://www.ruby-forum.com/t/dynamically-created-queries/101681/2 "2006-08-07T13:24:23Z")

</div>

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/](http://www.ruby-forum.com/).
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](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](http://www.sciwerks.com)

---

<div class="post-metadata">

**Author:** ![matthew](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ed655f/32.png) [@matthew](https://www.ruby-forum.com/u/matthew)\
**Post date:** [August 7, 2006, 2:53pm UTC](https://www.ruby-forum.com/t/dynamically-created-queries/101681/3 "2006-08-07T14:53:19Z")

</div>

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/](http://www.ruby-forum.com/).
> > 
> > * * *
> > 
> > Rails mailing list  
> > removed\_email\_address@domain.invalid  
> > [http://lists.rubyonrails.org/mailman/listinfo/rails](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](http://www.sciwerks.com)