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.

Ghyll

Maybe you could get the SQL Server to act as a view for the concenacted information? Maybe turn it into a copy based Distributed Database? Maybe turn the whole thing into a Webserivce or something like that? Any way to sidestep the complexities of the backend should work. – Bowden

previously asked question: #3378996 – Patronage

It is indeed a previously asked question, but if so the answer is NO. As far as I can see all the options in the other question either use string concatentation somewhere or they fetch all the remote rows and filter locally. – Hemihedral

Do you always select from one table? A distributed query may still work efficiently. – Bilinear

I'm not familiar with the OPENQUERY. So, I just curious. Can you just create a Stored Procedure in the local or remote server and then use it with parameters? (SqlCommand.Parameters.AddWithValue) – Autecology

Do you have control over the oracle database? Can you create procedures there and execute them using linked server? – Geryon

Did you tried to create a synonym for linked server table and run your query over the synonym? – Slaby

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.

Begonia

After setting RPC Out to true, I almost got it working. I was having a hard time concatentating some characters to the "?" such as: EXEC('SELECT * FROM User.Table WHERE col1 LIKE ?-%', @var) At linked_server – Ghyll

AFAIK about LIKE I would try 'LIKE ?' in the query and craft the argument with appropriate sequence of text and '%'s. It worked with 'LIKE ?-%' ? – Begonia

@user7733611 EXEC('SELECT * FROM User.Table WHERE col1 LIKE ? || ''%''', @var) At linked_server or EXEC('SELECT * FROM User.Table WHERE col1 LIKE CONCAT(?, ''%'')', @var) At linked_serverSame is possible using approach from my answer. – Dyaus

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;

Dyaus

Unfortunately the table on the linked server is too large for this. – Ghyll

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.

Aide

Answer 4

Here is best example for create dynamic query and execute it. https://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure. I think its help you.

Bevbevan

Nested sp_executesql was my initial idea as well, but after some testing I came to conclusion that in his case it would not work i.e. it will still allow SQL injection. It is possible that I did it wrong though. – Bilinear