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