CREATE DATABASE `user_system` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `user_system`;
CREATE TABLE `users` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用戶名',
`email` VARCHAR(100) NOT NULL UNIQUE COMMENT '電子郵件',
`password_hash` CHAR(60) NOT NULL COMMENT 'bcrypt 雜湊',
`password_hint_q` VARCHAR(100) DEFAULT NULL COMMENT '密碼提示問題',
`password_hint_a` VARCHAR(100) DEFAULT NULL COMMENT '密碼提示答案(加密)',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '1=正常,0=禁用',
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_username` (`username`),
INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
<?php
// Database.php - PDO 單例連線
class Database {
private static ?PDO $instance = null;
public static function getInstance(): PDO {
if (self::$instance === null) {
$dsn = 'mysql:host=' . env('DB_HOST') . ';dbname=' . env('DB_NAME') . ';charset=utf8mb4';
self::$instance = new PDO($dsn, env('DB_USER'), env('DB_PASS'), [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
}
return self::$instance;
}
}
// UserService.php - 業務邏輯封裝
class UserService {
private PDO $db;
public function __construct() {
$this->db = Database::getInstance();
}
// 註冊
public function register(string $username, string $email, string $password, string $hintQ, string $hintA): array {
// 驗證
if (strlen($username) < 3) return ['success' => false, 'msg' => '用戶名至少 3 字元'];
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) return ['success' => false, 'msg' => 'Email 格式錯誤'];
if (strlen($password) < 8) return ['success' => false, 'msg' => '密碼至少 8 字元'];
// 檢查重複
$stmt = $this->db->prepare('SELECT id FROM users WHERE username = ? OR email = ?');
$stmt->execute([$username, $email]);
if ($stmt->fetch()) return ['success' => false, 'msg' => '用戶名或 Email 已存在'];
// 雜湊密碼
$hash = password_hash($password, PASSWORD_DEFAULT);
$hintAHash = password_hash($hintA, PASSWORD_DEFAULT); // 提示答案也加密
// 寫入
$stmt = $this->db->prepare('INSERT INTO users (username, email, password_hash, password_hint_q, password_hint_a) VALUES (?, ?, ?, ?, ?)');
$stmt->execute([$username, $email, $hash, $hintQ, $hintAHash]);
return ['success' => true, 'msg' => '註冊成功', 'user_id' => $this->db->lastInsertId()];
}
// 登入
public function login(string $username, string $password): array {
$stmt = $this->db->prepare('SELECT id, username, password_hash, status FROM users WHERE username = ? OR email = ?');
$stmt->execute([$username, $username]);
$user = $stmt->fetch();
if (!$user) return ['success' => false, 'msg' => '用戶不存在'];
if ($user['status'] != 1) return ['success' => false, 'msg' => '帳號已停用'];
if (!password_verify($password, $user['password_hash'])) return ['success' => false, 'msg' => '密碼錯誤'];
// 登入成功,更新最後登入時間 (可選)
$this->db->prepare('UPDATE users SET updated_at = NOW() WHERE id = ?')->execute([$user['id']]);
return ['success' => true, 'msg' => '登入成功', 'user' => [
'id' => $user['id'],
'username' => $user['username'],
]];
}
// 用戶名唯一性檢查 (AJAX 用)
public function checkUsernameAvailable(string $username): bool {
$stmt = $this->db->prepare('SELECT 1 FROM users WHERE username = ? LIMIT 1');
$stmt->execute([$username]);
return !$stmt->fetch();
}
}
?>
<!DOCTYPE html>
<html><head><meta charset="UTF-8"><title>用戶註冊</title>
<style>.error{color:red}.success{color:green}</style></head><body>
<h2>用戶註冊</h2>
<form id="regForm" method="POST" action="register_action.php">
<div><label>用戶名:<input type="text" name="username" required minlength="3"></label>
<button type="button" onclick="checkUsername()">檢查可用性</button>
<span id="usernameMsg"></span>
</div>
<div><label>Email:<input type="email" name="email" required></label></div>
<div><label>密碼:<input type="password" name="password" required minlength="8"></label></div>
<div><label>確認密碼:<input type="password" name="password2" required></label></div>
<div><label>密碼提示問題:<select name="hint_q">
<option value="pet">寵物名字</option>
<option value="city">想去的城市</option>
<option value="sport">喜歡的運動</option>
</select></label></div>
<div><label>提示答案:<input type="text" name="hint_a" required></label></div>
<button type="submit">註冊</button>
</form>
<div id="result"></div>
<script>
function checkUsername() {
const name = document.querySelector('[name=username]').value;
if (!name) return alert('請先輸入用戶名');
fetch('check_username.php?username=' + encodeURIComponent(name))
.then(r => r.json())
.then(data => {
document.getElementById('usernameMsg').innerHTML = data.available
? '<span class="success">可用</span>'
: '<span class="error">已被註冊</span>';
});
}
document.getElementById('regForm').addEventListener('submit', async (e) => {
e.preventDefault();
const fd = new FormData(e.target);
if (fd.get('password') !== fd.get('password2')) return alert('兩次密碼不一致');
const res = await fetch('register_action.php', {method:'POST', body:fd});
const data = await res.json();
document.getElementById('result').innerHTML = data.success
? '<div class="success">' + data.msg + '</div>'
: '<div class="error">' + data.msg + '</div>';
if (data.success) setTimeout(() => location.href='login.php', 1500);
});
</script>
</body></html>
<?php
require_once 'Database.php';
require_once 'UserService.php';
header('Content-Type: application/json; charset=UTF-8');
$username = trim($_POST['username'] ?? '');
$email = trim($_POST['email'] ?? '');
$password = $_POST['password'] ?? '';
$password2 = $_POST['password2'] ?? '';
$hintQ = $_POST['hint_q'] ?? '';
$hintA = $_POST['hint_a'] ?? '';
$service = new UserService();
$result = $service->register($username, $email, $password, $hintQ, $hintA);
echo json_encode($result);
?>
<?php
require_once 'Database.php';
require_once 'UserService.php';
header('Content-Type: application/json; charset=UTF-8');
$username = trim($_GET['username'] ?? '');
if ($username === '') exit(json_encode(['available' => false, 'msg' => '用戶名不可為空']));
$service = new UserService();
$available = $service->checkUsernameAvailable($username);
echo json_encode(['available' => $available, 'msg' => $available ? '可使用' : '已被註冊']);
?>
類似註冊流程,調用 UserService::login(),成功後建立 Session:
<?php
session_start();
$_SESSION['user_id'] = $user['id'];
$_SESSION['username'] = $user['username'];
// 可選:生成 JWT Token 用於 API 認證
?>
password_hash() + password_verify() (bcrypt/argon2)htmlspecialchars(),Content-Security-Policy 標頭csrf_token,驗證 $_SESSION['csrf'] === $_POST['csrf']