karlley's tech blog

学習メモや記録とか

PostgreSQLのSQLインジェクションの対策

前回の記事ではPostgreSQLを使ったSinatraのメモアプリにSQLインジェクション攻撃ができることを確認したので、その対策について調べてみました。

環境

今回紹介するSQLインジェクションの対策は下記の環境で行いました。

結論

SQLインジェクションの対策には次の2つを行う。

  1. プレースホルダー(バインド機構)でSQL文を組み立てる。
  2. パラメータを正しくリテラルとして展開する。
  3. 文字列リテラル: エスケープすべき文字をエスケープする。
  4. 数値リテラル: 数値以外の文字を混入させない。

対策1. プレースホルダー(バインド機構)でSQL文を組み立てる

プレースホルダー(バインド機構)については以前の記事にまとめていますので参考にしてください。

変数を使ってSQL内のパラメータへ値を機械的に代入しSQL文を組み立てることでSQLの書き換えを防止することができます。DBや言語の種類によってはプレースホルダーでのSQL組み立てをサポートしてくれるライブラリやメソッドが用意されている場合もあります。

例) フォーム画面から送信される値params を使ってmemos テーブルにレコードを作成する。

-- NG
INSERT INTO memos (id, title, content) VALUES ('params[:id]', 'params[:title]', 'params[:content]')

-- OK
INSERT INTO memos (id, title, content) VALUES ($1, $2, $3)

DBに送信する値params を変数$ に置き換えています。ここの変数名はDBの種類等で異なるようです。 置き換えた変数$1$2$3 に送信する値params[:id]params[:title]params[:content]機械的に代入(バインド)することでSQLの書き換えを防止します。 バインド処理はSQLではできないのでpg gemのexec_params メソッドを使います。詳細は後述します。

対策2. パラメータを正しくリテラルとして展開する

SQL組み立てに使用するパラメータを正しくリテラルとして展開しないと意図しないSQLの実行を許してしまいます。 リテラル展開時にエスケープする文字はDBの種類や設定によって異なるので、DBやライブラリに用意されている展開用のメソッドを使うのが一般的なようです。

pg gemのexec_params メソッドはこの点も考慮されているようです(pg gemのexec系メソッドの使い分け)。

対策例

上記2点を踏まえた上での対策例を紹介します。

例) フォーム画面から送信される値params を使ってmemos テーブルにレコードを作成する。

require 'pg'

# paramsをバインド処理後にSQLを実行
def excute(query, params)
  connection = PG::Connection.new(dbname: memo_app)
  connection.exec_params(query, params)
end

# 元となるSQL、送信されたパラメータの配列を使いexcuteメソッドを実行
def create_memo(id, title, content)
  query = "INSERT INTO memos (id, title, content) VALUES ($1, $2, $3)"
  params = [params[:id], params[:title], params[:content]]
  excute(query, params)
end

create_memo メソッドで生成されるSQLは次のようなSQLです。

INSERT INTO #{TABLE_NAME} (id, title, content) VALUES ('params[:id]', 'params[:title]', 'params[:content]')

exec_params メソッドの次のような構文になっています。 オプションで型やデータ型を指定できるようですが割愛します。

exec_params('SQL文', [展開する値の配列])

この方法で「プレースホルダーでSQL文を組み立てる」、「パラメータを正しくリテラルとして展開する」という2点をクリアすることができました。前回の記事と同じ方法でSQLインジェクションを試してみましたが防止できていることを確認できました。

まとめ

時間は掛かりましたがSQLインジェクションの原因と対策について学ぶことができて良かったです。 セキュリティは難しいですがブラックボックスな部分が垣間見れるので面白みも感じることができるので楽しかったです。

参照

SQLインジェクションを試してみる - karlley's tech blog

静的プレースホルダと動的プレースホルダについて - karlley's tech blog

pg gem のexec系メソッドの使い分け - karlley's tech blog

安全なSQLの呼び出し方

PostgreSQL: The world's most advanced open source database

Sinatra: README (Japanese)

ged/ruby-pg: A PostgreSQL client library for Ruby