DevExtreme过滤器转MySQL WHERE子句PHP教程

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 等），可能需要更复杂的递归解析逻辑。

通过这些方法，您可以安全高效地将前端的过滤逻辑无缝集成到后端数据库查询中，提升应用的交互性和数据处理能力。