2020 年度 DB 技術 : SQL の利用 (2)
はじめに
今回は, Ruby スクリプトから MySQL サーバにアクセスし, テーブルの情報を Web ブラウザから閲覧するための HTML ファイルを作成する.
前回との大きな違いは, テーブルの検索に使うためのキーワードをユーザに入力させる点である. スクリプトの「引数」としてユーザがキーワードを指定する. ユーザからの入力を利用するため, 「SQL インジェクション」といったセキュリティ的な脆弱性が生じないようなプログラムを書く必要がある.
今回の演習で利用する Ruby のライブラリ.
- mysql2 <URL:https://rubygems.org/gems/mysql2/>
- 右カラムに「ドキュメント」内に "Usage (使い方)" 有り.
- Active Record <URL:https://github.com/rails/rails/tree/master/activerecord>
- Active Record の基礎 - Rails ガイド <URL:https://railsguides.jp/active_record_basics.html>
- Rails セキュリティガイド 7.2 節 <URL:https://railsguides.jp/security.html>
- ERB (埋め込み Ruby) [標準添付ライブラリ]
セキュリティ対策
- IPA, 安全なウェブサイトの作り方 <URL:https://www.ipa.go.jp/security/vuln/websecurity.html>
キーワードの入力方法: 引数の利用
本演習では, ruby スクリプトの引数に与えたデータを用いてテーブルの検索・データ挿入・削除などを行うことにする. その準備として, まずは引数の利用方法について演習する.
ファイル test.rb を新規作成し, 以下のように 1 行書いてみる. スクリプト実行時に引数で与えられた文字列はスペース区切りで配列 ARGV に格納されるため, 以下のように ARGV[0] は 1 番目の配列要素を意味する.
$ vi 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
or
SQL 文 : SELECT * from 商品 where 商品番号 = '?' (? は文字型) 入力: ' or 1 -- 入力: '; show tables --
上記を 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 j4db; 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)
"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 ‘ => ¥ ‘
- ¥ => ¥¥
- MySQL ではシングルクォート「’」やバックスラッシュ「\」を単なる文字列となるように置換（エスケープ）する.
- 数値リテラルに対しては, 数値以外の文字を混入させない.
Ruby の mysql2 ライブラリを使う場合, 以下で述べるように, SQL 文を作る際に文字列連結を使うのではなく, プレースホルダを使うのが有効である. 文字列連結を用いると, 先に述べたような "1 or 1=1" のような不正な入力をそのまま受け付けてしまうためである.
引数が「整数型」の場合
整数型の属性「価格」で検索する例を考える. プレースホルダの利用の有無による違いを確認するために, 以下でのプログラム (db3.rb) では文字列連結の場合とプレースホルダの場合の 2 通りを利用している. なお, スクリプト中で読み込む db_info.yml は前回作成したものを利用する.
$ vi db3.rb
require 'mysql2'
require 'erb'
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.rb は入力された価格の行を出力するものである). スクリプト作者の意図通りに「整数」が入力された場合は特に問題はない.
$ ruby db3.rb 2000
文字列連結の場合
{"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}
プレースホルダの場合
{"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}
SQL インジェクションの例として有名な "or 1=1" を加えると, 文字列連結の場合に問題が生じる.
$ ruby db3.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.rb "2000 ; show tables" 文字列連結の場合 /usr/lib/ruby/vendor_ruby/mysql2/client.rb:120: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) from /usr/lib/ruby/vendor_ruby/mysql2/client.rb:120:in `block in query' from /usr/lib/ruby/vendor_ruby/mysql2/client.rb:119:in `handle_interrupt' from /usr/lib/ruby/vendor_ruby/mysql2/client.rb:119:in `query' from db3.rb:22:in `<main>'
引数が「文字型」の場合
文字型の属性「商品番号」で検索する例を考える. db3.rb を db3-1.rb としてコピーし, db3-1.rb の中の変数 sql を以下のように修正してみよ. 今までの価格は「整数型」であったが, 今回の商品番号は「文字型」なので, 文字列連結で SQL 文を生成する場合は引数の値をシングルクォートで囲まねばならない. 一方でプレースホルダを使う場合は ? をシングルクォートで囲む必要はない.
$ cp db3.rb db3-1.rb
$ vi db3-1.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-1.rb A01
文字列連結の場合
{"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}
プレースホルダの場合
{"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}
不正な入力をすると, やはり文字列連結の場合には本来表示されない行が表示されてしまう.
$ ruby db3-1.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}
プレースホルダの場合
(出力なし)
課題 (2)
- 上記の db3.rb もしくは db3-1.rb を改良し, 検索結果を HTML ファイルに出力しなさい. さらに, スクリプトの引数も HTML ファイルに表示するようにしなさい. HTML ファイルへの出力方法は前回の資料を参照すること. なお, HTML のタグや css を積極的に利用して, 人が読みやすい Web となるように出力を工夫しなさい.
- 検索に用いるテーブルは上記で用いた「商品」テーブル以外のものにすること.
- 提出物：作成した Ruby スクリプト. HTML ファイルをブラウザで表示したもの (スクリーンショット)
- 出力のイメージ (もっと見やすくすること)
注意事項
本課題ではユーザの入力 (引数) を HTML ファイルに表示することを求めているが, プログラム中で,
引数 (商品番号) : <%= ARGV[0] %>
のように書いてしまうと「クロスサイトスクリプティング」という脆弱性の原因となる. 具体的には, 入力 (引数) として,
- <body bgcolor=black>
- <script>alert(“hogehoge!”)</script>
などといった HTML タグや JavaScript スクリプトを試しに与えてみると良い. 作者の予期しない表示となるだろう.
クロスサイトスクリプティング対策としては, 入力値を制限したり, サニタイジングを行う. サニタイジングとは, HTML タグや JavaScript スクリプトが必要とする &，<，>，”，’ の５文字の特殊文字について, そのまま画面に表示する文字列となるように置換（エスケープ）することを意味する. Ruby では
require "cgi/escape"
をして, escapeHTML メソッドを
引数 (商品番号) : <%= CGI.escapeHTML( ARGV[0] ) %>
のように使うと良いだろう. 例えば, 引数に < を与えると, < が < に変換される.
Appendix
以下は参考資料. 2020 年度の演習では行う必要は無い.
Active Record の場合
mysql2 の場合と同様に, 文字列連結は SQL インジェクションの原因となりやすい. 以下のような書き方をしてはいけない.
User.where("商品番号 = #{ARGV[0]}").each do |item|
p item
end
SQL 対策としてプレースホルダを使うのが良い. プレースホルダの利用の有無による違いを確認するために, 以下の内容を db4.rb として保存せよ. スクリプト中で読み込む db_info.yml は前回作成したものを利用する. なお, Active Record ではプレースホルダを用いた場合には ' や \ といった文字は自動的にエスケープされる.
$ vi db4.rb
require 'active_record'
require 'erb'
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.rb 2000 文字列連結の場合 #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000> プレースホルダの場合 (1) #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000> プレースホルダの場合 (2) #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>
SQL インジェクションの例として有名な "or 1=1" を加えると, 文字列連結の場合に問題が生じる.
$ ruby db4.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.rb "2000 ; show tables " 文字列連結の場合 /usr/lib/ruby/vendor_ruby/mysql2/client.rb:120: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 '; show tables )' at line 1: SELECT `商品`.* FROM `商品` WHERE (価格 = 2000 ; show tables ) (ActiveRecord::StatementInvalid)
次に, db4.rb 中の変数 sql を以下のように修正する. 商品番号は文字型なので文字列連結で SQL 文を生成する場合は引数の値をシングルクォートで囲まねばならないが, プレースホルダを使う場合は ? をシングルクォートで囲む必要はない.
$ vi db4.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.rb A01 文字列連結の場合 #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000> プレースホルダの場合 (1) #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000> プレースホルダの場合 (2) #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>
mysql2 で使ったのと同じ不正な入力をすると, 文字列連結の場合にエラーが出る.
$ ruby db4.rb "A01' or 1 -- " (注: -- の前後に空白が必要) 文字列連結の場合 /usr/lib/ruby/vendor_ruby/mysql2/client.rb:120: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.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)
(出力なし)