データベース入門 - PerlでSQLite

第6章名簿検索システムを作ろう-2

前章でデータベースファイル member.dbの作成を完了しました。
本章では、名簿検索システムのフォームや各ファイルの配置作業を行います。

6-1. 検索フォームの準備

まず最初に、名簿検索の機能を決めておきます。
検索の操作機能は、第1ステップとして、IDのソートと性別による抽出とします。

■ 名簿検索システム「仕様」（第1ステップ）

設計項目	内容
検索機能	1. IDソート（昇順/降順） 2. 性別による抽出
フォーム name/value値	1. IDソート = name:「sort」, value:昇順「1」, 降順「2」 2. 性別 = name:「sex」, value:両方「0」, 男性のみ「1」, 女性のみ「2」
文字コード	UTF-8
プログラム名	member1.cgi

上記の「仕様」に基づき、データベース検索のための入力フォームを作成しましょう。
名簿検索の操作はウェブブラウザにより行われるため、入力フォームはHTMLで作成することになります。

HTMLの文字コードは、UTF-8です。
ファイル名はindex.htmlで保存することにします。

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<title>名簿データベース</title>
<style>
table { border-collapse:collapse; margin:1em 0; }
td,th { border:1px solid #555; padding:8px; }
</style>
</head>
<body>

<form action="member1.cgi">
<table>
<tr>
	<td>ソート</td>
	<td>
		<input type="radio" name="sort" value="1" checked>ID順（昇順）
		<input type="radio" name="sort" value="2">ID順（降順）
	</td>
</tr><tr>
	<td>男女別</td>
	<td>
		<input type="radio" name="sex" value="0" checked>両方
		<input type="radio" name="sex" value="1">男性のみ
		<input type="radio" name="sex" value="2">女性のみ
	</td>
</tr>
</table>
<input type="submit" value="検索">
</form>

</body>
</html>

6-2. 検索プログラムの作成

検索プログラムを作成します。
SQLiteを使った検索システムを作成する場合のポイントは、二点あります。

第1には、フォームからの検索「条件」を判断して、いかに「select文」を作成するかが重要です。
name値「sort」の値が、1であれば昇順ソート、2であれば降順ソートですので、select文は次のようになります。

/* 昇順 */
select * from member order by id asc;

/* 降順 */
select * from member order by id desc;

また、性別についてですが、name値「sex」の値が、1で男性のみ、2で女性のみですので、次のようになります（0の場合は男女両方ですので、性別の定義はないことになります）。

/* 男性且つ昇順 */
select * from member where sex = '男' order by id asc;

/* 男性且つ降順 */
select * from member where sex = '男' order by id desc;

/* 女性且つ昇順 */
select * from member where sex = '女' order by id asc;

/* 女性且つ降順 */
select * from member where sex = '女' order by id desc;

上記のselect文を、引数による検索条件によって、Perlで組み立てることになります。

ポイント-1
SQLiteによるプログラム制作では、select文の組み立てが重要。

第2には、セキュリティ上の注意です。 SQLiteへの指示文を作成するとき、外部からの引数をそのまま命令文に与える場合は注意が必要です。「SQLインジェクション」(*1) と呼ばれる脆弱性がないようにすることです。
具体的には、フォームからの引数については、汚染チェックと呼ばれる引数の正当性をチェックするようにします。たとえば、name値「sort」による値は「数値」ですので、数値以外は受け付けないように処理します。

ポイント-2
外部の引数から、select文を組み立てる際には、引数に対して汚染チェックを行う。

以上を加味して、検索プログラムを次のような流れで作成してみました。
引数の正当性をチェックし（17～18行目）、検索条件を判断して（24～35行目）、それに基づいたselect文を定義し（41行目）、データを抽出します（57行目）。

プログラムの文字コードは、UTF-8です。
ファイル名はmember1.cgiで保存することにします。

#!/usr/local/bin/perl

# 名簿検索システム-1
# created by (c)kentweb

use strict;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use CGI;
my $cgi = new CGI;

# 引数を受取り
my $sort = $cgi->param('sort');
my $sex  = $cgi->param('sex');

# 引数の正当性
$sort =~ s/\D//g;
$sex  =~ s/\D//g;

# select文
my $select = "select * from member";

# 性別条件
if ($sex == 1) {
	$select .= " where sex = '男'";
} elsif ($sex == 2) {
	$select .= " where sex = '女'";
}

# ソート条件
if ($sort == 1) {
	$select .= " order by id asc;";
} else {
	$select .= " order by id desc;";
}

# DB接続
my $dbh = DBI->connect("dbi:SQLite:dbname=member.db");

# 命令実行
my $sth = $dbh->prepare($select);
$sth->execute;

header();
print <<EOM;
<table>
<tr>
	<th>会員ID</th>
	<th>名前</th>
	<th>ふりがな</th>
	<th>性別</th>
	<th>住所</th>
</tr>
EOM

# データ抽出
while (my ($id,$name,$kana,$sex,$addr) = $sth->fetchrow_array) {
	print qq|<tr><td>$id</td>|;
	print qq|<td>$name</td>|;
	print qq|<td>$kana</td>|;
	print qq|<td>$sex</td>|;
	print qq|<td>$addr</td></tr>\n|;
}

# 完了
$sth->finish();
undef $sth;
$dbh->disconnect;

print <<EOM;
</body>
</html>
EOM

#-----------------------------------------------------------
#  ヘッダー
#-----------------------------------------------------------
sub header {
	print <<EOM;
Content-type: text/html; charset=utf-8

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<title>名簿データベース</title>
<style>
table { border-collapse:collapse; margin:1em 0; }
td,th { border:1px solid #555; padding:8px; }
th { background:#ccc; }
</style>
</head>
<body>
EOM
}

上記のプログラムは、以下の名簿検索フォームから試すことができます。

ソート	ID順（昇順） ID順（降順）
男女別	両方男性のみ女性のみ

6-3. 必要ファイルの配置

テスト環境 (XAMPP) における全体のディレクトリ構成とファイルの配置例について取り決めておきましょう。
XAMPPのテスト環境が基本ですので、今回はホームディレクトリの下に、「memberフォルダ」を作成して、ファイル1式を配置することにします。

C:\xampp
    |
    +-- htdocs / ... ホームディレクトリ
          |
          +-- member / index.html .... 検索フォーム
                       member1.cgi ... 検索プログラム
                       member.db ..... 前章で作成したデータベースファイル

なお、実際の本番サーバで動作させる場合には、次章での設置例を参考にしてみてください。
SQLiteデータベースファイル「member.db」については、Windows環境で作成したものを、そのままUnix系サーバへFTP転送（バイナリーモードで行うこと）すれば、そのまま動作するようです（あらためて本番サーバで変換作業を行う必要はない）。

【脚注】
*1 : SQLデータベースと連動したWebサイトにて、脆弱性のあるプログラムにSQL文の一部を与えることにより、不正操作を行う攻撃のこと。