Do not use SQL LIKE operator for case insensitive uniqueness validation

It can result in wrong results if values contain special % or _ characters. It is safer to use SQL LOWER function and compare for equality.

2 files changed, 29 insertions, 2 deletions

diff --git a/activerecord/lib/active_record/validations/uniqueness.rb b/activerecord/lib/active_record/validations/uniqueness.rb
index d1225a9ed9..4db4105389 100644
--- a/activerecord/lib/active_record/validations/uniqueness.rb
+++ b/activerecord/lib/active_record/validations/uniqueness.rb
@@ -56,8 +56,9 @@ module ActiveRecord
         column = klass.columns_hash[attribute.to_s]
         value = column.limit ? value.to_s.mb_chars[0, column.limit] : value.to_s if column.text?
 
-        if !options[:case_sensitive] && column.text?
-          relation = table[attribute].matches(value)
+        if !options[:case_sensitive] && value && column.text?
+          # will use SQL LOWER function before comparison
+          relation = table[attribute].lower.eq(table.lower(value))
         else
           value    = klass.connection.case_sensitive_modifier(value)
           relation = table[attribute].eq(value)
diff --git a/activerecord/test/cases/validations/uniqueness_validation_test.rb b/activerecord/test/cases/validations/uniqueness_validation_test.rb
index b4f3dd034c..0f1b3667cc 100644
--- a/activerecord/test/cases/validations/uniqueness_validation_test.rb
+++ b/activerecord/test/cases/validations/uniqueness_validation_test.rb
@@ -162,6 +162,32 @@ class UniquenessValidationTest < ActiveRecord::TestCase
     end
   end
 
+  def test_validate_case_sensitive_uniqueness_with_special_sql_like_chars
+    Topic.validates_uniqueness_of(:title, :case_sensitive => true)
+
+    t = Topic.new("title" => "I'm unique!")
+    assert t.save, "Should save t as unique"
+
+    t2 = Topic.new("title" => "I'm %")
+    assert t2.save, "Should save t2 as unique"
+
+    t3 = Topic.new("title" => "I'm uniqu_!")
+    assert t3.save, "Should save t3 as unique"
+  end
+
+  def test_validate_case_insensitive_uniqueness_with_special_sql_like_chars
+    Topic.validates_uniqueness_of(:title, :case_sensitive => false)
+
+    t = Topic.new("title" => "I'm unique!")
+    assert t.save, "Should save t as unique"
+
+    t2 = Topic.new("title" => "I'm %")
+    assert t2.save, "Should save t2 as unique"
+
+    t3 = Topic.new("title" => "I'm uniqu_!")
+    assert t3.save, "Should save t3 as unique"
+  end
+
   def test_validate_case_sensitive_uniqueness
     Topic.validates_uniqueness_of(:title, :case_sensitive => true, :allow_nil => true)