PHP实现DevExtreme过滤条件到MySQL WHERE子句的转换

本文详细介绍了如何使用PHP将DevExtreme前端框架生成的类NoSQL过滤条件数组，安全有效地转换为MySQL数据库的WHERE子句。文章提供了针对PDO和MySQLi两种数据库扩展的实现方案，包括生成带参数占位符的SQL语句和提取对应参数值的函数，旨在帮助开发者构建健壮的数据查询接口。

在现代Web应用开发中，前端框架如DevExtreme常以一种结构化的数组形式定义数据过滤条件，这种格式类似于NoSQL查询语法，但后端数据库通常是关系型数据库如MySQL。将前端的过滤条件动态转换为后端可执行的SQL WHERE 子句，是构建灵活数据接口的关键一环。

考虑以下MySQL表结构：

item_id	StockNo	SizeCd
1	12003	UNIT
2	12007	JOGO
3	12008	PACOTE
4	12033	JOGO
5	12034	JOGO
6	12038	UNIT

前端DevExtreme框架可能发送如下的过滤请求体：

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

其中，filter 字段是一个数组，它定义了查询的条件。我们的目标是将这个数组转换为符合MySQL语法的 WHERE 子句，例如：WHERESizeCd= 'UNIT' ORSizeCd= 'JOGO'。在转换过程中，需要特别注意字段名不加引号，而字符串值需要加引号，并确保防止SQL注入。

使用PDO进行转换

使用PDO（PHP Data Objects）是PHP连接数据库的推荐方式，因为它支持预处理语句，能够有效防止SQL注入攻击。我们需要编写两个辅助函数：一个用于生成带有参数占位符的SQL查询字符串，另一个用于从过滤数组中提取这些参数的值。

假设我们有以下DevExtreme风格的过滤数组：

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

1. 生成带参数占位符的SQL查询字符串

arrayToQuery 函数负责遍历过滤数组，构建一个包含问号占位符（?）的SQL WHERE 子句。

/**
 * 将DevExtreme风格的过滤数组转换为带占位符的SQL WHERE子句。
 *
 * @param string $tableName 表名。
 * @param array $filterArray 过滤条件数组。
 * @return string 生成的SQL查询字符串。
 */
function arrayToQuery(string $tableName, array $filterArray) : string 
{
    $select = "SELECT * FROM `{$tableName}` WHERE ";

    foreach($filterArray as $item) {
        if(is_array($item)) {
            // 处理形如 ["SizeCd","=","UNIT"] 的条件
            // 字段名用反引号括起来，防止与SQL关键字冲突
            $select .= "`{$item[0]}` {$item[1]} ?"; 
        } else {
            // 处理形如 "or" 的逻辑运算符
            $select .= " {$item} ";
        }
    }

    return $select;
}

2. 提取参数值数组

arrayToParams 函数用于从过滤数组中提取所有需要绑定到SQL查询中的值。这些值将作为预处理语句的参数。

/**
 * 从DevExtreme风格的过滤数组中提取所有参数值。
 *
 * @param array $filterArray 过滤条件数组。
 * @return array 提取出的参数值数组。
 */
function arrayToParams(array $filterArray) : array  
{
    $return = [];
    foreach($filterArray as $item) {
        if(is_array($item)) {
            // 提取条件数组中的第三个元素作为参数值
            $return[] = $item[2];
        }
    }
    return $return;
}

PDO使用示例

结合这两个函数，我们可以构建并执行安全的PDO查询：

// 假设的过滤数组
$filterArray = [["SizeCd","=","UNIT"],"or",["SizeCd","=","JOGO"],"or",["SizeCd","=","PACOTE"]];

// 示例输出
var_dump(
    arrayToQuery("your_table_name", $filterArray),
    arrayToParams($filterArray)
);

/*
输出结果:
string(66) "SELECT * FROM `your_table_name` WHERE `SizeCd` = ? or `SizeCd` = ? or `SizeCd` = ?"
array(3) {
  [0]=>
  string(4) "UNIT"
  [1]=>
  string(4) "JOGO"
  [2]=>
  string(6) "PACOTE"
}
*/

