Opal

We've beefed up Opal's Advanced search quite a bit!

• First, we've implemented the new Feature Search Types, which allows for the searching user to search for feature values in different ways(like text-field searching, drop-down grouped values, etc.). This gives the Advanced Search a better feel and lets users search for items more effectively.

• We've also improved the Advanced Search's security features quite a bit. Now there's better SQL Sanitiation that prevents again SQL Injection Attacks. Since we're accepting several different forms of input(drop-down boxes, text fields, etc.), we needed to make sure no one can inject any Malicious Code into the search. This becomes increasingly difficult since we're not just searching for Item attributes, but attributes from several other ActiveRecord models also!

Just to clarify, I mentioned that Opal's advanced search now performs sql sanitation for an unlimited number of different conditions for FeatureValues. Since we don't know how many Features a user is going to search for, we need to make the sanitiation dynamic. How can this be done in Rails, you might ask? Well, I used ActiveRecord's Conditions Sanitizer, and since you can pass conditions into ActiveRecord's find in a variety of Formats, we chose the "String of Conditions, Hash of Values" format, it looks like this:

  Company.find(:first, :conditions => [
    "id = :id AND name = :name AND division = :division AND created_at > :accounting_date",
    { :id => 3, :name => "37signals", :division => "First", :accounting_date => '2005-01-01' }
  ])

Using this format, I created a loop that added all selected Features(indexed by id) to a string, and added their associated values to a hash, also indexed by the Feature's id. Here's a look at what I did in code:

 values_array = Array.new # array to contain strings of certain conditions, ie: ["x = :x_value", "y LIKE :y_value"], which we will then join with the appropriate conjunction, ie: conditions.join(" AND ")  to create the required string
 values_hash = Hash.new # hash to contain values, ie: {:x_value => "someValue", :y_value => "%someValue%"}   
 for i in 1..params[:feature].size # loop through for every feature value, create a set of ActiveRecord conditions
     num_of_features_to_search = 1 # increment number of features to search  
     # Create MySQL Conditions for Matching Items with these Features         
      values_array << "value = :feature_#{i}" # add string to array that conforms to ActiveRecords Conditions format, ie: "x = :x_value" 
      values_hash["feature_#{i}".to_sym] = "#{params[:feature][i.to_s]["value"]}"        
     end 
  end 
 matching_feature_values = PluginFeatureValue.find(:all, :conditions => [values_array.join(" AND "), values_hash]) # convert conditions array to string seperated by AND db operator        

Using this format, ActiveRecord handles SQL sanitation, and we can search for any number of features safely.

Looking Good, just a little more testing before I can sign off on this...

Ahh! The new injection-proof code listed above for the New Feature search will only work with a OR-type lookup in mysql. This means that all items with one matching feature(out of all selected) will be listed. This is not what we want!

I've fixed the OR lookup problem by creating a hash of Item IDs found that match each feature selected. I then took that hash and selected only the common Item IDs that had a match for EVERY feature, using this function:

  def get_common_elements_for_hash_of_arrays(hash) # get an array of common elements contained in a hash of arrays, for every array in the hash.
    #example_hash = {:feature_0 => [1,2,3], :feature_1 => [2,4,5], :feature_2 => [2,5,6] } # for testing
    return hash.values.inject{|acc,elem| acc & elem} # inject & operator into hash values.
  end

Now we're looking pretty good.