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?

Not only `-Variable` has problems with embedded `=`, it also does not parametrize the SQL command. It substitutes whatever goes after the `=` in the `name=value` clause into the SQL text, which is the reason why you need to put the `value` in single quotes - they are not treated specially, they are just dumped in the SQL. If the parameter value comes from outside, you have to escape it yourself to make it a valid and safe SQL string literal, complete with single quotes, before you pass it to `Invoke-SqlCmd`. — GSerg, Aug 27 '18 at 13:40
@GSerg this is true unless your script already has the single quotes in it. i.e. '$(myVar)', then just the value is needed, otherwise you create a TSQL syntax error. — Jim, Feb 02 '23 at 20:20

score 17 · Accepted Answer · answered Feb 02 '16 at 16:23

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.

Thanks for doing the digging and finding the root cause Matthew. That saved me time trying to finangle my code to get it to work with Invoke-Sqlcmd knowing that there was not going to be a clean solution. — David Zehr, Oct 01 '19 at 17:27

score 11 · Answer 2 · edited Nov 01 '19 at 10:25

11

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

edited Nov 01 '19 at 10:25

MartenCatcher

2,713
8
26
39

answered Nov 01 '19 at 08:15

Jeroen Jongman

111
1
2

This helped me get around an issue with Powershell and $( in my SQL script. I used CHAR(36) instead of the $ symbol, thank you! – ekarcnevets Aug 25 '23 at 13:25

score 2 · Answer 3 · answered Oct 01 '19 at 17:25

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.

Is it possible to pass variable to -InputFile ? – Sagar Mar 12 '21 at 15:31 — Sagar, Mar 12 '21 at 15:31

PGHE · Answer 4 · 2017-08-23T22:09:37.223

0

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.

edited Aug 23 '17 at 22:09

answered Aug 23 '17 at 20:57

PGHE

1,585
11
20

Even though this might address OP's question, for future reference of link only answers, please write down the components which address the problem. In the event that the link breaks down, the answer still lives on. – eshirima Aug 23 '17 at 21:18

Escape variable in sqlcmd / Invoke-SqlCmd

4 Answers4

Linked

Related