Querying MYSQL DB with powershell variables

Question

I am trying to parse through data from a MYSQL DB into powershell and then use that data to update AD accounts based off of a powershell variables & upn but I cannot get the MySQL data and powershell variable to function properly. I am not a SQL guy so not sure if it is with MYSQL syntax or the way the powershell script is written.

This is the command at the end of the script I can't get to function but I included the whole code below this

$mysqlquery1 =  Invoke-MySqlQuery -Connection $oConnection  -Query 'Select * from user_informations where login='"$user.sAMAccountName"

#
# Script.ps1
#
$GetUsersemail = Get-ADUser -Filter * -Properties mail, sAMAccountName, userPrincipalName -SearchBase "OUNAME"| Where-Object {($_.mail -ne $null)} | Select-Object mail,sAMAccountName,userPrincipalName

Import-Module activedirectory
Set-Location C:\MySQL\MySQL-master
Unblock-File -Path .\MySQL.psm1
Import-Module .\mysql.psm1

[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
[string]$sMySQLUserName = sqluserid
[string]$sMySQLPW = 'password'
[string]$sMySQLDB = 'DB'
[string]$sMySQLHost = 'IP'
[string]$sConnectionString = "server="+$sMySQLHost+";port=3306;uid=" + $sMySQLUserName + ";pwd=" + $sMySQLPW + ";database="+$sMySQLDB+";SslMode=none"

$oConnection = New-Object MySql.Data.MySqlClient.MySqlConnection($sConnectionString)
$Error.Clear()

try

{
    $oConnection.Open()
    write-warning ("it connected")
$dataSet = New-Object System.Data.DataSet
}
catch
{
    write-warning ("Could not open a connection to Database $sMySQLDB on Host $sMySQLHost. Error: "+$Error[0].ToString())
}




ForEach ($user in $GetUsersemail) {
    $spn = $null
    $fullemail = $null
    $spnsplit = $user.mail.split("@")
    $spn = "@" + $spnsplit[1]
    $fullemail = $user.sAMAccountName + $spn

        if ($fullemail -ne $user.userPrincipalName) {


    Switch ($spn) {
            "@upn1.com" {
                Set-ADUser -UserPrincipalName $fullemail -Identity $user.sAMAccountName
                           }
            "@upn2.com" {   
                    Set-ADUser -UserPrincipalName $fullemail -Identity $user.sAMAccountName
                             }
             "@upn3" { 
                    Set-ADUser -UserPrincipalName $fullemail -Identity $user.sAMAccountName
                    }
            "@upn4" {
                    Set-ADUser -UserPrincipalName $fullemail -Identity $user.sAMAccountName
                    }
             "@upn5" {
                    Set-ADUser -UserPrincipalName $fullemail -Identity $user.sAMAccountName
                    }
              "@upn6" {
                    Set-ADUser -UserPrincipalName $fullemail -Identity $user.sAMAccountName
                    }
              "@upn7" {
                    Set-ADUser -UserPrincipalName $fullemail -Identity $user.sAMAccountName
                   }
               "@upn8" {
                    Set-ADUser -UserPrincipalName $fullemail -Identity $user.sAMAccountName
                    }
                    }
                    }

               $mysqlquery1 =  Invoke-MySqlQuery -Connection $oConnection  -Query 'Select * from user_informations where login='"$user.sAMAccountName" 
    }

   $oConnection.Close()

Just out of curiosity, the switch statement diversifies but executes the very same Set-ADUser ? Couldn't you then simply omit the switch? — user6811411
– user6811411, Commented Jul 25, 2018 at 16:16

TessellatingHeckler · Accepted Answer · 2018-07-25 16:02:30Z

2

You need to be careful with PowerShell string quoting; single quoted strings 'abc' cannot have variables inside them, double quoted strings "abc" can, e.g. "abc $xyz" but you can't use "abc $xyz.property" because PowerShell treats the .property as part of the text, rather than part of the variable. For that, you need a subexpression, "abc $($xyz.property)"

Depending on how you need the string to end up being quoted, try:

"select * from user_informations where login='$($user.sAMAccountName)'"

Double quotes on the outside, so you can put single quotes and subexpressions inside. That should come out like:

select * from user_informations where login='john.hancock'

(Should that be user_information, singular?)

answered Jul 25, 2018 at 16:02

TessellatingHeckler

29.3k4 gold badges55 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JJM Over a year ago

Thanks, this looks like it will work, just have more testing to perform on it. user_informations is actually correct.. Not sure who named that DB.

JJM Over a year ago

I don't know MYSQL all that well but the resolution above did work but the variable needed to be in on caps. So ($USER.SAMACCOUNTNAME), other wise the query came back empty, hopefully this last bit of information will help other people trying to write a similar script. The final command i used is below.Invoke-MySqlQuery -Connection $oConnection -Query "select * from user_informations where login='$($USER.SAMACCOUNTNAME)'"

Collectives™ on Stack Overflow

Querying MYSQL DB with powershell variables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related