ActiveRecord Query Building Questions

Rails
1

In my own framework, I have a tool which converts forms into queries and
has certain features I’m not seeing talked about for ActiveRecord.
Before I go transcribing the tools I’m used to working with into Ruby ,
I figured I’d make sure that AR doesn’t already have a “rails way” to do
some of these things.

Sorry this is a bit long, but I think an example is needed to explain
what I’m looking for.

Let’s assume a form has these input fields (searching for cars), and for
simplicity we’ll assume these names correspond to model and SQL fields
of the same names.

autoMake
autoModel
autoYear
autoColor

To perform this query in my system (ruby-ish pseudo code)

foundAutos = recordData(
inputs = ‘autoMake, autoModel, autoYear, autoColor’,
where = FORM)

inputs = a list of input fields that are allowed to participate in the
search. Effectively what happens is that the [params] array is filtered
to eliminate any elements not specified in inputs, then that array is
passed on to the query builder. This allows complete automation of the
query building w/o fear of parameter injection.

where = is like AR’s :conditions. There’s several options for passing
info, and assigning it the constant FORM declares that the query is
built based on keys passed via params (vs literal and sem-literal
queries like AR also does).

Question 1: input filtering

So far in AR, I don’t see a way to automate an entire query from form
data except through the technique of (:all, :conditions =>
params[:object]) and that does not appear to offer any input filter to
prevent parameter injection. Am I correct in this? AWDWR seems to imply
this technique can be dangerous, and I would suspect param injection is
why.

Question 2: parameter operators

In my system, I have a convention for altering whether parameters are
searched using = != or LIKE by adding the character Op (for “operator”)
to the end of a parameter name like autoYearOp, the value of that
parameter is one of 'eq, neq, bw, ew, lt, lte, gt, gte, cn, btw for
EQual to, Not Equal, Begins With, Ends With, etc.

So, if my params include autoYear = 1997 and autoYear = gte, then the
query builder generates “autoYear >= 1997,” or if autoColor = ‘blu’ and
autoColorOp = ‘bw’, then I’ll get “autoColor LIKE ‘blu%’”.

I don’t see ActiveRecord having anything like that. Does it?

If Rails has nothing built-in that’s fine, I can develop my own methods
for this, I just don’t want to re-create existing features.

Thanks.

– gw

2

On 10/30/07, Greg W. [email protected] wrote:

where = is like AR’s :conditions. There’s several options for passing
this technique can be dangerous, and I would suspect param injection is
So, if my params include autoYear = 1997 and autoYear = gte, then the
query builder generates “autoYear >= 1997,” or if autoColor = ‘blu’ and
autoColorOp = ‘bw’, then I’ll get “autoColor LIKE ‘blu%’”.

I don’t see ActiveRecord having anything like that. Does it?

If Rails has nothing built-in that’s fine, I can develop my own methods
for this, I just don’t want to re-create existing features.

ActiveRecord performs input sanitization with the :conditions option:

Foo.find :all, :conditions => {:foo => params[:foo]}
Foo.find :all, :conditions => [‘foo = ?’, params[:foo]]

For more advanced conditions, see the Ambition or ez_where libraries:

http://errtheblog.com/post/11998
http://brainspl.at/articles/2006/01/30/i-have-been-busy

–
Rick O.
http://lighthouseapp.com
http://weblog.techno-weenie.net
http://mephistoblog.com

3

Rick O. wrote:

On 10/30/07, Greg W. [email protected] wrote:

where = is like AR’s :conditions. There’s several options for passing
this technique can be dangerous, and I would suspect param injection is
So, if my params include autoYear = 1997 and autoYear = gte, then the
query builder generates “autoYear >= 1997,” or if autoColor = ‘blu’ and
autoColorOp = ‘bw’, then I’ll get “autoColor LIKE ‘blu%’”.

I don’t see ActiveRecord having anything like that. Does it?

If Rails has nothing built-in that’s fine, I can develop my own methods
for this, I just don’t want to re-create existing features.

ActiveRecord performs input sanitization with the :conditions option:

Foo.find :all, :conditions => {:foo => params[:foo]}
Foo.find :all, :conditions => [‘foo = ?’, params[:foo]]

But “sanitization” = SQL & XSS injection type stuff. When I say
parameter injection I mean the form is only supposed to have those four
fields, but of course, a form can be edited before submitting, so
someone can add parameters by adding to the form. I don’t see how Rails
prevents those extra inputs from being added to params and therefore
from being added to the query.

For more advanced conditions, see the Ambition or ez_where libraries:

http://errtheblog.com/post/11998
Ruby on Rails Blog / What is Ruby on Rails for?

Neither of these is quite what I am looking for. Thanks for the links
though, each is close to some aspects of what I want, so I may be able
to borrow/extend theirs.

I’m not looking for more advanced conditions, I’m looking for something
to build the query w/o having to define conditions by interpreting the
form in more advanced ways on its own–at least for this certain genre
of form-to-query queries.

Thx.

– gw