2021 年度 DB 技術 : SQL の利用 (2)

はじめに

今回は, Ruby スクリプトから MySQL サーバにアクセスし, テーブルの情報を Web ブラウザから閲覧するための HTML ファイルを作成する.

前回との大きな違いは, テーブルの検索に使うためのキーワードをユーザに入力させる点である. スクリプトの「引数」としてユーザがキーワードを指定する. ユーザからの入力を利用するため, 「SQL インジェクション」といったセキュリティ的な脆弱性が生じないようなプログラムを書く必要がある.

今回の演習で利用する Ruby のライブラリ.

セキュリティ対策

サンプルファイル

以下で述べる 1st Step から 6th Step までのサンプルスクリプトをサーバ上に 用意している.それを活用して構わない.

mysql2 のサンプル

  $ ls ~sugiyama/sample3

    db3-1.rb  db3-2.rb  db_info.yml   test.rb 

ActiveRecord のサンプル

  $ ls ~sugiyama/sample4

    db4-1.rb  db4-2.rb  db_info.yml       

例えば以下のようにして,自分のディレクトリにコピーして使うことができる.

mysql2 の 1st Step のサンプルを使う場合:

  $ cp ~sugiyama/sample1/db1-1.rb .

また,各ステップでの差分は,diff コマンドで調べることができる.-c オプションを付けて diff コマンドを実行した場合には,互いに異なる行の先頭に "!" が付く.

$ diff -c  ~/sample3/db3-1.rb  ~/sample3/db3-2.rb 

.... (出力は略) ....

キーワードの入力方法: 引数の利用

本演習では, ruby スクリプトの引数に与えたデータを用いてテーブルの検索・データ挿入・削除などを行うことにする. その準備として, まずは引数の利用方法について演習する.

ファイル test.rb を新規作成し, 以下のように 1 行書いてみる. スクリプト実行時に引数で与えられた文字列はスペース区切りで配列 ARGV に格納されるため, 以下のように ARGV[0] は 1 番目の配列要素を意味する.

$ vi test.rb  (もしくは, cp ~sugiyama/sample3/test.rb . )

  puts ARGV[0]

以下のように適当な文字列を引数としてスクリプトを実行してみよ. 空白は引数の区切りと解釈されるが, 空白文字を入れたい場合はエスケープするか全体をクォーテーションで囲む.

$ ruby test.rb herohero

  herohero

$ ruby test.rb herohero hhh

  herohero

$ ruby test.rb herohero\ hhh

  herohero hhh

$ ruby test.rb "herohero hhh"

  herohero hhh

$ ruby test.rb 'herohero hhh'

  herohero hhh

$ ruby test.rb "h'erohero hhh"

  h'erohero hhh

SQL インジェクション

SQL インジェクションは, プログラムの作者が予想していなかったデータを注入することにより, 不正な処理を行わせる攻撃方法のことである.

例えば, 以下のような SQL 文と入力があったらどうなるだろうか?

SQL 文 : SELECT * from 商品 where 価格 = ?     (? は数値型)

入力: 1 or 1=1
入力: 1; show tables

もしくは,以下のような SQL 文と入力があったらどうなるだろうか?

SQL 文 : SELECT * from 商品 where 商品番号 = '?'  (? は文字型)

入力: ' or 1 -- 
入力: '; show tables -- 

以下では mysql モニタで実際に試してみることにする. mysql モニタを起動して 自分のデータベースに接続すること.

$ mysql -u hogehoge -p

  Enter password: 
  Welcome to the MariaDB monitor.  Commands end with ; or \g.
  Your MariaDB connection id is 256
  Server version: 10.1.38-MariaDB-0+deb9u1 Debian 9.8

  Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


  MariaDB [(none)]> use jXXXX       (自分のデータベースに接続)

  Reading table information for completion of table and column names
  You can turn off this feature to get a quicker startup with -A

  Database changed

