powershell 参数化SQL查询语法错误

roejwanj 于 2023-06-23 发布在 Shell

关注(0)|答案(2)|浏览(134)

我理解参数化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的avonds”。

powershell

来源：https://stackoverflow.com/questions/45736097/parameterized-sql-query-incorrect-syntax

2条答案

按热度按时间

liwlm1x91#

您根本没有使用预准备语句（参数化查询）。使用格式运算符将字符串插入到字符串模板中，与通过串联构建语句没有什么不同。
你的代码应该看起来有点像这样：

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

赞(0）回复(0）举报 2023-06-23

yduiuuwa2#

这个答案并不意味着要取代Ansgar的，但是关于语法，我想和可能欣赏它的人分享一个不错的替代方案。using namespace用于简写中的类型。

using namespace System.Data
using namespace System.Data.SqlClient

$Command = [SqlCommand]::new()
$Command.Connection = $Connection

# Set values
$Command.Parameters.Add('@foo', [SqlDbType]::VarChar, 200).Value = 'foo'
$Command.Parameters.Add('@bar', [SqlDbType]::VarChar, 200).Value = 'bar'

# Show values
$Command.Parameters | Select-Object -Property ParameterName, Value

# Console Output
#
# > ParameterName Value               
# > ------------- -----               
# > @foo          foo                 
# > @bar          bar

赞(0）回复(0）举报 2023-06-23

我来回答

powershell 参数化SQL查询语法错误

2条答案

相关问题

热门标签

最新问答