將DevExtreme過濾器轉換為MySQL WHERE子句的PHP教程

本文旨在提供一套PHP解決方案，將DevExtreme等前端框架生成的類NoSQL過濾數組結構動態轉換為標準的MySQL WHERE 子句。教程將詳細介紹如何使用PDO和MySQLi兩種方式構建安全的SQL查詢，包括參數化查詢的實現和數據轉義的最佳實踐，以有效防止SQL注入，確保數據庫操作的安全性與靈活性。

1. 理解問題背景與目標

在現代Web應用開發中，前端框架如DevExtreme常以結構化的JSON或數組形式定義數據過濾條件，例如：

 {
  "from": "get_data",
  "skip": 0,
  "take": 50,
  "requireTotalCount": true,
  "filter": [["SizeCd","=","UNIT"],"or",["SizeCd","=","JOGO"]]
}

其中，filter 字段是一個嵌套數組，它清晰地表達了過濾邏輯：[[字段, 運算符, 值], 邏輯運算符, [字段, 運算符, 值], ...]。我們的目標是將這種格式的過濾條件轉換成MySQL數據庫能夠理解的WHERE 子句，例如：WHERESizeCd= 'UNIT' ORSizeCd= 'JOGO'。轉換過程中，必須確保字段名不帶引號，而字符串值需要正確地加引號或作為預處理語句的參數。

2. 使用PDO實現安全的查詢轉換

PDO（PHP Data Objects）是PHP連接數據庫的推薦方式，它支持預處理語句，能夠有效防止SQL注入攻擊。我們將創建兩個輔助函數：一個用於構建帶有佔位符的SQL查詢字符串，另一個用於提取參數值。

假設我們有以下過濾數組：

 $filterArray = [
    ["SizeCd","=","UNIT"],
    "or",
    ["SizeCd","=","JOGO"],
    "or",
    ["SizeCd","=","PACOTE"]
];

2.1 構建帶有佔位符的SQL查詢字符串

arrayToQuery 函數負責遍歷過濾數組，將每個條件轉換為\字段` 運算符?` 的形式，並拼接邏輯運算符。

 /**
 * 將過濾數組轉換為帶有佔位符的SQL WHERE子句。
 *
 * @param string $tableName 目標表名。
 * @param array $filterArray DevExtreme風格的過濾數組。
 * @return string 包含佔位符的SQL查詢字符串。
 */
function arrayToQuery(string $tableName, array $filterArray) : string
{
    // 確保表名被反引號包圍，以處理特殊字符或保留字$select = "SELECT * FROM `{$tableName}` WHERE ";

    foreach($filterArray as $item) {
        if(is_array($item)) {
            // 條件數組：[字段, 運算符, 值]
            // 字段名用反引號包圍，值用問號佔位符$select .= "`{$item[0]}` {$item[1]} ?";
        } else {
            // 邏輯運算符："or", "and"
            $select .= " {$item} ";
        }
    }

    return $select;
}

2.2 提取查詢參數值

arrayToParams 函數負責從過濾數組中提取所有條件的值，這些值將用於PDO的參數綁定。

 /**
 * 從過濾數組中提取所有條件的值。
 *
 * @param array $filterArray DevExtreme風格的過濾數組。
 * @return array 包含所有參數值的數組。
 */
function arrayToParams(array $filterArray) : array
{
    $params = [];
    foreach($filterArray as $item) {
        if(is_array($item)) {
            // 提取條件數組中的第三個元素（即值）
            $params[] = $item[2];
        }
    }
    return $params;
}

2.3 PDO查詢示例

結合上述函數，我們可以輕鬆地執行PDO查詢：

 // 示例數據$filterArray = [
    ["SizeCd","=","UNIT"],
    "or",
    ["SizeCd","=","JOGO"],
    "or",
    ["SizeCd","=","PACOTE"]
];

// 假設您已建立PDO連接// $dsn = 'mysql:host=localhost;dbname=your_database';
// $username = 'your_username';
// $password = 'your_password';
// try {
// $conn = new PDO($dsn, $username, $password);
// $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// } catch (PDOException $e) {
// die("數據庫連接失敗: " . $e->getMessage());
// }
// 替換為您的實際PDO連接對象$conn = null; // 佔位符，請替換為您的實際PDO連接$tableName = "your_table_name"; // 替換為您的實際表名// 生成SQL查詢字符串和參數數組$sql = arrayToQuery($tableName, $filterArray);
$params = arrayToParams($filterArray);

echo "生成的SQL查詢: " . $sql . "\n";
echo "綁定的參數: " . print_r($params, true) . "\n";

// 實際執行查詢if ($conn) {
    try {
        $stmt = $conn->prepare($sql);
        $stmt->execute($params);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo "查詢結果:\n";
        print_r($results);
    } catch (PDOException $e) {
        echo "查詢執行失敗: " . $e->getMessage();
    }
} else {
    echo "請提供有效的PDO連接對象。\n";
}

輸出示例(不含實際查詢結果):

生成的SQL查詢: SELECT * FROM `your_table_name` WHERE `SizeCd` = ? or `SizeCd` = ? or `SizeCd` = ?
綁定的參數: Array
(
    [0] => UNIT
    [1] => JOGO
    [2] => PACOTE
)

