sql - 在自定义条件下清理 SQL

Question

我需要创建一个简单的搜索，但我负担不起使用 Sphinx 的费用。

这是我写的:

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 ')

现在，sanitize_sql_for_conditions 不起作用了!它只返回原始字符串。

如何重写此代码以避开恶意代码？

Answer 1

如果将“#{keyword}”替换为“?”，您可以这样做。使用问号将自动清理 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