mysql_connect() 等函數自 PHP 5.5 棄用,PHP 7.0 移除。現代專案應使用 mysqli(程序/物件雙介面)或 PDO(資料庫抽象層),支援預處理語句防止 SQL 注入。
<?php
$host = 'localhost';
$user = 'root';
$pass = 'your_strong_password';
$db = 'ruida';
// 建立連線
$mysqli = new mysqli($host, $user, $pass, $db);
if ($mysqli->connect_errno) {
die('連線失敗: ' . $mysqli->connect_error);
}
$mysqli->set_charset('utf8mb4'); // 關鍵:支援 emoji
// 準備預處理語句
$sql = 'SELECT id, name, price FROM product WHERE status = ?';
$stmt = $mysqli->prepare($sql);
$status = 1;
$stmt->bind_param('i', $status); // i=integer, s=string, d=double, b=blob
$stmt->execute();
$result = $stmt->get_result();
echo "<table border='1'><tr><th>ID</th><th>名稱</th><th>價格</th></tr>";
while ($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . htmlspecialchars($row['id']) . "</td>";
echo "<td>" . htmlspecialchars($row['name']) . "</td>";
echo "<td>" . htmlspecialchars($row['price']) . "</td>";
echo "</tr>";
}
echo "</table>";
$stmt->close();
$mysqli->close();
<?php
$dsn = 'mysql:host=localhost;dbname=ruida;charset=utf8mb4';
$user = 'root';
$pass = 'your_strong_password';
try {
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // 使用真實預處理
]);
$stmt = $pdo->prepare('SELECT id, name, price FROM product WHERE status = ?');
$stmt->execute([1]);
echo "<table border='1'><tr><th>ID</th><th>名稱</th><th>價格</th></tr>";
foreach ($stmt as $row) {
echo "<tr><td>{$row['id']}</td><td>" . htmlspecialchars($row['name']) . "</td><td>{$row['price']}</td></tr>";
}
echo "</table>";
} catch (PDOException $e) {
die('資料庫錯誤: ' . $e->getMessage());
}
prepare() + bind_param()/execute([...]),杜絕 SQL 注入。utf8 僅支援 BMP 平面。mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) 或 PDO ATTR_ERRMODE => ERRMODE_EXCEPTION。htmlspecialchars() 防 XSS。mysqli::pconnect 或 PDO ATTR_PERSISTENT,並配合連線池中介軟體。| 錯誤現象 | 原因 | 修正 |
|---|---|---|
| 中文亂碼 | 未設定 charset | set_charset('utf8mb4') / DSN 加 charset=utf8mb4 |
| SQL 注入漏洞 | 字串拼接 SQL | 改用預處理語句 |
| Call to undefined function mysql_connect() | PHP 7+ 已移除 | 改用 mysqli/PDO |
| Commands out of sync | 多語句未釋放結果集 | 每次查詢後 free_result() 或 closeCursor() |