数値型の場合

"1 or 1=1" が入力されると, "1=1" は真であるため, すべてのレコードで条件が成り立つことになる. 本来は価格を指定させることで表示する商品を限定するはずであったが, "or 1=1" のために見せなくない情報も見せてしまうことになる.

MariaDB [j4db]> select * from 商品 where 価格 = 1 or 1=1;

+--------------+--------------------------------+--------+
| 商品番号     | 商品名                         | 価格   |
+--------------+--------------------------------+--------+
| A01          | オフィス用紙 A4                |   2000 |
| A02          | オフィス用紙 A3                |   4000 |
| A03          | オフィス用紙 B5                |   1500 |
| B01          | トナーカートリッジ黒           |  25000 |
| C01          | ホワイトボード                 |  14000 |
| X00          | ノート                         |    120 |
| X01          | テープ                         |    100 |
| Y01          | はさみ                         |    100 |
| A04          | 紙                             |    300 |
+--------------+--------------------------------+--------+
9 rows in set (0.01 sec)

念のために確認してみる."or 1=1" を外して "1" のみの入力とすると,価格が 1 なものは無いのでレコードは表示されない.

MariaDB [j4db]> select * from 商品 where 価格 = 1;

Empty set (0.001 sec)

次に "1 ; show tables" を入力してみる. 当然ながら 2 つの SQL 文が実行されることになる. ";" の後に "show tables" などの命令が書かれると, データベース上のテーブル名が簡単にバレることになる. ここでは行わないが, "delete" や "drop" といった命令が入力されるとデータが消されてしまうことになる.

MariaDB [j4db]> select * from 商品 where 価格 = 1; show tables;

Empty set (0.01 sec)

+----------------+
| Tables_in_j4db |
+----------------+
| 商品           |
| 商品2          |
| 学生           |
| 履修           |
| 履修情報       |
+----------------+
5 rows in set (0.00 sec)

文字型の場合

文字型の場合はシングルクォートで囲まれるが, "--" 以下はコメントアウトとして扱われるので, " ' or 1 -- " を入力すると (シングルクォートの間に書くと), テーブルに含まれる全ての行が表示されてしまう.

MariaDB [j4db]> select * from 商品 where 商品番号 = '' or 1 -- '
    -> ;
+--------------+--------------------------------+--------+
| 商品番号     | 商品名                         | 価格   |
+--------------+--------------------------------+--------+
| A01          | オフィス用紙 A4                |   2000 |
| A02          | オフィス用紙 A3                |   4000 |
| A03          | オフィス用紙 B5                |   1500 |
| B01          | トナーカートリッジ黒           |  25000 |
| C01          | ホワイトボード                 |  14000 |
| X00          | ノート                         |    120 |
| X01          | テープ                         |    100 |
| Y01          | はさみ                         |    100 |
| A04          | 紙                             |    300 |
+--------------+--------------------------------+--------+
9 rows in set (0.00 sec)

次に,"A01'; show tables ; -- " のように入力されると, データベース上のテーブル名が簡単にバレることになる. ここでは行わないが, "delete" や "drop" されるとデータが消されてしまうことになる.

MariaDB [j4db]> SELECT * FROM 商品 WHERE 商品番号 = 'A01'; show tables ; -- '
+--------------+-----------------------+--------+
| 商品番号     | 商品名                | 価格   |
+--------------+-----------------------+--------+
| A01          | オフィス用紙 A4       |   2000 |
+--------------+-----------------------+--------+
1 row in set (0.00 sec)

+----------------+
| Tables_in_j4db |
+----------------+
| 商品           |
| 商品2          |
| 学生           |
| 履修           |
| 履修情報       |
+----------------+
5 rows in set (0.01 sec)

SQL インジェクション対策

