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 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
Invoke-SqlCmd's -Variable flag does not seem to escape anything and the docs for Invoke-Sqlcmd do not claim that it does. Just to be sure I ran Invoke-Sqlcmd "PRINT '`$(FOO)'" -Variable @("FOO='; SELECT @@VERSION; PRINT 'Compromised") -TrustServerCertificate -Verbose and got it to print the injected sql to print 'Compromise' and the version instead of it just printing the sql code.
As an alternative to Invoke-Sqlcmd I've written Invoke-SafeSqlcmd which I believe to be resistant to sql injection. It uses another custom cmdlet called New-SqlConnection which tries to make up for the lackluster connection string support in the SqlServer module.
You can call it like so:
$Connection = New-SqlConnection -Database dbname -Options @{ TrustServerCertificate = $True }
Invoke-SafeSqlcmd `
-Connection $Connection `
-Query "PRINT @FOO" `
-Parameters @{ FOO = "'; SELECT @@VERSION; PRINT 'Compromised" } `
-Verbose
# Returns: '; SELECT @@VERSION; PRINT 'Compromised
Invoke-SafeSqlCmd
<#
.SYNOPSIS
A safe, parameterized version of Invoke-SqlCmd that prevents SQL injection.
.DESCRIPTION
This function executes SQL queries using proper parameterization to prevent SQL injection attacks.
It supports both connection strings and existing SqlConnection objects, and can return results
as objects or raw DataTable/DataSet.
.EXAMPLE
$Connection = New-SqlConnection -Database dbname -Options @{ TrustServerCertificate = $True }
Invoke-SafeSqlcmd `
-Connection $Connection `
-Query "PRINT @FOO" `
-Parameters @{ FOO = "'; SELECT @@VERSION; PRINT 'Compromised" } `
-Verbose
#>
function Invoke-SafeSqlCmd {
[CmdletBinding(DefaultParameterSetName='ConnString')]
[OutputType([PSCustomObject[]], [System.Data.DataTable], [System.Data.DataSet], [object])]
param(
[Parameter(Mandatory)]
[ValidateNotNullOrEmpty()]
[string] $Query,
[Parameter()]
[Hashtable] $Parameters = @{},
[Parameter(Mandatory, ParameterSetName='ConnString')]
[ValidateNotNullOrEmpty()]
[string] $ConnectionString,
[Parameter(Mandatory, ParameterSetName='ConnObj')]
[ValidateNotNull()]
[object] $Connection,
[Parameter()]
[ValidateSet('Hashtable', 'PSObject', 'DataTable', 'DataSet', 'Scalar')]
[string] $As = 'Hashtable',
[Parameter()]
[ValidateRange(1, 65535)]
[int] $QueryTimeout = 30,
[switch] $KeepConnectionOpen
)
# Validate Connection object if provided
if ($Connection) {
$IsConnectionInvalid =
$Connection -isnot [System.Data.SqlClient.SQLConnection] -and
$Connection -isnot [Microsoft.Data.SqlClient.SqlConnection]
if ($IsConnectionInvalid) {
throw "Connection parameter must be a valid SqlConnection object."
}
}
# Create connection if ConnectionString parameter set is used
if ($PSCmdlet.ParameterSetName -eq 'ConnString') {
Write-Verbose "Creating new SQL connection from connection string"
$Connection = New-SqlConnection -ConnectionString $ConnectionString
# Since we aren't returning this ConnectionObject we should dispose of it
if ($KeepConnectionOpen) {
Write-Warning "KeepConnectionOpen is ignored when using ConnectionString parameter set."
}
$KeepConnectionOpen = $false
}
$IsMicrosoftNamespace = $Connection.GetType().Namespace -eq 'Microsoft.Data.SqlClient'
# Set up PRINT statement handling
$InfoMessageHandler = $null
if ($VerbosePreference -ne 'SilentlyContinue') {
$InfoMessageHandler = {
param($SenderArg, $EventArg)
Write-Host $EventArg.Message -ForegroundColor Yellow
}
$Connection.add_InfoMessage($InfoMessageHandler)
}
# Prepare SQL command
$SqlCmd = $Connection.CreateCommand()
$SqlCmd.CommandText = $Query
$SqlCmd.CommandTimeout = $QueryTimeout
# Add parameters
Write-Verbose "Adding $($Parameters.Count) parameters to SQL command"
foreach ($Param in $Parameters.GetEnumerator()) {
$ParamName = if ($Param.Key.StartsWith('@')) { $Param.Key } else { "@$($Param.Key)" }
$SqlCmd.Parameters.AddWithValue($ParamName, $Param.Value) | Out-Null
Write-Verbose "Added parameter: $ParamName"
}
if ($IsMicrosoftNamespace) {
$DataAdapterType = [Microsoft.Data.SqlClient.SqlDataAdapter]
} else {
$DataAdapterType = [System.Data.SqlClient.SqlDataAdapter]
}
try {
# Open connection if needed
if ($Connection.State -ne 'Open') {
$Connection.Open()
}
# Execute based on return type
switch ($As) {
'Scalar' {
return $SqlCmd.ExecuteScalar()
}
'DataSet' {
$DataAdapter = $DataAdapterType::new($SqlCmd)
$DataSet = [System.Data.DataSet]::new()
$DataAdapter.Fill($DataSet) | Out-Null
return $DataSet
}
'DataTable' {
$DataAdapter = $DataAdapterType::new($SqlCmd)
$DataTable = [System.Data.DataTable]::new()
$DataAdapter.Fill($DataTable) | Out-Null
return $DataTable
}
{ $_ -in @('PSObject', 'Hashtable') } {
$Reader = $null
try {
$Reader = $SqlCmd.ExecuteReader()
$Results = @()
while ($Reader.Read()) {
$Row = @{}
for ($i = 0; $i -lt $Reader.FieldCount; $i++) {
$FieldName = $Reader.GetName($i)
$FieldValue = if ($Reader.IsDBNull($i)) { $null } else { $Reader.GetValue($i) }
$Row[$FieldName] = $FieldValue
}
# Convert to PSObject if requested
if ($As -eq 'PSObject') {
$Row = [PSCustomObject] $Row
}
$Results += $Row
}
$Reader.Close()
return $Results
} finally {
if ($Reader -and -not $Reader.IsClosed) {
$Reader.Close()
$Reader.Dispose()
}
}
}
}
} catch {
throw "SQL operation failed: $_"
} finally {
# Remove InfoMessage handler if we added it
if ($InfoMessageHandler) {
$Connection.remove_InfoMessage($InfoMessageHandler)
}
# Clean up resources
if (-not $KeepConnectionOpen -and $Connection.State -eq 'Open') {
$Connection.Close()
$Connection.Dispose()
}
if ($SqlCmd) {
$SqlCmd.Dispose()
}
}
}
New-SqlConnection
<#
.SYNOPSIS
Creates a SqlConnection object
.NOTES
Some $Options keywords may not be set-able due to using
System.Data.SqlClient instead of the newer Microsoft.Data.SqlClient package.
If an unsupported keyword is required, add it to the connection string directly.
.EXAMPLE
Use a ConnectionString
$SqlConn = New-SqlConnection -ConnectionString 'all_values_set_here'
.EXAMPLE
Use Integrated Authentication
# Implicit
$SqlConn = New-SqlConnection -Database 'dbname'
# Explicit
$SqlConn = New-SqlConnection -Server 'localhost' -Database 'dbname' IntegratedSecurity
.EXAMPLE
Use user/pass and pass additional ConnectionString properties
$SqlConn = New-SqlConnection `
-Server 'localhost' `
-Database 'dbname' `
-Username 'user' `
-Password 'pass' `
-Options @{ Encrypt = $True }
#>
function New-SqlConnection {
[CmdletBinding(DefaultParameterSetName='WindowsAuth')]
[OutputType([System.Data.SqlClient.SQLConnection], [String])]
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidUsingPlainTextForPassword', '', Justification='Usually cannot pass in a SecureString or Credential object to this script but the option exists.')]
param(
[Parameter(ParameterSetName='SqlAuth')]
[Parameter(ParameterSetName='WindowsAuth')]
[Parameter(ParameterSetName='EntraAuth')]
[ValidateNotNullOrEmpty()]
[string] $Server = [System.Net.Dns]::GetHostEntry($env:COMPUTERNAME).hostname,
[Parameter(Mandatory,ParameterSetName='SqlAuth')]
[Parameter(Mandatory,ParameterSetName='WindowsAuth')]
[Parameter(Mandatory,ParameterSetName='EntraAuth')]
[ValidateNotNullOrEmpty()]
[string] $Database,
[Parameter(ParameterSetName='SqlAuth')]
[Parameter(ParameterSetName='WindowsAuth')]
[Parameter(ParameterSetName='EntraAuth')]
[uint32] $Port = 1433,
[Parameter(Mandatory,ParameterSetName='SqlAuth')]
# Username not mandatory for EntraAuth because not all auth methods require it
[Parameter(ParameterSetName='EntraAuth')]
[ValidateNotNullOrEmpty()]
[string] $Username,
[Parameter(Mandatory,ParameterSetName='SqlAuth',ValueFromPipeline)]
# Password not mandatory for EntraAuth because not all auth methods require it
[Parameter(ParameterSetName='EntraAuth',ValueFromPipeline)]
[ValidateNotNullOrEmpty()]
[object] $Password,
[Parameter(Mandatory,ParameterSetName='ConnectionString')]
[ValidateNotNullOrEmpty()]
[string] $ConnectionString,
[Parameter(ParameterSetName='SqlAuth')]
[Parameter(ParameterSetName='WindowsAuth')]
[Parameter(ParameterSetName='EntraAuth')]
[Hashtable] $Options = @{},
# Not set to mandatory so we can use this as the default option without
# prompting the user.
[Parameter(ParameterSetName='WindowsAuth',HelpMessage="Use Windows Authentication")]
[switch] $IntegratedSecurity,
[Parameter(Mandatory,ParameterSetName='EntraAuth',HelpMessage="Use MS Entra Authentication")]
[ValidateSet('Active Directory Integrated', 'Active Directory Password', 'Active Directory Service Principal', 'Active Directory Interactive', 'Active Directory Default', 'Active Directory Managed Identity')]
[string] $EntraAuthentication,
# Note to self: Should've use -OutputAs where the string was a non-default option
[Parameter(HelpMessage='Return a connection string instead of the SqlConnection object')]
[switch] $StringOnly
)
# MARK: PREPARE
# Ensure SqlServer module is available for the SQL client assemblies
Import-Module SqlServer -ErrorAction Stop -Verbose:$False
$SqlModule =
Get-Module -Name SqlServer -ListAvailable |
Sort-Object Version -Descending |
Select-Object -First 1
if ($EntraAuthentication -and $SqlModule.Version -lt [version]'22.2.0') {
throw "Entra Authentication requires SqlServer module version 22.2.0 or higher. Current version: $($SqlModule.Version)"
}
# Convert SecureString to plain text for connection string builder
if ($Password -is [SecureString]) {
$BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Password)
try {
$Password = [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($BSTR)
} finally {
[System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($BSTR)
}
} elseif ($Password -isnot [string] -and $Password) {
throw "Password must be a string or SecureString"
}
$CanUseNewAssembly = $SqlModule.Version.Major -ge 22
if ($CanUseNewAssembly) {
$BuilderType = [Microsoft.Data.SqlClient.SQLConnectionStringBuilder]
$SqlConnectionType = [Microsoft.Data.SqlClient.SQLConnection]
} else {
$BuilderType = [System.Data.SqlClient.SQLConnectionStringBuilder]
$SqlConnectionType = [System.Data.SqlClient.SQLConnection]
}
# MARK: BUILD STRING
# Set connection properties
switch -Wildcard ($PSCmdlet.ParameterSetName) {
'ConnectionString' {
$Builder = $BuilderType::new($ConnectionString)
break
}
'*' {
# Set mandatory args, process extra options (if any), then
# fall through to the other case statements.
$Builder = $BuilderType::new("Server=tcp:$Server,$Port;Initial Catalog=$Database;")
foreach ($Option in $Options.GetEnumerator()) {
$Builder[$Option.Key] = $Option.Value
}
}
'WindowsAuth' {
$Builder["Integrated Security"] = $True
break
}
'EntraAuth' {
if ($SqlModule.Version -lt [version]'22.2.0') {
throw "Entra Authentication requires SqlServer module version 22.2.0 or higher. Current version: $($SqlModule.Version)"
}
# Apply auth specific settings
$Builder["Authentication"] = $EntraAuthentication
switch($EntraAuthentication) {
{ $_ -in @('Active Directory Password', 'Active Directory Service Principal') }
{
if (-not $Username) {
throw "Username is required for $EntraAuthentication"
}
if (-not $Password) {
throw "Password is required for $EntraAuthentication"
}
$Builder["User ID"] = $Username
$Builder["Password"] = $Password
break
}
'Active Directory Managed Identity' {
# Managed Identity does not use a username or password
if ($Password) {
Write-Warning "Managed Identity does not use a password. Ignoring this parameter."
}
if ($Username) {
# Support for user-assigned managed identity
$Builder["User ID"] = $Username
}
break
}
}
# Encryption is required for Azure SQL
if (-not $Options.ContainsKey('Encrypt')) {
Write-Warning "Setting Encrypt=True for Entra Authentication"
$Builder["Encrypt"] = $True
}
if (-not $Options.ContainsKey('TrustServerCertificate')) {
Write-Warning "Setting TrustServerCertificate=False for Entra Authentication"
$Builder["TrustServerCertificate"] = $False
}
break
}
'SqlAuth' {
$Builder["User ID"] = $Username
$Builder["Password"] = $Password
break
}
default { throw "How did you break this?" }
}
# MARK: RETURN
if ($StringOnly) {
return $Builder.ConnectionString
}
return $SqlConnectionType::new($Builder.ConnectionString)
}
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
$sa. Also, $sa must be a string array. To force it to a string array, use $sa = @("V1=$v"). You can add multiple variables by adding more strings to $sa' in $sa and convert it to a string array doesn't help. It seems it will just construst a string of select x'x as V1 using $sa.
Invoke-Sqlcmd "PRINT '`$(FOO)'" -Variable @("FOO='; SELECT @@VERSION; PRINT 'Compromised") -TrustServerCertificate -VerboseI 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
