Forum: Ruby on Rails Variable numbers of terms in a query

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Milo T. (Guest)
on 2007-05-17 20:54
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?
Gregory S. (Guest)
on 2007-05-17 21:35
(Received via mailing list)
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
Milo T. (Guest)
on 2007-05-17 22:09
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.
Gregory S. (Guest)
on 2007-05-17 22:52
(Received via mailing list)
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
Milo T. (Guest)
on 2007-05-18 12:37
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")
This topic is locked and can not be replied to.