Creating a parametrized field name for a SELECT clause

Question

I'm currently working on a data API and I would like to generate an SQL query based on the requested field names. Those field names will be publicly available and the users will also be able to use alias to rename the data returned by the API.

The records available in those tables won't be filed as "top secret" but I still want to prevent any SQL injection by using a parametrized field name and alias to avoid people from adding unwanted data.

DECLARE @requestedFieldName NVARCHAR(max) = 'FirstName';

DECLARE @alias NVARCHAR(max) = 'First name';

SELECT @requestedFieldName AS @alias FROM MyTable

There's plenty of examples using parameters in WHERE clauses and other clause involving a value to be matched/assigned/set with a field... however, I couldn't find any example involving a parametrized field name / alias in SQL server (there's some SO question about JDBC and MySQL but none with SQL Server)

Is there a way to parametrized a field name or should I consider building an intermediate interface that will hold the list of every available fields a user can request? (I know the second option is used a lot but we have A LOT of table and their structure will change on a regular basis).

Answer 1

Use varchar parameters to get the columns list and the table name.
Then check the values of those parameters against the sys.columns table.
If all columns match then you can safely use the values to create a dynamic SQL and execute it. If you also need the user to pass the conditions to build the where clause, do the same test for the columns there.

Update Now that I've created a sample code, I think that this is probably not the best solution for you, unless you can find a way to dynamically create the stored procedure for each table. The reason for this is the where clause, and the fact that each table have a different number of columns with different data types, making the creation of the where clause parameter list very table dependent. (and actually, the creation of the number of the select parameters is also dependent on the table).
Therefore, I'm not sure that this is a practical solution, but it was a fun t-sql challenge for me Nevertheless, I've already created the code sample, and perhaps someone will find a way to use such a procedure, so I'll share it:

First, DDL for a sample table:

CREATE TABLE [dbo].[TblDays](
    [Day_Id] [int] IDENTITY(1,1) NOT NULL,
    [Day_Date] [date] NOT NULL,
    [Day_Name] [nchar](10) NOT NULL,
 CONSTRAINT [PK_TblDays] PRIMARY KEY CLUSTERED 
(
    [Day_Id] ASC
)

and a sample for a safe dynamic sql stored procedure:

CREATE PROCEDURE DynamicalySelectFromTblDays
(
    @ErrorMessage varchar(100) output,
    @SelectCol1 sysname,
    @SelectCol2 sysname = null,
    @SelectCol3 sysname = null,
    @WhereCol1 sysname = null,
    @WhereCol2 sysname = null,
    @WhereCol3 sysname = null,
    @WhereValue1 int = null,
    @WhereValue2 date = null,
    @WherValue3 nchar(10) = null
)
AS

DECLARE @ExceptedColumnCount int,
        @ActualColumnsCount int,
        @TableName sysname = 'TblDays',
        @SQL varchar(max)

-- get the number of columns expcected to get back from sys.columns
SELECT @ExceptedColumnCount = COUNT(*) 
FROM (VALUES (@SelectCol1), (@SelectCol2), (@SelectCol3)) as x(a)
WHERE a is not null 

-- get the number of columns from sys.columns
SELECT @ActualColumnsCount = COUNT(*)
FROM sys.columns c
INNER JOIN sys.tables t ON(c.object_id = t.object_id)
WHERE t.name = @TableName
AND c.name IN(@SelectCol1, @SelectCol2, @SelectCol3)

-- only if we get all of the non null columns back from the sys.columns table
IF @ExceptedColumnCount = @ActualColumnsCount AND (@ExceptedColumnCount = 0 OR @ActualColumnsCount > 0)
BEGIN

    -- same test for where clause columns
    SELECT @ExceptedColumnCount = COUNT(*) 
    FROM (VALUES (@WhereCol1), (@WhereCol2), (@WhereCol3)) as x(a)
    WHERE a is not null 

    SELECT @ActualColumnsCount = COUNT(*)
    FROM sys.columns c
    INNER JOIN sys.tables t ON(c.object_id = t.object_id)
    WHERE t.name = @TableName
    AND c.name IN(@WhereCol1, @WhereCol2, @WhereCol3)

    IF @ExceptedColumnCount = @ActualColumnsCount AND (@ExceptedColumnCount = 0 OR @ActualColumnsCount > 0)
    BEGIN

        -- dynamically build the sql statement:
        SET @SQL = 'SELECT '+ COALESCE(@SelectCol1 +', ', '') + COALESCE(@SelectCol2 +', ', '')+ COALESCE(@SelectCol3, '') +
                   ' FROM '+ @TableName 

        IF COALESCE(@WhereCol1, @WhereCol2, @WhereCol3) IS NOT NULL
        BEGIN

            SET @SQL = @SQL + ' WHERE '
            IF @WhereCol1 IS NOT NULL 
                SET @SQL = @SQL + @WhereCol1 +' = '+ CAST(@WhereValue1 as varchar(10))
            IF @WhereCol2 IS NOT NULL 
                SET @SQL = @SQL + ' AND ' + @WhereCol2 +' = '''+ CONVERT(char(10), @WhereValue2, 120) +''''
            IF @WhereCol3 IS NOT NULL 
                SET @SQL = @SQL + ' AND ' + @WhereCol3 +' = '''+ @WherValue3 +''''
        END

        PRINT @SQL
        -- EXEC(@SQL) -- Commented out since it's better to print first and look at the results, and only then execute it.
    END
    ELSE
    BEGIN
        -- perhaps using raise error instead would suit your needs better
        SELECT @ErrorMessage = 'where columns does not match table columns'
    END
END
ELSE
BEGIN
    -- perhaps using raise error instead would suit your needs better
    SELECT @ErrorMessage = 'select columns does not match table columns'
END

GO

And of course, a sample execute code:

DECLARE @ErrorMessage varchar(100),
        @SelectCol1 sysname = 'Day_Id',
        @SelectCol2 sysname = 'Day_Date',
        @SelectCol3 sysname = 'Day_Name',
        @WhereCol1 sysname = 'Day_Id',
        @WhereCol2 sysname = 'Day_Date',
        @WhereCol3 sysname = null,
        @WhereValue1 int = 1,
        @WhereValue2 date = convert(date, '14/04/2015', 103),
        @WherValue3 nchar(10) = null

EXEC DynamicalySelectFromTblDays    @ErrorMessage output, 
                                    @SelectCol1, 
                                    @SelectCol2, 
                                    @SelectCol3,
                                    @WhereCol1,
                                    @WhereCol2,
                                    @WhereCol3,
                                    @WhereValue1,
                                    @WhereValue2,
                                    @WherValue3


PRINT @ErrorMessage

And the result of all this mess is this:

SELECT Day_Id, Day_Date, Day_Name FROM TblDays WHERE Day_Id = 1 AND Day_Date = '2015-04-14'

You can get the columns list for each table to your c# code and build the dynamic query there. it would probably be a shorter and faster code.