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

Code Reference

escape_wildcards skips escaping for non-MySQL/PostgreSQL adapters.

ransack/lib/ransack/constants.rb

Lines 162 to 174 in 90a602a

	# 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.

ransack/spec/ransack/predicate_spec.rb

Lines 158 to 176 in 90a602a

	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