Optional Parmaters in a SQL Query, inline and sanitized

Rails
1

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?

Parked at Loopia

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

2

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