（2024/2/4）XSS脆弱性があるとの指摘を受け、taiju.phpのソースを一部修正しました。
こんばんは。
以前から、phpやSQLを使ってホームページを制作し、公開してみたいと思っていましたので、サーバーを借りてサンプルを作成しました。
Webプログラミングに関する書籍は多くあります。
書いてある内容通りに実装すると、ローカルでphpやsqlを使ったページを作成する事はできますが、公開する方法までは書いていない事が殆どです。
せっかくなので自分の作品を公開したいですよね。自分でプログラミングしたホームページだったら、思い入れも深そうです。
触発されたのは、地元の図書館のホームページです。毎週何気なく利用していますが、書名を入力するとDBから書誌の情報を参照して表示させています（たぶん）。
図書館のように膨大なデータはありませんが、こういう動きをするサイトを自分で作ってみたいと思ったのです。
０．目的
・SQLを使ってDBを操作する。
・単純な仕組みで良いので、データベースを使った動的なページを作成する。
・それを本記事と全世界に公開する。
１．作るもの
まずは超簡単に、自分の体重を登録、照会できるページを作ってHP公開するところまでやってみようと思います。
これを作る事で、いつでもどこでも自分の体重を入力したり、チェックしたりできます。これで健康意識も爆上がりし、自分の寿命もさぞ延びる事でしょう。
どこかのおっさんの体重なんて誰も興味ない（公開したって害はない）だろうし。
２．環境準備
(1).php、mysqlをローカル環境で扱うための環境
基本的に以下の書籍で、環境準備をしました。
XAMPPと同時にMYSQLの親戚のような、MariaDBも同時にインストールします。
これにより、SQLを使った動的ページの生成ができる環境が整います。このあたりの準備は書籍を元に実施したので、本記事ではかなり簡略化して記載しています。
例によって本の通りにやってもエラーになる事もあるので、ネットの記事も拾いながら試行錯誤します。
この本は比較的本に従ってできた方かな？
(2).レンタルサーバー
以前ホームページ作成でお世話になっていた忍者ツールズでは、DBは使えないとの事で断念。いろいろ検討しましたが、操作が個人的にやりやすく、10日間の無料プランが付いているlolipopを選択しました。
シン・アカウント等、他にもレンタルサーバーが色々ありますがネット上の評価は同じくらいなので、個人の好みですかね。
３．実装（データベース）
以下2点を実装します。
・テーブルを作成・操作するSQLを準備する。
・Webページで、登録・照会機能を付ける。
(1).テーブル作成、操作
①ローカル環境での実現
テーブルのCREATEは、コマンドプロンプトで実行するよう、書籍には案内されていました。
これはこれで面白いのですが、レンタルサーバー上でこの操作ができるのかが分かりませんでした。
おそらく変な方法ではありますが、phpにSQLを実行する構文を書いて、URLを入力する事で、テーブルCREATEするという方法を取ります。
いや、おそらく、っていうか絶対変なやり方ですけどね。できちゃったので、この方法で進みます。
<?php
//これをURL欄に入力して実行する事でテーブル生成される。
// DBに接続：Webサーバー
//$dsn = 'mysql:host=mysql212.phy.lolipop.lan;dbname=LAAxxxxxxx-test;charset=utf8';
//$user = 'LAA1xxxxxxx';
//$password = 'xxxxxxxx';
// DBに接続：ローカル
$dsn = 'mysql:host=localhost;dbname=tennis;charset=utf8';
$user = 'tennisuser';
$password = 'password'; // tennisuserに設定したパスワード
try {
// PDOインスタンスの作成
$db = new PDO($dsn, $user, $password);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $db->prepare("
CREATE TABLE taiju (
title CHAR(8) PRIMARY KEY,
taiju INT NOT NULL,
memo text
) DEFAULT CHARACTER SET=utf8;"
);
$stmt->execute();
} catch (PDOException $e){
$alert = "<script type='text/javascript'>alert('SQL失敗( ；∀；)');</script>";
echo $alert;
exit('エラー：' . $e->getMessage());
}
?>
このようなテーブルを生成します。
上でコメントアウトしている以下の3行はローカル用のソースです。WebサーバーにアップするときはDB作成した際のパラメータを入力します。
$dsn = 'mysql:host=mysql212.phy.lolipop.lan;dbname=LAAxxxxxxx-test;charset=utf8';
$user = 'LAA1xxxxxxx';
$password = 'xxxxxxxx';
「作成するサーバー」欄の「mysql212.phy.lolipop.lan」をhost名に設定。
「データベース名」欄の「LAAxxxxxxx-xxxx」をdbnameに入力。
「パスワード」欄の「xxxxxxxx」をpasswordに入力。
これが正しく設定できれば、全世界にデータベースを展開する事ができます。
なお、PHPファイルはエラーメッセージを表示させるようにしてあります（正常系のメッセージはうまくできませんでした）。
今はいったんローカルで稼働確認。このphpファイルをフォルダに配置し、'http://localhost/tennis/create.php'
を入力してエンターすると、、まずは真っ白な画面になります。これで正常更新されました。
もう一回エンターすると、こうなります。
つまり、もうテーブルは生成されているよ、という事です。
次は、insert用のphpファイルを作成。
<?php
//これをURL欄に入力して実行する事でテーブル生成される。
// DBに接続：Webサーバー
//$dsn = 'mysql:host=mysqlxxx.phy.lolipop.lan;dbname=LAAxxxxxxxx-test;charset=utf8';
//$user = 'LAAxxxxxxx';
//$password = 'xxxxxxxx'; // tennisuserに設定したパスワード
// DBに接続：ローカル
$dsn = 'mysql:host=localhost;dbname=tennis;charset=utf8';
$user = 'tennisuser';
$password = 'password'; // tennisuserに設定したパスワード
// データの受け取り
//$date = $_POST['date'];
//$taiju = $_POST['taiju'];
$title = '20230101';
$taiju = '630';
try {
// PDOインスタンスの作成
$db = new PDO($dsn, $user, $password);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $db->prepare("
INSERT INTO taiju(title,taiju,memo)
VALUES('20230328', 630,'テスト用SQLメモ２');"
);
$stmt->execute();
} catch (PDOException $e){
$alert = "<script type='text/javascript'>alert('SQL失敗( ；∀；)');</script>";
echo $alert;
exit('エラー：' . $e->getMessage());
}
?>
insertは、コマンドプロンプト上ではこうします。
INSERT INTO taiju(title,taiju,memo)
-> VALUES('20230301',630,'memo');
とりあえずDBの中身を見るにはコマンドプロンプトしか今はないので、select * で稼働確認。
MariaDB [tennis]> SELECT * FROM TAIJU;
+----------+-------+------------------------------+
| title | taiju | memo |
+----------+-------+------------------------------+
| 20230301 | 630 | memo |
| 20230328 | 630 | テスト用SQLメモ２ |
| 20230402 | 630 | テスト用SQLメモ２ |
| 20230403 | 630 | テスト用SQLメモ２ |
| 20240131 | 627 | 水を沢山飲むと増えにくそう。 |
+----------+-------+------------------------------+
よし、ええやんええやん。
これの他に、drop用のSQLを組み込んだphpファイルも作りました。
(2).サーバーDB作成
次に、webサーバー上でphpとSQLを動かしてみる事にします。
lolipopでは、一番安いライトプランでDBを1つまで作成できます。
https://lolipop.jp/pricing/
DBの中に、複数テーブルを持たせる事も勿論できるので、このプランでも結構遊べるのではないかと推測。
月220円なら、お財布にも優しいでしょう。
と思ったら、良く見ると「220円～」と書いてあり、詳細を見ると、1か月単月だと550円、220円は3年間契約したプランでした（笑）なんだこの差。
いったんは3か月プラン（月495円）でやるか、と思ったら、なぜか12か月固定のプランになっていました。
無料期間を過ぎて停止されていたからかも。よく分かりませんでしたが、自分で立てた目標としては一年くらいかかりそうなので、背水の陣、一年契約します。
ユーザー登録後、管理画面から、DBを作成します。
＜ユーザー管理画面＞
https://user.lolipop.jp/
サーバーの管理・設定→データベースを選択。
この画面でDBを作成する事ができます。
先に作成した、「create.php」のパラメータをwebサーバー用に変更し、サーバーにアップロードして、URLを入力して動かしてみます。
1回目は真っ白な画面。2回目は、、
ローカルと同じ挙動（2回実行すると重複エラー）になりました。
webサーバーでも同じようにできそうです。
これで、webサーバーにテーブルを生成して、操作ができる事が分かりました。
ローカル環境で色々遊んで、webサーバーにいざアップしてみると動かない、、という事はなさそうです。
事前の安心を得るための作業はできました。
４．実装（Ｗｅｂサイト）
作成するサイトは大変単純なものですが、実装にはそれなりの労力がかかります。本のサンプルで紹介されていたサークルの掲示板サイトを踏襲します。
３で作成したcreate.php、insert.phpを実行してテーブルを作成する処理を含め、以下の構成でアプリケーションを開発します。
こんなサイトを作成します。どシンプルですが、最初の一歩としては上出来。
上記図のtaiju.phpは以下のソースです。
（2024/2/4：追記）XSSの脆弱性があると指摘を受けたため、出力時のソースを修正しました。「エスケープ対応修正」とコメントのある個所です。
以下の記事のように、スクリプト（今回は記事の通り、alert(1)のスクリプトを入力欄に入れて実行すると、画面表示のたびにアラートが出るようになってしまうのです。
怖いですね。こんな簡単に自分の手でXSSが再現できるとは思いませんでした。
指摘いただいた方に御礼申し上げます。
（2024/2/4：追記ここまで）
<?php
// 1ページに表示される書き込みの数
$num = 10;
$test = 'abcd';
// DBに接続：Webサーバー
$dsn = 'mysql:host=mysql212.phy.lolipop.lan;dbname=********-test;charset=utf8';
$user = '********';
$password = '*******'; // tennisuserに設定したパスワード
// DBに接続：ローカル
//$dsn = 'mysql:host=localhost;dbname=tennis;charset=utf8';
//$user = 'tennisuser';
//$password = 'password'; // tennisuserに設定したパスワード
// GETメソッドで2ページ目以降が指定されているとき
$page = 1;
if (isset($_GET['page']) && $_GET['page'] > 1){
$page = intval($_GET['page']);
}
try {
// PDOインスタンスの生成
$db = new PDO($dsn, $user, $password);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
// プリペアドステートメントを作成
$stmt = $db->prepare("SELECT * FROM taiju ORDER BY title DESC LIMIT :page, :num");
// パラメータを割り当て
$page = ($page-1) * $num;
$stmt->bindParam(':page', $page, PDO::PARAM_INT);
$stmt->bindParam(':num', $num, PDO::PARAM_INT);
// クエリの実行
$stmt->execute();
} catch (PDOException $e){
exit("エラー：" . $e->getMessage());
}
?>
<!doctype html>
<html lang="ja" >
<head>
<title>体重っち</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
</head>
<body>
<!--
<?php include('navbar.php'); ?>
-->
<main role="main" class="container" style="padding:60px 15px 0">
<div>
<!-- ここから「本文」-->
<h1>体重メモ</h1>
<form action="writetaiju.php" method="post">
<div class="form-group">
<label>日付（YYYYMMDD）</label>
<!--
<input type="date" name="title" class="form-control">
-->
<input type="text" name="title" class="form-control">
</div>
<div class="form-group">
<label>体重</label>
<input type="text" name="taiju" class="form-control">
</div>
<div class="form-group">
<label>メモ（思った事など）</label>
<input type="text" name="memo" class="form-control">
</div>
<input type="submit" class="btn btn-primary" value="書き込む">
</form>
<hr>
<?php while ($row = $stmt->fetch()): ?>
<div class="card">
<div class="title-body">
<!--17:58 2024/02/04 エスケープ対応修正 from -->
<p class="card-title"><?php echo htmlspecialchars(nl2br($row['title']), ENT_QUOTES, "UTF-8") ?></p>
<!--<p class="card-title"><?php echo nl2br($row['title']) ?></p>-->
<!--17:58 2024/02/04 エスケープ対応修正 to -->
</div>
</div>
<div class="card">
<div class="taiju-body">
<p class="card-taiju"><?php echo nl2br($row['taiju']) ?></p>
</div>
</div>
<div class="card">
<div class="memo-body">
<!--17:58 2024/02/04 エスケープ対応修正 from -->
<p class="card-memo"><?php echo htmlspecialchars(nl2br($row['memo']), ENT_QUOTES, "UTF-8") ?></p>
<!--<p class="card-memo"><?php echo nl2br($row['memo']) ?></p>-->
<!--17:58 2024/02/04 エスケープ対応修正 to -->
</div>
</div>
<hr>
<?php endwhile; ?>
<?php
// ページ数の表示
try {
// プリペアドステートメントの作成
$stmt = $db->prepare("SELECT COUNT(*) FROM taiju");
// クエリの実行
$stmt->execute();
} catch (PDOException $e){
exit("エラー：" . $e->getMessage());
}
// 書き込みの件数を取得
$comments = $stmt->fetchColumn();
// ページ数を計算
$max_page = ceil($comments / $num);
// ページングの必要性があれば表示
if ($max_page >= 1){
echo '<nav><ul class="pagination">';
for ($i = 1; $i <= $max_page; $i++){
// ここを修正しないと、更新後に戻ってくるページがtaiju.phpではなくなる。
echo '<li class="page-item"><a href="taiju.php?page='.$i.'">'.$i.'</a></li>';
}
echo '</ul></nav>';
}
?>
<!-- 本文ここまで -->
</div>
</main>
<script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" crossorigin="anonymous"></script>
<script>window.jQuery || document.write('<script src="/docs/4.5/assets/js/vendor/jquery-slim.min.js"><\/script>')</script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.bundle.min.js"></script>
</body>
</html>
ボタン押下時に呼び出す更新処理を作ります。
<?php
// データの受け取り
$title = $_POST['title'];
$taiju = $_POST['taiju'];
$memo = $_POST['memo'];
// 必須項目チェック（体重が空ではないか？）
if ($taiju == ''){
header("Location: taiju.php"); // 空のときtaiju.phpへ移動
exit();
}
// 必須項目チェック（体重は3桁の数字か？
if (!preg_match("/^[0-9]{3}$/", $taiju)){
header("Location: taiju.php"); // 書式が違うときtaiju.phpへ移動
exit();
}
// DBに接続：Webサーバー
$dsn = 'mysql:host=mysql212.phy.lolipop.lan;dbname=*********-test;charset=utf8';
$user = '*******';
$password = '********'; // tennisuserに設定したパスワード
// DBに接続
//$dsn = 'mysql:host=localhost;dbname=tennis;charset=utf8';
//$user = 'tennisuser';
//$password = 'password'; // tennisuserに設定したパスワード
try {
// PDOインスタンスの作成
$db = new PDO($dsn, $user, $password);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
// プリペアドステートメントを作成
$stmt = $db->prepare("
INSERT INTO taiju(title,taiju,memo)
VALUES (:title, :taiju,:memo)"
);
// プリペアドステートメントにパラメータを割り当てる
$stmt->bindParam(':title', $title, PDO::PARAM_STR);
$stmt->bindParam(':taiju', $taiju, PDO::PARAM_STR);
$stmt->bindParam(':memo', $memo, PDO::PARAM_STR);
// クエリの実行
$stmt->execute();
// taiju.phpに戻る
header('Location: taiju.php');
exit();
} catch (PDOException $e){
exit('エラー：' . $e->getMessage());
}
?>
これで必要なファイルは揃いました。これまで作成した以下のphpファイルを、接続先のDBを変更したうえでサーバーにアップロードします。
create.php
taiju.php
insert.php
drop.php
ロリポップ！FTPを選択すればFTP画面に遷移します。ここは特に難しい点はないので詳細手順は割愛。
これで、以下のURLで、全世界にPHPとMYSQLを組み込んだホームページが公開されました！
項目を入力し、「書き込む」ボタンを押下すると・・
ちゃんとDBに登録し、画面表示されたようです。成功ですね。
４．最後に
色々手順は踏みましたが、まず小さい目標は達成できました。HTMLで作った素朴なホームページも味があって良いですが、DBを使って動的なページを作るのも楽しいです。
これに対して、今後色々と試していきます。
・クッキーを設定する
・既存のデータを更新する
・DBに画像のファイル名を登録し、DBの値によって表示させる画像を制御する
・Webapiを組み込む（chatGPTとか）
まだ実験は続きますが、とりあえず最初の一歩を踏み出せたので記事を投稿しました。
引き続き実験を続けていきます！