I have a stored procedure with a parameter of type nvarchar(max). And I need to call it in PowerShell.
// $s can be a long string with all kind of charaters, ', new line, etc.
Invoke-Sqlcmd -ServerInstance Server "exec MyProc '$s'"
However, the above calling doesn't work well when $s has ' (I know I can handle this case by replacing '' to '' but) or \n\r, etc. Also it has the risk of SQL injection.
Is it a way to invoke the stored without string concatenation?
To avoid SQL injections you will need to do parameterized SQL queries. Unfortunately Invoke-Sqlcmd does not support something like this!
Even with useing the -Variable flag will not prevent SQL injection (see https://powershellstation.com/2016/01/05/invoke-sqlcmd-considered-harmful/)
Instead you will need to use a Powershell module such as SQLHelper, which uses the .Net Package "System.Data.SQLClient".
NOTE: This does not work with Powershell 7 or grater as System.Data.SQLClient was removed from the System directory and needs to be installed via Nuget. Therefor the name also changed to Microsoft.Data.SQLClient.
I wasn't able to find a Powershell Module yet that supports parameterized SQL queries in Powershell 7
Use the -Variable parameter for Invoke-Sqlcmd.
$vars = @("VAR1=$s")
Invoke-SqlCmd -ServerInstance Server "exec MyProc @procParam=`$(VAR1)" -Variable $vars
This will automatically escape special characters and avoid SQL injection.
I believe (correct me if I'm wrong) you need to state the parameter you are passing to for stored procedures. That is what @procParam is in the above code.
Note: The backtick dollar sign (`$) is important as otherwise the string will be expanded before it reaches the function, which will result in an error. You could also switch the quotes to single quotes, which would make the backtick not required.
It is important to note that -Variable only accepts a String array. This means to delcare a single value, you must use the syntax @() to denote an array (as in the example).
Reference: Invoke-Sqlcmd
