PowerShell脚本写入SQL时含撇号的值处理失败求助

问题根源 #

你当前的代码存在两个核心问题：

1. 手动转义逻辑无效：Insert()方法会返回新字符串，但你没有将结果赋值回原变量，导致原数据里的撇号根本没被修改。
2. SQL语句拼接风险：直接用字符串拼接生成SQL不仅容易因特殊字符触发语法错误，还存在严重的SQL注入风险。

正确解决方案：使用参数化查询 #

参数化查询是处理SQL特殊字符和注入风险的标准方案，无需手动转义任何字符。以下是修改后的代码：

$csvData = Import-Csv -Path $moveToSQL.Item($iterator)
$correctCols = $csvData | Select-Object 'NPI', 'Provider Last Name (Legal Name)', 'Provider First Name', 'Provider Middle Name', 'Provider Credential Text'

# 预定义参数化SQL语句（用@占位符代替硬编码值）
$SqlQuery = @"
IF NOT EXISTS (SELECT 1 FROM my.dbo.table WHERE NPINum = @NPI)
    BEGIN
        INSERT INTO my.dbo.table (NPINum, LastName, FirstName, MiddleName, Credentials)
        VALUES (@NPI, @LastName, @FirstName, @MiddleName, @Credentials)
    END
ELSE
    IF NOT EXISTS
            (SELECT 1 FROM my.dbo.table
            WHERE NPINum = @NPI AND LastName = @LastName
            AND FirstName = @FirstName
            AND MiddleName = @MiddleName
            AND Credentials = @Credentials)
        BEGIN
            UPDATE my.dbo.table
            SET LastName = @LastName, FirstName = @FirstName, MiddleName = @MiddleName, Credentials = @Credentials
            WHERE NPINum = @NPI;
        END
"@

$SqlCommand.CommandText = $SqlQuery

foreach ($row in $correctCols) {
    # 清空之前的参数（避免循环中参数重复）
    $SqlCommand.Parameters.Clear()

    # 添加参数，无需手动处理撇号
    $SqlCommand.Parameters.AddWithValue("@NPI", $row.NPI)
    $SqlCommand.Parameters.AddWithValue("@LastName", $row.'Provider Last Name (Legal Name)')
    $SqlCommand.Parameters.AddWithValue("@FirstName", $row.'Provider First Name')
    $SqlCommand.Parameters.AddWithValue("@MiddleName", $row.'Provider Middle Name')
    $SqlCommand.Parameters.AddWithValue("@Credentials", $row.'Provider Credential Text')

    # 执行命令（ExecuteNonQuery更适合INSERT/UPDATE类写入操作）
    $SqlConnection.Open()
    $SqlCommand.ExecuteNonQuery()
    $SqlConnection.Close()
}

关键修改说明 #

• 移除手动转义代码：参数化查询会自动处理所有特殊字符（包括撇号、引号等），无需手动干预。
• 使用参数占位符：SQL语句中用@参数名代替具体值，彻底避免字符串拼接的语法错误和注入风险。
• 循环中清空参数：如果复用同一个SqlCommand对象，每次循环必须清空之前的参数集合，防止参数冲突。
• 改用ExecuteNonQuery：你的操作是数据写入，不需要读取结果，ExecuteNonQuery比ExecuteReader更高效且符合场景。

额外优化建议 #

• 可以将数据库连接的打开/关闭移到循环外，只打开一次连接，循环执行命令后再关闭，大幅提升批量操作的性能。
• 对于可能为null的字段（比如中间名），可以添加空值处理逻辑，避免参数传递错误。

内容的提问来源于stack exchange，提问作者Avarsiral