Invoke-SQLCMD with special characters

Question

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.

Answer 1

A parametrized query will help you here. It's a well-established way to create queries that protect against SQL injections and often improve performance too. The advantage in your case is that parametrization handles string escaping, so you don't need to worry about passing an apostrophe.

As a side note, depending on your database, trying to escape strings might be fool's errand, as PHP/MySql's notorious mysql_real_escape_string() proves with attack opportunities. Writing secure code is hard, and any database is vulnerable to attacks via poor development practices.

Using parameters requires one to build a .Net SqlCommand and add parameters into its Parameters collection.

The bad news is that configuring a SqlCommand requires working proficiency in .Net, and the documentation is huge. Relevant subsections are about commands and parameters. Static typing for parameters is possible, though implicit conversion works in many cases. For static types, see .Add() method's numerous overloads. Further develpoment questions and script improvement would be better on-topic at Stack Overflow.

In case of link rot, a sample from the web that seems to work fine. Note that it relies on implicit conversion.

function exec-query( $sql,$parameters=@{},$conn,$timeout=30,[switch]$help){
 if ($help){
 $msg = @"
Execute a sql statement.  Parameters are allowed.
Input parameters should be a dictionary of parameter names and values.
Return value will usually be a list of datarows.
"@
 Write-Host $msg
 return
 }
 $cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
 $cmd.CommandTimeout=$timeout
 foreach($p in $parameters.Keys){
    [Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
 }

 $ds=New-Object system.Data.DataSet
 $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
 $da.fill($ds) | Out-Null

 return $ds
}

$conn=new-object data.sqlclient.sqlconnection "Server=<MyServer>;initial catalog=<SomeDatabase>;Integrated Security=True"
$conn.open()
(exec-query "select column from dbo.mytable where column=@type" -parameter @{type=1} -conn $conn).tables
$conn.dispose()

Answer 2

If you have the option to use an alternative, the Invoke-DbaQuery command from the dbatools module makes this a quite a bit easier.

Given you've already...

create database [DB_Name]

use [DB_Name]

create table table_Name (
   CN varchar(100),
   Surname varchar(100)
);

...you can pass in a properly parameterised query as in the following example...

PS > $Userlist=@"
CN,Surname
Peter,Vandivier
Paddy,Mc'Laggen
Bobby,Tables'; Drop Table table_name; --
"@ |ConvertFrom-Csv


PS > foreach($user in $Userlist){
    Invoke-DbaQuery `
        -Database 'DB_Name' `
        -SqlInstance 'localhost' `
        -Query 'Insert [table_name] (CN,Surname) select @CN, @Surname;' `
        -SqlParameters @{CN=$user.CN;Surname=$user.Surname}
}
PS > Invoke-DbaQuery `
        -SqlInstance 'localhost' `
        -Query 'select * from table_name;' `
        -Database 'DB_Name'

CN    Surname
--    -------
Peter Vandivier
Paddy Mc'Laggen
Bobby Tables'; Drop Table table_name; --


PS >