SQL 服务器 - 用于批量更新的 SQL 查询

Question

我需要使用文本文件更新表格。目前，如果我从 txt 文件执行Get-Content然后运行 SQL 更新查询，我的代码工作正常，但仅限于小数据的情况下。如果文本大小太长或包含一些特殊字符，则会引发如下错误：

使用"0"参数调用"ExecuteReader"的异常："语法不正确，接近'）

以下是我正在使用的代码：

Function DatabaseQueries(){
    #To connect to the SQL database
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "Server=$IPSource ; Database=$DBNameSource ; User ID=$UserIDSource ; Password=$LoginPwdSource;"
    $Connection.Open()
    #Query to get the ID of the stored script field from propertyentry 
    $Command1 = New-Object System.Data.SQLClient.SQLCommand
    $Command1.Connection = $Connection
    $Command1.CommandText = "SELECT [ID]    FROM [dbo].[propertyentry] WHERE [PROPERTY_KEY]='com.onresolve.jira.groovy.groovyrunner:customfields' "
    $Reader = $Command1.ExecuteReader() 
    while ($Reader.Read()) {
        $ID = $Reader.GetValue($1)
    }
    #To get the updated script file
    $ScriptDir = $ParentDir + 'Script.txt'
    $ScriptData = Get-Content "$ScriptDir"
    $Connection.Close()
    #Query to update the Script in JIRA database 
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = @"
    Update [dbo].[propertytext] set [propertyvalue] ='$ScriptData' Where ID=$ID
"@
    $Reader = $Command.ExecuteReader()
    $Connection.Close()
}

Answer 1

如果未指定文件内容和数据库结构，则很难编写完整的解决方案。您肯定遇到了某种SQL注入。SQL 查询串联被认为是有害的，您应该避免它。使用 ADO.NET 参数传递变量（在您的示例中$Command.Parameters.AddWithValue）。请参阅以下示例：

function Invoke-Sql(
    $ConnectionString,
    $Query,
    $Parameters
) {
    $conn = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
    $cmd = New-Object System.Data.SqlClient.SqlCommand -ArgumentList $Query,$conn
    $conn.Open()
    foreach ($arg in $Parameters.GetEnumerator()){
        $cmd.Parameters.AddWithValue($arg.Key, $arg.Value) | Out-Null;
    }
    $reader = $cmd.ExecuteReader()
    if ($reader.Read()) {
      [string[]]$columns = 0..($reader.FieldCount-1) |
          % { if ($reader.GetName($_)) { $reader.GetName($_) } else { "(no name $_)" } }
      do {
        $obj = @{}
        0..($reader.FieldCount-1) | % { $obj.Add($columns[$_], $reader[$_]) }
        New-Object PSObject -Property $obj
      } while ($reader.Read())
    }
    $reader.Dispose()
    $cmd.Dispose()
    $conn.Dispose()
}
Invoke-Sql `
    -ConnectionString "Server=.SQL2014;Database=Test1;Integrated Security=true" `
    -Query 'SELECT Name, Id [ObjectId], Id + 3, @arg FROM IdNameTest' `
    -Parameters @{arg = 'Some text'''}
Invoke-Sql `
    -ConnectionString "Server=.SQL2014;Database=Test1;Integrated Security=true" `
    -Query 'UPDATE IdNameTest SET Name=@name WHERE Id=@id' `
    -Parameters @{name = "'DROP DATABASE Death;! %&@!$"; id=1}

Answer 2

感谢您的回复，我已经找到了一种仅使用替换函数来执行查询的方法，因为它在单个倒逗号之间混淆了

select REPLACE(Cast(propertyvalue AS varchar(Max)), '''', '''''') FROM [dbo].[propertytext] WHERE ID=$ID