Powershell SQLConnection JSON插入在PS5中工作，但在PS7中不工作

Question

因此，我有一个powershell脚本，该脚本调用一个web，该脚本以json的形式返回数据，在其中我获取并推入sql server中的一个表中。运行时环境是Windows Server 2016，脚本在Powershell 5中运行良好，但是当我在Powershell 7中运行脚本时，我会得到数据处理错误，特别是在包含单引号的名称(或者在POC字段下面的示例中)数据周围。(即Bob O‘’Connor)。我尝试了几种方法来转义sql中的数据，并且希望避免在插入之前必须解析PS中的所有响应。

是否有人知道PS 5和7之间sql客户端的差异，这会导致数据处理中的这种变化，或者可能是修复它的一种简单方法？

这是相关的PS代码。

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = 'myConnString'
$sqlConn.Open()
$sqlcmd = $sqlConn.CreateCommand()
$sqlcmd.Connection = $sqlConn
$response = Invoke-RestMethod $URL -Method 'GET' -Headers $headers
$respjson = $response | ConvertTo-Json -Depth 10 -Compress
$sqlcmd.CommandText = $InsertStatement -f $respjson
$result = $sqlcmd.ExecuteNonQuery()

这是用于将json记录大容量插入表中的sql脚本。

DECLARE @JSON NVARCHAR(MAX);
SET @JSON = N'{0}';
INSERT INTO MyTable
SELECT * FROM OPENJSON (@JSON, N'$.result')
WITH ( 
Location    VARCHAR(10) '$.u_location',
ContactVerified VARCHAR(5)  '$.u_contact_verified',
POCType     VARCHAR(20) '$.u_type_of_poc',
POC         VARCHAR(50) '$.u_poc',
Active      VARCHAR(5)  '$.u_active'
);

Answer 1

注意：

• 值得退一步，考虑一下AlwaysLearning的建议：“您的代码很容易被SQL注入。与其在INSERT语句中将JSON复制到{0}中，您应该使用nvarchar(max) SqlParameter，这样就不必考虑转义'字符了。”

至于你眼前的问题：

虽然ConvertTo-Json在PowerShell (Core) 7中的行为原则上可能有所不同(因为底层实现相对于PowerShell发生了变化)，但听起来您的问题与此无关：

要使生成的SQL命令在语法上工作，需要将JSON字符串中的'字符转义为''。

$sqlcmd.CommandText = $InsertStatement -f ($respjson -replace "'", "''")

Answer 2

基于以前的回答和评论的反馈。下面是我修改后的代码，它转换成sql参数供将来的访问者使用。

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = 'myConnString'
$sqlConn.Open()
$sqlcmd = $sqlConn.CreateCommand()
$sqlcmd.Connection = $sqlConn
$response = Invoke-RestMethod $URL -Method 'GET' -Headers $headers
$respjson = $response | ConvertTo-Json -Depth 10 -Compress
#$sqlcmd.CommandText = $InsertStatement -f $respjson
$sqlcmd.CommandText = $InsertStatement
$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter('@JSON', [Data.SqlDbType]::NVarChar, -1))).Value = $respjson
$sqlcmd.Prepare()
$result = $sqlcmd.ExecuteNonQuery()
$sqlcmd.Parameters.Clear()

添加SQL文件更新

INSERT INTO MyTable
SELECT * FROM OPENJSON (@JSON, N'$.result')
WITH ( 
Location   VARCHAR(10) '$.u_location',
ContactVerified VARCHAR(5)  '$.u_contact_verified',
POCType     VARCHAR(20) '$.u_type_of_poc',
POC         VARCHAR(50) '$.u_poc',
Active      VARCHAR(5)  '$.u_active'
);

我测试了这种方法和mklement0推荐的方法，性能基本相同。