Pass string parameter when running Invoke-SqlCmd?

Question

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?

Answer 1

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

Answer 2

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)
}