ゼミII実習


ゼミIIで使用しているテキストがあります

動的Webページ作成(その11)PHPとMySQLの連携(その3)


今回はまずユーザ認証のページのから考えてみましょう。

前回紹介した次の参考書を参考にしています。

・PHP HACKS、Jack D. Herrington、牧野 聡 訳、オライリージャパン、2006、3600円

それでは実習を進めていきますが、まずセキュリティポリシーについて考えておきましょう。

今回のMySQLとPHPの連携のシステムではまず次のようなセキュリティポリシーを取りたいと思います。

・MySQLのデータベースの作成、テーブルの作成はローカルホスト上で行う。ただしテーブルに対しデータの入力、削除、変更はユーザによりWebページ上から行われる。
・MySQLにユーザを登録し、そのユーザがPHP経由で特定のデータベースにアクセスできるようにする。
・ユーザはデータをデータベースに追加、変更、削除することができる。この時ユーザ認証を行う。

以上のセキュリティポリシーの件は実習室のシステムにより勝手にユーザを作れないようになっています。そこで関係なくなりましたが参考のために削除しないでおきます。

それでは作業を進めていきましょう。

実習室のコンピュータは再起動後データベースが消えてしまいますので、まずデータベースを作ります。

メモ帳などのテキストエディタを起動し、次のデータをコピーして貼り付けます。ファイル名はwcup2006ren.datとしてHドライブに保存しておきましょう。

グループ,国名,国旗のファイル名
A,ドイツ,ger_tn.png
A,コスタルカ,crc_tn.png
A,ポーランド,pol_tn.png
A,エクアドル,ecu_tn.png
A,ドイツ,ger_tn.png
A,コスタリカ,crc_tn.png

もう一つメモ帳などのテキストエディタを起動し、次のプログラムをコピーして貼り付けます。ファイル名はwcup2006_import4.sqlとしてHドライブに保存しておきましょう。

CREATE DATABASE wcup2006;
USE wcup2006;
CREATE TABLE entry (
  id INT AUTO_INCREMENT PRIMARY KEY,
  wgroup CHAR(1),
  country VARCHAR(50),
  flag CHAR(10)
);
LOAD DATA INFILE 'h:\\wcup2006ren.dat' REPLACE INTO TABLE entry
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
IGNORE 1 LINES
(wgroup, country, flag);
SELECT * FROM entry;

<参考>今回CREATE TABLE文でフィールドidを次のように書いています。

  id INT AUTO_INCREMENT PRIMARY KEY,

これはINT型、つまり整数で自動的に番号が入る(AUTO_INCREMENT)ように設定しています。番号は1から始まります。またフィールドidは主キー(PRIMARY KEY)として設定され、重複するデータのないフィールドとして用いられています。

スタート>すべてのプログラム>教材ツール>MySQLの順にクリックしてください。MySQLの画面がコマンドプロンプトを使用して現れます。

メッセージが表示された後で次のようなプロンプトが表示されています。

mysql>

このプログラムを試すために、次のSQL文を使用します。

source  h:\wcup2006_import4.sql

うまくいくと次のような表示が返されます。

Query OK, 1 row affected (0.01 sec)
 
Database changed
Query OK, 0 rows affected (0.11 sec)
 
