LIKE predicates do not escape values in SQLite and other non-MySQL/PostgreSQL RDBMS

[tmyksj]

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 90a602a992f4e3a438d63e0fc2f7e1a0fb3acab0
• sqlite3-ruby: 2.7.3

Code Reference

escape_wildcards skips escaping for non-MySQL/PostgreSQL adapters. https://github.com/activerecord-hackery/ransack/blob/90a602a992f4e3a438d63e0fc2f7e1a0fb3acab0/lib/ransack/constants.rb#L162-L174

Test Reference

predicate_spec.rb asserts that escaping is not applied in SQLite. https://github.com/activerecord-hackery/ransack/blob/90a602a992f4e3a438d63e0fc2f7e1a0fb3acab0/spec/ransack/predicate_spec.rb#L158-L176

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