Rails迁移中执行SQL是否存在SQL注入风险？

Great question—this is a super common point of confusion, especially since migrations feel like "one-and-done" operations that don't touch user input day-to-day. Let's break down why the risk still exists, and where you might be missing something:

Key Risk Points to Consider #

• Dynamic data in migrations isn't always "safe"
  Even if you're inserting data for a one-time setup, if that data comes from any untrusted or unfiltered source (like legacy database records, external API responses, or even hardcoded values that get modified later), directly concatenating it into raw SQL puts you at risk. For example, if you write something like:

  execute("INSERT INTO user_roles (user_id, role_id) VALUES (#{user.id}, #{role.id})")
  

  If user.id somehow contained a malicious value (say, from a corrupted legacy dataset), this would execute arbitrary SQL against your database. Rails doesn't sanitize raw execute calls by default—you're bypassing the ORM's built-in parameterization.

• Migrations run across all environments
  That "safe" test data you used in development might not be what's used in production. If your migration pulls dynamic values from environment-specific sources (like a production database backup or external service), you can't guarantee those values are clean. Unlike controller actions, migrations don't go through Rails' strong parameters or request sanitization pipelines—they interact directly with the database.

• Future maintenance creates hidden risks
  Right now, it's a one-time operation, but what if a teammate modifies this migration later to add more dynamic logic? Or copies this raw SQL pattern into another migration or rake task? The "one-time" code can become a reusable (and unsafe) pattern without anyone noticing.

How to Mitigate the Risk #

• Use ActiveRecord's ORM methods instead of raw SQL
  Let Rails handle parameterization for you. Instead of raw execute, use:

  UserRole.create!(user_id: user.id, role_id: role.id)
  

  Or bulk inserts if you're adding multiple records:

  UserRole.insert_all!(
    [
      { user_id: 1, role_id: 2 },
      { user_id: 3, role_id: 2 }
    ]
  )
  

  Both of these methods automatically parameterize the query, eliminating injection risks.

• If you must use raw SQL, use parameterized queries
  If raw SQL is necessary (for complex joins or performance reasons), never concatenate values directly. Use Rails' parameterized execute syntax:

  # PostgreSQL uses $1, $2 placeholders
  execute(
    "INSERT INTO user_roles (user_id, role_id) VALUES ($1, $2)",
    [user.id, role.id]
  )
  
  # MySQL uses ? placeholders
  execute(
    "INSERT INTO user_roles (user_id, role_id) VALUES (?, ?)",
    [user.id, role.id]
  )
  

  This tells the database to treat the values as safe parameters, not executable SQL.

• Avoid untrusted dynamic data in migrations entirely
  Whenever possible, hardcode static values for one-time setup, or filter/validate any dynamic data before using it. For example, if you're pulling from a legacy table, sanitize IDs or values to ensure they're integers (or the expected type) before inserting.

Wrap-Up #

The core issue here is that migrations bypass Rails' usual request-level security safeguards, and raw SQL concatenation—even for one-time operations—opens the door to injection if any dynamic data is involved. It's less about immediate risk from user input (since migrations aren't handling web requests) and more about unfiltered data sources, cross-environment execution, and future code maintenance.

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