Avoiding SQL injections with complex dynamic SQL targeting OLEDB Linked Server

Question

In SQL Server 2016, I'm running a complex dynamic SQL query on an OLE-DB Linked Server, inside a stored procedure.

I'm currently building the dynamic SQL as a string, concatenating parameters in many places. So, I'm concerned about SQL injection.

The linked server is actually connecting to the OLE-DB provider interface to OSISoft PI, which is a specialised historian database. I can't define stored procedures within PI, so I think dynamic SQL is the only way to get the flexibility I need.

I do use the QUOTENAME(input, '''') function to wrap the user-supplied parameters in quote marks, which should also escape any quote marks found in the input. But I'm not certain if this constitutes an EFFECTIVE defense against SQL injections. I mostly did it because it makes the literals simpler in the string concatenation.

The stored procedure currently looks something like this:

-- Wrap user-supplied parameters in quotes to simplify SQL string building
DECLARE @Tag1   NVARCHAR(30) = QUOTENAME(@Tag1Input, '''')
DECLARE @Tag2   NVARCHAR(30) = QUOTENAME(@Tag2Input, '''')
DECLARE @Tag3   NVARCHAR(30) = QUOTENAME(@Tag3Input, '''')
DECLARE @Tag4   NVARCHAR(30) = QUOTENAME(@Tag4Input, '''')
DECLARE @Tag5   NVARCHAR(30) = QUOTENAME(@Tag5Input, '''')
DECLARE @Tag6   NVARCHAR(30) = QUOTENAME(@Tag6Input, '''')
DECLARE @Tag7   NVARCHAR(30) = QUOTENAME(@Tag7Input, '''')
DECLARE @Tag8   NVARCHAR(30) = QUOTENAME(@Tag8Input, '''')
DECLARE @Tag9   NVARCHAR(30) = QUOTENAME(@Tag9Input, '''')
DECLARE @Tag10  NVARCHAR(30) = QUOTENAME(@Tag10Input, '''')
DECLARE @starttimeq NVARCHAR(10) = QUOTENAME(@starttime, '''')
DECLARE @endtimeq   NVARCHAR(10) = QUOTENAME(@endtime, '''')
DECLARE @timestepq  NVARCHAR(10) = QUOTENAME(@timestep, '''')
DECLARE @calcbasisq NVARCHAR(30) = QUOTENAME(@calcbasis, '''')

-- Build SQL statement
DECLARE @sql NVARCHAR(2000) = 'SELECT tag, time, value 
FROM piarchive..piavg 
WHERE 
    tag IN (' + @Tag1 + ', ' + @Tag2 + ', ' +@Tag3 + ', ' + @Tag4 + ', ' + @Tag5 + ', ' + @Tag6 + ', ' + @Tag7+ ', ' + @Tag8 + ', ' + @Tag9 + ', ' + @Tag10 + ') 
    AND time BETWEEN ' + @starttimeq + ' AND ' + @endtimeq + '
    AND timestep = ' + @timestepq + ' 
    AND calcbasis = ' + @calcbasisq + '
UNION
SELECT ''calculatedValue'' AS tag, time, value 
FROM piarchive..piavg
WHERE 
    expr = ''(''' + @Tag2 + ''' * (''' + @Tag3 + '''-''' + @Tag4 + ''') / (''' + @Tag2 + '''-''' + @Tag4 + ''') * 100.0 + ''' + @Tag5 + ''' * (''' + @Tag4 + '''-''' + @Tag1 + ''') / (''' + @Tag5 + '''-''' + @Tag1 + ''') * (''' +@Tag3 + '''-''' + @Tag2 + ''') / (''' + @Tag4 + '''-''' + @Tag2 + ''') * 100.0) / ((''' +@Tag3 + '''-''' + @Tag4 + ''') / (''' + @Tag2 + '''-''' + @Tag4 + ''') * 100.0 + (''' + @Tag4 + '''-''' + @Tag1 + ''') / (''' + @Tag5 + '''-''' + @Tag1 + ''') * (''' +@Tag3 + '''-''' + @Tag2 + ''') / (''' + @Tag4 + '''-''' + @Tag2 + ''') * 100.0)''
    AND time BETWEEN ' + @starttimeq + ' AND ' + @endtimeq + ' 
    AND timestep = ' + @timestepq + ' 
    AND calcbasis = ' + @calcbasisq + '
ORDER BY time ASC, tag ASC'

-- Invoke dynamic SQL on PI OLEDB linked server
EXEC (@sql) AT PI

As far as I can tell, I can't use sp_executesql to run a query on an OLE-DB Linked Server. (Please correct me if I'm wrong).

And it seems like the EXEC(@sql, <params>) AT LinkedServer syntax only supports positional ? parameters due to OLE-DB limitations. Because of the hideous expr filter clause, I really want to use named parameters instead of positional parameters.

How can I safely prepare this SQL string, to guard against SQL injection attacks, when I can't use sp_executesql or named parameters? Is there an elegant approach or do I just have to brute-force it with 46 positional parameters including many repeats?

Answer 1

In the end, I worked with the administrators of the PI system to enable the PI DATE and TIME function views within the pifunction catalog. These function views convert the PI-specific time literals into formats that SQL Server can handle, within the SQL Server execution context. (When I tried to set up these function views myself I got an error dialog which said Error creating TIME - View creation failed. [PI SDK] Item not found in collection: %OSI. It turns out this error was caused by my account not having sufficient security permissions within PI.)

After the function views were set up, I could replace the dynamic SQL with a conventional SQL query running inside the SQL Server context, using standard parameter substitutions. This eliminates the SQL injection risk, as well as significantly improving the readability of the code.