Escape variable in 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.

Use CHAR(61) to replace the equal sign.

$variable = "'Hello=World'"
$variables = @( "MyVariable=$($variable.replace("=","'+CHAR(61)+'"))" )

Invoke-SqlCmd -ServerInstance 'localhost' -Database 'master' -Query 'SELECT $(MyVariable) AS foo' -Variable $variables

I also found myself needing to pass a base64 encoded piece of information which had those pesky '='s in them. What worked for me was adding a replacement token into the variable array that I pass to the query and then using SQL's REPLACE function in my query to replace my token with an '=' sign.

So you can update your code to look like this:

$equalsSignReplacement = '[EQUALSIGN]'
$myVariable = 'aGVsbG8NCg=='
$variables = 
    "EqualsSignReplacement=$($equalsSignReplacement)",
    "MyVariable=$($myVariable.Replace('=',$equalsSignReplacement))"

Invoke-SqlCmd `
    -ServerInstance 'localhost' `
    -Database 'master' `
    -Username 'matthew' `
    -Password 'qwerty' `
    -Query 'SELECT REPLACE('$(MyVariable)','$(EqualsSignReplacement)','=') AS foo' `
    -Variable $variables

The downside to this solution is that you need to be proactive with it's application. You need to know ahead of time which variables might have an 'equals sign' in them and then update not only your powershell code, but also your SQL scripts to make sure that the replacement happens correctly.

Just be sure to use a replacement token that is unique to your variables so you don't accidentally replace valid text with '=' in your queries.