参数化SQL查询语法不正确 [英] Parameterized SQL Query Incorrect Syntax

问题描述

我理解参数化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()
}

但当名称中有'时，我仍然会收到错误：

使用"0"参数调用"ExecuteNonQuery"时出现异常：""%s"附近的语法不正确。

$user.physicalDeliveryOfficeName值为"Park的平均值"。

推荐答案

您根本没有使用预准备语句(参数化查询)。使用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()
}

这篇关于参数化SQL查询语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！