Rails Active Record查询用户输入未Sanitize引发排序报错的解决方法

Let's start by breaking down why your query is throwing that error. Your user input "John Doe's" contains an apostrophe, which is causing chaos in your SQL structure. Combine that with a missing THEN keyword in your CASE statement, and Rails ends up misinterpreting part of your query as an invalid sort direction—hence the ArgumentError: Direction "one's%" is invalid message.

Here are the most reliable ways to fix this:

1. Fix the CASE Syntax + Use Proper Parameter Binding #

First, correct the missing THEN in your CASE expression (it's required for valid SQL). Then, leverage Active Record's named parameter binding, which automatically handles sanitization of special characters like apostrophes:

@term = "John Doe's"
Supplier.order(
  "case when name LIKE :term then 1 else 2 end, name asc",
  term: "#{@term}%"
)

Active Record will escape the apostrophe in the parameter value behind the scenes, turning "John Doe's%" into a safe SQL string like 'John Doe\'s%'—no more broken syntax!

2. Use Arel for Safer, Programmatic Queries (Top Recommendation) #

To avoid writing raw SQL entirely (and eliminate syntax errors/injection risks), use Arel—Rails' built-in query builder. It handles all sanitization automatically, so you don't have to worry about apostrophes or special characters:

@term = "John Doe's"
supplier_table = Supplier.arel_table

# Build the priority ordering condition with Arel
priority_order = Arel::Nodes::Case.new(
  supplier_table[:name].matches("#{@term}%")
).when(true).then(1).else(2)

# Execute the properly sanitized query
Supplier.order(priority_order, supplier_table[:name].asc)

This approach is more maintainable and less error-prone than raw SQL strings.

3. Manually Sanitize Input (If You Must Use Raw SQL) #

If you absolutely need to use string interpolation in raw SQL (not ideal, but sometimes necessary), use Rails' sanitize_sql_like method to escape special characters like apostrophes and percent signs:

@term = "John Doe's"
sanitized_term = ActiveRecord::Base.sanitize_sql_like(@term)
Supplier.order("case when name LIKE '#{sanitized_term}%' then 1 else 2 end, name asc")

sanitize_sql_like is specifically designed to escape characters with special meaning in SQL LIKE clauses, keeping your interpolated string valid and safe.

Why These Solutions Work #

• Parameter Binding: Active Record automatically escapes special characters in parameter values, eliminating manual sanitization work and SQL injection risks.
• Arel: By building queries programmatically, you avoid typos in raw SQL and let Rails handle all sanitization logic.
• sanitize_sql_like: This method targets exactly the characters that cause issues in LIKE clauses, ensuring your interpolated strings are safe to use.

内容的提问来源于stack exchange，提问作者przbadu