0

I have the following part of a script that takes objects form Exchange and AD and inserts into a MYSQL table. But some of the Full Names variables have a ' like the distribution group PA's.

Here's the script part of:

    # 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"
              }
           }

and the error from the console:

Exception calling "Fill" with "2" argument(s): "You have an error in your SQL s yntax; check the manual that corresponds to your MySQL server version for the r ight syntax to use near 's', account_name='!PAs.wingrave', hide_from_gal='False ' WHERE account_name='!PAs' at line 1" At C:\Support\Control Panel\Test4_distribution.ps1:63 char:22 + $dataAdapter.Fill <<<< ($dataSet, "data") | Out-Null # Fill dataset from data adapter, with name "data" + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

!PA's DB:distribution - Updated

Any help would be great thanks .... the error comes from this variable : $strDistName = $strDist.Name but I need to do this check on all variables....

Thanks

1
  • Might replace help? Something like If ($strDistName –match “’”) { $strDistName = $strDistName.Replace(“’”, “’’”) } Which will return !PA’’s May not be exactly what MySQL requires, it’s been a while since I’ve worked with that, but replace the 2 single quotes with whatever MySQL needs to escape the original single quote.
    – Bruce
    Feb 22, 2013 at 21:22

1 Answer 1

1

You might want to try something like this:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .