Dynamic SQL select multiple columns based on json field(s) without exposing SQL Injection risks

Question

I'm creating a 'select colA, colB, colC, ...' stored proc, and I want the choice of columns to return to be based on JSON input.

The most basic way to achieve that is

DECLARE @jsonField nvarchar(max) = 'col1, col2, col3' --will get this from input json
DECLARE @sql nvarchar(max) = N'Select '+@jsonField +'from tblFoo'
exec sys.sp_executesql @sql

But doing the above would create way too large a security hole for SQL Injection. The safe way that I can figure out is:

DECLARE @jsonUseField1 bit = 0
DECLARE @jsonUseField2 bit = 1--again would be getting this from json in the actual proc
--would also have to declare jsonField3, 4, 5, ...
DECLARE @sql nvarchar(max) = N'Select idFieldName'
if(@jsonUseField1 = 1)
begin
    set @sql = @sql + ', field1Name'
end
if(@jsonUseField2 = 1)
begin
    set @sql = @sql + ', field2Name'
end
set @sql = @sql +'from tblFoo'
exec sys.sp_executesql @sql

The downside to the above method is that I'd have to add 5 extra lines to the proc per field I want to be queryable. This would make the stored proc long and hard to maintain. I'd also have to add a new field to the class that forms the json every time I want a new field.

Is there a way to achieve this behaviour without having to explicitly code out each queryable field, while still protecting against Injection? For example, is there a way to alter the first example to split the json field by a delimiter of some sort, and then rejoin after applying QUOTENAME() to each segment?

I'm using SQL Server 2019

I thought I'd share the solution I ended up running with incorporating Gareth's answer and Charlie's comment, and adding an explicit table reference (because I do joins and other things in the dynamic SQL) in case it is helpful to anyone.

DECLARE @ColumnNames NVARCHAR(MAX) = 
    (
        SELECT  STRING_AGG('tblFooTable.'+QUOTENAME(c.name), ',')
        FROM    sys.columns AS c
        WHERE   object_id =  OBJECT_ID(N'dbo.tblFooTable', 'U')
        AND  c.name IN (SELECT TRIM(value) FROM OPENJSON(@jsonData, '$.footblColumnNames'))
    );
DECLARE @sql2 nvarchar(max) = N'Select '+@ColumnNames

Answer 1

You can validate the columns being passed by checking that they are valid column names, e.g.

DECLARE @ColumnNames NVARCHAR(MAX) = 
    (
        SELECT  STRING_AGG(QUOTENAME(c.name), ',')
        FROM    sys.columns AS c
        WHERE   object_id =  OBJECT_ID(N'dbo.Foo', 'U')
        AND     EXISTS
                (   SELECT  1
                    FROM    STRING_SPLIT(@jsonField, ',') AS ss
                    WHERE   TRIM(ss.value) = c.name
                )
            );

This splits your string into the individual column names using STRING_SPLIT(), then uses this to filter the list of actual column names in the table (from sys.columns), then rebuilds a single string using STRING_AGG()

Anything parsed that doesn't correlate to a valid column name, won't be used, and all columns names that do exist will be correctly escaped with QUOTENAME().

You can then use this new variable in your dynamic statement, and be sure it only contains valid column names.

DECLARE @sql nvarchar(max) = N'SELECT '+@ColumnNames +' FROM tblFoo';

Working Demo on SQL Fiddle