How to parameterize SQL queries in a migration in Rails.

rails_migration_parameterization_example.rb

class Omghi < ActiveRecord::Migration[6.1]
  def up
    # Some value we want to use in our query, that we don't want to interpolate.
    # The answers on Stack Overflow say to escape this param and then interpolate
    # it into the query, but when I went to OWASP training, they were pretty
    # emphatic that sanitization is inferior to parameterization. I don't 100%
    # know why (it was a giant auditorium, I didn't raise my hand and ask)
    param = 'hello world'

    # By using `exec_query` instead of `execute`, we can pass params.
    # A few things to NOTE:
    # * Param references are in the underlying database representation,
    #   eg here, `$1` is PostgreSQL notation. It does not appare to understand
    #   ActiveRecord's param notation.
    # * The way these migrations work, they don't directly have any of these
    #   methods, instead they catch the call to `execute` or `exec_query`, and
    #   delegate them to the migration's `connection`
    # * Also note that if you're in the `change` method, the connection is
    #   really a command recorder, which infers the `up` and `down` based on
    #   what you call on it.
    # * I haven't verified these exist on the command recorder, but on the connection,
    #   here are some alternative methods that may be better suited to your use case:
    #   `connection.methods.grep(/exec/).sort`
    #   # => exec_delete exec_insert exec_insert_all exec_query exec_update execute, ...
    rows = exec_query 'select $1 as param', 'SQL', [param] # <-- SOLUTION IS HERE

    # Showing that it works
    puts "RESULT: #{rows.to_a.pretty_inspect}"

    # `exit` will exit the program, rolling back the transaction.
    # This lets us rerun the migration over and over, while we're experimenting
    exit
  end

  def down
    # noop, this migration is for experimentation
  end
end