Query OK, 6 rows affected (0.07 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0
 
+----+--------+------------+------------+
| id | wgroup | country    | flag       |
+----+--------+------------+------------+
| 1  | A      | ドイツ     | ger_tn.png |
| 2  | A      | コスタルカ | crc_tn.png |
| 3  | A      | ポーランド | pol_tn.png |
| 4  | A      | エクアドル | ecu_tn.png |
| 5  | A      | ドイツ     | ger_tn.png |
| 6  | A      | コスタリカ | crc_tn.png |
+----+--------+------------+------------+
6 rows in set (0.00 sec)

これでデータベースwcup2006ならびにテーブルentryの準備ができました。

GRANT文は使用できないのでとばしてください。

その前にセキュリティポリシーに従いアクセスするためのユーザを用意します。

MySQLクライアントの画面で次の文を入力し、エンターキーを1回たたきます。下のように2行で書くこともできますがその場合IDENTIFIEDの前に空白を入れるようにしてください。

GRANT SELECT,INSERT,DELETE,UPDATE ON wcup2006.* TO ba150999@localhost 
      IDENTIFIED BY 'pekepeke';

実習室のシステムにより勝手にユーザを作れないようになっています。このコマンドは使えなくなりましたが、自分で環境を準備して勉強する場合には役に立つと思いますので削除しないでおきます。

GRANT文は新規にユーザを作成し権限を与えます。
SELECT,INSERT,DELETE,UPDATEとしてSELECT文、INSERT文、DELETE文、UPDATE文の使用の権限を与えています。
ONの次にデータベース名を指定しています。ここの設定ではデータベース名.テーブル名として設定します。この例ではデータベースwcup2006のすべてのテーブルにアクセスできます。
TOの次でユーザ名ba150999を与えています。@の次にホスト名を指定しています。
IDENTIFIED BYの次の’’で囲まれた文字列がパスワードになります。

うまくいった場合次のように表示されます。

Query OK, 0 rows affected (0.28 sec)

今度はデータ操作の画面をPHPで用意しますが、その画面に入るにはデータ認証を行うようにしてみましょう。

この時ユーザ認証用にデータベースを用意しておきます。

メモ帳などのテキストエディタを使って次のプログラムをコピーして貼り付け、ファイル名はreg_user.sqlとしてHドライブに保存しておきましょう。

CREATE DATABASE auth2006;
USE auth2006;
CREATE TABLE user_info (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  password VARCHAR(50)
);
INSERT INTO user_info (name, password) VALUES ('ba150999', MD5('pokopoko'));
SELECT * FROM user_info;

<参考>INSERT文の中で関数MD5を使用しています。与えられた文字列に対し、32文字からなるハッシュ値を返します。MySQL側で文字列に対し検索用の番号を作って返しています。

このユーザ登録部分は他人に見られないように管理してください。

このプログラムを試すために、次のSQL文を使用します。

source  h:\reg_user.sql

うまくいくと次のような表示が返されます。

Query OK, 1 row affected (0.01 sec)
 
Database changed
Query OK, 0 rows affected (0.09 sec)
 
Query OK, 1 row affected (0.05 sec)
 
+----+----------+----------------------------------+
| id | name     | password                         |
+----+----------+----------------------------------+
| 1  | ba150999 | b748e6a119d4aeacb1d9a095490f173e |
+----+----------+----------------------------------+
1 row in set (0.00 sec)

次にログイン用のプログラムを用意します。

まず、Apache Webサーバを起動しておきましょう。

スタート>すべてのプログラム>ネットワークツール>Apacheの順にクリックします。

タスクバー右下にApacheのアイコンが出ます。クリックして出てきたメニューでstartをクリックしてApacheサーバを起動します。

メモ帳などのテキストエディタで次のプログラムをコピーして貼り付けます。ファイル名はcheck.phpとしてHドライブに保存しておきましょう。

<html>
<head>
<title>
ユーザ認証画面
</title>
</head>
  
<body>
<?php if ($_GET['res'] == 1){ ?>
ログインできません<br>
<?php } ?>
<br>
<form action="login.php" method="POST">
<table>
<tr>
<td>ユーザ名</td>
<td>
<input type="text" name="user" size="50">
</td>
</tr>
<tr>
<td>パスワード</td>
<td>
<input type="password" name="password" size="40">
</td>
</tr>
<tr>
<td colspan="2" align="right">
<input type="submit" value="ログイン">
</td>
</tr>
</table>
</form>
</body>
</html>

<参考>formタグで入力したデータをPOST形式でlogin.phpに渡しています。

<form action="login.php" method="POST">

次の部分は$_GETで返ってきたパラメータresの値を調べています。変なような気がしますが、login.phpで再度このcheck.phpを指定することができます。その時にパラメータresを指定して値を渡しています。

<?php if ($_GET['res'] == 1){ ?>

C:¥Program Files¥Apache Group¥Apache2¥htdocsのフォルダにHドライブに今保存したcheck.phpをコピーします。

インターネットエクスプローラを使って、次のURLで確認します。

http://localhost/check.php

フォームを使用したユーザ認証用の画面が表示されます。

次にこのページに入力された情報を処理するプログラムを用意します。

メモ帳などのテキストエディタで次のプログラムをコピーして貼り付けます。ファイル名はlogin.phpとしてHドライブに保存しておきましょう。

<?php
  $db = mysql_connect("localhost", "root", "") 
        or die("接続できませんでした\n");
        
  mysql_select_db("auth2006", $db)
        or die("該当するデータベースがないようです\n");
 
  $query = "SELECT id FROM user_info WHERE name = "
           ."'"
           .$_POST['user']
           ."' AND password = MD5("
           ."'"
           .$_POST['password']
           ."')";
 
  $result = mysql_query($query, $db);
  $row = mysql_num_rows($result);
  if($row != 0){
    $u = mysql_result($result, 0, 0);
    session_start();
    $_SESSION['user'] = $u;
    header("Location: welcome.php");
  } else header("Location: check.php?res=1");
  
  mysql_close($db);
?>

<参考>次の部分で入力されたユーザ名、パスワードを使って正規ユーザかどうかを判定しています。

  $query = "SELECT id FROM user_info WHERE name = "
           ."'"
           .$_POST['user']
           ."' AND password = MD5("
           ."'"
           .$_POST['password']
           ."')";

返ってきた結果$resultをもとに

  $row = mysql_num_rows($result);

で該当するユーザのレコードがあるかどうか調べています。レコードが0、つまり該当するユーザの情報がない時には

header("Location: check.php?res=1");

によりcheck.phpを開き、パラメータresに1の値を設定しておきます。これに対応してcheck.phpで処理が行われます。

該当するユーザのレコードがある場合には

    $u = mysql_result($result, 0, 0);

idの値が$uに入ります。それから関数session_startによりセッションが開始されます。これは一時的にデータを保持する仕組みです。$_SESSIONというスーパーグローバル変数を使用してキーを与えます。ここではuserとしています。文字列でなので’’で囲んでいます。それから値として$uを入れます。

    session_start();
    $_SESSION['user'] = $u;
    header("Location: welcome.php");

最後にwelcome.phpのページを指定しています。

C:¥Program Files¥Apache Group¥Apache2¥htdocsのフォルダにHドライブに今保存したlogin.phpをコピーします。

動作をチェックするためのプログラムも用意します。メモ帳などのテキストエディタで次のプログラムをコピーして貼り付けます。ファイル名はwelcome.phpとしてHドライブに保存しておきましょう。

以上のファイルが用意できた状態でインターネットエクスプローラを使って、次のURLで確認します。

http://localhost/check.php

フォームを使用したユーザ認証用の画面が表示されるので、正規のユーザ名とパスワードを入力してログインした時と、そうでない時にページがどのように変わるかチェックしておきましょう。

ここまでうまくいけば、welcome.phpをデータベース管理用のページに書き替えましょう。すでに練習したプログラムを使ってみましょう。

welcome.phpを次のように書き替えてください。

<?php
  session_start();
  if($_SESSION['user'] == null || $_SESSION['user'] < 1) {
    header("Location: check.php");
    exit;
  }
  header("Content-Type: text/html; charset=SJIS");
  
  //コメントしてある部分はGRANT文でユーザを設定した場合です。
  //$db = mysql_connect("localhost", "ba150999", "pekepeke") 
  //      or die("接続できませんでした\n");
  $db = mysql_connect("localhost", "root", "") 
        or die("接続できませんでした\n");
 
  //MySQLの設定によりコメントした部分の設定が必要な場合があります。
  //実習室では以下のコメントしてあるプログラムは必要ありません。
  //mysql_query("SET NAMES sjis")
  //      or die("SET NAMES sjis の設定ができません");  
 
  mysql_select_db("wcup2006", $db)
        or die("該当するデータベースがないようです\n");
 
  $query = "SELECT * FROM entry";
  
  $result = mysql_query($query, $db);
?>
 
<html>
<head><title>検索結果</title></head>
<body>
<?php
  print mysql_result($result, 0, 1);
  
  $_SESSION = array();
  session_destroy();
  mysql_close($db);
?>
<br><br>
<a href="logout.php">ログアウト</a>
</body>
</html>

<参考>ここでもセッションを開始しています。セッションはクッキーの情報を扱うものなのでクライアント側ではクッキーを扱えるようにしておく必要があります。また、関数session_startを書いた部分からユーザのセッション用のデータにアクセスすることができるようになります。

  if($_SESSION['user'] == null || $_SESSION['user'] < 1) {
    header("Location: check.php");
    exit;
  }

この部分でセッション用のデータをチェックしています。問題がある時はcheck.phpを開きます。

  $_SESSION = array();
  session_destroy();
  mysql_close($db);

データベースを閉じる前に$_SESSIONを初期化し、セッション変数を関数session_destroyにより破棄しています。

そしてメモ帳などのテキストエディタで次のプログラムをコピーして貼り付けます。ファイル名はlogout.phpとしてHドライブに保存しておきましょう。

<?php
  header("Location: check.php");
?>

C:¥Program Files¥Apache Group¥Apache2¥htdocsのフォルダにHドライブに今保存したlogout.phpをコピーします。

以上のファイルが用意できた状態でインターネットエクスプローラを使って、次のURLで確認します。

http://localhost/check.php

フォームを使用したユーザ認証用の画面が表示されるので、正規のユーザ名とパスワードを入力してログインした時に結果が表示されますが組の名前だけ出力するようにしています。

大体の流れがつかめたでしょうか。次の練習はちょっと大変ですができたらチャレンジしてみてください。いずれもユーザ認証を必ず受けるようにしてください。

また、ページを通して操作を行うことになるので、formタグ、inputタグによる入力欄、ボタンなどを使ってユーザインターフェイスを考えてください。

練習1.SELECT文による結果からデータを表として出力してください。
<ヒント>まずSQL文で試してみましょう。表を出力する場合には次のような文を用います。

mysql> SELECT * FROM entry;

出力結果は以下のようになります。

+----+--------+------------+------------+
| id | wgroup | country    | flag       |
+----+--------+------------+------------+
| 1  | A      | ドイツ     | ger_tn.png |
| 2  | A      | コスタルカ | crc_tn.png |
| 3  | A      | ポーランド | pol_tn.png |
| 4  | A      | エクアドル | ecu_tn.png |
| 5  | A      | ドイツ     | ger_tn.png |
| 6  | A      | コスタリカ | crc_tn.png |
+----+--------+------------+------------+
6 rows in set (0.33 sec)

練習2.DELETE文を使っていらない行を削除してください。
<ヒント>まずSQL文で試してみましょう。フィールド(あるいはカラム名)idを使用して削除するします。出力結果および確認の画面は以下のようになります。

mysql> DELETE from entry WHERE id = 6;
Query OK, 1 row affected (0.30 sec)
 
mysql> SELECT * FROM entry;
+----+--------+------------+------------+
| id | wgroup | country    | flag       |
+----+--------+------------+------------+
| 1  | A      | ドイツ     | ger_tn.png |
| 2  | A      | コスタルカ | crc_tn.png |
| 3  | A      | ポーランド | pol_tn.png |
| 4  | A      | エクアドル | ecu_tn.png |
| 5  | A      | ドイツ     | ger_tn.png |
+----+--------+------------+------------+
5 rows in set (0.00 sec)

練習3.UPDATE文を使ってコスタルカとなっているところをコスタリカと訂正してください。
<ヒント>まずSQL文で試してみましょう。フィールド(あるいはカラム名)idを使用して変更するレコードを指定しSET句により修正を行います。出力結果および確認の画面は以下のようになります。

mysql> UPDATE entry SET country = 'コスタリカ' WHERE id = 2;
Query OK, 1 row affected (0.29 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT * FROM entry;
+----+--------+------------+------------+
| id | wgroup | country    | flag       |
+----+--------+------------+------------+
| 1  | A      | ドイツ     | ger_tn.png |
| 2  | A      | コスタリカ | crc_tn.png |
| 3  | A      | ポーランド | pol_tn.png |
| 4  | A      | エクアドル | ecu_tn.png |
| 5  | A      | ドイツ     | ger_tn.png |
+----+--------+------------+------------+
5 rows in set (0.00 sec)

<参考>MySQLのクライアント画面で日本語を入力する時はAltキーを押したまま半角/全角キーを1回たたきます。

練習4.INSERT文を使ってB組のデータを入力してみましょう。B組のデータは以下の通りです。

B,イングランド,eng_tn.png
B,パラグアイ,par_tn.png
B,トリニダードトバゴ,tri_tn.png
B,スウェーデン,swe_tn.png

<ヒント>まずSQL文で試してみましょう。フィールド(あるいはカラム名)の組にデータを対応させます。idは自動的に値が入るのでフィールドに指定していません。出力結果および確認の画面は以下のようになります。

mysql> INSERT INTO entry (wgroup, country, flag)
    -> VALUES('B', 'イングランド', 'eng_tn.png');
Query OK, 1 row affected (0.28 sec)
 
mysql> SELECT * FROM entry;
+----+--------+--------------+------------+
| id | wgroup | country      | flag       |
+----+--------+--------------+------------+
| 1  | A      | ドイツ       | ger_tn.png |
| 2  | A      | コスタリカ   | crc_tn.png |
| 3  | A      | ポーランド   | pol_tn.png |
| 4  | A      | エクアドル   | ecu_tn.png |
| 5  | A      | ドイツ       | ger_tn.png |
| 6  | B      | イングランド | eng_tn.png |
+----+--------+--------------+------------+
6 rows in set (0.00 sec)

余裕があれば複数のレコードを挿入できるように工夫してみましょう。

練習5.1から4までの操作を一つの画面から選択して行えるようにしてください。