Início / dba / Perguntas / 299362
Accepted
Panzercrisis
Asked: 2021-09-10 14:16:21 +0800 CST

No SQL Server, existe uma maneira de dinamizar colunas de número desconhecido SEM usar SQL dinâmico?

  • 772

Essencialmente, se tivermos isso:

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

o objetivo seria obter uma string de volta que se parece com isso:

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

O problema aqui é que a contagem - e os nomes - das colunas são ambos desconhecidos. Agora, existem maneiras de fazer isso usando PIVOTSQL dinâmico, mas em geral, o SQL dinâmico pode ser propenso a injeção se você o estiver usando apenas com um monte de dados aleatórios. No Stack Overflow e neste site, vejo algumas perguntas com respostas postadas que usam essa combinação, mas elas não mencionam a injeção ou afirmam que é imune à injeção, o que me deixa um pouco desconfortável.

Então... existe alguma maneira de fazer isso sem uma consulta dinâmica no SQL Server? A injeção de SQL nem sempre é imediata; às vezes, o código injetado fica inativo dentro de tabelas, esperando para ser usado em consultas SQL dinâmicas. Como você pode fazer isso sem usar SQL dinâmico - ou, falhando nisso, existe pelo menos realmente uma maneira que não seja vulnerável à injeção?

Duas notas, em resposta aos comentários:

#1: No que diz respeito a PIVOTser usado ou não, tudo o que realmente quero fazer, no meu caso pessoal, é alternar linhas e colunas e criar um objeto JSON a partir disso. Eu tenho uma tabela que se parece um pouco com a acima, que está sendo usada, pelo menos em um caso, para armazenar chaves e valores de um dicionário C#. No entanto, ao usar JsonConvert.DeserializeObject, preciso que fique assim:

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

Assim não:

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

Por alguns motivos, quero que essa transformação seja feita em SQL, se possível.

#2: A preocupação com a injeção de SQL é esta: Quando você concatena valores, se um dos valores tiver um whatever'; SELECT * FROM SomeOtherTable; --tipo de script nele, mesmo que o script não tenha sido executado quando foi inserido originalmente (devido ao bom uso dos parâmetros SQL e precauções como essa), ele ainda pode ser executado mais tarde se for apenas concatenado cegamente em uma consulta SQL dinâmica.

Terei que mudar de ideia e lidar com isso em C#, se isso não puder ser perfeitamente protegido. Eu realmente não posso assumir que os dados não têm algo louco assim lá, então não me sinto confortável em concatená-los em algo que é executado, a menos que haja uma maneira verdadeiramente segura (mais do que apenas escapar de ticks) para fazê-lo.

sql-server pivot

1 respostas

  1. Best Answer

    Se você deseja apenas uma única string de volta, não precisa PIVOTe não precisa determinar dinamicamente os nomes das colunas. Supondo o SQL Server 2017 ou melhor:

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

    As colunas definidas na pergunta foram limitadas a no máximo 2 caracteres, mas se você puder ter mais, como Charlieface apontou, QUOTENAME()é limitado a 128 caracteres. Nesse caso, você desejará algo um pouco mais seguro, novamente assumindo o SQL Server 2017 ou melhor:

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

    Se você estiver em uma versão mais antiga do SQL Server, poderá usar a menos eficiente e mais complicada FOR XML PATH(e deixarei escapar caracteres JSON inseguros usando REPLACEcomo exercício para o leitor):

    ;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, '') + '}';

    Quanto a se proteger da injeção de SQL, escrevi alguns posts sobre isso:

    • 4

relate perguntas