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"
               $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....


  • 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 '13 at 21:22

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.