SQL インジェクションの対策は以下の 2 つである.

  • 文字列リテラルに対しては,エスケープすべき文字をエスケープする.
    • MySQL ではシングルクォート「’」やバックスラッシュ「\」を単なる文字列となるように置換(エスケープ)する.
      • ‘ => ‘‘ or ‘ => ¥ ‘
      • ¥ => ¥¥
  • 数値リテラルに対しては, 数値以外の文字を混入させない.

Ruby の mysql2 ライブラリを使う場合

Ruby の mysql2 ライブラリを使う場合, SQL 文を作る際に以下のような文字列連結を使うのは避けるべきである (ARGV[0] は入力を意味する). 文字列連結を用いると, "1 or 1=1" のような不正な入力をそのまま受け付けてしまうためである.

sql1 = “SELECT * FROM 商品 WHERE 価格 = “ + ARGV[0].to_s 
sql2 = “SELECT * FROM 商品 WHERE 商品番号 =' "  + ARGV[0].to_s + “’” 

sql1 = “SELECT * FROM 商品 WHERE 価格 = #{ARGV[0]}”
sql2 = “SELECT * FROM 商品 WHERE 商品番号 = ‘#{ARGV[0]}’

文字列連結の代わりにプレースホルダを使うのが有効である.

引数が「整数型」の場合

整数型の属性「価格」で検索する例を考える. プレースホルダの利用の有無による違いを確認するために, 以下でのプログラム (db3-1.rb) では文字列連結の場合とプレースホルダの場合の 2 通りを利用している. なお, スクリプト中で読み込む db_info.yml は前回作成したものを利用する.

$ vi db3-1.rb   (もしくは cp ~sugiyama/sample3/db3-1.rb . )

  require 'mysql2'
  require 'yaml'

  # 設定ファイルの読み込み
  mydb = YAML.load_file( "db_info.yml" )

  # データベースへの接続
  client = Mysql2::Client.new(
    :host => "#{mydb["SERV"]}",
    :username => "#{mydb["USER"]}",
    :password => "#{mydb["PASS"]}",
    :database => "#{mydb["DBNM"]}"
  )

  puts "文字列連結の場合"
  sql = "SELECT * FROM 商品 WHERE 価格 = #{ARGV[0]}"
  result = client.query( sql )
  result.each do |item|
  puts item
  end

  puts "プレースホルダの場合"
  sql = client.prepare("SELECT * FROM 商品 WHERE 価格 = ?")
  result = sql.execute( client.escape( ARGV[0] ) )
  result.each do |item|
    puts item
  end

このスクリプトを以下のように実行して, それぞれの時の出力を確認する (db3-1.rb は入力された価格の行を出力するものである). スクリプト作者の意図通りに「整数」が入力された場合は特に問題はない.

$ ruby db3-1.rb 2000

  文字列連結の場合
  {"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}

  プレースホルダの場合
  {"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}

SQL インジェクションの例として有名な "or 1=1" を加えると, 文字列連結の場合に問題が生じる.

$ ruby db3-1.rb "2000 or 1=1"

  文字列連結の場合
  {"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}
  {"商品番号"=>"A02", "商品名"=>"オフィス用紙 A3", "価格"=>4000}
  {"商品番号"=>"A03", "商品名"=>"オフィス用紙 B5", "価格"=>1500}
  {"商品番号"=>"B01", "商品名"=>"トナーカートリッジ黒", "価格"=>25000}
  {"商品番号"=>"C01", "商品名"=>"ホワイトボード", "価格"=>14000}
  {"商品番号"=>"X00", "商品名"=>"ノート", "価格"=>120}
  {"商品番号"=>"X01", "商品名"=>"テープ", "価格"=>100}
  {"商品番号"=>"Y01", "商品名"=>"はさみ", "価格"=>100}
  {"商品番号"=>"A04", "商品名"=>"紙", "価格"=>300}

  プレースホルダの場合
  {"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}

入力に ; が含まれているとエラーになる. 複数の SQL 文を一度に実行することは mysql2 では許していないようである (全てのライブラリでこのような対策がなされているかは自明でない. 違うライブラリを使う時はその都度試してみるべきだろう).

$ ruby db3-1.rb "2000 ; show tables"

  Traceback (most recent call last):
  4: from db3-1.rb:18:in `<main>'
  3: from /usr/lib/ruby/vendor_ruby/mysql2/client.rb:130:in `query'
  2: from /usr/lib/ruby/vendor_ruby/mysql2/client.rb:130:in `handle_interrupt'
  1: from /usr/lib/ruby/vendor_ruby/mysql2/client.rb:131:in `block in query'
  /usr/lib/ruby/vendor_ruby/mysql2/client.rb:131:in `_query': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'show  tables' at line 1 (Mysql2::Error)

引数が「文字型」の場合

文字型の属性「商品番号」で検索する例を考える. db3-1.rb を db3-2.rb としてコピーし, db3-2.rb の中の変数 sql を以下のように修正してみよ. 今までの価格は「整数型」であったが, 今回の商品番号は「文字型」なので, 文字列連結で SQL 文を生成する場合は引数の値をシングルクォートで囲まねばならない. 一方でプレースホルダを使う場合は ? をシングルクォートで囲む必要はない.

$ cp db3-1.rb db3-2.rb 

$ vi db3-2.rb     (もしくは cp ~sugiyama/sample3/db3-2.rb . )

  ...(略)...

  #sql = "SELECT * FROM 商品 WHERE 価格 = #{ARGV[0]}"
  sql = "SELECT * FROM 商品 WHERE 商品番号 = '#{ARGV[0]}'"

  ...(略)...

  #sql = client.prepare("SELECT * FROM 商品 WHERE 価格 = ?")
  sql = client.prepare("SELECT * FROM 商品 WHERE 商品番号 = ?")

  ...(中略)...

このスクリプトを以下のように実行して, それぞれの時の出力を確認する. まず, 想定通りの入力がある場合は特に問題ない.

$ ruby db3-2.rb A01

  文字列連結の場合
  {"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}

  プレースホルダの場合
  {"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}

不正な入力をすると, やはり文字列連結の場合には本来表示されない行が表示されてしまう.

$ ruby db3-2.rb "A01' or 1 -- "       (注: -- の前後に空白が必要)

  文字列連結の場合
  {"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}
  {"商品番号"=>"A02", "商品名"=>"オフィス用紙 A3", "価格"=>4000}
  {"商品番号"=>"A03", "商品名"=>"オフィス用紙 B5", "価格"=>1500}
  {"商品番号"=>"B01", "商品名"=>"トナーカートリッジ黒", "価格"=>25000}
  {"商品番号"=>"C01", "商品名"=>"ホワイトボード", "価格"=>14000}
  {"商品番号"=>"X00", "商品名"=>"ノート", "価格"=>120}
  {"商品番号"=>"X01", "商品名"=>"テープ", "価格"=>100}
  {"商品番号"=>"Y01", "商品名"=>"はさみ", "価格"=>100}
  {"商品番号"=>"A04", "商品名"=>"紙", "価格"=>300}

  プレースホルダの場合
                         (出力なし)

Active Record の場合

mysql2 の場合と同様に, 文字列連結は SQL インジェクションの原因となりやすい. 以下のような書き方をしてはいけない.

User.where("商品番号 = #{ARGV[0]}").each do |item|
  p item
end

やはり mysql2 と同様に,SQL 対策としてプレースホルダを使うのが良い.

引数が「整数型」の場合

プレースホルダの利用の有無による違いを確認するために, 以下の内容を db4-1.rb として保存せよ. スクリプト中で読み込む db_info.yml は前回作成したものを利用する. なお, Active Record ではプレースホルダを用いた場合には ' や \ といった文字は自動的にエスケープされる.

$ vi db4-1.rb   (もしくは cp ~sugiyama/sample4/db4-1.rb . )

  require 'active_record'
  require 'yaml'

  mydb = YAML.load_file( "db_info.yml" )

  # DB接続設定
  ActiveRecord::Base.establish_connection(
    adapter:  mydb["ADPT"],
    host:     mydb["SERV"],
    username: mydb["USER"],
    password: mydb["PASS"],
    database: mydb["DBNM"]
  )

  # テーブルにアクセスするためのクラスを宣言
  class User < ActiveRecord::Base
    self.table_name = '商品'
  end

  puts "文字列連結の場合"
  result = User.where( "価格 = #{ARGV[0]}" )
  result.each do |item|
    p item
  end

  puts "プレースホルダの場合 (1)"
  result = User.where( "価格 = ?", ARGV[0] )
  result.each do |item|
    p item
  end

  puts "プレースホルダの場合 (2)"
  result = User.where( 価格: ARGV[0] )
  result.each do |item|
    p item
  end

このスクリプトを以下のように実行して, それぞれの時の出力を確認する. まず, スクリプト作者の意図通りに整数を入力すると特に問題は生じない.

$ ruby db4-1.rb 2000

  文字列連結の場合
  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>

  プレースホルダの場合 (1)
  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>

  プレースホルダの場合 (2)
  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>

SQL インジェクションの例として有名な "or 1=1" を加えると, 文字列連結の場合に問題が生じる.

$ ruby db4-1.rb "2000 or 1=1"

  文字列連結の場合
  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>
  #<User 商品番号: "A02", 商品名: "オフィス用紙 A3", 価格: 4000>
  #<User 商品番号: "A03", 商品名: "オフィス用紙 B5", 価格: 1500>
  #<User 商品番号: "B01", 商品名: "トナーカートリッジ黒", 価格: 25000>
  #<User 商品番号: "C01", 商品名: "ホワイトボード", 価格: 14000>
  #<User 商品番号: "X00", 商品名: "ノート", 価格: 120>
  #<User 商品番号: "X01", 商品名: "テープ", 価格: 100>
  #<User 商品番号: "Y01", 商品名: "はさみ", 価格: 100>
  #<User 商品番号: "A04", 商品名: "紙", 価格: 300>

  プレースホルダの場合 (1)
  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>

  プレースホルダの場合 (2)
  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>

入力に ; が含まれているとエラーになる. エラーメッセージからすると,複数の SQL 文を一度に実行することを許さないようにしていないようである.

$ ruby db4-1.rb "2000 ; show tables "

  文字列連結の場合
  syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '; show tables )' at line 1: SELECT `商品`.* FROM `商品` WHERE (価格 = 2000 ; show tables ) (ActiveRecord::StatementInvalid)

引数が「文字型」の場合

次に, db4-1.rb をコピーして db4-2.rb とし,fb4-2.rb 中の変数 sql を以下のように修正する. 商品番号は文字型なので文字列連結で SQL 文を生成する場合は引数の値をシングルクォートで囲まねばならないが, プレースホルダを使う場合は ? をシングルクォートで囲む必要はない.

$ cp db4-1.rb  db4-2.rb

$ vi db4-2.rb    (もしくは cp ~sugiyama/sample4/db4-2.rb . )

  ...(略)...

  #result = User.where( "価格 = #{ARGV[0]}" )
  result = User.where( "商品番号 = '#{ARGV[0]}'" )

  ...(略)...

  #result = User.where( "価格 = ?", ARGV[0] )
  result = User.where( "商品番号 = ?", ARGV[0] )

  ...(略)...

  #result = User.where( 価格: ARGV[0] )
  result = User.where( 商品番号: ARGV[0] )

  ...(略)...

このスクリプトを以下のように実行して, それぞれの時の出力を確認する. まず, 想定通りの入力がある場合は特に問題ない.

$ ruby db4-2.rb A01

  文字列連結の場合
  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>

  プレースホルダの場合 (1)
  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>

  プレースホルダの場合 (2)
  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>

mysql2 で使ったのと同じ不正な入力をすると, 文字列連結の場合にエラーが出る.

$ ruby db4-2.rb "A01' or 1 -- "       (注: -- の前後に空白が必要)

  文字列連結の場合
  /usr/lib/ruby/vendor_ruby/mysql2/client.rb:131:in `_query': Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1: SELECT `商品`.* FROM `商品` WHERE (商品番号 = 'A01' or 1 -- ') (ActiveRecord::StatementInvalid)

上記のメッセージを見ると, 作られる SQL 文の末尾が「-- ')」となっており, 括弧閉じがコメントアウトされていることがわかる. 引数の値を若干変更すれば, 文字列連結の場合にやはり本来表示されない行が表示されてしまうことがわかる.

$ ruby db4-2.rb "A01' or 1 )  -- "       (注: -- の前後に空白が必要)

  文字列連結の場合
  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>
  #<User 商品番号: "A02", 商品名: "オフィス用紙 A3", 価格: 4000>
  #<User 商品番号: "A03", 商品名: "オフィス用紙 B5", 価格: 1500>
  #<User 商品番号: "B01", 商品名: "トナーカートリッジ黒", 価格: 25000>
  #<User 商品番号: "C01", 商品名: "ホワイトボード", 価格: 14000>
  #<User 商品番号: "X00", 商品名: "ノート", 価格: 120>
  #<User 商品番号: "X01", 商品名: "テープ", 価格: 100>
  #<User 商品番号: "Y01", 商品名: "はさみ", 価格: 100>
  #<User 商品番号: "A04", 商品名: "紙", 価格: 300>

  プレースホルダの場合 (1)
            (出力なし)

  プレースホルダの場合 (2)
            (出力なし)

課題

  • 引数で与えた数値もしくは文字列を元に検索し,その検索結果を HTML ファイルに出力するプログラムを作成しなさい. さらに, スクリプトの引数も HTML ファイルに表示するようにしなさい. HTML ファイルへの出力方法は前回の資料を参照すること. なお, HTML のタグや css を積極的に利用して, 人が読みやすい Web となるように出力を工夫しなさい.
    • mysql2 か Active Record のどちらか好きな方を使うこと.
    • 検索に用いるテーブルは上記で用いた「商品」テーブル以外のものにすること.
    • 提出物:作成した Ruby スクリプト. HTML ファイルをブラウザで表示したもの (スクリーンショット). 但し,スクリーンショットには必ずブラウザのアドレスバー (http://pluto.epi.it.matsue-ct.jp/~jXXXX/YYY.htm といった文字列が表示されている部分) を含めること.
    • 出力のイメージ (もっと見やすくすること)

注意事項

本課題ではユーザの入力 (引数) を HTML ファイルに表示することを求めているが, プログラム中で,

引数 (商品番号) : <%= ARGV[0] %>    

のように書いてしまうと「クロスサイトスクリプティング」という脆弱性の原因となる. 具体的には, 入力 (引数) として,

  • <body bgcolor=black>
  • <script>alert(“hogehoge!”)</script>

などといった HTML タグや JavaScript スクリプトを試しに与えてみると良い. 作者の予期しない表示となるだろう.

クロスサイトスクリプティング対策としては, 入力値を制限したり, サニタイジングを行う. サニタイジングとは, HTML タグや JavaScript スクリプトが必要とする &,<,>,”,’ の5文字の特殊文字について, そのまま画面に表示する文字列となるように置換(エスケープ)することを意味する. Ruby では

require "cgi/escape"

をして, escapeHTML メソッドを

引数 (商品番号) : <%= CGI.escapeHTML( ARGV[0] ) %>         

のように使うと良いだろう. 例えば, 引数に < を与えると, < が &lt; に変換される.