Hi,
Question - Can anyone recommend a safe way/strategy to input some search conditions via URL parameters? So the requirements are:
[1] Only use URL parameters (e.g. key & value pair, where value is a string) [2] At the server side convert these to a query string (i.e. the WHERE clause in a SQL select statement) [3] Need to "scrub" the inputs to avoid SQL injection [4] Want to support more than basic column_name = value, so I need also (here column_name would be the key): (a) column_name = true/false [for boolean] (b) NOT support - i.e. column_name != value (c) IS NOT NULL support, i.e. column_name IS NOT NULL (d) Range, i.e. column_name IS BETWEEN value1 AND value2 (e) Some permutations of these, e.g. column name != true (so NOT and a boolean)
Any suggestions? Anything I'm missing that solves this in Rails or ActiveScaffold?
Background: The background is I'm using the ActiveScaffold ( http://activescaffold.com/) out of the box views. It has a server side "conditions_from_params" that is supported & it passes the URL parameters around well (e.g. then sorting buttons at columns continues to work even through you've filtered down the list of rows). Here's an extract of how I started to add some support to this for "range" and "boolean", however I agree it's messy. I'm just looking for a better way to do the below, plus then add on the other above-mentioned support I have. Obviously I could just pass the exact parameters I want for the WHERE clause in a query however then I assume there would be SQL injection potential issues...
# Builds search conditions by search params for column names. This allows urls like "contacts/list?company_id=5". def conditions_from_params conditions = nil params.reject {|key, value| [:controller, :action, :id].include?(key.to_sym)}.each do |key, value| next unless active_scaffold_config.model.column_names.include?(key) # reject keys that don't match table column in database
if value.match(".*[.][.].*") # Range was specified elements = value.split('..') conditions = merge_conditions(conditions, ["#{active_scaffold_config.model.table_name}.#{key.to_s} BETWEEN '#{elements[0]}' AND '#{elements[1]}' "]) else # No range if ( value.upcase == "TRUE" || value.upcase == "FALSE") # Boolean conditions = merge_conditions(conditions, ["#{active_scaffold_config.model.table_name}.#{key.to_s} = ?", value.upcase == "TRUE" ? true : false]) else # Non Boolean conditions = merge_conditions(conditions, ["#{active_scaffold_config.model.table_name}.#{key.to_s} = ?", value]) end end
end conditions end
Thanks
