Sanitize SQL in custom conditions

Question

I need to create a simple search but I can't afford to use Sphinx.

Here's what I wrote:

keywords = input.split(/\s+/)
queries = []

keywords.each do |keyword|
  queries << sanitize_sql_for_conditions(
              "(classifications.species LIKE '%#{keyword}%' OR 
               classifications.family LIKE '%#{keyword}%' OR 
               classifications.trivial_names LIKE '%#{keyword}%' OR
               place LIKE '%#{keyword}%')")
end

options[:conditions] = queries.join(' AND ')

Now, sanitize_sql_for_conditions does NOT work! It returns simply returns the original string.

How can I rewrite this code to escape malicious code?

Answer 1

What do you mean you can't afford to use Sphinx? It's free!

Answer 2

If you replace the "#{keyword}" with a "?", you can do something like this. Using the question mark will automatically sanitize SQL.

keywords = input.split(/\s+/)
queries = []
vars = []

keywords.each do |keyword|
  queries << "(classifications.species LIKE '%?%' OR 
               classifications.family LIKE '%?%' OR 
               classifications.trivial_names LIKE '%?%' OR
               place LIKE '%?%')"
  vars = vars << keyword << keyword << keyword << keyword
end

options[:conditions] = [queries.join(' AND '), vars].flatten