☰

Powershell 脚本插入带有撇号错误的 SQL 语句

tag-icon • tag-icon mysql scripting powershell
Powershell 脚本插入带有撇号错误的 SQL 语句

我有以下脚本的一部分，该脚本从 Exchange 和 AD 中获取对象并将其插入到 MYSQL 表中。但一些全名变量带有 '，如通讯组 PA。

以下是脚本部分：

    # Get Distributions List ready for writing and write MySQL
           write-host " "
           write-host "Writing Distribution List"
           write-host " "
           # Get Room Mailboxes
           $strdist = Get-DistributionGroup
           foreach ($strdist.Name in $strdist) 
           {

                $strDistName = $strDist.Name
                $strDistSamAccountName = $strDist.SamAccountName
                $strDistHidden = $strDist.HiddenFromAddressListsEnabled

               #write-host "Distribution List Name: "                $strDistName
               #write-host "Distribution List Account Name: "        $strDistSamAccountName
               #write-host "Distribution List Hidden from GAL: "     $strDistHiddenFromAddressListsEnabled




      # Check to See if full_name Name exists
               $sqlDistLookup = "SELECT account_name FROM distribution WHERE account_name='$strDistSamAccountName'"
               $DistLookup = Execute-MySQLQuery $con $sqlDistLookup 


               # Write to MySQL resource
               If ($DistLookup) {
               $sqlDistUpdateRecord = "UPDATE distribution SET full_name='$strDistName', account_name='$strDistSamAccountName', hide_from_gal='$strDistHidden' WHERE account_name='$strDistSamAccountName'"
               $DIstUpdateRecord = Execute-MySQLQuery $con $sqlDistUpdateRecord
               Write-host $strDistName " DB:distribution - Updated"
               } 
               else
               {
               $sqlDistNewRecord = "INSERT INTO distribution (full_name,account_name,hide_from_gal) VALUES ('$strDistName','$strDistSamAccountName','$strDistHidden')"
               $DistNewRecord = Execute-MySQLQuery $con $sqlDistNewRecord
               Write-host $strDistName " DB:distribution - NEW"
              }
           }

以及来自控制台的错误：

使用“2”个参数调用“填充”时发生异常：“您的 SQL 语法有错误；请查看与您的 MySQL 服务器版本相对应的手册，以了解在“s”附近使用的正确语法，account_name='！PAs.wingrave'，hide_from_gal='False ' WHERE account_name='！PAs' 在第 1 行“在 C:\Support\Control Panel\Test4_distribution.ps1:63 char:22 + $dataAdapter.Fill <<<< ($dataSet, "data") | Out-Null # 从数据适配器填充数据集，名称为“data”+ CategoryInfo：NotSpecified：（:) []，MethodInvocationException + FullyQualifiedErrorId：DotNetMethodException

!PA 的 DB:distribution - 已更新

任何帮助都将非常感谢......错误来自这个变量：$strDistName = $strDist.Name但我需要对所有变量进行此检查......

谢谢

答案1

你可能想尝试这样的事情：

$sqlDistUpdateRecord = "UPDATE distribution SET full_name='{0}', account_name='{1}', hide_from_gal='{2}' WHERE account_name='{1}'" -f $strDistName, $strDistSamAccountName, $strDistHidden

相关内容