Variable numbers of terms in a query

Rails
1

I have to produce a front end to a database where users can select from
a variety of parameters they want to search on, e.g.

a > 1
b = 2
c >= 3

The numerical values could be floats as well as integers, and so a
drop-down list isn’t really convenient.
I can assemble a string along the lines of “a > 1 and b = 2 and c >= 3”
and insert it into another command, but if I do it like this:

@things = Thing.find(:all, :conditions => ["? and foreign_key_id is NOT
NULL", @findstring])

…it won’t work as the symbols will be quoted, and putting the string
directly into an SQL query would be bad as users could enter anything in
those text boxes.
Can anyone suggest a way around this?

2

On Thu, May 17, 2007 at 06:54:34PM +0200, Milo T. wrote:

and insert it into another command, but if I do it like this:

@things = Thing.find(:all, :conditions => ["? and foreign_key_id is NOT
NULL", @findstring])

…it won’t work as the symbols will be quoted, and putting the string
directly into an SQL query would be bad as users could enter anything in
those text boxes.
Can anyone suggest a way around this?

Ruby is your friend. I’m going to assume that you are receiving this in
params in an action something like this:

params[“db_terms”] = {
“a” => { “oper” => “>”, “value” => “1” },
“b” => { “oper” => “=”, “value” => “2” },
“c” => { “oper” => “>=”, “value” => “3” }
}

I’m also assuming you have a canonical list of acceptable field names
(as
strings) in a constant named KnownFields and a similar list of
acceptable
operators (as strings) in KnownOperators. Since we need a dependable
(but
not necessarily sorted) order, we’ll turn it into an array instead of a
hash first, then produce a condition string:

db_terms = params[:db_terms].select { |field,condition|
KnownFields.include?(field) &&
Hash === condition &&
KnownOperators.include?(condition[“oper”]) &&
!condition[“value”].blank?
}

If a value or operator is missing or left empty or an unknown field or
operator is maliciously inserted in the query params, this will get rid
of
that part of the query. Next is the easy part:

if db_terms.empty?

What do you do when no valid conditions are given?

else
condition_string = db_terms.map { |field,condition|
“#{field} #{condition[‘oper’] ?” }.join(’ AND ')
condition_values = db_terms.map { |field,condition| condition[‘value’]
}
@things = Thing.find :all,
:conditions => [ “#{condition_string} AND foreign_key_id IS NOT
NULL”,
*condition_values ]
end

–Greg

3

Gregory S. wrote:

Ruby is your friend. I’m going to assume that you are receiving this in
params in an action something like this:

params[“db_terms”] = {
“a” => { “oper” => “>”, “value” => “1” },
“b” => { “oper” => “=”, “value” => “2” },
“c” => { “oper” => “>=”, “value” => “3” }
}

Indeed so.

I’m also assuming you have a canonical list of acceptable field names
(as
strings) in a constant named KnownFields and a similar list of
acceptable
operators (as strings) in KnownOperators.

I do have a list of acceptable field names, which is defined in the
thing model and called with Thing.params. I did come up with this
method, but I don’t think it’s very good.

Thing.params.each do |param|
if params[:search_param][param] # i.e. if a, b, c selected etc.
findarray << “p.#{param} #{params[:param_sym][param]}
‘#{params[:param_value][param]}’”
end
end

The idea of params[:param_sym][param] is so that only allowed operators
from a set of radio button options can be selected, which are defined in
the view. The bit in escapted single quotes is the text from the user. I
then join findarray on " and " and insert it into the search string.

Your system looks rather less crude than mine, so I’ll give it a try.
Many thanks.

4

On Thu, May 17, 2007 at 08:09:59PM +0200, Milo T. wrote:
[…]

The idea of params[:param_sym][param] is so that only allowed operators
from a set of radio button options can be selected, which are defined in
the view. The bit in escapted single quotes is the text from the user. I
then join findarray on " and " and insert it into the search string.

You can’t trust anything coming to you over the net. It doesn’t matter
if
you only have radio buttons with acceptable values in the HTML you send.
Any malicious user can send any data whatsoever in your params. You must
always validate on the server side regardless of any validation or
restrictions you have in HTML or JS.

Your system looks rather less crude than mine, so I’ll give it a try.
Many thanks.

Good luck.

–Greg

5

Gregory S. wrote:

You can’t trust anything coming to you over the net. It doesn’t matter
if
you only have radio buttons with acceptable values in the HTML you send.
Any malicious user can send any data whatsoever in your params. You must
always validate on the server side regardless of any validation or
restrictions you have in HTML or JS.

Thanks. Would something like this be sufficiently secure, or have I
still missed something? It is supposed to look up the values for the
search term and operator in an array or hash respectively, and
single-quote any text values passed in by the user.
This is, again, probably not very good but I would be interested to know
in what way it is flawed.

search terms collected in an array

findarray = []

form passes in a number to represent the operator

operators = { “1” => “=”,
“2” => “<”,
“3” => “>”,
“4” => “<=”,
“5” => “=>”,
“6” => “!=” }

loop through each of the known search terms

to see if it is defined. If it is, and the operator

value is found in the hash, then add a search term

to the array

params[:search_param] - the search term in the database

params[:param_sym] - an integer denoting which operator to use

params[:param_value] - the user’s text input

Thing.params.each do |param|
if params[:search_param][param] &&
operators[params[:param_sym][param]]
findarray << “p.#{param} #{symbols[params[:param_sym][param]]}
‘#{params[:param_value][param]}’”
end
end

if there’s anything in the array, form

search string from it

@findstring == “”
unless findarray.empty?
@findstring = " and " + findarray.join(" and ")
end

actually run the search

@things = Thing.find_by_sql(“select t.* from things t, params p where
t.id = p.thing_id #{@findstring} and p.thing_id is NOT NULL”)