主页 / dba / 问题 / 299362
Accepted
Panzercrisis
Asked: 2021-09-10 14:16:21 +0800 CST

在 SQL Server 中，有没有一种方法可以在不使用动态 SQL 的情况下以未知数列为中心？

  • 772

基本上，如果我们有这个：

create table #temp ([key] nchar, [value] nvarchar(2));
insert #temp ([key], [value]) values
             ('a',   '1'),
             ('b',   '2'),
             ...
             ('z',   '26')

目标是得到一个看起来像这样的字符串：

{"a":"1","b":"2",..."z":"26"}

这里的问题是列的数量和名称都是未知的。现在，有一些方法可以使用PIVOT动态 SQL 来做到这一点，但一般来说，如果您只是将动态 SQL 与一堆随机数据一起使用，那么它可能很容易被注入。在 Stack Overflow 和这个网站上，我看到一些问题的答案使用了这种组合，但他们要么没有提到注射，要么声称它对注射免疫，这让我有点不安。

那么......有没有办法在 SQL Server 中没有动态查询的情况下做到这一点？SQL 注入并不总是立竿见影的。有时注入的代码会在表中处于休眠状态，等待在动态 SQL 查询中使用。在不使用动态 SQL 的情况下如何做到这一点- 或者如果做不到这一点，至少有一种真正不易受到注入攻击的方法吗？

针对评论的两点说明：

#1：至于是否PIVOT使用，就我个人而言，我真正想做的就是切换行和列并从中创建一个 JSON 对象。我有一个看起来有点类似于上面的表，至少在一种情况下，它被用来存储 C# 字典中的键和值。但是，在使用时JsonConvert.DeserializeObject，我需要它看起来像这样：

{
    "Key1": "Value1",
    "Key2": "Value2",
    ...
}

不是这样的：

[{
    "Key": "Key1",
    "Value": "Value1",
}, {
    "Key": "Key2",
    "Value": "Value2"
}, {
    ...
}]

出于某些原因，如果可能的话，我希望在 SQL 中进行这种转换。

#2：SQL注入的问题是：当你连接值时，如果其中一个值中有一个whatever'; SELECT * FROM SomeOtherTable; --类型的脚本，即使最初插入时脚本没有运行（因为很好地使用了SQL参数和类似的预防措施），如果它只是盲目地连接到动态 SQL 查询中，它仍然可以在以后运行。

如果不能完全防止这种情况，我将不得不改变主意并在 C# 中处理这个问题。我真的不能假设数据中没有像那样疯狂的东西，所以我不愿意将它连接到正在运行的东西中，除非有一种真正安全的方法（不仅仅是逃避滴答声）这样做。

sql-server pivot

1 个回答

  1. Best Answer

    如果您只想要一个字符串，则不需要PIVOT，也不需要动态确定列名。假设 SQL Server 2017 或更高版本：

    ;WITH x(y) AS 
(
  SELECT QUOTENAME([key], '"') + ':' + QUOTENAME([value], '"')
  FROM #temp
)
SELECT output = N'{' + STRING_AGG(y, ',') + '}' FROM x;

    问题中定义的列最多限制为 2 个字符，但如果您可能有更多，正如 Charlieface 指出的那样，QUOTENAME()限制为 128 个字符。在这种情况下，您需要更安全一些的东西，再次假设 SQL Server 2017 或更高版本：

    ;WITH x(y) AS 
(
  SELECT '"' + STRING_ESCAPE([key],   'json') + '":"' 
             + STRING_ESCAPE([value], 'json') + '"'
  FROM #temp
)
SELECT output = N'{' + STRING_AGG(y, ',') + '}' FROM x;

    如果您使用的是旧版本的 SQL Server，则可以使用效率较低且更麻烦的方法FOR XML PATH（我将转义不安全的 JSON 字符REPLACE作为读者练习）：

    ;WITH x(y) AS 
(
  SELECT ',' + QUOTENAME([key], '"') + ':' + QUOTENAME([value], '"')
  FROM #temp
)
SELECT output = N'{' + STUFF(
    (SELECT y FROM x FOR XML PATH, TYPE).value
    (N'.[1]', N'nvarchar(max)'), 1, 1, '') + '}';

    至于保护自己免受 SQL 注入，我写了几篇文章：

    • 4

相关问题