// 实际PDO数据库操作
try {
    // 假设 $conn 是一个已建立的PDO连接对象
    $dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8';
    $username = 'root';
    $password = 'password';
    $conn = new PDO($dsn, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $tableName = "your_table_name"; // 替换为你的表名
    $sql = arrayToQuery($tableName, $filterArray);
    $params = arrayToParams($filterArray);

    $stmt = $conn->prepare($sql);
    $stmt->execute($params);

    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($results);

} catch (PDOException $e) {
    echo "数据库错误: " . $e->getMessage();
}

使用MySQLi进行转换（非预处理方式）

如果项目仍在使用MySQLi扩展且不方便使用预处理语句（尽管强烈推荐使用），则需要在构建SQL字符串时手动对值进行转义，以防止SQL注入。

/**
 * 将DevExtreme风格的过滤数组转换为MySQLi风格的SQL WHERE子句。
 * 注意：此方法直接将值嵌入SQL，需使用mysqli_real_escape_string进行转义以防SQL注入。
 * 
 * @param mysqli $mysqli MySQLi连接对象。
 * @param string $table 表名。
 * @param array $filterArray 过滤条件数组。
 * @return string 生成的SQL查询字符串。
 */
function arrayToQueryMysqli($mysqli, string $table, array $filterArray) : string 
{
    $select = "SELECT * FROM `{$table}` WHERE ";
    foreach($filterArray as $item) {
        if(is_array($item)) {
            // 对值进行转义并用单引号括起来
            $escapedValue = $mysqli->real_escape_string($item[2]);
            $select .= "`{$item[0]}` {$item[1]} '" . $escapedValue . "'";
        } else {
            $select .= " {$item} ";
        }
    }
    return $select;
}

// MySQLi使用示例
// 假设 $mysqli 是一个已建立的MySQLi连接对象
$mysqli = new mysqli("localhost", "root", "password", "testdb");

if ($mysqli->connect_error) {
    die("连接失败: " . $mysqli->connect_error);
}

$filterArray = [["SizeCd","=","UNIT"],"or",["SizeCd","=","JOGO"],"or",["SizeCd","=","PACOTE"]];
$tableName = "your_table_name"; // 替换为你的表名

$query = arrayToQueryMysqli($mysqli, $tableName, $filterArray);
echo "生成的SQL: " . $query . "\n";

$result = $mysqli->query($query);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        print_r($row);
    }
    $result->free();
} else {
    echo "查询失败: " . $mysqli->error;
}

$mysqli->close();

注意事项与总结

1. 安全性：
   • 强烈推荐使用PDO的预处理语句。它通过将SQL逻辑与数据分离，是防御SQL注入最有效的方法。arrayToQuery 和 arrayToParams 组合正是为PDO预处理设计的。
   • 如果必须使用MySQLi且不采用预处理，务必使用 mysqli_real_escape_string() 函数对所有用户输入的值进行转义，以防止恶意字符破坏SQL语句结构。
2. 字段名引用：在生成的SQL中，字段名（如 SizeCd）使用反引号（`）括起来。这是一种良好的实践，可以避免字段名与SQL关键字冲突。
3. 灵活性与扩展性：
   • 当前的实现适用于简单的 AND/OR 条件链。对于更复杂的嵌套过滤（例如 (A AND B) OR C），可能需要更高级的解析逻辑，例如递归函数来处理嵌套数组。
   • 如果需要支持更多的运算符（如 LIKE, IN, BETWEEN），则需要扩展 arrayToQuery 函数的逻辑来识别并正确处理这些运算符及其对应的SQL语法。
4. 错误处理：在实际应用中，应始终包含适当的错误处理机制，例如 try-catch 块处理PDO异常，或检查MySQLi查询结果。
5. 性能：对于非常复杂的过滤条件，生成SQL字符串可能会有轻微的性能开销。但对于大多数应用场景，这种开销可以忽略不计。

通过上述PHP函数，开发者可以高效且安全地将DevExtreme等前端框架的类NoSQL过滤条件转换为MySQL的 WHERE 子句，从而实现前后端数据交互的无缝对接。

以上是PHP实现DevExtreme过滤条件到MySQL WHERE子句的转换的详细内容。更多信息请关注PHP中文网其他相关文章！