Learning Objectives
- 說明 PDO 與 mysqli 的差別，用 DSN、帳號、密碼與連線選項建立 PDO 連線
- 用 query 與 fetch／fetchAll 讀取資料，選擇適合的取回模式（FETCH_ASSOC、FETCH_OBJ、FETCH_COLUMN、FETCH_KEY_PAIR…）
- 用 prepare／execute 與 ? 或 :name 佔位符執行查詢、新增、修改、刪除，並用 rowCount、lastInsertId 取得結果
- 用交易（beginTransaction、commit、rollBack）讓多個修改一起成功或一起取消
- 用 try／catch 處理 PDOException，看懂 SQLSTATE 錯誤訊息
- 說明 SQL 注入如何發生，以及預備陳述式為什麼能防止
把資料存進資料庫
會員、商品、訂單、留言，網站的資料幾乎都存在資料庫裡。PHP 程式是資料庫的「用戶端」：連線到資料庫伺服器， 送出 SQL，取回結果，再產生網頁。PHP 連 MySQL 主要有兩種方式：
| PDO（PHP Data Objects） | mysqli | |
|---|---|---|
| 支援的資料庫 | MySQL、PostgreSQL、SQLite、SQL Server… 換資料庫時程式改動少 | 只有 MySQL（與 MariaDB） |
| 寫法 | 物件導向 | 物件導向或函式（mysqli_query()） |
| 預備陳述式的佔位符 | ? 與具名的 :name |
只有 ? |
兩者都能安全地使用預備陳述式。本課用 PDO：同一套寫法可以用在不同的資料庫，具名佔位符也比較好讀。 SQL 本身（SELECT、INSERT、交易）的寫法請參考 CodeLab 的 SQL 課程，本課專注在 PHP 這一端。
本課的範例連到一個模擬的 MySQL 8 資料庫 php17_shop，裡面有三張表：
| 資料表 | 欄位 | 資料 |
|---|---|---|
products（商品） |
id、name、price（DECIMAL）、stock（INT） | 筆記本 45、原子筆 15.5、水壺 380、便當盒 250 |
accounts（帳戶） |
id、owner、balance（DECIMAL） | Amy 1000、Ken 500 |
members（會員） |
id、username、password | admin、amy（示範 SQL 注入用，密碼直接存放是錯誤做法） |
建立連線：DSN 與選項
$pdo = new PDO(
'mysql:host=localhost;dbname=php17_shop;charset=utf8mb4', // DSN：資料庫種類、主機、資料庫名稱、字元集
'root', // 帳號
'88888888', // 密碼
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // 出錯時丟出 PDOException
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // fetch 預設回傳關聯陣列
]
);
- DSN（Data Source Name）一定要寫
charset=utf8mb4，中文和 emoji 才不會變成問號 - 錯誤模式：PHP 8.0 起預設就是
ERRMODE_EXCEPTION（更早的版本預設是安靜模式，出錯只回傳 false，很容易漏掉錯誤）。明確寫出來，程式在舊環境也一樣 - 帳號密碼不要直接寫在程式裡交到 Git，實務上放在不進版本控制的設定檔或環境變數（本課為了示範才寫在程式裡）
- 連線失敗（密碼錯、資料庫不存在）也會丟出
PDOException
取回的值是什麼型別？ PHP 8.1 起，pdo_mysql 取回的整數欄位是 int、浮點數欄位（FLOAT、DOUBLE）是 float；
DECIMAL、日期時間仍然是字串（例如 "45.00"、"2026-09-01 09:00:00"）。DECIMAL 是精確的十進位數，換成 float 會有誤差，所以 PDO 保留字串。
連線、查詢、逐筆走訪
$pdo->query() 回傳一個 PDOStatement（查詢結果），可以直接放進 foreach，每一圈取出一筆。注意最後 var_dump 的型別：id、stock 是 int，price（DECIMAL）是字串。
fetch 的模式
fetch() 取一筆、fetchAll() 取全部；傳入的模式決定每一筆的樣子：
| 模式 | 一筆資料的樣子 | 適合 |
|---|---|---|
PDO::FETCH_ASSOC |
['name' => '筆記本', 'price' => '45.00'] |
最常用 |
PDO::FETCH_NUM |
[0 => '筆記本', 1 => '45.00'] |
搭配 [$a, $b] = … 解構 |
PDO::FETCH_BOTH（沒有設定時的預設） |
兩種鍵都有，資料重複一次 | 很少需要 |
PDO::FETCH_OBJ |
物件，用 $row->name 讀取 |
喜歡物件寫法時 |
PDO::FETCH_COLUMN（fetchAll） |
只取第一欄，得到 ['筆記本', '原子筆', …] |
一欄的清單 |
PDO::FETCH_KEY_PAIR（fetchAll） |
第一欄當鍵、第二欄當值：['筆記本' => 120, …] |
下拉選單、對照表 |
沒有下一筆時，fetch() 回傳 false，所以可以寫 while ($row = $stmt->fetch()) { … }。
各種取回模式
這個連線沒有設定 DEFAULT_FETCH_MODE。比較右邊每個變數的陣列：$assoc 的鍵是欄位名稱、$num 的鍵是數字、$stock 是名稱對庫存的對照表。最後一行看看沒有指定模式時，一筆資料有哪些鍵。
想一想：連線與取回
products 的 price 是 DECIMAL(8,2)、stock 是 INT。下面的程式會印出什麼？
<?php
$pdo = new PDO('mysql:host=localhost;dbname=php17_shop;charset=utf8mb4', 'root', '88888888');
$row = $pdo->query('SELECT price, stock FROM products WHERE id = 1')->fetch(PDO::FETCH_ASSOC);
echo gettype($row['price']), ' ', gettype($row['stock']);
連線時沒有設定 ATTR_DEFAULT_FETCH_MODE。下面的程式會印出什麼？
<?php
$pdo = new PDO('mysql:host=localhost;dbname=php17_shop;charset=utf8mb4', 'root', '88888888');
$row = $pdo->query('SELECT name, stock FROM products WHERE id = 1')->fetch();
echo count($row);
SQL 注入：不要把輸入接進 SQL 字串
要查詢使用者指定的資料，最直覺的寫法是把變數接進 SQL 字串：
$sql = "SELECT * FROM members WHERE username = '$user' AND password = '$pass'";
如果有人在密碼欄輸入 ' OR '1'='1，組出來的 SQL 是：
SELECT * FROM members WHERE username = 'amy' AND password = '' OR '1'='1'
輸入裡的單引號提前結束了字串，後面的 OR '1'='1' 變成 SQL 語法的一部分。AND 比 OR 先計算，條件變成
「(帳號是 amy 而且密碼是空的) 或 '1'='1'」，後者永遠成立，於是每一筆會員都符合，不知道密碼也能登入。
這就是 SQL 注入（SQL injection）：使用者的輸入改變了 SQL 的結構。同樣的手法還能讀出整個資料庫、刪除資料表。
解法：預備陳述式（prepared statement）。SQL 裡要放資料的地方寫佔位符，資料另外交給 execute()：
$stmt = $pdo->prepare('SELECT * FROM members WHERE username = ? AND password = ?');
$stmt->execute([$user, $pass]);
SQL 的結構在 prepare 時就固定了，資料一律被當成一個值（PDO 會正確地加上引號與跳脫，或交給資料庫和 SQL 分開處理），
輸入裡有引號也只是密碼的一部分，不會變成語法。只要 SQL 裡有任何來自外部的值（表單、網址、Cookie、甚至資料庫裡別人存的資料），
一律用預備陳述式，不要自己串接或跳脫。
串接字串 vs. 預備陳述式
模擬有人用 POST 送出帳號 amy、密碼 ' OR '1'='1。同一組輸入分別用字串串接與預備陳述式查詢，看第 9 行組出來的 $sql，以及兩次登入的結果。
預備陳述式的寫法
| 寫法 | 例子 |
|---|---|
? 依順序 |
prepare('… WHERE price < ? AND stock > ?') → execute([100, 50]) |
:name 具名 |
prepare('… VALUES (:name, :price)') → execute(['name' => '便利貼', 'price' => '29.90']) |
bindValue |
$stmt->bindValue(':id', 3, PDO::PARAM_INT); 綁定一個值 |
bindParam |
$stmt->bindParam(':id', $id, PDO::PARAM_INT); 綁定變數本身，每次 execute() 時才讀它目前的值 |
同一個 SQL 裡 ? 和 :name 不能混用。執行修改之後：
$stmt->rowCount()：INSERT、UPDATE、DELETE 影響了幾筆。MySQL 只計算真的被改變的列（值沒變就不算）；SELECT 的筆數請用count($stmt->fetchAll())$pdo->lastInsertId()：剛剛 INSERT 產生的 AUTO_INCREMENT 編號，回傳的是字串$pdo->exec($sql)：執行沒有變數的修改，直接回傳影響的筆數
佔位符只能代替值，不能代替資料表名稱、欄位名稱或 ORDER BY 的方向。這些需要依使用者選擇時，用白名單：
$col = in_array($_GET['sort'], ['name', 'price'], true) ? $_GET['sort'] : 'id';。
LIKE 的萬用字元放在值裡：execute(['%' . $q . '%'])。
查詢、新增、修改、刪除
同一個 $pdo 連線依序執行 SELECT、INSERT、UPDATE、DELETE。第 20 行的 bindParam 綁定的是變數 $id 本身，迴圈每一圈改了 $id，execute() 就用新的值查詢。
交易與錯誤處理
轉帳要做兩件事：A 的帳戶扣錢、B 的帳戶加錢。如果扣完錢之後程式出錯，錢就憑空消失了。 交易（transaction）把多個修改包成一個單位，全部成功才保存，中途出問題就全部取消：
$pdo->beginTransaction(); // 開始交易：之後的修改先不保存
try {
// … 好幾個 UPDATE …
$pdo->commit(); // 全部成功：保存
} catch (Exception $e) {
$pdo->rollBack(); // 出錯：取消這次交易裡所有的修改
throw $e; // 實務上通常再丟出去，交給上層處理
}
交易需要支援交易的儲存引擎（MySQL 的預設 InnoDB 支援）。
SQL 出錯時（資料表不存在、違反唯一鍵…），PDO 丟出 PDOException，訊息的格式是：
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'php17_shop.acounts' doesn't exist
42S02 是標準的 SQLSTATE 錯誤類別（getCode() 回傳它），1146 是 MySQL 自己的錯誤編號，後面是說明。
錯誤訊息可能包含資料表名稱、SQL 片段，不要直接顯示給使用者，記到伺服器的紀錄檔，畫面上只顯示「系統忙碌，請稍後再試」（第 18 課）。
轉帳：成功就 commit，失敗就 rollBack
Amy（編號 1）有 1000 元、Ken（編號 2）有 500 元。第一次轉 300 元成功；第二次轉 5000 元，扣款之後發現餘額變成負數，丟出例外並 rollBack()，兩個 UPDATE 都被取消。最後故意查詢一個拼錯的資料表，看 PDOException 的內容。
想一想：注入、交易與錯誤
下面哪一種寫法能防止 SQL 注入？
$id = $_GET['id'];
products 原本有 4 筆資料。下面的程式會印出什麼？
<?php
$pdo = new PDO('mysql:host=localhost;dbname=php17_shop;charset=utf8mb4', 'root', '88888888');
$pdo->beginTransaction();
$n = $pdo->exec('DELETE FROM products WHERE price < 100');
$pdo->rollBack();
echo $n, ' ', $pdo->query('SELECT COUNT(*) FROM products')->fetchColumn();
在 PHP 8.3，連線時沒有設定任何選項。下面的程式會以什麼結束？
<?php
$pdo = new PDO('mysql:host=localhost;dbname=php17_shop;charset=utf8mb4', 'root', '88888888');
$stmt = $pdo->query('SELECT * FROM product');
var_dump($stmt);
動手做：依價格查詢商品
模擬的請求是 /index.php?max=100。請用預備陳述式查出價格小於或等於 max 的商品，依價格由低到高排列，每行印出「名稱：價格 元（庫存 N）」，最後印出「共 N 筆」。max 已經用 filter_var 驗證成整數。
重點回顧
- PHP 連 MySQL 用 PDO（多種資料庫通用、支援具名佔位符）或 mysqli；本課用 PDO
new PDO('mysql:host=…;dbname=…;charset=utf8mb4', 帳號, 密碼, 選項)；建議設定ERRMODE_EXCEPTION（PHP 8 的預設）與DEFAULT_FETCH_MODE => FETCH_ASSOCquery()執行沒有變數的 SQL；fetch()取一筆（沒有了回傳 false）、fetchAll()取全部、fetchColumn()取一個值，PDOStatement 也可以直接 foreach- 取回模式：FETCH_ASSOC、FETCH_NUM、FETCH_OBJ、FETCH_COLUMN、FETCH_KEY_PAIR；沒設定時是 FETCH_BOTH
- PHP 8.1 起整數欄位取回成 int、浮點數成 float，DECIMAL 與日期時間仍是字串
- SQL 裡有任何外部的值，一律用預備陳述式：
prepare加?或:name，再execute([…])；字串串接會造成 SQL 注入 rowCount()是修改影響的筆數，lastInsertId()是新編號（字串）；bindParam綁定變數、bindValue綁定值- 多個修改要一起成功：
beginTransaction()、commit()，出錯rollBack() - SQL 錯誤丟出
PDOException（SQLSTATE[…]訊息），記錄下來，不要直接顯示給使用者