Rails 3中避免SQL注入：含动态表名的代码改写需求

Let's break down how to secure this query—direct string interpolation with dynamic values like table_name is exactly how SQL injection attacks happen, so we'll replace that with Rails' built-in safe methods.

The Problem with Your Original Code #

When you use #{table_name} or #{class_name} directly in your SQL string, there's no escaping. If an attacker can control these values (even indirectly), they could inject malicious SQL to drop tables, access sensitive data, or worse.

Solution 1: Use Rails' SQL Sanitization Helpers #

Rails 3 provides tools to safely handle dynamic table names and values. We'll use quote_table_name to escape table names (since they're identifiers, not values) and sanitize_sql_array to handle value placeholders:

# First, safely escape the dynamic table name
escaped_table = SomeTableName.connection.quote_table_name(table_name)

# Build the JOIN clause with sanitized values
join_clause = ActiveRecord::Base.sanitize_sql_array([
  "INNER JOIN %s ON linked_config_items.linked_type = ? AND linked_config_items.linked_id = %s.id",
  escaped_table,
  class_name,
  escaped_table
])

# Build the WHERE clause with safe interpolation
where_clause = ActiveRecord::Base.sanitize_sql_array([
  "%s.saved = ? AND %s.deleted_at IS NULL",
  escaped_table,
  true,
  escaped_table
])

# Execute the safe query
SomeTableName.joins(join_clause).where(where_clause)

• quote_table_name ensures the dynamic table name is properly escaped for your database (handles special characters or reserved words).
• sanitize_sql_array uses ? placeholders for values like class_name and true, which Rails automatically escapes to prevent injection.

Solution 2: Use Arel (Rails' Query Builder) #

For a more "Rails-native" approach, use Arel (which underpins Rails' ActiveRecord queries in Rails 3). This avoids manual SQL string building entirely:

# Get Arel table objects for both your main model and the dynamic table
main_table = SomeTableName.arel_table
dynamic_table = Arel::Table.new(table_name)

# Define the JOIN condition using Arel's safe methods
join_condition = main_table[:linked_type].eq(class_name)
  .and(main_table[:linked_id].eq(dynamic_table[:id]))

# Build the full query
SomeTableName.joins(
  main_table.join(dynamic_table, Arel::Nodes::InnerJoin).on(join_condition).join_sources
).where(
  dynamic_table[:saved].eq(true).and(dynamic_table[:deleted_at].eq(nil))
)

Arel automatically handles all escaping and placeholder logic, so you don't have to worry about manual sanitization. It's also more maintainable if you need to modify the query later.

Key Takeaways #

• Never interpolate user-controlled (or dynamic) values directly into SQL strings.
• Use Rails' built-in sanitization tools or Arel for safe dynamic queries.
• quote_table_name is for escaping identifiers (table/column names), while placeholder methods are for values.

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