0

I need to "inject" a file that contains a T-SQL procedure into many T-SQL scripts. Something along the lines of :

EXECUTE  '../CSV_From_Sql.sql', #CsvFileCreator <other parameters>

where #CsvFileCreator is the entry point (i.e. the procedure name) in '../CSV_From_Sql.sql'.

Note that results produced by #CsvFileCreator must be within (accessable from) the scope/namespace of the invoking script.

At the moment I am not allowed to create stored procedures. To meet the scoping requirements #CsvFileCreator must be "injected" (copied) into each script. This has to be done in a way that allows #CsvFileCreator to be simultaneously updated in all queries (and eventually converted to a stored procedure).

The desired effect is, in essence, an "insert text here" operation (i.e. replace the EXECUTE statement with the contents of the file). Which should be extremely simple to do except that Microsoft's documentation does not seem to allow for this.

Some context: In my case SQL server is being used as the back end to a python 3.X GUI report generator. End users have absolutely no access to the SQL code and there's no opportunity for injection. All users can do is make their choices (via checkboxes, spinboxes, etc) and press a "Create Reports" button (this causes python to invoke SQL server). Also the very nature of the app means it will NEVER be on a network that is connected to the outside world.

For reasons that I will not get into, only "pure" T-Sql commands can be used and the use of any type of manual operation (e.g. use of tools like SSMS, bcp, sqlcmd, etc.) is not possible. In my case I could automatically insert this "boilerplate procedure" before invoking each query.

Is there a pure T-SQL way to get the "execute file" or "copy file" effect?

user1459519
  • 712
  • 9
  • 20
  • So you need to pass the (string?) contents of a file as a parameter to multiple sprocs? Also, I wouldn't say it *should be extremely simple* considering that your limitations include using *none* of the tools that could possibly help... no powershell either I assume? – Jacob H May 29 '18 at 18:54
  • 1
    See if this thread helps you get started: https://stackoverflow.com/questions/11539424/how-to-read-a-files-contents-into-an-sql-variable – Jacob H May 29 '18 at 18:56
  • This is not a simple task because it has HUGE security ramifications. This is incredibly dangerous as sql injection is wide open here. This kind of thing is EXACTLY why stored procedures exist in the first place. OPENROWSET might help but this sounds like a classic xy problem. http://xyproblem.info/ – Sean Lange May 29 '18 at 19:01
  • I agree with @SeanLange. What is the problem that inserting an extra script at the beginning of every script is meant to solve? – Ann L. May 29 '18 at 19:21
  • @Jacob H Thanks for hint, looks like a "pure T-SQL" workaround is relatively easy: just read in the procedure and then execute it using dynamic SQL. (Should have thought of it myself, the "injected" procedure uses a fair bit of dynamic SQL.) – user1459519 May 29 '18 at 20:27
  • @Sean Lange: Generally I agree with you but in this case injection is not a concern. I've updated the question to add context and explain why that can't happen. And if I had permission to use stored procedures the question wouldn't even have been asked. – user1459519 May 29 '18 at 20:39
  • @Ann L: it's a "brute force & ignorance" workaround for the fact that right now I can't create a stored procedure. – user1459519 May 29 '18 at 20:42
  • I get what you mean that the data is not vulnerable. But the pattern is vulnerable and that is partly why it is difficult to do what you want here. I would talk to the DBA / sysadmin and explain what you are trying to do. Working around the limitations to accomplish a task for business is usually a sign that something needs to change. It would take far less effort in most places I have ever worked to get permission to create procedures on a test machine than it would to write something as complicated as you are trying to do here. – Sean Lange May 29 '18 at 20:48
  • @Sean Lange, it's only intended for use as a temporary workaround until I can convert it to a stored procedure. I can't spend time waiting around for IM to (maybe) eventually grant me that privilige though. BTW., what do you mean by "the pattern is vulnerable"? – user1459519 May 29 '18 at 21:11
  • The pattern of reading a query from a file and running it. Anytime you take queries and directly execute them the possibility of sql injection is there unless the queries are parameterized. And reading from a file that wouldn't be possible. – Sean Lange May 30 '18 at 13:18

1 Answers1

1

To the best of my knowledge, it isn't possible to open a text file from within a stored proc and execute its contents. And, as has been pointed out in the comments, this has BIG ramifications for security.

Having said that, it is possible to execute dynamic SQL, using sp_execute_sql. (See here for full details.) And where you get the chunk of text that you execute is up to you.

So, some possibilities include:

  • Pass text to script as a parameter
  • Return text to script as the result of a scalar function. (Never done this, but ought to be possible.)
  • Read text to execute from a table

You refer to "scripts", and you also refer to them as "queries". So I am assuming these are executed through various means, and that you don't have one central application routine that executes them (and could be modified to do other things before hand.)

A final thought. T-SQL is not a language/environment in which file IO or text processing can be easily done, and it doesn't allow any introspection. But, Windows (or other operating systems) are great for that.

If these scripts are living as "scripts" -- text files -- you could write a program in Windows to pre-process them and output a file that includes the merged SQL, which you then execute. Exactly what you wanted, except that it happens in Windows rather than in T-SQL.

Ann L.
  • 13,760
  • 5
  • 35
  • 66
  • 1
    You've essentially figured out whats going on. The queries/scripts (both mean the same thing) that are used are NOT kept as stored procedures. Instead python (which could do a "Brute Force & Ignorance" merge) reads the query from a file and passes it to SQL Server. (End-users have no access to these files and using files makes app distribution easier.) The "Martin Smith" answer to the hint provided by @Jacob H (how to read a file from within SQL), when used with dynamic SQL, should give me the workaround that I need. We'll see. – user1459519 May 29 '18 at 21:47