Andry Andry - 11 months ago 46
SQL Question

Parameterized SQL Query Incorrect Syntax

The way I understand parameterized SQL queries is the way to avoid escape character errors. Yet I still encounter this problem.

try {
import-module ActiveDirectory

$abc = get-aduser -ResultSetSize 9998 -Properties employeeid,samaccountname,Department,physicalDeliveryOfficeName,mail,useraccountcontrol,telephonenumber,cn,title,mobile,company,description,manager

$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "server=server.local;database=db;trusted_connection=true;"
$connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection

#$command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@department",[Data.SQLDBType]::VarChar, 250)))
#$command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@physicalDeliveryOfficeName",[Data.SQLDBType]::VarChar, 200)))

foreach ($user in $abc){

$command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@physicalDeliveryOfficeName",[Data.SQLDBType]::VarChar, 200))).value = $user.physicalDeliveryOfficeName
$command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@department",[Data.SQLDBType]::VarChar, 250))).value = $user.Department
#$Command.Parameters['@department'].value = $user.Department
#$Command.Parameters['@physicalDeliveryOfficeName'].value = $user.physicalDeliveryOfficeName

#if (!$user.department) { $Command.Parameters['@department'].value = [System.DBNull]::Value }
#if (!$user.physicalDeliveryOfficeName) { $Command.Parameters['@physicalDeliveryOfficeName'].value = [System.DBNull]::Value }

$insert = "INSERT INTO [Database].[ad].[UserAccountsT] (employeeid,samaccountname,distinguishedName,givenname,sn,title,department,physicaldeliveryofficename,email,telephoneNumber,mobile,company,description,useraccountcontrol,cn,manager) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')" -f $user.employeeid,$user.samaccountname,$user.DistinguishedName,$user.GivenName,$user.Surname,$user.title,$user.Department,$user.physicalDeliveryOfficeName,$user.mail,$user.telephonenumber,$user.mobile,$user.company,$user.description,$user.userAccountControl,$user.cn,$user.manager
$Command.CommandText = $insert
$command.ExecuteNonQuery() > $null
$command.Parameters.Clear()
}
}

catch { write-host Everything goes wrong at $_ for $user at $user.physicalDeliveryOfficeName $user.Department !!! }
finally { $connection.close() }


But when there is a
'
in the name I still get an error:
Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near 's'.

$user.physicalDeliveryOfficeName
value is "Park 's avonds"

Answer Source

You're not using a prepared statement (parameterized query) at all. Using the format operator for inserting strings into a string template is no different from building the statement by concatenation.

Your code should look somewhat like this:

$command = New-Object Data.SQLClient.SQLCommand
$command.Connection = $connection
$command.CommandText = 'INSERT INTO [table] (field1, field2) VALUES (@foo, @bar)'

foreach ($user in $abc) {
    $command.Parameters.Add((New-Object Data.SqlClient.SqlParameter('@foo', [Data.SQLDBType]::VarChar, 200))).Value = $user.physicalDeliveryOfficeName
    $command.Parameters.Add((New-Object Data.SqlClient.SqlParameter('@bar', [Data.SQLDBType]::VarChar, 250))).Value = $user.Department

    $command.Prepare()
    $command.ExecuteNonQuery() | Out-Null
    $command.Parameters.Clear()
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download