使用特殊字元呼叫 SQLCMD

我目前正在開發一個將 AD 與 SQL Server 數據集成的項目，我主要通過 powershell（尤其是 Invoke-SQLCMD）處理該項目。我遇到的問題是，如果有人在 AD 中的某個欄位中有特殊字元，那麼當我嘗試執行插入時它會引發錯誤。

$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)')"
}

這段程式碼在 99% 的情況下都可以正常工作，但就像前面提到的，如果 AD 使用者的姓氏是“Mc’Laggen”，那麼其中的撇號就會把事情搞砸。有沒有什麼好的方法可以通過這樣的值來使其工作，或者我應該解析每個屬性並在有什麼特別之處添加轉義字元？在實際腳本中，大約有 20 個屬性，所以我想知道是否有更簡單的解析方法。

參數化查詢將在這里為您提供幫助。這是一種行之有效的創建查詢的方法，可以防止 SQL 注入並經常提高性能。您的情況的優點是參數化處理字元串轉義，因此您不必擔心傳遞撇號。

作為旁注，根據您的數據庫，嘗試轉義字元串可能是愚蠢的差事，因為 PHP/MySql 的臭名昭著mysql_real_escape_string()證明了攻擊機會。編寫安全程式碼很困難，任何數據庫都容易受到不良開發實踐的攻擊。

使用參數需要建構一個 .NetSqlCommand並將參數添加到其Parameters集合中。

壞消息是配置 aSqlCommand需要熟練使用 .Net，而且文件非常龐大。相關小節是關於命令和參數的。參數的靜態類型是可能的，儘管隱式轉換在許多情況下都有效。對於靜態類型，請參閱.Add()方法的眾多重載。進一步的開發問題和腳本改進將在Stack Overflow上得到更好的主題。

在連結失效的情況下，來自網路的範例似乎可以正常工作。請注意，它依賴於隱式轉換。

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

如果您可以選擇使用替代方法，則dbatoolsInvoke-DbaQuery模組中的命令會使這變得容易一些。

鑑於你已經…

create database [DB_Name]

use [DB_Name]

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

…您可以傳遞正確參數化的查詢，如下例所示…

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 >

引用自：https://dba.stackexchange.com/questions/251722