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? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Thu, May 17, 2007 at 06:54:34PM +0200, Milo Thurston wrote:> 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?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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Gregory Seidman 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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Thu, May 17, 2007 at 08:09:59PM +0200, Milo Thurston 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Gregory Seidman 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") -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---