# Optional Parmaters in a SQL Query, inline and sanitized

**URL:** <https://www.ruby-forum.com/t/optional-parmaters-in-a-sql-query-inline-and-sanitized/121780>\
**Category:** Rails\
**Created:** [November 30, 2007, 8:59pm UTC](https://www.ruby-forum.com/t/optional-parmaters-in-a-sql-query-inline-and-sanitized/121780 "2007-11-30T20:59:06Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![divotdave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/bbe5ce/32.png) [@divotdave](https://www.ruby-forum.com/u/divotdave)\
**Post date:** [November 30, 2007, 8:59pm UTC](https://www.ruby-forum.com/t/optional-parmaters-in-a-sql-query-inline-and-sanitized/121780/1 "2007-11-30T20:59:06Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![divotdave](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/bbe5ce/32.png) [@divotdave](https://www.ruby-forum.com/u/divotdave)\
**Post date:** [November 30, 2007, 9:01pm UTC](https://www.ruby-forum.com/t/optional-parmaters-in-a-sql-query-inline-and-sanitized/121780/2 "2007-11-30T21:01:57Z")

</div>

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