0

I have a stored procedure that accepts a script and execute it against the database:

CREATE PROCEDURE [dbo].[usp_ExecuteScript]
  @Script nvarchar(MAX)
AS
EXECUTE sp_executesql @Script;

When I call this procedure from command-line with sqlcmd Utility like: 

sqlcmd -E -d <mydb> -Q "usp_ExecuteScript @Script=$(script)" /v script=my_script

where my_script is SELECT * FROM Users WHERE UserName ='John', I am forced to escape all special characters such as 'qoute' manually otherwise, it fails to execute.
This gets cumbersome when I am looping through a number of long and complex scripts to pass them as @Script argument to be executed this way.

Do you have any idea to get it work?

Mostafa Armandi
  • 879
  • 1
  • 11
  • 24
  • 1
    What's the point of that procedure? You could use `sp_execute` directly. – sticky bit Jan 12 '20 at 16:59
  • 2
    This looks to me like an [x-y problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). To avoid escaping, you could execute the proc via a parameterized query in PowerShell. Note sure what value SQLCMD provides in this case. – Dan Guzman Jan 12 '20 at 17:01
  • @stickybit, because I do some extra stuff in the usp_ExecuteScript procedure as well. – Mostafa Armandi Jan 12 '20 at 17:04
  • 1
    Then show us that. This just looks like a way to let your RDBMS be easily injected into. – Thom A Jan 12 '20 at 17:08

0 Answers0