Summary
Ransack's *_cont, *_not_cont, and similar LIKE-based predicates correctly escape special characters in MySQL, PostgreSQL, and PostGIS. However, in SQLite and other RDBMS, escaping is skipped, which can lead to unintended matches when values contain % or _.
Steps to Reproduce
Run a Ransack query using *_cont: '%_' against a dataset that includes values with and without underscores. In SQLite, the query matches records that do not contain underscores, due to lack of escaping.
Expected Behavior
The query should escape % and _ so that they are treated as literal characters in all supported databases.
Actual Behavior
In SQLite, the escaping is skipped, resulting in unintended matches.
Environment
- Ruby: 3.4.5
- Rails: 8.0.2
- Ransack: commit 90a602a
- sqlite3-ruby: 2.7.3
Code Reference
escape_wildcards skips escaping for non-MySQL/PostgreSQL adapters.
|
# replace % \ to \% \\ |
|
def escape_wildcards(unescaped) |
|
case ActiveRecord::Base.connection.adapter_name |
|
when "Mysql2".freeze |
|
# Necessary for MySQL |
|
unescaped.to_s.gsub(/([\\%_])/, '\\\\\\1') |
|
when "PostGIS".freeze, "PostgreSQL".freeze |
|
# Necessary for PostgreSQL |
|
unescaped.to_s.gsub(/([\\%_.])/, '\\\\\\1') |
|
else |
|
unescaped |
|
end |
|
end |
Test Reference
predicate_spec.rb asserts that escaping is not applied in SQLite.
|
describe 'cont' do |
|
it_has_behavior 'wildcard escaping', :name_cont, |
|
(case ActiveRecord::Base.connection.adapter_name |
|
when "PostGIS", "PostgreSQL" |
|
/"people"."name" ILIKE '%\\%\\.\\_\\\\%'/ |
|
when "Mysql2" |
|
/`people`.`name` LIKE '%\\\\%.\\\\_\\\\\\\\%'/ |
|
else |
|
/"people"."name" LIKE '%%._\\%'/ |
|
end) do |
|
subject { @s } |
|
end |
|
|
|
it 'generates a LIKE query with value surrounded by %' do |
|
@s.name_cont = 'ric' |
|
field = "#{quote_table_name("people")}.#{quote_column_name("name")}" |
|
expect(@s.result.to_sql).to match /#{field} I?LIKE '%ric%'/ |
|
end |
|
end |
Suggested Fix
Use ActiveRecord::Base.sanitize_sql_like to escape LIKE values consistently across all adapters.
https://api.rubyonrails.org/v8.0.2/classes/ActiveRecord/Sanitization/ClassMethods.html#method-i-sanitize_sql_like
Reproduction Code
# frozen_string_literal: true
# Create Gemfile dynamically
File.write('Gemfile', <<~GEMFILE)
source 'https://rubygems.org'
gem 'rails', '8.0.2'
gem 'ransack', github: 'activerecord-hackery/ransack', ref: '90a602a992f4e3a438d63e0fc2f7e1a0fb3acab0'
gem 'sqlite3', '2.7.3'
GEMFILE
# Install dependencies
system 'bundle install'
# Setup Bundler
require 'bundler'
Bundler.setup(:default)
# Require necessary libraries
require 'active_record'
require 'logger'
require 'ransack'
# Setup in-memory SQLite DB
ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')
ActiveRecord::Base.logger = Logger.new($stdout)
# Define schema
ActiveRecord::Schema.define do
create_table :products, force: true do |t|
t.string :name
end
end
# Define model
class Product < ActiveRecord::Base
def self.ransackable_attributes(_auth_object = nil)
%w[name]
end
end
# Seed data
Product.create!(name: '100% Pure')
Product.create!(name: '50_50 Blend')
Product.create!(name: 'Back\\Slash')
Product.create!(name: 'NormalText')
# Ransack query with special characters
q = Product.ransack(name_cont: '%_')
puts "\nGenerated SQL:"
puts q.result.to_sql
puts "\nResults:"
puts q.result.map(&:name)Output (Excerpt)
Generated SQL:
SELECT "products".* FROM "products" WHERE "products"."name" LIKE '%%_%'
Results:
100% Pure
50_50 Blend
Back\Slash
NormalText
Summary
Ransack's
*_cont,*_not_cont, and similar LIKE-based predicates correctly escape special characters in MySQL, PostgreSQL, and PostGIS. However, in SQLite and other RDBMS, escaping is skipped, which can lead to unintended matches when values contain%or_.Steps to Reproduce
Run a Ransack query using
*_cont: '%_'against a dataset that includes values with and without underscores. In SQLite, the query matches records that do not contain underscores, due to lack of escaping.Expected Behavior
The query should escape
%and_so that they are treated as literal characters in all supported databases.Actual Behavior
In SQLite, the escaping is skipped, resulting in unintended matches.
Environment
Code Reference
escape_wildcardsskips escaping for non-MySQL/PostgreSQL adapters.ransack/lib/ransack/constants.rb
Lines 162 to 174 in 90a602a
Test Reference
predicate_spec.rbasserts that escaping is not applied in SQLite.ransack/spec/ransack/predicate_spec.rb
Lines 158 to 176 in 90a602a
Suggested Fix
Use
ActiveRecord::Base.sanitize_sql_liketo escape LIKE values consistently across all adapters.https://api.rubyonrails.org/v8.0.2/classes/ActiveRecord/Sanitization/ClassMethods.html#method-i-sanitize_sql_like
Reproduction Code
Output (Excerpt)