從零建立簡易留言板系統,第一篇建立資料庫連線共用檔案、管理員登入/登出機制,奠定後續功能基礎。
CREATE DATABASE `guestbook` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `guestbook`;
-- 管理員帳號表
CREATE TABLE `admins` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`username` VARCHAR(50) NOT NULL UNIQUE,
`password_hash` VARCHAR(255) NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 留言表
CREATE TABLE `messages` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`username` VARCHAR(50) NOT NULL COMMENT '留言者暱稱',
`email` VARCHAR(100) DEFAULT '' COMMENT 'Email',
`website` VARCHAR(200) DEFAULT '' COMMENT '個人網站',
`qq` VARCHAR(20) DEFAULT '' COMMENT 'QQ 號',
`content` TEXT NOT NULL COMMENT '留言內容',
`ip` VARBINARY(16) NOT NULL COMMENT 'IP 位址',
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`reply` TEXT DEFAULT NULL COMMENT '管理員回覆',
`replied_at` TIMESTAMP NULL DEFAULT NULL COMMENT '回覆時間',
INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 建立預設管理員(密碼:admin123)
INSERT INTO `admins` (`username`, `password_hash`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');
config.php<?php
// config.php
return [
'db' => [
'host' => '127.0.0.1',
'name' => 'guestbook',
'user' => 'root',
'pass' => '', // 請修改
'charset' => 'utf8mb4',
],
'app' => [
'name' => '簡易留言板',
'timezone' => 'Asia/Taipei',
'debug' => true,
],
'auth' => [
'session_name' => 'gb_admin',
'cookie_lifetime' => 86400, // 1 天
],
];
includes/db.php<?php
// includes/db.php
declare(strict_types=1);
function getPDO(): PDO {
static $pdo = null;
if ($pdo === null) {
$config = require __DIR__ . '/../config.php';
$db = $config['db'];
$dsn = "mysql:host={$db['host']};dbname={$db['name']};charset={$db['charset']}";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, $db['user'], $db['pass'], $options);
}
return $pdo;
}
includes/common.php<?php
// includes/common.php - 所有頁面開頭 require_once
require_once __DIR__ . '/db.php';
session_start();
// 時區
date_default_timezone_set('Asia/Taipei');
// 除錯模式
$config = require __DIR__ . '/../config.php';
if ($config['app']['debug']) {
error_reporting(E_ALL);
ini_set('display_errors', '1');
} else {
error_reporting(E_ALL & ~E_DEPRECATED);
ini_set('display_errors', '0');
ini_set('log_errors', '1');
}
// 輸出跳脫助手
function h(string $str): string {
return htmlspecialchars($str, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}
// 重導向
function redirect(string $url, int $code = 302): never {
header('Location: ' . $url, true, $code);
exit;
}
// 閃存訊息
function flash(string $msg, string $type = 'info'): void {
$_SESSION['flashes'][] = ['msg' => $msg, 'type' => $type];
}
function get_flashes(): array {
$f = $_SESSION['flashes'] ?? [];
unset($_SESSION['flashes']);
return $f;
}
// CSRF
function csrf_token(): string {
if (empty($_SESSION['csrf'])) {
$_SESSION['csrf'] = bin2hex(random_bytes(32));
}
return $_SESSION['csrf'];
}
function csrf_check(string $token): bool {
return isset($_SESSION['csrf']) && hash_equals($_SESSION['csrf'], $token);
}
// 取得客戶端 IP
function get_client_ip(): string {
$headers = ['HTTP_CLIENT_IP', 'HTTP_X_FORWARDED_FOR', 'HTTP_X_REAL_IP', 'REMOTE_ADDR'];
foreach ($headers as $h) {
if (!empty($_SERVER[$h])) {
$ips = array_map('trim', explode(',', $_SERVER[$h]));
foreach ($ips as $ip) {
if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE)) {
return $ip;
}
}
return $ips[0];
}
}
return '0.0.0.0';
}
admin/login.php<?php
// admin/login.php
require_once __DIR__ . '/../includes/common.php';
$pdo = getPDO();
$error = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (!csrf_check($_POST['csrf'] ?? '')) {
$error = '無效請求';
} else {
$username = trim($_POST['username'] ?? '');
$password = $_POST['password'] ?? '';
if ($username && $password) {
$stmt = $pdo->prepare('SELECT * FROM `admins` WHERE `username` = ?');
$stmt->execute([$username]);
$admin = $stmt->fetch();
if ($admin && password_verify($password, $admin['password_hash'])) {
// 登入成功:重生 Session ID
session_regenerate_id(true);
$_SESSION['admin_id'] = $admin['id'];
$_SESSION['admin_user'] = $admin['username'];
$_SESSION['admin_login_at'] = time();
flash('登入成功', 'success');
redirect('index.php');
}
}
$error = '帳號或密碼錯誤';
}
}
?>
<!DOCTYPE html>
<html lang="zh-TW">
<head>
<meta charset="UTF-8">
<title>管理員登入</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="container py-5">
<div class="row justify-content-center">
<div class="col-md-4">
<div class="card">
<div class="card-header"><h4>管理員登入</h4></div>
<div class="card-body">
<?php if ($error): ?>
<div class="alert alert-danger"><?= h($error) ?></div>
<?php endif; ?>
<form method="post">
<input type="hidden" name="csrf" value="<?= csrf_token() ?>">
<div class="mb-3">
<label class="form-label">帳號</label>
<input type="text" name="username" class="form-control" required autocomplete="username">
</div>
<div class="mb-3">
<label class="form-label">密碼</label>
<input type="password" name="password" class="form-control" required autocomplete="current-password">
</div>
<button class="btn btn-primary w-100" type="submit">登入</button>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
admin/logout.php<?php
// admin/logout.php
require_once __DIR__ . '/../includes/common.php';
// 清除 Session
$_SESSION = [];
// 刪除 Cookie
$params = session_get_cookie_params();
setcookie(session_name(), '', time() - 3600, $params['path'], $params['domain'], $params['secure'], $params['httponly']);
session_destroy();
flash('已登出', 'info');
redirect('login.php');
includes/admin_auth.php<?php
// includes/admin_auth.php - 受保護頁面開頭 require_once
require_once __DIR__ . '/common.php';
if (empty($_SESSION['admin_id'])) {
// 記住原本想去的頁面
$_SESSION['intended_url'] = $_SERVER['REQUEST_URI'];
redirect('login.php');
}
// 選擇性:閒置逾時登出(30 分鐘)
if (isset($_SESSION['admin_login_at']) && time() - $_SESSION['admin_login_at'] > 1800) {
require_once __DIR__ . '/../admin/logout.php';
exit;
}
$_SESSION['admin_login_at'] = time();
下一篇將實作:
基礎架構建立完成,後續功能開發將更順暢。