CODEWITHSUNDEEP
sqlrubyactiverecord
peter
peter

Reputation: 42182

Protecting against sql injection using activerecord

Following on the question how can I use like query in ruby with sinatra? I have the following problem securing my sql from injection.Here is my method to make a query from the type string, it receives a v(alue) to search for and a k(ey) (=field) to look in. After that the various selctions are joined by selection.join(' and ')

def string_selector(k, v)
  case
  when v[/\|/]
    v.scan(/([^\|]+)(\|)([^\|]+)/).map {|p| "lower(#{k}) LIKE '%#{p.first.downcase}%' or lower(#{k}) LIKE '%#{p.last.downcase}%'"}
  when v[/[<>=]/]
    v.scan(/(<=?|>=?|=)([^<>=]+)/).map { |part| p part; "#{k} #{part.first} '#{part.last.strip}'"}
  else
    # "lower(#{k}) LIKE '%#{v.downcase}%'" #(works)
    ("lower(#{k}) LIKE ?", '%#{v.downcase}%') #doesn't work
  end
end

But i get the error 

selectors.rb:38: syntax error, unexpected keyword_end, expecting $end
from C:/../1.9.1/rubygems/core_ext/kernel_require.rb:55:in `require'

What could i be doing wrong ?

Upvotes: 0

Views: 259

Answers (1)

Jacob Brown
Jacob Brown

Reputation: 7561

There's got to be a better way to do what you are trying to do if you are using ActiveRecord... However, if you need to support your string_selector functionality for some reason, I would at least use Arel:

def string_selector(k, v)
  tbl = Arel::Table.new(:test) # your table, or you could pass this in...
  condition = case v
  when /\|/
    vals = v.split(/\|/)
    first = vals.shift
    vals.inject(tbl[k].matches("%#{first.strip}%")) do |acc, val|
      acc.or(tbl[k].matches("%#{val.strip}%"))
    end
  when /<>/
    tbl[k].not_eq(v.gsub(/<>/, '').strip)
  when /\=/
    tbl[k].eq(v.gsub(/\=/, '').strip)
  else
    tbl[k].matches(v.strip)
  end
  tbl.where(condition).to_sql
end

Please note that matches will perform a case insensitive query for you (e.g., by using ILIKE in PostgreSQL).

Upvotes: 1

Related Questions