Last active
July 13, 2021 14:20
-
-
Save JoshCheek/44d892d4034c5b1c620ee1cc9acbfe42 to your computer and use it in GitHub Desktop.
ActiveRecord query parameterization inconsistency
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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