Rails sanitize user input in active record query

Question

Let's say I have following data:

models/supplier.rb

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

I have following queries which are failing to sanitize user's input from search field:

@term = "John Doe's"

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

Query 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..

Query 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">

I am not able to figure out a solution for this problem, Please help me complete this query in secure way.

Answer 1

All you need to escape your input is to use

ActiveRecord::Base.connection.quote(value)

This works for all types and is what rails use.

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

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