注意事項：

• 使用PDO預處理語句和參數綁定是防止SQL注入的最佳實踐。
• 字段名使用反引號(`) 包裹，可以避免與MySQL保留字衝突。
• 表名也應使用反引號包裹。

3. 使用MySQLi實現查詢轉換（帶轉義）

對於使用MySQLi擴展的用戶，也可以實現類似的轉換。然而，如果不是使用MySQLi的預處理語句，而是直接拼接字符串，則必須手動對值進行轉義以防止SQL注入。

3.1 構建SQL查詢字符串（帶轉義）

arrayToQueryMysqli 函數在構建SQL字符串時，直接將值通過mysqli->real_escape_string() 進行轉義，並用單引號' 包裹。

 /**
 * 將過濾數組轉換為MySQLi風格的SQL WHERE子句，並對值進行轉義。
 *
 * @param mysqli $mysqli MySQLi連接對象。
 * @param string $tableName 目標表名。
 * @param array $filterArray DevExtreme風格的過濾數組。
 * @return string 完整的SQL查詢字符串。
 */
function arrayToQueryMysqli($mysqli, string $tableName, array $filterArray) : string
{
    // 確保表名被反引號包圍$select = "SELECT * FROM `{$tableName}` WHERE ";
    foreach($filterArray as $item) {
        if(is_array($item)) {
            // 條件數組：[字段, 運算符, 值]
            // 字段名用反引號包圍，值通過real_escape_string 轉義後用單引號包圍$escapedValue = $mysqli->real_escape_string($item[2]);
            $select .= "`{$item[0]}` {$item[1]} '{$escapedValue}'";
        } else {
            // 邏輯運算符$select .= " {$item} ";
        }
    }
    return $select;
}

3.2 MySQLi查詢示例

// 示例數據$filterArray = [
    ["SizeCd","=","UNIT"],
    "or",
    ["SizeCd","=","JOGO"],
    "or",
    ["SizeCd","=","PACOTE"]
];

// 替換為您的實際MySQLi連接設置// $mysqli = new mysqli("localhost", "your_username", "your_password", "your_database");
// if ($mysqli->connect_errno) {
// die("MySQLi 連接失敗: " . $mysqli->connect_error);
// }
$mysqli = null; // 佔位符，請替換為您的實際MySQLi連接$tableName = "tablename"; // 替換為您的實際表名// 生成SQL查詢字符串if ($mysqli) {
    $query = arrayToQueryMysqli($mysqli, $tableName, $filterArray);
    echo "生成的SQL查詢: " . $query . "\n";

    // 執行查詢$result = $mysqli->query($query);

    if ($result) {
        echo "查詢成功，獲取到" . $result->num_rows . " 條記錄。\n";
        // 示例：打印第一行數據// if ($row = $result->fetch_assoc()) {
        // print_r($row);
        // }
        $result->free(); // 釋放結果集} else {
        echo "查詢失敗: " . $mysqli->error . "\n";
    }
    $mysqli->close(); // 關閉連接} else {
    echo "請提供有效的MySQLi連接對象。\n";
}

輸出示例(不含實際查詢結果):

生成的SQL查詢: SELECT * FROM `tablename` WHERE `SizeCd` = 'UNIT' or `SizeCd` = 'JOGO' or `SizeCd` = 'PACOTE'

注意事項：

• 儘管mysqli->real_escape_string() 可以防止大部分SQL注入，但強烈推薦使用MySQLi的預處理語句(prepare/bind_param)來處理參數，因為它比手動轉義更安全、更不易出錯。
• 本示例中的arrayToQueryMysqli 函數直接將轉義後的值拼接到SQL字符串中，這不如預處理語句靈活和安全。在實際生產環境中，如果使用MySQLi，應優先考慮其預處理語句功能。

4. 總結與最佳實踐

本文詳細介紹瞭如何將DevExtreme等前端框架生成的過濾數組轉換為MySQL的WHERE 子句。

• PDO是首選：使用PDO的預處理語句和參數綁定是構建動態SQL查詢的最安全、最推薦的方式，它能有效防止SQL注入。
• MySQLi的替代方案：如果必須使用MySQLi且不使用其預處理語句，務必使用mysqli->real_escape_string() 對所有外部輸入的值進行轉義。但最佳實踐仍然是使用MySQLi的預處理語句。
• 字段與表名處理：始終使用反引號(`) 包裹字段名和表名，以避免與SQL保留字衝突，並提高代碼的健壯性。
• 靈活性與擴展性：當前的解決方案處理了簡單的AND/OR 邏輯和基本操作符。對於更複雜的嵌套條件（例如(A AND B) OR C）或更多操作符（LIKE, IN, BETWEEN 等），可能需要更複雜的遞歸解析邏輯。

通過這些方法，您可以安全高效地將前端的過濾邏輯無縫集成到後端數據庫查詢中，提升應用的交互性和數據處理能力。

以上是將DevExtreme過濾器轉換為MySQL WHERE子句的PHP教程的詳細內容。更多資訊請關注PHP中文網其他相關文章！