c# - JSON_Value 错误：“JSON_VALUE 或 JSON_QUERY”的参数 2 必须是字符串文字

Question

我正在尝试使用 JSON_VALUE 和 SQL Server 2016。不过，我必须动态构建我的 SQL 查询。

JSON：

{"pageId":"9","moduleId":"6","moduleType":"Pages"}

这是我的错误：

“JSON_VALUE 或 JSON_QUERY”的参数 2 必须是字符串文字。

            var sqlParams = new List<SqlParameter>();

            StringBuilder sb = new StringBuilder();

            // start the initial select query...
            sb.Append("SELECT * FROM dbo.FileSystemItems WHERE ");

            int counter = 0;
            foreach (var item in metadata)
            {
                // only add an AND if we are NOT the first record...
                if (counter != 0)
                {
                    sb.Append(" AND ");
                }

                // setup our json path and value items...
                string pathParam = string.Format(CultureInfo.CurrentCulture, "jsonPathParam{0}", counter);
                string valueParam = string.Format(CultureInfo.CurrentCulture, "jsonPathValue{0}", counter);

                sb.AppendFormat(CultureInfo.CurrentCulture, "JSON_VALUE(FileMetadata, @{0}) = @{1}", pathParam, valueParam);

                // add in our parameters to assist with sql injection
                sqlParams.Add(new SqlParameter(pathParam, string.Format(CultureInfo.CurrentCulture, "N'$.{0}'", item.Key)));
                sqlParams.Add(new SqlParameter(valueParam, string.Format(CultureInfo.CurrentCulture, "N'{0}'", item.Value)));

                counter++;
            }

            return await BIContext.FileSystemItems
                          .Where(x => x.ModuleId == moduleId)
                          .FromSql(sb.ToString(), sqlParams.ToArray())
                          .Select(s => new FileSystemItemJsonDTO
                          {
                              FileId = s.FileId,
                              FileName = s.FileName,
                              FileType = s.FileType,
                              LastWriteTime = s.LastWriteTime,
                              FileSystemItemDataId = s.FileSystemItemDataId,
                              ModuleId = moduleId,
                              FileMetadata = s.FileMetadata,
                              FileSize = s.FileSize
                          })
                          .ToListAsync().ConfigureAwait(false);

这是作为 SQL 查询生成的 StringBuilder 结果

SELECT * FROM dbo.FileSystemItems WHERE JSON_VALUE(FileMetadata, @jsonPathParam0) = @jsonPathValue0 AND JSON_VALUE(FileMetadata, @jsonPathParam1) = @jsonPathValue1

我已经尝试过这篇文章中的项目：

JSON_VALUE 不采用动态 JSON 路径

但它仍然给我同样的错误。

我究竟做错了什么？

我需要支持 2016 年和 2017 年。

更新：

我也发现了这个：https ://dapper-tutorial.net/knowledge-base/46860751/csharp-dapper-using-json-value-for-sql-server-2016 ->

这说不可能？

然后我发现了这个：https ://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15

它说我的版本受支持：

Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64) 2020 年 10 月 31 日 02:43:57 版权所有 (c) Windows 10 Enterprise 10.0 (Build 19041) 上的 Microsoft Corporation Developer Edition（64 位） :)

更新 2：

在下面的响应之后，我找到了适用于 SQL server 2016 和 SQL server 2017 的代码。

