Mysql – Powershell script to insert SQL Statement with a apostrophe error

MySQLpowershellscripting

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

Best Answer

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

Related Solutions

Powershell – Printer monitoring script (PowerShell)

You probably do not want to try to grovel thru logs like a unix admin to try to get up to date on printer states. Wouldn't it be nice if there was a way to get notified of a printer event instead. The good news is that in windows there is. I'd first read this article: Notify with WMI. then take a look at Windows PowerShell 2.0 CTP2 WMI Event Monitoring

I tested this simple powershell script:

$query = "Select * From __InstanceCreationEvent within .1 Where TargetInstance ISA 'Win32_printjob'"
$action = write-host "Printed Document status" (gwmi win32_printjob).status ", time" (gwmi win32_printjob).timesubmitted
register-wmievent -query $query -action $action -sourceidentifier "printerwatcher"

You shuold get an output line when you print something

Powershell Foreach-Object with if statement not working – help!

OK, so I've been tinkering with this one for a bit and got it working. At first, it looked like the script was running as expected, except that it would show an error message in the console whenever the reg query command didn't find the reg key that it was looking for. That in itself was not an issue, but after that I would have expected it to continue and run the if code block - instead it seemed to be skipping right on to processing the next object in the foreach-object queue from the pipeline. I am not sure what was going on there, but eventually I got the code to the point where it was evaluating everything correctly and trying to run the if code block. This time, I was getting the following error whenever the script hit an Out-Host statement: Out-Host : A positional parameter cannot be found that accepts argument mystringvar. Apparently there is something about using Out-Host that causes ForEach-Object to think that the processing code block is done. Anyway, I overcame this by switching to using Write-Output instead. Here's the final working code:

import-module bsonposh
add-pssnapin Quest.ActiveRoles.ADManagement

get-qadcomputer -sizelimit 0 -searchroot 'OU=MyComputers,DC=MyDomain,DC=lcl' | test-host -property name |ForEach-Object -process {
    $targnm = $_.name
    $tststr=(reg query "\\$targnm\HKLM\SOFTWARE\JavaSoft\Java Runtime Environment" /v Java6FamilyVersion)
    if(-not($tststr |select-string -simplematch '1.6.0_20'))
    {
        $mssg="Updating to JRE 6u20 on $targnm"
        Write-Output $mssg
        Out-File -filepath c:\install_jre_log.txt -inputobject $mssg -Append
        cmd /c \\myfileserver\apps\java\installjreremote.cmd $targnm
    }
    else
    {
        $mssg ="JRE 6u20 found on $targnm"
        Write-Output $mssg
        Out-File -filepath c:\install_jre_log.txt -inputobject $mssg -Append
    }
}

Best Answer

Related Solutions

Powershell – Printer monitoring script (PowerShell)

Powershell Foreach-Object with if statement not working – help!

Related Topic