深入淺出SQL佔位符

一、什麼是SQL佔位符

SQL佔位符是一種佔用SQL語句中某些值的標記或佔位符。當執行SQL時，將使用該標記替換為實際的值，並將這些值傳遞給查詢。SQL佔位符使查詢更加安全，防止SQL注入攻擊，並且可以提高性能。

二、SQL中的佔位符類型

SQL中的佔位符類型有兩種：問號佔位符和命名佔位符。

1. 問號佔位符

問號佔位符是使用位置索引來引用參數的通用佔位符。在執行查詢時，將根據查詢中問號的順序解析綁定參數的值。

SELECT * FROM customers 
WHERE first_name = ? AND last_name = ?

上述查詢中使用了兩個問號佔位符，第一個問號佔位符綁定了參數值”John”，第二個問號佔位符綁定了參數值”Doe”。

2. 命名佔位符

命名佔位符是使用名稱引用參數的佔位符。在執行查詢時，將查找佔位符的名稱，並使用相應的綁定值替換佔位符。

SELECT * FROM customers 
WHERE first_name = :first_name AND last_name = :last_name

上述查詢中使用了兩個命名佔位符，分別為:first_name和:last_name，這兩個命名佔位符綁定了John和Doe兩個參數值。

三、佔位符的優點

SQL佔位符有以下優點：

1. 防止SQL注入攻擊

假設查詢中使用了字符串連接符”+”來拼接數據，那麼當向查詢中輸入惡意數據時，可能會執行任意的指令。例如：

SELECT * FROM users WHERE username = 'admin'
AND password = '' OR '1'='1'

在上面的查詢中，’1’=’1’條件永遠為真，所以查詢將返回所有用戶的記錄。這是一種針對SQL的注入攻擊。使用佔位符，可以避免這種攻擊，因為佔位符只會接受綁定值，而不是任意字符串拼接。例如：

SELECT * FROM users WHERE username = ? AND password = ?

在使用佔位符的情況下，綁定的參數將被當作參數而不是一部分查詢字符串。因此，無法通過輸入惡意語句來執行任意指令。

2. 提高性能

使用佔位符能夠提高查詢的性能，因為在執行的過程中，數據庫查詢計劃只需要生成一次。

SELECT * FROM users WHERE username = 'admin' AND password = 'password'

在上面的查詢中，每次代表用戶執行查詢時，查詢計劃需要重新生成。而使用佔位符的查詢可以重複使用相同的查詢計劃，從而提高性能，例如：

SELECT * FROM users WHERE username = ? AND password = ?

四、如何使用SQL佔位符

在PHP中，我們可以使用預處理語句和綁定參數來實現SQL佔位符的使用。

1. 使用預處理語句

準備語句（或預處理語句）是一種準備並編譯SQL語句的機制，以便稍後可以為不同的入參值執行相同的語句。

// 創建預處理語句
if ($stmt = $mysqli->prepare("SELECT * FROM customers WHERE first_name = ?")) {
    // 綁定參數
    $stmt->bind_param("s", $first_name);

    // 設置參數並執行查詢
    $first_name = "John";
    $stmt->execute();

    // 獲取查詢結果
    $result = $stmt->get_result();

    // 處理結果
    while ($row = $result->fetch_assoc()) {
        // 處理每一行結果
    }
    // 釋放結果
    $result->free();
}

在上面的示例中，我們使用了prepare()函數來創建一個預處理語句。然後，我們使用bind_param()函數來綁定參數。bind_param()函數接受兩個參數，第一個參數是一個字符串，標識綁定參數的數據類型（s代表字符串類型），第二個參數是要綁定到佔位符的參數的值。最後，我們執行查詢並處理結果。

2. 綁定多個參數的預處理查詢

有時一個查詢可能需要多個參數，我們可以使用多個佔位符來代表參數值。

// 創建預處理語句
if ($stmt = $mysqli->prepare("SELECT * FROM customers WHERE first_name = ? OR last_name = ?")) {
    // 綁定參數
    $stmt->bind_param("ss", $first_name, $last_name);

    // 設置參數並執行查詢
    $first_name = "John";
    $last_name = "Doe";
    $stmt->execute();

    // 獲取查詢結果
    $result = $stmt->get_result();

    // 處理結果
    while ($row = $result->fetch_assoc()) {
        // 處理每一行結果
    }
    // 釋放結果
    $result->free();
}

在上面的示例中，我們使用了bind_param()函數來綁定兩個字符串類型的數據。我們使用”ss”字符作為第一個參數，表明我們要綁定兩個字符串類型的數據。最後，我們設置參數並執行查詢。

五、總結

SQL佔位符是一種使查詢更加安全和高效的機制，它可以防止SQL注入攻擊，提高查詢性能。在PHP中，我們可以使用預處理語句和綁定參數來使用SQL佔位符。