SQL Like escaping helper method. [Rob Gilson & Yves Senn]

Closes #14222.

This is a follow up to #6104

This does not have the backwards compatibility issues brought up in
implementation to break.

3 files changed, 48 insertions, 0 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 62fa39b4f4..0a12049e5d 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,3 +1,19 @@
+*   `sanitize_sql_like` helper method to escape a string for safe use in a SQL
+    LIKE statement.
+
+    Example:
+
+        class Article
+          def self.search(term)
+            where("title LIKE ?", sanitize_sql_like(term))
+          end
+        end
+
+        Article.search("20% _reduction_")
+        # => Query looks like "... title LIKE '20\% \_reduction\_' ..."
+
+    *Rob Gilson*, *Yves Senn*
+
 *   Do not quote uuid default value on `change_column`.
 
     Fixes #14604.
diff --git a/activerecord/lib/active_record/sanitization.rb b/activerecord/lib/active_record/sanitization.rb
index 5a71c13d91..ef63949208 100644
--- a/activerecord/lib/active_record/sanitization.rb
+++ b/activerecord/lib/active_record/sanitization.rb
@@ -107,6 +107,12 @@ module ActiveRecord
         end.join(', ')
       end
 
+      # Sanitizes a +string+ so that it is safe to use within a sql
+      # LIKE statement. This method uses +escape_character+ to escape all occurrences of "\", "_" and "%"
+      def sanitize_sql_like(string, escape_character = "\\")
+        string.gsub(/[\\_%]/) { |x| [escape_character, x].join }
+      end
+
       # Accepts an array of conditions. The array has each value
       # sanitized and interpolated into the SQL statement.
       #   ["name='%s' and group_id='%s'", "foo'bar", 4]  returns  "name='foo''bar' and group_id='4'"
diff --git a/activerecord/test/cases/sanitize_test.rb b/activerecord/test/cases/sanitize_test.rb
index 954eab8022..18182efc46 100644
--- a/activerecord/test/cases/sanitize_test.rb
+++ b/activerecord/test/cases/sanitize_test.rb
@@ -51,4 +51,30 @@ class SanitizeTest < ActiveRecord::TestCase
     select_author_sql = Post.send(:sanitize_sql_array, [''])
     assert_equal('', select_author_sql)
   end
+
+  def test_sanitize_sql_like
+    assert_equal '100\%', Binary.send(:sanitize_sql_like, '100%')
+    assert_equal 'snake\_cased\_string', Binary.send(:sanitize_sql_like, 'snake_cased_string')
+    assert_equal 'C:\\\\Programs\\\\MsPaint', Binary.send(:sanitize_sql_like, 'C:\\Programs\\MsPaint')
+    assert_equal 'normal string 42', Binary.send(:sanitize_sql_like, 'normal string 42')
+  end
+
+  def test_sanitize_sql_like_with_custom_escape_character
+    assert_equal '100!%', Binary.send(:sanitize_sql_like, '100%', '!')
+    assert_equal 'snake!_cased!_string', Binary.send(:sanitize_sql_like, 'snake_cased_string', '!')
+    assert_equal 'C:!\\Programs!\\MsPaint', Binary.send(:sanitize_sql_like, 'C:\\Programs\\MsPaint', '!')
+    assert_equal 'normal string 42', Binary.send(:sanitize_sql_like, 'normal string 42', '!')
+  end
+
+  def test_sanitize_sql_like_example_use_case
+    searchable_post = Class.new(Post) do
+      def self.search(term)
+        where("title LIKE ?", sanitize_sql_like(term))
+      end
+    end
+
+    assert_sql /LIKE '20\\% \\_reduction\\_'/ do
+      searchable_post.search("20% _reduction_").to_a
+    end
+  end
 end