How can I pass parameters to a pass-through query in c#?

Question

I have a C# program that uses OPENQUERY() to select from a linked server. This pass-through query takes in a parameter. To guard against SQL injection, I would like to pass this parameter in a manner similar to SqlCommand.Parameters.AddWithValue, however OPENQUERY() does not accept variables.

So far, I have been using SqlCommand to pass parameters to queries run against tables in a SQL Server. However I also need to access the linked server which is Oracle. Is there any way to add in this parameter without concatenating it as a string?

string query = "SELECT * FROM OPENQUERY(linked_server, 
'SELECT * FROM User.Table WHERE col1 = @parameter1 ')";

EDIT: I don't have permissions to create stored procedures on the remote Oracle server. So executing Sp_executesql on a stored procedure doesn't seem like the immediate answer for me.

Answer 1

There is an EXEC invocation especially for linked servers (see docs):

EXEC( 'SELECT * FROM User.Table WHERE col1 = ?', '<param>' ) AT linked_server

You could put all that in string query and you would be safe from an SQL injection into the SELECT statement, although you could get a syntax error for the EXEC statement.

Answer 2

This is not best approach from performance standpoint but you could filter on SQL Server side:

string query = "SELECT * 
                FROM OPENQUERY(linked_server, 'SELECT * FROM User.Table') s
                WHERE col1 = @parameter1";

---

EDIT:

From How to pass a variable to a linked server query:

When you query a linked server, you frequently perform a pass-through query that uses the OPENQUERY, OPENROWSET, or OPENDATASOURCE statement.

This article provides three examples of how to pass a variable to a linked server query.

To pass a variable to one of the pass-through functions, you must build a dynamic query.

Approach 1:

Pass Basic Values

When the basic Transact-SQL statement is known, but you have to pass in one or more specific values, use code that is similar to the following sample:

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)

Approach 2:

Use the Sp_executesql Stored Procedure

To avoid the multi-layered quotes, use code that is similar to the following sample:

DECLARE @VAR char(2)
SELECT  @VAR = 'CA'
EXEC MyLinkedServer.master.dbo.sp_executesql
   N'SELECT * FROM pubs.dbo.authors WHERE state = @state',
   N'@state char(2)',
   @VAR

and in your example:

DECLARE @parameter1 <your_type> = ?;

EXEC linked_server.master.dbo.sp_executesql
     @'SELECT * FROM User.Table WHERE col1 = @parameter1 '
    ,N'@parameter1 <your_type>'
    ,@parameter1;

If you need to do some other operation on local side:

DECLARE @parameter1 <your_type> = ?;
CREATE #temp(col_name <type>, ...);

INSERT INTO #temp(col_name)    
EXEC linked_server.master.dbo.sp_executesql
     @'SELECT col_name1,... FROM User.Table WHERE col1 = @parameter1 '
    ,N'@parameter1 <your_type>'
    ,@parameter1;

SELECT *
FROM #temp
-- JOIN any local table (SQL Server's side)
-- WHERE any_condition;

Answer 3

To be able to gain some SQL server query caching it is best to use the sp_executesql stored procedure.

It by design likes to pass parameters in to the query, good for security (SQL injection prevention) as well as performance when you need to consider implied conversions (a real performance killer).

You can do this a simple as calling

using (SqlCommand cmd = _con.CreateCommand())
{
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.CommandText = "sp_executesql";
     cmd.Parameters.Add("sel_query", myQuery);
     return cmd.ExecuteReader();
}  

The myQuery variable can contain any valid TSQL and can be as complex as you like. Just make sure that what goes in it is intended and safe, for your server as well as the one you are connecting to.

Some dynamic samples can be found when searching the web, look at this example. You might however step back, take a coffee and think, perhaps you would like to decouple the others server table definition from your code.

You might consider generating a view in your server that queries the linked server, then you only need to query a local object, works good on stored procedures and functions as well.

The decoupling also allows you to query other databases that your client doesn't have Drivers for. I have created views like this on DB2, Oracle etc. using this method without having to install the drivers on the client systems.