Ruby+sqlite3 - 2018 Special Lecture c on Information Processing

実験用データベースを作成し，それをRubyプログラムから利用する例を示す。

sqlite3 fruits.sq3
CREATE TABLE fruits(item, at, memo);
INSERT INTO fruits VALUES ("りんご", 150, 'ふじ');
INSERT INTO fruits VALUES ("みかん", 30, '有田');
SELECT * FROM fruits;
りんご|150|ふじ
みかん|30|有田

Rubyプログラムからのアクセス

RubyからSQLite3データベースをアクセスするには sqlite3 ライブラリを用いる。

#!/usr/bin/env ruby
# coding: utf-8
require 'sqlite3'

db = SQLite3::Database.new("DBファイル")
db.execute(SQL文)

#!/usr/bin/env ruby
# coding: utf-8
require 'sqlite3'

db = SQLite3::Database.new("fruits.sq3")
result = db.execute("SELECT * FROM fruits;")
result.each do |row|
  puts row.join(",")
end

SELECTした結果はイテレータになり，eachで検索結果を 1レコードずつ取り出せる(row)。rowは配列になり，カラムごとに取り出せる。

#!/usr/bin/env ruby
# coding: utf-8
require 'sqlite3'
require 'kconv'

db = SQLite3::Database.new("fruits.sq3")
sql = "INSERT INTO fruits VALUES (?, ?, ?)"
while true
  STDERR.print 'Fruit: '
  fr = gets
  break if fr==nil              # 入力終端でbreak
  STDERR.print 'unit of price: '
  pr = gets.to_i
  STDERR.print 'memo: '
  memo = gets.chomp
  db.execute(sql, fr.chomp.toutf8, pr, memo)
end
STDERR.puts "Done"

INSERT INTO fruits VALUES (?, ?, ?)

となっている。? の部分はプレースホルダといい， executeメソッドにより ? が後続する引数の値に置き換えられてSQL文が発行される。

./fr-2.rb
Fruit: いちご
unit of price: 20
memo: おとめ心
Fruit: 梨
unit of price: 120
memo: 幸水
Fruit: [C-d]Done

./fr-1.rb
りんご,150,ふじ
みかん,30,有田
いちご,20,おとめ心
梨,120,幸水

