2

I am trying to find a solution to execute inline SQL with parameters against some linked server databases. From what I have found online the only proper way to query a linked server is to use OPENQUERY. And the only proper way to prevent SQL injection is with parameters.

However, the way OPENQUERY is structured it does not look like parameters are possible. Am I right to assume the only proper way to query with this type of configuration is with stored procedures? Are there any alternatives? Are there any similar database configuration that could be used?

Any thoughts or advice on the subject would be greatly appreciated.

1 Answer 1

2

No, you shouldn't use OPENQUERY IMHO. How about this construct, which allows you to use sp_executesql and parameters:

DECLARE @someParam INT = 5;

EXEC LinkedServerName.master.sys.sp_executesql
  @stmt = N'SELECT @i, @@SERVERNAME, @@VERSION;', 
  @params = N'@i INT', @i = @someParam;
3
  • This doesn't work: DECLARE @someParam VARCHAR = 5; EXEC LinkedServerName.master.sys.sp_executesql @stmt = N'SELECT * from tblTest WHERE id = @i', @params = N'@i VARCHAR', @i = @someParam; This does: select * from openquery([LinkedServerName], 'SELECT * from tblTest WHERE id = 5') The first says: Invalid object name 'tblTest'. Mar 11, 2015 at 17:32
  • Well, tblTest probably doesn't exist in master, right? So change master in the exec to whatever your database name is. The OPENQUERY approach works because it is setting the context to your default database. The EXEC overrides that. Mar 11, 2015 at 17:43
  • Yeah I just saw you edit, that works. Thank you so much! Mar 11, 2015 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.