1

Essentially if we have this:

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

the goal would be to get a string back that looks like this:

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

The problem here is that the count - and names - of the columns are both unknown. Now, there are ways to do this using PIVOT with dynamic SQL, but in general, dynamic SQL can be prone to injection if you're just using it with a bunch of random data. On Stack Overflow and on this site, I see some questions with answers posted that use this combination, but they either don't mention injection or make a claim that it's immune to injection, which I'm a little uneasy about.

So...is there any way to do this without a dynamic query in SQL Server? SQL injection isn't always immediate; sometimes the injected code sits dormant inside of tables, waiting to be used in dynamic SQL queries. How can you do this without using dynamic SQL - or failing that, is there at least truly a way that's not vulnerable to injection?


Two notes, in response to the comments:

#1: As far as whether PIVOT is used or not goes, all I'm really wanting to do, in my personal case, is to switch rows and columns and create a JSON object out of it. I have a table that looks somewhat similar to the above, which is being used, at least in one case, to store keys and values out of a C# dictionary. However when using JsonConvert.DeserializeObject, I need it to look like this:

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

not like this:

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

For certain reasons, I want this transformation to be made in SQL, if possible.

#2: The concern with SQL injection is this: When you concatenate values, if one of the values has a whatever'; SELECT * FROM SomeOtherTable; -- type of script in it, even if the script wasn't run when it was originally inserted (because of good use of SQL parameters and precautions like that), it can still be run later if it's just concatenated blindly into a dynamic SQL query.

I will have to change my mind and just deal with this in C#, if that can't be perfectly guarded against. I can't really assume the data doesn't have something crazy like that in there, so I'm not comfortable concatenating it into something that's run, unless there's a truly safe way (more than just escaping ticks) to do so.

0

1 Answer 1

4

If you just want a single string back, you don't need PIVOT, and you don't need to dynamically determine column names. Assuming SQL Server 2017 or better:

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

The columns defined in the question were limited to a max of 2 characters, but if you might have more, as Charlieface pointed out, QUOTENAME() is limited to 128 characters. In that case you'll want something a little safer, again assuming SQL Server 2017 or better:

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

If you're on an older version of SQL Server, you can use the less efficient and more cumbersome FOR XML PATH (and I'll leave escaping unsafe JSON characters using REPLACE as an exercise for the reader):

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

As for protecting yourself from SQL injection, I wrote a couple of posts on that:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.