SQLインジェクション対策となるPrepared Statement(プリペアドステートメント)について調べた

はじめに

フィヨルドブートキャンプの課題を進めていて、ged/ruby-pg: A PostgreSQL client library for RubyでのInsert方法を調べていたらPrepared Statemementで実行する方法を紹介している記事があった。
単純に connection.exec("INSERT INTO ~") というようなSQL文を実行したら良いと思っていたので、当該記事での実行方法との違いやそもそもプリペアドステートメントとは何なのかについて調べた。

プリペアドステートメントとは - IT用語辞典 e-Words
このサイトによれば、ユーザーからの入力値をあらかじめ用意した雛形の可変部分に挿入してSQL文を作り、それを言語処理系（今回の場合はRuby）によって事前コンパイルすることでパフォーマンスメリット、SQLインジェクション防止効果があるという。
この入力値によって実行するSQLが変わることを、あらかじめ決まったSQL文を実行する（=静的）ものではないので、動的SQLといい、この時に用いるのがPrepared Statementの方法ということらしい。
prepare=準備する statement=文、宣言

SQLインジェクションとなるSQL文を試してみる

SQLインジェクションとは？仕組み・被害事例・対策をわかりやすく解説 | クラウド型WAF『攻撃遮断くん』

課題にも“SQLインジェクションへの対策をすること”が書かれていたので、このPrepared Statementを用いればユーザーの入力値に悪意のあるSQLが仕込まれても防ぐことができるんだろう、ということで試してみた。
まずは以下のようなテーブルを用意。

create table memos (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  body text NOT NULL,
  is_delete boolean NOT NULL
);

 id | title | body | is_delete
----+-------+------+-----------

SQLインジェクションが発生するようなユーザー入力値を再現しSQL実行すると、一回のINSERTで2つのデータを作成することができた。このようなSQLを使えば開発者の想定していない誤作動を引き起こすことができてとても危険であることがわかった。

# frozen_string_literal: true

require 'pg'

connection = PG.connect(
  host: 'localhost',
  port: 5432,
  dbname: 'sinatra_app',
  user: 'user',
  password: 'password'
)

userinput_title = 'testuser'
userinput_body = "dummy', FALSE), ('attacker', 'hacked', FALSE)--"
connection.exec("INSERT INTO memos (title, body, is_delete) VALUES ('#{userinput_title}', '#{userinput_body}')")

 id |  title   |  body  | is_delete
----+----------+--------+-----------
  1 | testuser | dummy  | f
  2 | attacker | hacked | f

Prepared Statementとなるように実装方法を変えて試してみた

上記の入力値はそのままで、INSERT処理をPrepared Statementに変更。
入力値を使ってSQL文字列を組み立てるのではなく、動的部分の変数に代入する方式のため、 userinput_body がそのままの文字列で格納されていることが確認できた。

connection.prepare('insert', 'INSERT INTO memos (title, body, is_delete) VALUES ($1, $2, FALSE)')
connection.exec_prepared('insert', [userinput_title, userinput_body])

 id |  title   |                      body                       | is_delete
----+----------+-------------------------------------------------+-----------
  1 | testuser | dummy', FALSE), ('attacker', 'hacked', FALSE)-- | f

なお、 prepare → exec_prepared ではなく、 exec メソッドでもこれは実現できる。

connection.exec('INSERT INTO memos (title, body, is_delete) VALUES ($1, $2, FALSE)', [userinput_title, userinput_body])

 id |  title   |                      body                       | is_delete
----+----------+-------------------------------------------------+-----------
  1 | testuser | dummy', FALSE), ('attacker', 'hacked', FALSE)-- | f

“exec_prepared”と“exec”の違い

参考記事によれば、動的部分のパラメータだけが異なる似たようなSQLを何度も実行する場合は、 exec_prepared を使用した方がパフォーマンス上有利らしい。
これは実行時にSQLの解析を都度実行する exec に比べ、 exec_prepared は処理系に解析を任せて動的部分だけが変化したSQLを流用するため、DBの実行計画も最適化されやすいのかな。
今回のようなシンプルなINSERT文の場合は、あまり恩恵はないらしいのでどっちでもいいみたい。

参考記事

pgを使ってPostgreSQLを操作する & SQLインジェクション - Hit the books!!
pg gem のexec系メソッドの使い分け - 時々とおまわり
postgresql - Ruby PG Gem `exec_params` vs `exec_prepared` - Stack Overflow
Method: PG::Connection#exec_prepared — Documentation for pg (1.5.9)

さいごに

ruby-pgのGithubにリンクが書かれていた公式ドキュメントが鯖落ちしており、外部ドキュメントなどを参考にしたので調べた結果などは推測が多いけど、単純なSQL文字列の構築はセキュリティリスクを招き、コードの読みやすさも劣ることがわかったので勉強になった。