MySQL 資料庫支援預備語句。預備語句或參數化語句用於高效地重複執行相同的語句，並防止 SQL 注入。
基本工作流程
預備語句的執行包含兩個階段：準備和執行。在準備階段，語句範本會被送往資料庫伺服器。伺服器會執行語法檢查，並初始化伺服器內部資源以供稍後使用。
MySQL 伺服器支援使用匿名、位置佔位符號 `?`。
準備階段之後是執行階段。在執行階段，客戶端會繫結參數值，並將它們送往伺服器。伺服器會使用先前建立的內部資源，以繫結的值執行語句。
範例 #1 預備語句
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* 非預備語句 */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
/* 預備語句，階段 1：準備 */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");
/* 預備語句，階段 2：綁定並執行 */
$id = 1;
$label = 'PHP';
$stmt->bind_param("is", $id, $label); // "is" 表示 $id 綁定為整數，$label 綁定為字串
$stmt->execute();重複執行
預備語句可以重複執行。每次執行時，都會評估繫結變數的當前值並將其發送到伺服器。語句不會再次被解析。語句範本也不會再次傳輸到伺服器。
範例 #2 INSERT 準備一次，執行多次
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* 非預備語句 */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
/* 預備語句，階段 1：準備 */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");
/* 預備語句，階段 2：綁定和執行 */
$stmt->bind_param("is", $id, $label); // "is" 表示 $id 綁定為整數，$label 綁定為字串
$data = [
1 => 'PHP',
2 => 'Java',
3 => 'C++'
];
foreach ($data as $id => $label) {
$stmt->execute();
}
$result = $mysqli->query('SELECT id, label FROM test');
var_dump($result->fetch_all(MYSQLI_ASSOC));上述範例將輸出
array(3) {
[0]=>
array(2) {
["id"]=>
string(1) "1"
["label"]=>
string(3) "PHP"
}
[1]=>
array(2) {
["id"]=>
string(1) "2"
["label"]=>
string(4) "Java"
}
[2]=>
array(2) {
["id"]=>
string(1) "3"
["label"]=>
string(3) "C++"
}
}
每個預備語句都會佔用伺服器資源。語句應該在使用後立即明確關閉。如果沒有明確關閉，則當 PHP 釋送語句控制代碼時，語句將被關閉。
使用預備語句並不總是執行語句最有效率的方式。只執行一次的預備語句會比非預備語句造成更多的客戶端-伺服器往返。這就是為什麼上述的 `SELECT` 沒有以預備語句的形式執行。
此外，對於 INSERT 操作，請考慮使用 MySQL 的多重 INSERT SQL 語法。對於此範例，多重 INSERT 比上述所示的預備語句需要更少的伺服器與客戶端之間的往返。
範例 #3 使用多重 INSERT SQL 減少往返次數
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$values = [1, 2, 3, 4];
$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?), (?), (?), (?)");
$stmt->bind_param('iiii', ...$values);
$stmt->execute();結果集值的資料類型
MySQL Client Server Protocol 為預備語句和非預備語句定義了不同的資料傳輸協定。預備語句使用所謂的二進位協定。MySQL 伺服器以二進位格式「按原樣」發送結果集資料。結果在發送之前不會序列化為字串。用戶端程式庫會接收二進位資料，並嘗試將值轉換為適當的 PHP 資料類型。例如，來自 SQL INT 欄位的結果將以 PHP 整數變數提供。
範例 #4 原生資料類型
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* 非預備語句 */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));上述範例將輸出
id = 1 (integer) label = PHP (string)
這個行為與非預備語句不同。預設情況下，非預備語句會將所有結果以字串形式返回。這個預設值可以使用連線選項更改。如果使用了連線選項，則沒有差異。
使用綁定變數擷取結果
預備語句的結果可以透過綁定輸出變數來擷取，也可以透過請求 mysqli_result 物件來擷取。
輸出變數必須在語句執行後綁定。語句結果集的每一欄都必須綁定一個變數。
範例 #5 輸出變數綁定
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* 非預備語句 */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$stmt->bind_result($out_id, $out_label);
while ($stmt->fetch()) {
printf("id = %s (%s), label = %s (%s)\n", $out_id, gettype($out_id), $out_label, gettype($out_label));
}上述範例將輸出
id = 1 (integer), label = PHP (string)
預備語句預設會傳回未緩衝的結果集。語句的結果不會隱式擷取並從伺服器傳輸到客戶端以進行客戶端緩衝。結果集會佔用伺服器資源，直到客戶端擷取所有結果為止。因此，建議及時使用結果。如果客戶端未能擷取所有結果，或客戶端在擷取所有資料之前關閉語句，則必須由 mysqli 隱式擷取資料。
也可以使用 mysqli_stmt::store_result() 緩衝預備語句的結果。
使用 mysqli_result 介面擷取結果
除了使用繫結結果之外，也可以透過 mysqli_result 介面擷取結果。mysqli_stmt::get_result() 會傳回一個已緩衝的結果集。
範例 #6 使用 mysqli_result 擷取結果
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* 非預備語句 */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");
$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$result = $stmt->get_result();
var_dump($result->fetch_all(MYSQLI_ASSOC));上述範例將輸出
array(1) {
[0]=>
array(2) {
["id"]=>
int(1)
["label"]=>
string(3) "PHP"
}
}
使用 mysqli_result 介面還提供了彈性操作客戶端結果集的額外好處。
範例 #7 用於彈性讀取的緩衝結果集
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
/* 非預備語句 */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP'), (2, 'Java'), (3, 'C++')");
$stmt = $mysqli->prepare("SELECT id, label FROM test");
$stmt->execute();
$result = $stmt->get_result();
for ($row_no = $result->num_rows - 1; $row_no >= 0; $row_no--) {
$result->data_seek($row_no);
var_dump($result->fetch_assoc());
}上述範例將輸出
array(2) {
["id"]=>
int(3)
["label"]=>
string(3) "C++"
}
array(2) {
["id"]=>
int(2)
["label"]=>
string(4) "Java"
}
array(2) {
["id"]=>
int(1)
["label"]=>
string(3) "PHP"
}
跳脫字元與 SQL 注入
繫結變數會與查詢分開傳送到伺服器，因此不會互相干擾。伺服器在語句模板解析後，會在執行時直接使用這些值。繫結參數不需要跳脫，因為它們永遠不會直接替換到查詢字串中。必須向伺服器提供繫結變數類型的提示，才能建立適當的轉換。詳情請參閱 mysqli_stmt::bind_param() 函式。
這種分離有時被認為是防止 SQL 注入的唯一安全措施，但如果所有值都格式正確，使用非預備語句也可以達到同樣的安全等級。需要注意的是，正確的格式化與跳脫字元不同，它比簡單的跳脫字元涉及更多邏輯。因此，預備語句只是資料庫安全性的這個要素中更方便且更不容易出錯的方法。
用戶端預備語句模擬
此 API 不包含用戶端預備語句模擬的模擬功能。
另請參閱