EF Core 8原生SQL查询参数未注入问题求助

问题描述 #

使用EF Core 8构建原生SQL查询时，参数未被正确替换。通过FormattableStringFactory创建带参数的查询，调试时参数值正确，手动执行查询可正常运行，但控制台显示执行命令时参数值以?占位，查询执行失败。

原代码示例 #

private static FormattableString GetCardsBaseQuery(CardListFilter filter)
{
    var rows = filter!.RowsPerPage ?? 10;

    int offset = filter.Page ?? 1 - 1;
    offset = offset < 0 ?
              rows :
              rows * offset;

    var orderByParameter = new SqlParameter { ParameterName = "orderBy", SqlDbType = SqlDbType.NVarChar, Value = filter.SortBy };
    var orderByDirectionParameter = new SqlParameter { ParameterName = "orderDirection", SqlDbType = SqlDbType.NVarChar, Value = filter.SortDirection };
    var offsetParametar = new SqlParameter { ParameterName = "offset", SqlDbType = SqlDbType.Int, Value = offset };
    var rawParameter = new SqlParameter { ParameterName = "rows", SqlDbType = SqlDbType.Int, Value = rows };

    //! if you modify this query then you should change the CardListModel(reader) constructor as well
    var query = FormattableStringFactory.Create(@"
        SELECT
            c.BatchId,
            c.LuckyId,
            c.RefId,
            SUBSTRING(c.RefId, 6, 16) as [RawRefId],
            (c.FirstName + ' ' + c.LastName) as [Name],
            c.Email,
            c.MobileNumber,
            c.HasMarketingPermisson,
            c.Status,
            c.PlayedAt,
            c.BigPrize,
            c.DipPrize,
            c.DrawPrize,
            c.BigPrizePaymentStatus,
            c.BonusDrawStatus
        FROM
            [Card] c
        ORDER BY {0} {1}
        OFFSET {2} ROWS
        FETCH NEXT {3} ROWS ONLY", orderByParameter.Value, orderByDirectionParameter.Value, offsetParametar.Value, rawParameter.Value);

    return query;
}

public async Task<CardListModel> GetCardsByFilterAsync(CardListFilter filter)
{
    var basequery = GetCardsBaseQuery(filter);
    var queryable = context.Database.SqlQuery<CardListModel>(basequery);

    var result = await queryable.ToListAsync();
    
    return result ;
}

错误信息（中文翻译） #

错误: 执行DbCommand失败(26ms) [参数=[p0='?' (大小=4000), p1='?' (DbType=Int32), p2='?' (DbType=Int32), p3='?' (DbType=Int32)], 命令类型='文本', 命令超时='300']
                        SELECT
                                c.BatchId,
                                c.LuckyId,
                                c.GilRefId,
                                SUBSTRING(c.GilRefId, 6, 16) as [RawGilRefId],
                                (c.FirstName + ' ' + c.LastName) as [Name],
                                c.Email,
                                c.MobileNumber,
                                c.HasMarketingPermisson,
                                c.Status,
                                c.PlayedAt,
                                c.BigPrize,
                                c.LuckyDipPrize,
                                c.BonusDrawPrize,
                                c.BigPrizePaymentStatus,
                                c.BonusDrawStatus
                        FROM
                                [Card] c
                        ORDER BY @p0 @p1
                        OFFSET @p2 ROWS
                        FETCH NEXT @p3 ROWS ONLY
失败: 02/03/2024 09:39:23.574 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command)

解决方案 #

1. 核心问题分析 #

SQL Server不支持将ORDER BY后的列名、排序方向作为参数传递，EF Core会将其视为普通字符串参数，导致数据库无法识别为合法的排序字段，进而引发执行错误。日志中的?是EF Core的参数占位符，并非参数值缺失，但核心矛盾是排序逻辑不能用参数化实现。

2. 修复后的代码实现 #

private static FormattableString GetCardsBaseQuery(CardListFilter filter)
{
    var rows = filter!.RowsPerPage ?? 10;

    // 修复原代码的括号逻辑错误：先处理默认页码再减1
    int offset = (filter.Page ?? 1) - 1;
    // offset小于0时设为0，避免负偏移量
    offset = offset < 0 ? 0 : rows * offset;

    // 排序字段白名单校验，仅允许指定列名，防止SQL注入
    var allowedSortColumns = new HashSet<string> 
    { 
        "BatchId", "LuckyId", "RefId", "Name", "Email", "MobileNumber", 
        "Status", "PlayedAt", "BigPrize" 
    };
    var sortBy = allowedSortColumns.Contains(filter.SortBy) ? filter.SortBy : "BatchId";

    // 排序方向校验，仅允许ASC/DESC
    var sortDirection = string.Equals(filter.SortDirection, "DESC", StringComparison.OrdinalIgnoreCase) 
        ? "DESC" 
        : "ASC";

    // 分页参数保持参数化，排序逻辑直接拼接合法值
    var query = FormattableStringFactory.Create(@"
        SELECT
            c.BatchId,
            c.LuckyId,
            c.RefId,
            SUBSTRING(c.RefId, 6, 16) as [RawRefId],
            (c.FirstName + ' ' + c.LastName) as [Name],
            c.Email,
            c.MobileNumber,
            c.HasMarketingPermisson,
            c.Status,
            c.PlayedAt,
            c.BigPrize,
            c.DipPrize,
            c.DrawPrize,
            c.BigPrizePaymentStatus,
            c.BonusDrawStatus
        FROM
            [Card] c
        ORDER BY {0} {1}
        OFFSET {2} ROWS
        FETCH NEXT {3} ROWS ONLY", sortBy, sortDirection, offset, rows);

    return query;
}

public async Task<CardListModel> GetCardsByFilterAsync(CardListFilter filter)
{
    var basequery = GetCardsBaseQuery(filter);
    var queryable = context.Database.SqlQuery<CardListModel>(basequery);

    var result = await queryable.ToListAsync();
    
    // 假设CardListModel包含Cards集合属性，需根据实际类型调整返回逻辑
    return new CardListModel { Cards = result };
}

3. 关键修复点 #

• 修正了offset计算的逻辑错误，确保页码偏移量计算正确
• 增加排序字段和方向的白名单校验，彻底避免SQL注入风险
• 排序逻辑直接拼接合法值，分页参数保持参数化，符合SQL Server语法要求

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