Rubyプログラムからレコード挿入を行なうには，INSERT構文を含む SQL文のデータの部分にプレースホルダ(`?')を指定し，その個数だけ後続する引数に対応する値を記述すればよい。

CGIプログラム化

データベースファイルの書き込み権限

CGIプログラムはWebサーバプログラムの実行権限で起動される。そのため，他人でも書き込める設定にしなければならない。また， sqlite3は，データベースファイルを更新するときに，ファイルと同じディレクトリに一時ファイルを作成するので，データベースファイルを置くディレクトリも書き込める設定にする必要がある。ただし，CGIプログラムと同一ディレクトリにするのは危険なので，他者書き込み可能なサブディレクトリを作成し，そこにデータベースファイルを移動する。

作成した tmpdb/ ディレクトリ内のファイルがHTTP経由で取得されぬよう tmpdb/.htaccess ファイル(ApacheHTTPDの場合)を以下のように作成する。

<Limit GET POST>
 Order deny,allow
 Deny from all
</Limit>

ディレクトリのCGI有効化

拡張子 .rb のファイルがCGIとして機能するよう .htaccess ファイルを以下のように作成する。

AddHandler cgi-script   .rb
AddType	"text/html; charset=utf-8" .rb
Options +ExecCGI

CGIプログラムの前提

課題

あらし対策

例示した fruits.rb はいたずらに弱い。完ぺきな対策は難しいが現実的には，自動的に入力フォームCGIを探しては spamを書き込んでくるロボットプログラムからの登録を防げればほぼ十分である。

そのためには，入力フォーム内に人間にしか判断できないヒント情報を入れておき，書き込み時にそれを判定するような仕組を入れる。一般的には画像化した文字を人間に読み取らせるCAPTCHA という自動アクセス抑止機構があるが，それよりはるかに簡易なものでも一定の抑止効果が得られる。人気サイトでない限り，簡易対策で始めてもよい。

この処理は，CGIフォームを自動的に探して適当にコメント欄らしき所にspam文を書いて来るタイプのロボットには有効であるが，この対策法を理解して適切な値を送るようにプログラムされたものには無力である。そのようなものにも対策するなら，以下のような処理を考える。

複雑にすればするほどspam避けの効果は高まるが，正当な利用者の手間が増え利用意欲が低下する。攻撃される確率といたずらによる被害からの回復のコストを考えて適度な対策を選択する必要がある。

修正・削除機能

既存のレコードを修正あるいは削除する機能も欲しい。この場合は，レコードの一覧表示のとき，各レコードに振られた idをhidden変数に添えて更新リンクを示す。そのため，dbの第1カラムに idを追加して設計する。つまり，これまで

とする。既存の表にカラムを追加することはできるが，通常末尾にしか足せないので新規に表を作る。ここでは，同じデータベース内に kudamono という表を上記のカラム構成で構築する。その上で，既存の fruits 表にあるレコードを item カラム以降にコピーする。

sqlite3 fruits.sq3
CREATE TABLE kudamono(id integer primary key autoincrement,
item, at, memo);
INSERT INTO kudamono(item, at, memo) SELECT * FROM fruits;
SELECT * FROM kudamono;
1|りんご|150|ふじ
2|みかん|30|有田
3|いちご|20|おとめ心
4|梨|120|幸水

このようにINSERT文の引数にSELECT文を入れることで，他の表のカラムをコピーすることができる。

この表にあるレコードを一覧表示しつつ，品目名に修正リンクを，また品目名の前に削除マークを付けるようにした例を示す(ソース: kudamono.rb)。

ユーザ認識機能

データの修正や削除を，書き込んだユーザのみに許可する仕組を入れてみる。このためには，各レコードに書き込み者を保持するカラムを追加するとともに，ログイン機能を付ける。

なお，ここのログイン機能は他者の乗っ取りなどの悪用にたいしてはほとんど抑止効果がない。もともとWebは誰にでもアクセスできることを目指して設計されたものなので，厳密なユーザ認証を行なうにはかなりのコストがかかる。改ざんやいたずらが全く許されないものを扱うのであれば，Webサーバプログラム(httpd)にはデータベースの書き込みができないようデータベースファイルを chmod したうえで，Webインタフェースを禁止し，データベースを直接扱うコマンドラインインタフェースを利用するような設計をするのがもっとも簡便で強固である。どうしてもWebインタフェースでの書き込みをさせたい場合は，データベース書き込みできるユーザ権限を持つhttpdを別途起動し， VPN回線からのアクセスのみ許可するなどの方法が考えられる。いずれにしても，インターネット公開Webサーバプロセスが書き込みできるような状態ではデータは守り切れないと心得る。

ユーザ情報の管理

ログインさせるためにはユーザの情報が必要で，これも表で持たせる。ここでは，以下の情報を保持するものとする。

sqlite3 kudamono.sq3
CREATE TABLE user(uid integer primary key autoincrement,
uname, pswd, email, name, skey);

パスワードはシステムのcrypt(3)関数を使って暗号化する。 crypt()関数は以下のように使用して暗号化文字列を得る。

文字列.crypt(SALT)

SALT は，ひとつの単語(つまりパスワード)に対する符号化文字列が単一にならないようにするための味付け用ランダム文字列である。単一になると符号化した文字列から，平文パスワードが容易に推測できる。またSALTには，符号化アルゴリズムを選ばせる役割も持たせられる。最近のシステムで広く利用できるMD5を利用する(※)。

※MD5は既に弱点の発見されたアルゴリズムであるため、パスワードが破られることで重大が被害がおよぶ場所では利用できない。その場合は、システムのログインパスワード管理で利用されている符号化アルゴリズム(SHA256, SHA512, Blowfishなど)を利用する。

MD5符号化を行なうには， SALTに "$1$xxxx" のような文字列(xxxxの部分はランダム)を指定する。

SALT自身が結果文字列に含まれていることに注意する。結果文字列を SALTに指定して再度crypt()関数を呼んでみる。

SALT指定では，3つ目の$以降が無視されるので "$1$hoge" を指定したのと同じ結果となる。

さて，ユーザ情報にパスワードを入れる場合，crypt文字列を格納する。これは容易には元に戻せないので誰も(管理者すら)元のパスワードが分からない。ログイン可否の判断は，ユーザが送って来たパスワードを，格納された符号化文字列をSALTにしてcryptしたものと符号化文字列を比較する。同じであれば，送って来たパスワードが正当なものと判断できる。

なお，今回のユーザ認識処理はそれなりの防御でよい場合の方式であり，しっかりした認証を行なう場合は暗号回線の確保や，そもそもWebを用いない編集方式の提供などを考えるべきことを再強調しておく。

ユーザ作成処理

1人目のユーザはsqlite3コマンドで作ってみる。 my@add.ress を自分のメイルアドレスに置き換えて作業例を見よ。なお，パスワードの符号化文字列は別の端末でirbを利用して得る。

sqlite3 kudamono.sq3
INSERT INTO user(uname, pswd, email, name)
VALUES ('my@add.ress',
  "$1$hoge$Z/OJvXjat3gzS6ZMyA./q1",
  'my@add.ress', "太郎");

この処理の動きを理解しやすくするためのRubyプログラムを示す (useradm.rb)。

#!/usr/bin/env ruby22
# coding: utf-8
require 'sqlite3'

if ARGV[1] == nil
  STDERR.puts "第1引数にユーザ名(届くメイルアドレス)を"
  STDERR.puts "第2引数にデータベースファイルを指定して下さい。"
  exit(2)
end

uname = ARGV[0]			# ユーザ名
string = [*'a'..'z', *'A'..'Z', *'*'..'9']	# 英数記号($が入らないように)
salt = string.sample(4).join	# ランダム文字列のSALT
pswd = string.sample(9).join	# ランダム文字列の初期パスワード
crpt = pswd.crypt("$1$"+salt)   # MD5化

db = SQLite3::Database.new(ARGV[1])
r = db.execute("SELECT uid, email FROM user WHERE uname=?", uname)[0]
if r                            # 既存ユーザの場合 r=[uid, email]
  db.execute("UPDATE user SET pswd=? WHERE uid=?", crpt, r[0])
else                            # 新規作成
  db.execute("INSERT INTO user(uname, pswd, email, name) VALUES(?,?,?,?)",
             uname, crpt, uname, uname)
end
printf("Set pswd for [%s] to [%s]\n", uname, pswd)

ログイン処理

データベースアクセスWebに登録したユーザとパスワードを要求するようにする。 Webでのユーザ認識処理が難しいのは，ログイン処理をした情報をクリックした先の次のセッションに持ち越せないところにある。それを簡易的に行なうためにcookieを利用する。

cookieは，Webサーバがブラウザに送った「変数=値」という文字列情報を，次回以降のアクセス時にブラウザからWebサーバ側に送り返してもらうことでアクセスのセッション管理を擬似的に可能にすることのできる仕組みである。 cookieを介してやりとりする変数を以後 cookie 変数と表現する。

cookie変数のやりとりは，まずサーバ側がクライアントに変数と値の組を送信するところから始まる。これを送信するCGIプログラムは，最初に送るContent-type行に続けて

Set-Cookie: 変数=値; expires=時刻

という並びを送信する。あるユーザがあるときに認証を終えたという情報をここに埋め込んでおけば，2回目以降のアクセスでログイン処理が省略できる。その目的の情報をセッションキーと呼ぶ。セッションキーは，認証に通ったユーザのみが取得・利用し得る文字列で，不正利用者が推測困難であればなんでもよい。ここでは接続者のIPアドレスを利用し，それにランダム文字列を組み合わせたものにする。

Apache Webサーバへの接続者IPアドレスは環境変数 REMOTE_ADDR で取得でき，これはRubyでは ENV["REMOTE_ADDR"] で得られる。これに現在時刻からなる文字列を組み合わせた

skeyprefix = ENV["REMOTE_ADDR"] + Time.now.strftime("/%Y%m%d%H/")

にランダム文字列を追加したものをセッションキーとする。

CGIプログラムが初回アクセス時のログイン処理でパスワード確認が成功したら，セッションキーを生成し，直ちに上記のSet-Cookie文を送る。 expiresは，cookieの有効期限で時刻の書式は

Sun, 02 Mar 2014 07:41:26 GMT

のような形式でUTC時刻指定する。たとえば5時間後の時刻を指定する文字列は以下のようにして得られる。

(Time.now.gmtime+3600*5).strftime("%a, %d %b %Y %H:%M:%S GMT")

サンプルソースでは5時間後の時刻でこれを生成している。例を示す (ソース: fruits3.rb)。

Ruby+SQLite3によるCGI

RubyからのSQLite3利用

実験用データベースの作成