Rails - Nov 2007 - Optional Parmaters in a SQL Query, inline and sanitized

I''m trying to achieve the following results.
This allows you to do things like
>> puts "Cat''''''".safe_quote
''Cat\''\''\''''
>> ["cat","dog",3,nil,"",nil].sql_join
=>
"''cat'',''dog'',3,NULL,'''',NULL"
>>
["cat","dog",3,nil,"",nil].values_in(''animal'')
=> " AND animal in
(''cat'',''dog'',3,NULL,'''',NULL)
"
>> [''c\at''].safe_quote
=> "''c\\\\at''"
>> Mymodel.find_by_sql("select * from mytables where (1 = 1
#{[20603,78881,20321].values_in(''ext'')})")
=> select * from mytables where (1 = 1 and ext in (20603,7881,20321) )
>>Mymodel.find_by_sql("select * from mytables where (1 = 1
#{[].values_in(''ext'')})")
=> select * from mytables where (1 = 1)


This would let me chain a ton of optional parameters directly into the
SQL query itself.



The following code is what I came up with.
 I was wondering if this was sane way to do this, or if there is a
better way that I''m missing?

http://pastie.caboo.se/123774
----

module SafeJoin
  #Use Array.join with ActiveRecord sanitize_sql
  include QuoteSafe
  def sql_join(delimit='','')
    self.collect {|aa| "#{safe_quote(aa)}"}.collect{|x|
x.gsub(/^$/,"''''")}.join(delimit)
  end

  # Return field in (x,y,z), or nothing.
  # Allows for easy insertion of optional fields in a SQL string.
  def values_in(field)
    records = self.sql_join
    return '''' if records.blank?
    " AND #{field} in (#{records}) "
  end
end

class String
  include QuoteSafe
end

class Array
  include SafeJoin
end
-- 
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
-~----------~----~----~----~------~----~------~--~---

Forgot this module too.
module QuoteSafe
  #Wrapper for ActiveRecord Sanitize_sql, returns a blank if no value
 def safe_quote(value=nil)
   value = self if value.nil?
   b = ActiveRecord::Base.method(:sanitize_sql).call(["?",value])
    b.scan(/^''''$/).empty? ? b : ''''
  end
end
-- 
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
-~----------~----~----~----~------~----~------~--~---