MariaDBを作ってPHPから記事を表示する
musikyun専用のMariaDBを作り、 PHPから記事データを取得してブラウザへ表示します。
ただ接続するだけではなく、 「PHPとMariaDBの間で何が起きているのか」 「なぜDB専用ユーザーを作るのか」 「なぜPermission deniedになったのか」 まで確認します。
今回作る仕組み
↓ HTTPS
nginx
↓
PHP-FPM
↓
PHP
↓
PDO
↓
MariaDB
↓
articles
↓
記事データ
↓
HTML
↓
ブラウザ
旧musikyunでは、記事データの保存をSupabaseが担当していました。
今回はそのうちのDatabase部分を、 VPS上のMariaDBへ置き換えていきます。
STEP 1|既存のデータベースを確認する
sudo mysql
MariaDBへ管理者として入り、既存のデータベースを確認しました。
SHOW DATABASES;
SHOW = 情報を表示する。DATABASES = MariaDBに存在するデータベース。→ musikyunという名前のDBがすでに存在しないことを確認してから、 新しいDBを作ります。
いきなり作成するのではなく、 既存環境と名前が衝突しないことを確認してから変更します。
「確認 → 変更 → 再確認」は、 サーバーやDBを扱うときの基本的な流れです。
STEP 2|musikyun専用DBを作る
CREATE DATABASE musikyun
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
CREATE DATABASE = 新しいデータベースを作成します。musikyun = 今回作るDBの名前です。CHARACTER SET utf8mb4 = 日本語や絵文字などを扱える文字コードを指定します。COLLATE = 文字列を比較・並び替えするときのルールを指定します。
作成したDBを選択します。
USE musikyun;
USEとは?「これからどのデータベースを操作するのか」を選択するSQLです。
実行後の表示が
MariaDB [musikyun]>
になれば、musikyun DBを操作している状態です。
STEP 3|articlesテーブルを作る
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
youtube_url VARCHAR(500) DEFAULT NULL,
image_url VARCHAR(500) DEFAULT NULL,
status VARCHAR(30) NOT NULL DEFAULT 'draft',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
slug VARCHAR(255) DEFAULT NULL,
author_id INT UNSIGNED DEFAULT NULL
);
DBの中でデータを保存する「表」です。
articlesは記事を保存する表で、
1行が1記事、各カラムが記事の情報になります。
INT = 整数。UNSIGNED = 負の値を使わない整数。AUTO_INCREMENT = 1、2、3…と自動採番。PRIMARY KEY = その行を識別する主キー。VARCHAR(255) = 最大255文字の可変長文字列。TEXT = 長い文章を保存する型。NOT NULL = NULLを許可しない。DEFAULT = 値を省略した場合の初期値。TIMESTAMP = 日時を扱う型です。
DEFAULT 'draft'としているため、
statusを指定せず記事を作ると、自動的に下書きになります。旧musikyunの
draft /
in_review /
needs_revision /
published /
closed
という状態管理を、今後このカラムで再現します。
作成後、テーブルの構造を確認しました。
DESCRIBE articles;
CREATE TABLEが成功しただけでは、
自分が意図した構造になっているかまでは確認できません。→ 作成したあとに、実際の結果を確認します。
STEP 4|テスト記事を保存する
INSERT INTO articles (
title,
content
) VALUES (
'テスト記事',
'musikyunのMariaDB接続テストです。'
);
INSERT INTO articles = articlesへ新しい行を追加する。title, content = 今回値を入れるカラム。VALUES = 実際に保存する値です。
今回はtitleとcontentしか指定していません。
↓
MariaDB
↓
idを自動採番
statusをdraftにする
created_atへ現在日時を入れる
↓
articlesへ保存
保存されたデータを確認します。
SELECT * FROM articles;
SELECT = データを取得する。* = すべてのカラム。FROM articles = articlesテーブルから取得する。実際に
id = 1、
status = draft、
created_atに日時が入ったことを確認しました。
STEP 5|musikyun専用DBユーザーを作る
CREATE USER 'musikyun_user'@'localhost'
IDENTIFIED BY 'ここには実際のパスワードを書かない';
MariaDBのrootは管理者です。
musikyun以外のDBまで操作できる強い権限を持っています。
musikyunから必要なのは、 musikyun DBを操作する権限だけです。
→ アプリ専用ユーザーを作り、 必要な範囲だけ権限を与えます。
GRANT ALL PRIVILEGES ON musikyun.*
TO 'musikyun_user'@'localhost';
GRANT = 権限を与える。ON musikyun.* = musikyun DB内のテーブルを対象にする。TO = 誰へ権限を与えるか。'musikyun_user'@'localhost' = VPS自身から接続するmusikyun_userです。
認証(Authentication) = 「あなたは誰ですか?」を確認すること。
認可(Authorization) = 「あなたは何をしてよいですか?」を決めること。
ユーザー名とパスワードで本人を確認するのが認証。
GRANTで操作できるDBを決めるのが認可です。
専用ユーザー自身で接続できることも確認しました。
mysql -u musikyun_user -p musikyun
-u = 接続するユーザーを指定。-p = パスワード入力を要求する。musikyun = 接続先DB。パスワードそのものをコマンドへ書かないため、 コマンド履歴にパスワードを直接残しません。
STEP 6|PHPからPDOでMariaDBへ接続する
PHP側にDB接続処理を作りました。
<?php
$config = require '/etc/musikyun/database.php';
$dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset=utf8mb4";
$pdo = new PDO(
$dsn,
$config['username'],
$config['password'],
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
PDO = PHP Data Objects。
PHPからMariaDBなどのデータベースへ接続し、 SQLを実行するための仕組みです。
今回は、 PHPとMariaDBの間をつなぐ窓口 と考えると分かりやすいです。
↓
PDO
↓
MariaDB
$pdoって何?PHPでは、
$から始まる名前は変数です。$pdoには、
new PDO(...)で作った
「DBへ接続して操作するためのPDOオブジェクト」が入ります。以後はこの
$pdoを使ってSQLを実行できます。
new PDO()では何が起きる?new = 新しいオブジェクトを作る。PDO = PHPが用意しているDB接続用クラス。DSN・ユーザー名・パスワードなどを渡して、 MariaDBへ接続できるPDOオブジェクトを作っています。
DSN = Data Source Name。
「どの種類のDBへ、どのホストの、どのDB名で接続するか」 を表す接続先情報です。
今回は
mysql:でMariaDB/MySQL系、
hostで接続先、
dbnameでDB名、
charsetで文字コードを指定しています。
STEP 7|PHPから記事を取得する
PDOでMariaDBへ接続できたので、 次はarticlesテーブルから記事を取得します。
<?php
require __DIR__ . '/db.php';
$stmt = $pdo->query(
'SELECT
id,
title,
content,
status,
created_at
FROM articles'
);
$articles = $stmt->fetchAll();
requireとは?別のPHPファイルを読み込み、 そのファイルの処理を実行します。
今回は
db.phpを読み込むことで、
そこで作った$pdoを
db-test.phpから使えるようにしています。
__DIR__とは?現在のPHPファイルが置かれている ディレクトリを表す特別な定数です。
今回なら、
/var/www/musikyun
を基準にしてdb.phpを読み込みます。
->って何?->は、
PHPでオブジェクトが持っている機能を使うときに使う記号です。$pdo->query(...)↓
「
$pdoが持っている
query()という機能を使う」
と読めます。
query()とは?SQLをデータベースへ送り、 実行するPDOの機能です。
今回は
SELECTをMariaDBへ送り、
articlesテーブルの記事を取得しています。
fetchAll()とは?SQLの実行結果をまとめて取得する機能です。
取得した記事はPHPの配列として
$articlesへ入ります。
↓
$pdo->query()
↓
SELECTをMariaDBへ送る
↓
MariaDBが記事を探す
↓
結果をPHPへ返す
↓
fetchAll()
↓
$articles
STEP 8|DB接続情報をWeb公開領域の外へ分離する
最初の接続テストでは、
DBユーザー名やパスワードを
/var/www/musikyun/db.php
に書いていました。
しかし、
/var/www/musikyunは
nginxのWeb公開領域です。
そこで設定ファイルを置く場所を別に作りました。
sudo mkdir -p /etc/musikyun
mkdir = make directory。
ディレクトリを作るコマンドです。-p = 必要な親ディレクトリも含めて作成できる指定です。/etc = Linuxで設定ファイルを置く代表的な場所です。
DB接続情報は次のファイルへ移しました。
/etc/musikyun/database.php
<?php
return [
'host' => 'localhost',
'dbname' => 'musikyun',
'username' => 'musikyun_user',
'password' => '実際のパスワードは記事に書かない',
];
PHPのプログラムと、 DBパスワードなどの秘密情報を 同じWeb公開領域に置かないためです。
公開するプログラムと 秘密の設定情報を分離します。
STEP 9|Linuxの権限を設定する
設定ファイルは、誰でも読める状態にはしません。
まずPHP-FPMがどのユーザーで動いているか確認しました。
grep -E '^(user|group) =' /etc/php/8.3/fpm/pool.d/www.conf
結果:
user = www-data
group = www-data
人間が普段ログインするユーザーではありません。
WebサーバーからPHPを実行するときに、 PHP-FPMが処理を行うためのLinuxユーザーです。
今回ブラウザからPHPを実行すると、 PHPの処理は主に
www-dataとして動きます。
設定ファイルの所有者とグループを変更しました。
sudo chown root:www-data /etc/musikyun/database.php
chownとは?change owner。 ファイルの所有者などを変更するコマンドです。
root:www-dataは、所有者 =
rootグループ =
www-dataという意味です。
さらにアクセス権を設定しました。
sudo chmod 640 /etc/musikyun/database.php
chmod 640を読むchmod = change mode。
ファイルのアクセス権を変更します。6 = 読み取り + 書き込み4 = 読み取り0 = 権限なしつまり今回の
640は、所有者 root → 読み取り・書き込み
グループ www-data → 読み取りのみ
その他 → 権限なし
という設定です。
実際の権限も確認しました。
ls -l /etc/musikyun/database.php
-rw-r----- 1 root www-data ... /etc/musikyun/database.php
-rw-r-----を読むrw- = 所有者rootは読み書きできる。r-- = グループwww-dataは読める。--- = その他のユーザーはアクセスできない。
STEP 10|Permission deniedから権限分離を理解する
設定ファイルを分離したあと、 ターミナルから次のテストを実行しました。
php db-test.php
すると、次のエラーが発生しました。
Permission denied
このコマンドを実行したのは
ubuntuユーザーです。しかし
database.phpの権限は、root → 読める
www-data → 読める
ubuntu → 読めない
という状態でした。
つまりPHPコードが間違っていたのではなく、 実行しているLinuxユーザーに読み取り権限がなかった ために拒否されたのです。
↓
database.php を読もうとする
↓
ubuntuには読み取り権限がない
↓
Permission denied
次に、本番のPHP-FPMと同じ
www-dataとして実行しました。
sudo -u www-data php db-test.php
sudo -u www-dataとは?sudoを使って、
指定したユーザーとしてコマンドを実行します。-u = 実行するユーザーを指定します。今回は 「www-dataとして
php db-test.phpを実行する」
という意味です。
今度は記事を正常に取得できました。
↓
database.php を読む
↓
www-dataには読み取り権限がある
↓
PDOでMariaDBへ接続
↓
記事取得成功
すべてのユーザーに同じ権限を与えるのではなく、 必要な人・プログラムに、必要な権限だけ与える 考え方です。
今回はPHP-FPMの
www-dataには
DB設定を読む権限を与え、
その他のユーザーからは読めないようにしました。
STEP 11|db.phpから非公開設定を読み込む
<?php
$config = require '/etc/musikyun/database.php';
$dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset=utf8mb4";
$pdo = new PDO(
$dsn,
$config['username'],
$config['password'],
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
$config = require ...とは?database.phpは
return [...]で設定値を返します。requireでそのファイルを読み込み、
返された配列を$configで受け取っています。
↓ return
設定値の配列
↓
$config
↓
new PDO()
↓
MariaDB
STEP 12|記事をブラウザへ表示する
最後にindex.phpから記事を取得し、
HTMLとしてブラウザへ表示しました。
<?php
require __DIR__ . '/db.php';
$stmt = $pdo->query(
'SELECT
id,
title,
content,
status,
created_at
FROM articles'
);
$articles = $stmt->fetchAll();
foreach ($articles as $article) {
echo '<h2>' . htmlspecialchars($article['title'], ENT_QUOTES, 'UTF-8') . '</h2>';
echo '<p>' . nl2br(htmlspecialchars($article['content'], ENT_QUOTES, 'UTF-8')) . '</p>';
echo '<p>状態: ' . htmlspecialchars($article['status'], ENT_QUOTES, 'UTF-8') . '</p>';
}
foreachとは?配列の中身を1件ずつ順番に処理する反復処理です。
$articlesに複数の記事が入っていれば、
1記事ずつ$articleへ取り出して表示します。
htmlspecialchars()はなぜ必要?DBから取り出した文字列を、 HTMLとしてそのまま解釈させないためです。
たとえば記事タイトルなどにHTMLやスクリプトとして 解釈される文字が含まれていても、 文字として表示する方向へ変換します。
これはXSS対策の基本の一つです。
Cross-Site Scripting。
Webページへ意図しないスクリプトなどを混入させ、 ブラウザで実行させる攻撃です。
「DBから取得したデータだから安全」とは限りません。 HTMLへ出力するときにも安全性を考えます。
nl2br()とは?文章中の改行をHTMLの
<br>へ変換します。本文の改行をブラウザでも見えるようにするために使っています。
STEP 13|ブラウザで最終確認する
ブラウザからmusikyunへアクセスしました。
MariaDBへ保存した次の記事が表示されました。
テスト記事
musikyunのMariaDB接続テストです。
状態: draft
VPS上のMariaDBへ保存した記事を、 PHPのPDOから取得し、 nginxとPHP-FPMを経由して ブラウザへ表示できました。
📘 全体の処理を追う
↓
nginxがリクエストを受け取る
↓
PHP-FPM(www-data)がPHPを実行
↓
index.php
↓
db.php
↓
/etc/musikyun/database.php
↓
PDO
↓
MariaDB
↓
musikyun / articles
↓
記事データをPHPへ返す
↓
foreachで1記事ずつ処理
↓
htmlspecialchars()でHTML出力用に変換
↓
HTML
↓
nginx
↓ HTTPS
ブラウザへ表示
📘 科目Bにつなげて考える
設定を読む → DBへ接続する → SQLを実行する → 結果を取得する → HTMLを作る、という順番で処理されます。
foreachで、
取得した記事を1件ずつ処理します。
fetchAll()で取得した複数の記事を
PHPの配列として扱います。
root / ubuntu / www-dataで 「誰が何を読めるか」が変わります。
プログラムでも、 入力された主体と許可された操作を分けて考えることが重要です。
📘 入力 → 処理 → 出力
ブラウザからページをリクエスト
↓
【処理】
nginx
↓
PHP-FPM
↓
PHP
↓
PDO
↓
SELECT
↓
MariaDB
↓
fetchAll()
↓
foreach
↓
HTML生成
↓
【出力】
記事がブラウザに表示される
musikyun専用のMariaDBとarticlesテーブルを作成し、 PHPのPDOから記事を取得して ブラウザへ表示できるようになりました。
さらに、DB専用ユーザー、 Web公開領域と秘密情報の分離、 Linuxの所有者・グループ・アクセス権、 PHP-FPMのwww-dataについても確認しました。
途中で発生したPermission deniedも、 「誰がPHPを実行しているのか」という視点から原因を確認できました。
まとめ
・MariaDBにmusikyun専用DBを作った。
・articlesテーブルへ記事を保存した。
・PHP専用のDBユーザーを作った。
・PDOでPHPとMariaDBを接続した。
・DB接続情報をWeb公開領域の外へ分離した。
・root / ubuntu / www-dataの違いを確認した。
・chownとchmodでファイル権限を設定した。
・Permission deniedの原因を実際に確認した。
・htmlspecialchars()を使って記事をHTMLへ出力した。
・ブラウザからMariaDBの記事を表示できた。