Escape variable in sqlcmd / Invoke-SqlCmd

Question

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?

Answer 1

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