I'm currently working on a project integrating AD with SQL Server data, which I am handling mainly via powershell (Invoke-SQLCMD in particular). The issue I'm running into is if someone has a special character in a field in AD, it's throwing an error when I'm trying to run my insert.
$OuListing = 'OU=FirstOU,DC=example,DC=com','OU=SecondOU,DC=example,DC=com'
$UserList = $OuListing | ForEach { get-aduser -filter * -SearchBase $_ -properties * -SearchScope 2 | where {$_.Enabled -eq $True} | Select CN,Surname }
$database = 'DB_Name'
$server = 'Server_Name'
$table = 'dbo.table_Name'
Invoke-SQLCMD -Database $Database -ServerInstance $Server -Query "Truncate Table $Table"
ForEach ($User in $Userlist)
{
Invoke-SQLCMD -Database $Database -ServerInstance $Server -Query "Insert into $Table (CN,Surname) Values ('$($User.CN)','$($User.Surname)')"
}
This code works fine 99% of the time, but like mentioned, if an AD users last name is "Mc'Laggen", the apostrophe in it throws things off. Is there any good way to pass through the values like this to make it work, or should I be parsing through each attribute and add in escape characters where there's anything special? In the actual script, there are approximately 20 attributes so I was wondering if there was a simpler method of parsing through.
-replace "'", "''"
or something? – mustaccio Oct 22 '19 at 18:52$hello = 'tést'; Invoke-SQLCMD -Database $Database -ServerInstance $Server -Query "SELECT i,k FROM test WHERE k='$hello' and i<10"
– Luuk Oct 22 '19 at 19:25