I am using powershell and using Invoke-SqlCmd
. I am able to pass variables to SQL:
$variables = @( "MyVariable='hello'" )
Invoke-SqlCmd `
-ServerInstance 'localhost' `
-Database 'master' `
-Username 'matthew' `
-Password 'qwerty' `
-Query 'SELECT $(MyVariable) AS foo' `
-Variable $variables
This gives me back hello
as expected. However, if I have a variable with a value containing an equals (=
):
$variables = @("MyVariable='aGVsbG8NCg=='") # base64 encoded 'hello'
It gives me the following error:
The format used to define the new variable for Invoke-Sqlcmd
cmdlet is invalid. Please use the 'var=value' format for defining a new variable.
I could not find any documentation on either sqlcmd
or Invoke-SqlCmd
on how I should escape values properly.
How do I escape variables sent to sqlcmd
/ Invoke-SqlCmd
?
After investigating using some reflection on
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll
Looking at ExecutionProcessor
's constructor, the following lines reveal the problem that it will fail if there are more than one equals sign in the variable definition.
My recommendation for anyone else trying to use Invoke-SqlCmd
is to save your time and sanity and just use the open source alternative Invoke-SqlCmd2 instead.
Microsoft, please fix.
I encounted this issue recently and had success using the answer from TheMadTechnician at:
Replace Single Quotes in PowerShell Or Excel CSV
The answer is to create sub expressions in your string to replace '
with ''.
Invoke-Sqlcmd -ServerInstance "$SQLServer" -Database "$SqlDB" -query "INSERT INTO dbo.ecca_sw_standard_2 (name, version, product_id) VALUES (N'$($CSVAppName -replace "'", "''")', N'$($CSVVersion -replace "'", "''")' , N'$($CSVAppID -replace "'", "''")')"
This way when the
string is expanded it will be appropriately escaped for SQL insertion.