sql - 参数化的SQL查询语法不正确

Question

我理解参数化SQL查询的方式是避免转义字符错误的方式。但是我仍然遇到这个问题。

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()
}

但是，当名称中包含 '时，我仍然会收到错误消息:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near 's'.

$user.physicalDeliveryOfficeName的值为“Park的avonds”。

Answer 1

您根本没有使用准备好的语句(参数化查询)。使用format运算符将字符串插入字符串模板与通过串联构建语句没有什么不同。

您的代码应如下所示:

$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()
}