sql - Rails 清理事件记录查询中的用户输入

Question

假设我有以下数据:

models/supplier.rb

| -- | ---------------- |
| id | name             |
| -- | ---------------- |
| 1  | John Doe's Store |
| 2  | Jane             |
| -- | ---------------- | 

我有以下查询未能清理用户在搜索字段中的输入:

@term = "John Doe's"

查询 1

Supplier.order("case when name LIKE :term 1 else 2 end, name asc", term: "#{@term}%")

ArgumentError: Direction "one's%" is invalid. Valid directions are: [:asc, :desc, :ASC, :DESC, "asc", "desc", "ASC", "DESC"]
from ~/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-4.2.7/lib/active_record/relation/query_methods.rb:1113:in `block (2 levels) in validate_order_args'

查询 2

Vulnerable to SQL Injection attack

Supplier.order("case when name LIKE '#{@term}%' then 1 else 2 end, name ASC").first
  
Supplier Load (2.6ms)  SELECT  "suppliers".* FROM "suppliers"  ORDER BY case when name LIKE 'John Doe's%' then 1 else 2 end LIMIT 1
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "s"
LINE 1: ...uppliers"  ORDER BY case when name LIKE 'John Doe's%' then 1..

查询 3

It will success for normal inputs which don't have ' (special character) in them, but this query is still vulnerable to SQL injection attack

@term = "John"

Supplier.order("case when name LIKE '#{@term}%' then 1 else 2 end, name ASC").first

#<Supplier:0x007fe4bfd8d758
 id: 188,
 name: "John Doe's Store">

我无法找到解决此问题的方法，请帮助我以安全的方式完成此查询。

Answer 1

所有你需要转义你的输入是使用

ActiveRecord::Base.connection.quote(value)

这适用于所有类型，也是 rails 使用的。

@term =  ActiveRecord::Base.connection.quote("John Doe's" + "%" )

Supplier.order("case when name LIKE #{@term} then 1 else 2 end, name ASC").first