Passing variables into Openquery and SQL Injection

Question

I have two databases (A and B), both SQL Server, on different servers. These databases are connected with a linked server.

I have to be able to insert rows with distinct values into a table in database B using a stored procedure on database A. This stored procedure uses OPENQUERY in order to do the INSERT statements into database B.

I know OPENQUERY does not accept variables for its arguments. OPENQUERY has specific syntax on how to do an insert into a linked DB:

INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')  
VALUES ('NewTitle');  

Nevertheless, the MS documentation shows a way to pass variables into a linked server query like this:

DECLARE @TSQL varchar(8000), @VAR char(2)

SELECT @VAR = 'CA'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + @VAR + ''''''')'

EXEC (@TSQL)

And here is the issue. Lets say the table in database B has two columns, ID (int) and VALUE (nvarchar(max))

Thus, for a stored procedure to be able to insert different values into a table in database B, my procedure looks like this:

CREATE PROCEDURE openquery_insert
    @var1 int,
    @var2 nvarchar(max)
AS 
BEGIN
    SET NOCOUNT ON;

BEGIN
    DECLARE @SQL_string nvarchar(max)
    SET @SQL_string = 'insert openquery(LINKEDSERVER, ''SELECT ID, VALUE from TABLE'') VALUES ('
    + CAST(@var1 AS NVARCHAR(5)) + ', ' 
    + '''' + CAST(@var2 AS NVARCHAR(max)) + ''''
    + ')'
        
    EXEC sp_executesql @SQL_string
END
END

The procedure can be called as

EXEC openquery_insert @var1 = 1, @var2 = 'asdf'

But if @var2 were to be ' DROP TABLE B--, a SQL injection attack would be successful.

Is there a way in order to prevent SQL Injection with OPENQUERY?

• I do not control what the values are for the arguments @var1 and @var2 when the procedure gets called
• I am not able to create functions or stored procedures on database B
• I have to use OPENQUERY, I can not use four part naming in order to do the insert
• I have to use a stored procedure on DB A

Thanks!

Answer 1

The "hacky" way is to insert your arguments into a local table first and then do the INSERT INTO ... SELECT using OPENQUERY.

This is all straightforward if your SP is ever called by one process in a synchronous fashion: you can have one table where you insert values into then execute OPENQUERY to grab them and then you delete those values from the table.

If concurrency is a requirement then you have to write logic that creates uniquely named tables etc. which quickly becomes somewhat messy.