Powershell -invokesql with character parameter

Question

I have code that reads an excel file and searches the value in the sql database. This is a part of the code:

    for ($i=1; $i -le $rowMax-1; $i++)
    { 
        $name = $sheet.Cells.Item($rowName+$i,$colName).text 
        $mail = $sheet.Cells.Item($rowMail+$i,$colMail).text 
        #test   $Query = ‘ SELECT  * FROM dbo.tbl1 where value = ‘ + “‘” +$mail + “‘”
        $Query = ‘ SELECT  * FROM dbo.tbl1 where id = 3’ 

        Invoke-Sqlcmd -serverinstance $DatabaseServerName -Database $Database -Username $Uid -Password $Pwd -query $Query   -ErrorAction Stop

        Write-Host ("My Name is: "+$name)
        Write-Host ("My mail is: "+$mail)
        Write-Host $query
    }
    #close excel file
    $objExcel.quit()

If I query with a number (SELECT * FROM dbo.tbl1 where id = 3), it goes ok:

id           : 3
className    : PersonBase
keyValue     : 3
typeCd       : 6
value        : karen.g@hotmail.com
description  :
usermodify   :
datemodify   :
My Name is: G Karen
My mail is: karen.g@hotmail.com
 SELECT  * FROM dbo.tbl1 where id = 3

If I execute with the query :

$Query = ‘ SELECT  * FROM dbo.tbl1 where value = ‘ + “‘” +$mail + “‘”

I get this error:

Invoke-Sqlcmd : Incorrect syntax near '''. Msg 102, Level 15, State 1, Procedure , Line 1. At line:8 char:1 + Invoke-Sqlcmd -serverinstance $DatabaseServerName -Database $Database ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

When I debug, to me, the output looks fine. I Can paste it onto the server and the select statement works fine:

My Name is: G Karen
My mail is: karen.g@hotmail.com
 SELECT  * FROM dbo.tbl1 where value = 'karen.g@hotmail.com'

What is going wrong?

Answer 1

PowerShell happily accepts Unicode (non-ASCII-range) quotation marks instead of the usual, ASCII-range ones - see this answer

For instance, as in your code:

• ‘ (LEFT SINGLE QUOTATION MARK, U+2018) can be used in lieu of the ASCII-range ' (APOSTROPHE, U+0027) , aka single quote.

• “ (LEFT DOUBLE QUOTATION MARK, U+201C) can be used in lieu of the ASCII-range " (QUOTATION MARK, U+0022), aka double quote.

By contrast, I suspect, SQL Server does not suspect these substitutions, so you'll have to use ASCII-range ' and " for quotation marks embedded in your queries.

Therefore, the immediate fix is:

$Query = ‘ SELECT  * FROM dbo.tbl1 where value = ‘ + “'” +$mail + “'”

Note how only the characters inside “...” were changed from ‘ to '; the outer quoting, which is interpreted - and removed - by PowerShell can keep using the non-ASCII-range quotes.

---

However, you can more simply use an expandable string ("...") with embedded variable references:

$Query = " SELECT  * FROM dbo.tbl1 where value = '$mail'"

Note that I've also switched the outer double quotes to their ASCII-range versions. It is generally better even in PowerShell to stick with the usual, ASCII-range quotation marks, lest problems arise due to character encoding when sharing code.