Escaping query for SQL LIKE in rails

Question

In the rails source ( https://github.com/rails/rails/blob/fe4b0eee05f59831e1468ed50f55fbad0ce11e1d/activerecord/lib/active_record/sanitization.rb#L112 ) there is a sanitize_sql_like method that (I hope) will disinfect them before using SQL to EIK

however, I cannot use this because Rails says that the method does not exist.

There is an apostrophe in my line, and the request

 @query = "Joe's" Model.where("lower(field) LIKE ?", "%#{@query}%") 

Using ActiveRecord::Base.sanitize does not help, since there are no query results.

How can I avoid @query and save my SQL code?

Answer 1

If you use where correctly, it will automatically exit input

 @query = "Joe's" Model.where("lower(field) LIKE ?", "%#{@query}%") 

Just note that your request is incorrect. You have a lower() operator, then you pass in input that is not lowercase. The request will always return 0.

In addition, lower() will reduce the database's ability to use the index. In most databases, LIKE is already case insensitive (except for PostgreSQL, where you should use ILIKE ).

 query = "Joe's" Model.where("field LIKE ?", "%#{query}%") 

or

 query = "Joe's" Model.where("field ILIKE ?", "%#{query}%") 

Here is a real example in a real database. As you can see, the input is correctly escaped in the final SQL.

 > query = "Joe's" > User.where("lower(email) LIKE ?", "%#{query}%") User Load (4.4ms) SELECT "users".* FROM "users" WHERE (lower(email) LIKE '%Joe''s%') => #<ActiveRecord::Relation []> 

Answer 2

I solved the same problem (in MySQL) using ActiveRecord :: Sanitization :: ClassMethods to properly sanitize user input. Unfortunately, the methods defined in ActiveRecord :: Sanitization :: ClassMethods are declared protected , so they are only available in the scope of the Model class. The methods defined in ActiveRecord :: Sanitization :: ClassMethods mix with all Model classes, so they are available directly from the Model area. To do this, you need to define a class / instance method or scope ( ActiveRecord area ) on your model in order to use them, and not use them externally, as in your example. However, constructively, it is probably preferable to encapsulate the query logic in the Model.

This solution also has the advantage of not only avoiding the single quote character, but also escaping other characters that will be interpreted as an SQL query, for example, the character "%" (among others). This should properly prevent SQL injection by avoiding other characters that might force the interpretation of values ​​rather than being treated as literals. There are also additional sanitization methods defined in ActiveRecord::Sanitization::ClassMethods that are useful for embedding user input (or other corrupt input) in other contexts of SQL queries. Here is a working solution tested on MySQL.

 class Model < ActiveRecord::Base # Finds a Model by case-insensitive substring match on Model.field # # @param query [String] A value to use in the substring match. # @return [ActiveRecord::Relation] An `Relation` of `Model`s whose # `field` includes the `query` substring. scope :find_by_field_substring, ->(query) do where(arel_table[:field].matches("%#{sanitize_sql_like(query)}%")) end end 

Then you can access this scope as follows:

 Model.find_by_field_substring "Joe's" => #<ActiveRecord::Relation [#<Model id: 1, field: "Joe's">]> 

The use of the ActiveRecord and ActiveRecord :: Relations scopes is fairly well documented, but may only be available in newer versions of Rails.

Note that your database may require a second parameter to the sanitize_sql_like method to specify a different escape character than "\".

Also note that escaping like this does not work for MySQL if it is running in NO_BACKSLASH_ESCAPES mode, because it forces you to use a different mechanism to escaping values. If you use the NO_BACKSLASH_ESCAPES mode, see this answer .

I have not tested this part, but instead of using the SQL AST constructor, which is under ActiveRecord, you can probably also use the style suggested in your first example, if it is defined in the model class.

 class Model < ActiveRecord::Base scope :find_by_field_substring, ->(query) do where("lower(field) LIKE ?", "%#{sanitize_sql_like(query)}%") end end