PHP + jQuery 實作留言板增刪操作（MVC 風格重構與安全加固）

原始代碼問題分析

• 使用已棄用 mysql_* 函數（PHP 7.0+ 移除）
• SQL 直接拼接變數，極高風險 SQL 注入
• HTML/JS/PHP 混寫，難以維護
• 無 CSRF/XSS 防護
• 刪除使用 GET 請求，違反 REST、易受 CSRF 攻擊

重構版：PDO + Prepared Statements + AJAX

資料庫結構

CREATE TABLE `messages` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `content` VARCHAR(500) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

config.php（配置分離）

<?php
return [
    'db' => [
        'host' => '127.0.0.1',
        'dbname' => 'test_db',
        'user' => 'db_user',
        'pass' => 'db_pass',
        'charset' => 'utf8mb4',
    ],
    'app' => [
        'csrf_key' => 'csrf_token',
        'per_page' => 30,
    ],
];

Database.php（PDO 單例）

<?php
class Database {
    private static $pdo = null;
    
    public static function get(): PDO {
        if (self::$pdo === null) {
            $cfg = require __DIR__ . '/config.php';
            $dsn = "mysql:host={$cfg['db']['host']};dbname={$cfg['db']['dbname']};charset={$cfg['db']['charset']}";
            self::$pdo = new PDO($dsn, $cfg['db']['user'], $cfg['db']['pass'], [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]);
        }
        return self::$pdo;
    }
}

MessageRepository.php（資料存取層）

<?php
class MessageRepository {
    public function getLatest(int $limit = 30): array {
        $sql = "SELECT id, content, created_at FROM messages ORDER BY id DESC LIMIT ?";
        $stmt = Database::get()->prepare($sql);
        $stmt->execute([$limit]);
        return $stmt->fetchAll();
    }
    
    public function create(string $content): bool {
        $sql = "INSERT INTO messages (content) VALUES (?)";
        $stmt = Database::get()->prepare($sql);
        return $stmt->execute([$content]);
    }
    
    public function delete(int $id): bool {
        $sql = "DELETE FROM messages WHERE id = ?";
        $stmt = Database::get()->prepare($sql);
        return $stmt->execute([$id]);
    }
}

api.php（統一 AJAX 入口，回傳 JSON）

<?php
require_once __DIR__ . '/Database.php';
require_once __DIR__ . '/MessageRepository.php';

header('Content-Type: application/json; charset=utf-8');
session_start();

$repo = new MessageRepository();
$action = $_POST['action'] ?? $_GET['action'] ?? '';
$response = ['success' => false, 'message' => '', 'data' => null];

try {
    switch ($action) {
        case 'list':
            $response['data'] = $repo->getLatest();
            $response['success'] = true;
            break;
            
        case 'create':
            // CSRF 驗證
            if (!hash_equals($_SESSION['csrf'] ?? '', $_POST['csrf'] ?? '')) {
                throw new Exception('CSRF token 無效');
            }
            $content = trim($_POST['content'] ?? '');
            if ($content === '') throw new Exception('內容不可為空');
            if (mb_strlen($content) > 500) throw new Exception('內容過長');
            
            if ($repo->create($content)) {
                $response['success'] = true;
                $response['message'] = '新增成功';
            } else {
                throw new Exception('資料庫寫入失敗');
            }
            break;
            
        case 'delete':
            if (!hash_equals($_SESSION['csrf'] ?? '', $_POST['csrf'] ?? '')) {
                throw new Exception('CSRF token 無效');
            }
            $id = (int)($_POST['id'] ?? 0);
            if ($id <= 0) throw new Exception('無效 ID');
            
            if ($repo->delete($id)) {
                $response['success'] = true;
                $response['message'] = '刪除成功';
            } else {
                throw new Exception('刪除失敗或資料不存在');
            }
            break;
            
        default:
            throw new Exception('未知操作');
    }
} catch (Exception $e) {
    $response['message'] = $e->getMessage();
}

echo json_encode($response);

index.php（視圖 + CSRF Token）

<?php
session_start();
if (empty($_SESSION['csrf'])) {
    $_SESSION['csrf'] = bin2hex(random_bytes(32));
}
$csrf = $_SESSION['csrf'];
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>留言板</title>
    <style>
        .msg { border-bottom: 1px solid #eee; padding: 10px 0; display: flex; justify-content: space-between; }
        .msg-content { flex: 1; }
        .msg-time { color: #999; font-size: 0.85em; margin-left: 10px; }
        .btn-del { color: #c00; cursor: pointer; }
        textarea { width: 100%; height: 80px; margin-bottom: 10px; }
    </style>
</head>
<body>
    <h2>PHP + jQuery 留言板</h2>
    
    <form id="msgForm">
        <input type="hidden" name="action" value="create">
        <input type="hidden" name="csrf" value="<?= htmlspecialchars($csrf) ?>">
        <textarea name="content" placeholder="輸入留言..." required></textarea>
        <button type="submit">送出</button>
    </form>
    
    <div id="msgList">載入中...</div>
    
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script>
    $(function() {
        const $list = $('#msgList');
        const $form = $('#msgForm');
        
        // 載入留言
        function load() {
            $.post('api.php', {action: 'list'}, function(res) {
                if (res.success) {
                    render(res.data);
                } else {
                    $list.html('<div class="err">' + res.message + '</div>');
                }
            }, 'json');
        }
        
        // 渲染
        function render(items) {
            if (!items.length) {
                $list.html('<p>暫無留言</p>');
                return;
            }
            $list.html(items.map(item => `
                <div class="msg" data-id="${item.id}">
                    <div class="msg-content">${escapeHtml(item.content)}</div>
                    <span class="msg-time">${item.created_at}</span>
                    <span class="btn-del" title="刪除">✕</span>
                </div>
            `).join(''));
        }
        
        // XSS 防護
        function escapeHtml(text) {
            return $('
').text(text).html();
        }
        
        // 新增
        $form.on('submit', function(e) {
            e.preventDefault();
            const data = $form.serialize();
            $.post('api.php', data, function(res) {
                if (res.success) {
                    $form.find('textarea').val('');
                    load();
                } else {
                    alert(res.message);
                }
            }, 'json');
        });
        
        // 刪除（事件委派）
        $list.on('click', '.btn-del', function() {
            if (!confirm('確定刪除？')) return;
            const id = $(this).closest('.msg').data('id');
            $.post('api.php', {
                action: 'delete',
                id: id,
                csrf: ''
            }, function(res) {
                if (res.success) load();
                else alert(res.message);
            }, 'json');
        });
        
        load();
    });
    </script>
</body>
</html>

關鍵安全強化點

擴展建議

• 分頁載入（LIMIT offset, count）
• 軟刪除（deleted_at 欄位）
• 編輯功能（PUT/PATCH）
• API Token 認證支援行動端