CODEWITHSUNDEEP
sql-serverpowershell
ca9163d9
ca9163d9

Reputation: 29159

Pass string parameter when running Invoke-SqlCmd?

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?

Upvotes: 5

Views: 7268

Answers (3)

S M
S M

Reputation: 13

I know it's been a while since there was any activity on this question. I came here from some Googling (this question was high up in the results), so I'm leaving this answer for anyone else who gets here the same way.

There is hope if you're willing to trust a module: DbaTools, specifically Invoke-DbaQuery https://docs.dbatools.io/Invoke-DbaQuery

I used this in code (shred some XML and pop it into a table as a convenience) and it worked very well:

clear

$sql="UPDATE dbo.db_refreshes SET t_sql=@t_sql WHERE environment=@environment AND dbname=@dbname"

$doc=new-object xml
$doc.Load('C:\temp\post-refresh-queries.xml')

$doc.items.Item|ForEach-Object {
    $item=$_
    $params=@{
        t_sql=$item.t_sql.'#cdata-section'
        environment=$item.environment
        dbname=$item.dbname
    }

    Invoke-dbaquery -SqlInstance devdb01 -Database dbathings-Query $sql -SqlParameter $params
}

The Invoke-DbaQuery page shows how to call a stored proc in example 7:

Invoke-DbaQuery -SqlInstance server1 -Database tempdb -Query Example_SP -SqlParameter @{ Name = "Maria" } -CommandType StoredProcedure

Upvotes: 0

DerOta
DerOta

Reputation: 157

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 greater 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

Upvotes: 2

spicy.dll
spicy.dll

Reputation: 957

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.

EDIT

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

Upvotes: 0

Related Questions