我只是想了解 IF/为什么它可以防止 SQL 注入，因为我没有参数化JSON_VALUE()

        public async Task<IList<FileSystemItemJsonDTO>> GetFileSystemItems(int moduleId, IDictionary<string, string> metadata)
        {
            var sqlParams = new List<SqlParameter>();

            StringBuilder sb = new StringBuilder();

            // start the initial select query...
            sb.Append("SELECT * FROM dbo.FileSystemItems WHERE ");

            int counter = 0;
            foreach (var item in metadata)
            {
                // only add an AND if we are NOT the first record...
                if (counter != 0)
                {
                    sb.Append(" AND ");
                }

                // setup our json path and value items...
                string valueParam = string.Format(CultureInfo.CurrentCulture, "jsonPathValue{0}", counter);

                // 2nd item for JSON_VALUE has to be string literal for SQL server 2016
                sb.AppendFormat(CultureInfo.CurrentCulture, "JSON_VALUE(FileMetadata, '$.{0}') = @{1}", item.Key, valueParam);

                // add in our parameters to assist with sql injection
                sqlParams.Add(new SqlParameter(valueParam, string.Format(CultureInfo.CurrentCulture, "{0}", item.Value)));

                counter++;
            }

            return await BIContext.FileSystemItems
                          .Where(x => x.ModuleId == moduleId)
                          .FromSql(sb.ToString(), sqlParams.ToArray())
                          .Select(s => new FileSystemItemJsonDTO
                          {
                              FileId = s.FileId,
                              FileName = s.FileName,
                              FileType = s.FileType,
                              LastWriteTime = s.LastWriteTime,
                              FileSystemItemDataId = s.FileSystemItemDataId,
                              ModuleId = moduleId,
                              FileMetadata = s.FileMetadata,
                              FileSize = s.FileSize
                          })
                          .ToListAsync().ConfigureAwait(false);
        }

标签： c#jsonsql-server-2016

Answer 1

正如@user2864740所提到的，我们可以使用表值参数和OPENJSON.

首先创建一个表类型，我建议你仔细考虑一下确切的列类型和长度：

CREATE TYPE Metadata TABLE (jsonKey nvarchar(100) PRIMARY KEY, jsonValue nvarchar(1000) NOT NULL);

查询使用关系除法：

SELECT *    -- Preferably don't use select *, specify necessary columns instead
FROM dbo.FileSystemItems
WHERE moduleId = @moduleId
AND NOT EXISTS (
    SELECT m.jsonKey, m.jsonValue
    FROM @metadata m
    EXCEPT
    SELECT j.[key], j.[value]
    FROM OPENJSON(FileMetadata) j
);

这基本上是说：不能有任何没有匹配键/值对的元数据要求。

另一个，有时更有效的版本，同样的事情：

AND EXISTS (SELECT 1
    FROM @metadata m
    LEFT JOIN OPENJSON(FileMetadata) j
        ON j.[key] = m.jsonKey AND j.[value] = m.jsonValue
    HAVING COUNT(j.[Key]) = COUNT(*)
);

这就是说：如果我们将所有具有匹配键/值的元数据相加，它们必须与所有元数据的数量相同。

如果你想要一个OR语义，一个简单的连接就足够了：

AND EXISTS (SELECT 1
    FROM @metadata m
    JOIN OPENJSON(FileMetadata) j
        ON j.[key] = m.jsonKey AND j.[value] = m.jsonValue
);

---

现在让我们在 C# 中查询它。在正确的位置添加上面的查询。

我倾向于将 TVP 参数构造变成扩展方法，我将把它留给你。

public async Task<IList<FileSystemItemJsonDTO>> GetFileSystemItems(int moduleId, IDictionary<string, string> metadata)
{
    var table = new DataTable();
    table.Columns.Add("jsonKey", typeof(string));
    table.Columns.Add("jsonValue", typeof(string));
    foreach (var item in metadata)
        table.Add(item.Key, item.Value);

    var params = new[] {
        new SqlParameter("@metadata", SqlDbType.Structured)
        {
            Direction = ParameterDirection.Input,
            TypeName = "dbo.Metadata",
            Value = table
        },
        new SqlParameter("@moduleId", SqlDbType.Int){Value = moduleId}
    };
    const string query = @"
SELECT ...
";
    return await BIContext.FileSystemItems
                          .FromSql(MyQuery, params)
                          .Select(s => new FileSystemItemJsonDTO
                          {
                              FileId = s.FileId,
                              FileName = s.FileName,
                              FileType = s.FileType,
                              LastWriteTime = s.LastWriteTime,
                              FileSystemItemDataId = s.FileSystemItemDataId,
                              ModuleId = moduleId,
                              FileMetadata = s.FileMetadata,
                              FileSize = s.FileSize
                          })
                          .ToListAsync().ConfigureAwait(false);
}