（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するという方法を取ります。
いや、おそらく、っていうか絶対変なやり方ですけどね。できちゃったので、この方法で進みます。

create.php


<?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ファイルを作成。

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：追記ここまで）

taiju.php

<?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>

ボタン押下時に呼び出す更新処理を作ります。

writetaiju.php

<?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とか）

まだ実験は続きますが、とりあえず最初の一歩を踏み出せたので記事を投稿しました。
引き続き実験を続けていきます！