Parameterized queries and SQL injection in ActiveRecord

sql_injection_and_parameterizing_queries.rb

require 'active_record'
ActiveRecord::Base.establish_connection adapter: 'sqlite3', database: ':memory:'
ActiveRecord::Base.logger = Logger.new $stdout
ActiveSupport::LogSubscriber.colorize_logging = false
ActiveRecord::Schema.define do 
  create_table(:users) { |t| t.string :name }
end

# Create some users
User = Class.new ActiveRecord::Base
%w[josh yumin mhar divya].each { |n| User.create! name: n }

# A malicious query that a user submitted in a form or w/e
user_supplied_name = <<SQL.chomp
not-a-name')
UNION
SELECT 1 as id, (SELECT group_concat(name) FROM users) as name
WHERE (''='
SQL

# GOOD: Explicitly parameterizing
# We name the value because AR knows about its type and and can prevent attacks
# due to type errors, eg https://twitter.com/sgrif/status/656292234975776768
User.where 'name = :n', n: "josh"  # => #<ActiveRecord::Relation [#<User id: 1, name: "josh">]>
User.where 'name = :n', n: "yumin" # => #<ActiveRecord::Relation [#<User id: 2, name: "yumin">]>
User.where 'name = :n', n: "mhar"  # => #<ActiveRecord::Relation [#<User id: 3, name: "mhar">]>
User.where 'name = :n', n: "divya" # => #<ActiveRecord::Relation [#<User id: 4, name: "divya">]>
User.where 'name = :n', n: user_supplied_name # => #<ActiveRecord::Relation []>

# GOOD: ActiveRecord's hash interfaces will parameterize for you
User.find_by name: user_supplied_name # => nil
User.where name: user_supplied_name   # => #<ActiveRecord::Relation []>

# BAD: Not parameterized, our user injects their own SQL!
User.where("name = '#{user_supplied_name}'") # => #<ActiveRecord::Relation [#<User id: 1, name: "josh,yumin,mhar,divya">]>
    .first                                   # => #<User id: 1, name: "josh,yumin,mhar,divya">
    .name                                    # => "josh,yumin,mhar,divya"

# >> -- create_table(:users)
# >> D, [2017-07-14T09:56:26.436322 #12218] DEBUG -- :    (0.8ms)  SELECT sqlite_version(*)
# >> D, [2017-07-14T09:56:26.436753 #12218] DEBUG -- :    (0.3ms)  CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar)
# >>    -> 0.0179s
# >> D, [2017-07-14T09:56:26.446063 #12218] DEBUG -- :    (0.1ms)  CREATE TABLE "ar_internal_metadata" ("key" varchar NOT NULL PRIMARY KEY, "value" varchar, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL)
# >> D, [2017-07-14T09:56:26.452824 #12218] DEBUG -- :   ActiveRecord::InternalMetadata Load (0.1ms)  SELECT  "ar_internal_metadata".* FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = ? LIMIT ?  [["key", "environment"], ["LIMIT", 1]]
# >> D, [2017-07-14T09:56:26.455960 #12218] DEBUG -- :    (0.0ms)  begin transaction
# >> D, [2017-07-14T09:56:26.456763 #12218] DEBUG -- :   SQL (0.1ms)  INSERT INTO "ar_internal_metadata" ("key", "value", "created_at", "updated_at") VALUES (?, ?, ?, ?)  [["key", "environment"], ["value", "default_env"], ["created_at", "2017-07-14 14:56:26.456219"], ["updated_at", "2017-07-14 14:56:26.456219"]]
# >> D, [2017-07-14T09:56:26.456927 #12218] DEBUG -- :    (0.0ms)  commit transaction
# >> D, [2017-07-14T09:56:26.459231 #12218] DEBUG -- :    (0.0ms)  begin transaction
# >> D, [2017-07-14T09:56:26.459705 #12218] DEBUG -- :   SQL (0.1ms)  INSERT INTO "users" ("name") VALUES (?)  [["name", "josh"]]
# >> D, [2017-07-14T09:56:26.459867 #12218] DEBUG -- :    (0.0ms)  commit transaction
# >> D, [2017-07-14T09:56:26.460021 #12218] DEBUG -- :    (0.0ms)  begin transaction
# >> D, [2017-07-14T09:56:26.460290 #12218] DEBUG -- :   SQL (0.1ms)  INSERT INTO "users" ("name") VALUES (?)  [["name", "yumin"]]
# >> D, [2017-07-14T09:56:26.460412 #12218] DEBUG -- :    (0.0ms)  commit transaction
# >> D, [2017-07-14T09:56:26.460569 #12218] DEBUG -- :    (0.0ms)  begin transaction
# >> D, [2017-07-14T09:56:26.460832 #12218] DEBUG -- :   SQL (0.1ms)  INSERT INTO "users" ("name") VALUES (?)  [["name", "mhar"]]
# >> D, [2017-07-14T09:56:26.460957 #12218] DEBUG -- :    (0.0ms)  commit transaction
# >> D, [2017-07-14T09:56:26.461098 #12218] DEBUG -- :    (0.0ms)  begin transaction
# >> D, [2017-07-14T09:56:26.461461 #12218] DEBUG -- :   SQL (0.1ms)  INSERT INTO "users" ("name") VALUES (?)  [["name", "divya"]]
# >> D, [2017-07-14T09:56:26.461584 #12218] DEBUG -- :    (0.0ms)  commit transaction
# >> D, [2017-07-14T09:56:26.462016 #12218] DEBUG -- :   User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE (name = 'josh') LIMIT ?  [["LIMIT", 11]]
# >> D, [2017-07-14T09:56:26.463302 #12218] DEBUG -- :   User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE (name = 'yumin') LIMIT ?  [["LIMIT", 11]]
# >> D, [2017-07-14T09:56:26.463685 #12218] DEBUG -- :   User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE (name = 'mhar') LIMIT ?  [["LIMIT", 11]]
# >> D, [2017-07-14T09:56:26.464193 #12218] DEBUG -- :   User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE (name = 'divya') LIMIT ?  [["LIMIT", 11]]
# >> D, [2017-07-14T09:56:26.464662 #12218] DEBUG -- :   User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE (name = 'not-a-name'')
# >> UNION
# >> SELECT 1 as id, (SELECT group_concat(name) FROM users) as name
# >> WHERE (''''=''') LIMIT ?  [["LIMIT", 11]]
# >> D, [2017-07-14T09:56:26.466902 #12218] DEBUG -- :   User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE "users"."name" = ? LIMIT ?  [["name", "not-a-name')\nUNION\nSELECT 1 as id, (SELECT group_concat(name) FROM users) as name\nWHERE (''='"], ["LIMIT", 1]]
# >> D, [2017-07-14T09:56:26.467942 #12218] DEBUG -- :   User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."name" = ? LIMIT ?  [["name", "not-a-name')\nUNION\nSELECT 1 as id, (SELECT group_concat(name) FROM users) as name\nWHERE (''='"], ["LIMIT", 11]]
# >> D, [2017-07-14T09:56:26.468856 #12218] DEBUG -- :   User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE (name = 'not-a-name')
# >> UNION
# >> SELECT 1 as id, (SELECT group_concat(name) FROM users) as name
# >> WHERE (''='') LIMIT ?  [["LIMIT", 11]]
# >> D, [2017-07-14T09:56:26.469450 #12218] DEBUG -- :   User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE (name = 'not-a-name')
# >> UNION
# >> SELECT 1 as id, (SELECT group_concat(name) FROM users) as name
# >> WHERE (''='') ORDER BY "users"."id" ASC LIMIT ?  [["LIMIT", 1]]