Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save JoshCheek/44d892d4034c5b1c620ee1cc9acbfe42 to your computer and use it in GitHub Desktop.
Save JoshCheek/44d892d4034c5b1c620ee1cc9acbfe42 to your computer and use it in GitHub Desktop.
ActiveRecord query parameterization inconsistency
# DB setup
require 'active_record'
ActiveRecord::Base.establish_connection adapter: 'postgresql', database: 'testdb'
ActiveRecord::VERSION::STRING # => "6.1.3.2"
# Helper method so errors don't kill the program
def show
yield
rescue StandardError => err
(err.cause||err).message.lines.first.strip
end
# Put a user in the DB that we'll try to query out
num = 123
User = Class.new ActiveRecord::Base
User.find_or_create_by! id: num
# For `where`, use ActiveRecord's parameter syntax
User.where 'id = ?', num # => #<ActiveRecord::Relation [#<User id: 123>]>
User.where 'id = ?', [num] # => #<ActiveRecord::Relation [#<User id: 123>]>
User.where 'id = $1', num # => #<ActiveRecord::Relation []>
User.where 'id = $1', [num] # => #<ActiveRecord::Relation []>
# Oof, this might bite someone!
User.find_by 'id = ?', num # => #<User id: 123>
User.find_by 'id = $1', num # => #<User id: 1>
# For fancier querying, use PostgreSQL's parameter syntax
show { User.find_by_sql "select ? as id", num } # => "undefined method `first' for 123:Integer"
show { User.find_by_sql "select ? as id", [num] } # => "ERROR: syntax error at or near \"as\""
show { User.find_by_sql "select ? as id", [[nil, num]] } # => "ERROR: syntax error at or near \"as\""
show { User.find_by_sql "select $1 as id", num } # => "undefined method `first' for 123:Integer"
show { User.find_by_sql "select $1 as id", [num] } # => [#<User id: 123>]
show { User.find_by_sql "select $1 as id", [[nil, num]] } # => [#<User id: 123>]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment