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.

Improve this question

1 Answer 1

Reset to default
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;
Improve this answer
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'.
    – ThreadedLemon
    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.
    – Aaron Bertrand
    Mar 11, 2015 at 17:43
  • Yeah I just saw you edit, that works. Thank you so much!
    – ThreadedLemon
    Mar 11, 2015 at 17:46

Your Answer

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

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