时间戳

首页 > 解决方案 > Powershell SQLConnection JSON Insert 适用于 PS5 但不适用于 PS7

json - Powershell SQLConnection JSON Insert 适用于 PS5 但不适用于 PS7

问题描述

所以我有一个 powershell 脚本，它调用一个 web api，它以 json 格式返回数据，我在其中将数据推送到 sql server 中的一个表中。运行时环境是 Windows Server 2016，脚本在 Powershell 5 中运行良好，但是当我在 Powershell 7 中运行脚本时，出现数据处理错误，尤其是在名称（或在 POC 字段下方的示例中）这样的数据周围在其中引用。（即鲍勃奥康纳）。我尝试了一些方法来转义 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'
);

标签： jsonsql-serverpowershell.net-core

解决方案

笔记：

  • 值得退后一步并考虑AlwaysLearning的建议：“您的代码对 SQL 注入非常开放。您应该使用SqlParameter{0} ，而不是将 JSON 复制到您的语句中，然后您不必考虑转义字符。”INSERTnvarchar(max) '

至于你眼前的问题：

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

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

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

推荐阅读