如何在C#中动态构建带OData风格条件的Raw SQL WHERE子句

要解决这个问题，核心是安全解析OData过滤表达式并转换为参数化的SQL WHERE子句，同时支持嵌套逻辑组合，绝对不能直接字符串拼接值（防止SQL注入）。下面是分步实现方案：

1. 核心思路 #

• 建立OData操作符与SQL操作符的映射关系
• 递归解析嵌套的过滤表达式（处理括号、and/or/not逻辑）
• 所有过滤值使用参数化占位符，避免注入风险
• 最终将解析后的SQL片段整合到基础查询语句中，通过SqlDataReader读取结果

2. 代码实现 #

辅助类：存储解析结果 #

用于保存转换后的SQL片段和对应的参数列表：

public class SqlFilterResult
{
    public string SqlFragment { get; set; }
    public List<SqlParameter> Parameters { get; set; } = new List<SqlParameter>();
}

操作符映射 #

定义OData比较操作符到SQL的对应关系：

private static readonly Dictionary<string, string> _operatorMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
{
    { "eq", "=" },
    { "ne", "<>" },
    { "gt", ">" },
    { "ge", ">=" },
    { "lt", "<" },
    { "le", "<=" }
};

递归解析OData过滤表达式 #

处理嵌套逻辑和比较条件，生成参数化SQL片段：

public static SqlFilterResult ParseODataFilter(string odataFilter)
{
    var result = new SqlFilterResult();
    if (string.IsNullOrWhiteSpace(odataFilter))
        return result;

    // 递归处理括号内的子表达式
    var bracketRegex = new Regex(@"\(([^()]+)\)");
    var bracketMatches = bracketRegex.Matches(odataFilter);
    foreach (Match match in bracketMatches)
    {
        var subFilter = match.Groups[1].Value;
        var subResult = ParseODataFilter(subFilter);
        var placeholder = $"@sub{result.Parameters.Count}";
        // 暂时替换括号内容，后续替换回解析后的SQL
        odataFilter = odataFilter.Replace(match.Value, placeholder);
        result.SqlFragment += $" {subResult.SqlFragment} ";
        result.Parameters.AddRange(subResult.Parameters);
    }

    // 替换逻辑操作符为SQL关键字
    odataFilter = odataFilter.Replace("and", "AND", StringComparison.OrdinalIgnoreCase)
                             .Replace("or", "OR", StringComparison.OrdinalIgnoreCase)
                             .Replace("not", "NOT", StringComparison.OrdinalIgnoreCase);

    // 处理比较操作符，生成参数化条件
    foreach (var op in _operatorMap)
    {
        var opRegex = new Regex($@"\b{op.Key}\b", RegexOptions.IgnoreCase);
        odataFilter = opRegex.Replace(odataFilter, match =>
        {
            var fullCondition = match.Value;
            // 拆分字段和值（示例："Name eq 'Alice'" → ["Name ", " 'Alice'"]）
            var parts = fullCondition.Split(new[] { op.Key }, StringSplitOptions.RemoveEmptyEntries);
            var fieldName = parts[0].Trim();
            var rawValue = parts[1].Trim().Trim('\''); // 移除字符串引号

            // 创建参数，避免SQL注入
            var paramName = $"@p{result.Parameters.Count}";
            // 这里需要根据字段实际类型调整参数类型，示例默认用字符串，实际要扩展
            result.Parameters.Add(new SqlParameter(paramName, rawValue));

            return $"{fieldName} {op.Value} {paramName}";
        });
    }

    // 替换之前的子表达式占位符为实际SQL片段
    result.SqlFragment = odataFilter;
    return result;
}

整合到查询逻辑 #

将解析后的过滤条件加入基础SQL，执行查询并读取结果：

public static IEnumerable<User> GetUsers(string odataFilter)
{
    var filterResult = ParseODataFilter(odataFilter);
    var baseSql = "SELECT Id, Username, Age, Status FROM Users";
    var fullSql = baseSql;

    // 拼接WHERE子句（如果有过滤条件）
    if (!string.IsNullOrWhiteSpace(filterResult.SqlFragment))
    {
        fullSql += $" WHERE {filterResult.SqlFragment}";
    }

    using (var connection = new SqlConnection("Your_Connection_String"))
    {
        connection.Open();
        using (var command = new SqlCommand(fullSql, connection))
        {
            // 添加所有参数
            command.Parameters.AddRange(filterResult.Parameters.ToArray());
            
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return new User
                    {
                        Id = reader.GetInt32(reader.GetOrdinal("Id")),
                        Username = reader.GetString(reader.GetOrdinal("Username")),
                        Age = reader.GetInt32(reader.GetOrdinal("Age")),
                        Status = reader.GetString(reader.GetOrdinal("Status"))
                    };
                }
            }
        }
    }
}

// 示例实体类
public class User
{
    public int Id { get; set; }
    public string Username { get; set; }
    public int Age { get; set; }
    public string Status { get; set; }
}

3. 关键注意事项 #

• 类型扩展：示例中默认按字符串处理过滤值，实际需要根据字段类型解析（比如数字、日期、布尔值），避免参数类型不匹配导致的SQL错误。
• 严谨解析：如果需要处理复杂的OData表达式（比如函数调用），建议使用Microsoft.OData.Core库解析表达式树，再转换为SQL，比正则解析更可靠。
• 字段校验：要验证解析出的字段是否存在于目标表中，防止攻击者构造无效字段引发异常。
• 注入防护：始终使用参数化查询，绝对不能直接将用户输入的值拼接进SQL字符串。

内容的提问来源于stack exchange，提问作者Dave