如何在PowerShell中捕获异常值并写入SQL数据库？

问题根源分析 #

1. 异常未触发catch块：原代码中Test-Path仅返回布尔值，不会抛出终止异常；即使取消注释New-Item，默认情况下它抛出的是非终止错误，无法触发catch逻辑，导致$status和$msg未被赋值，最终插入SQL时为空。
2. 变量未初始化/定义：$date变量未赋值，插入时该字段为空；$status和$msg未提前初始化，若未进入任何分支会处于未定义状态。
3. SQL字符串拼接风险：直接拼接变量生成SQL语句，不仅存在注入风险，变量为空时还可能引发语法错误。

修正方案及代码 #

核心改进点

• 强制将非终止错误转为终止错误，确保异常能被catch捕获
• 提前初始化变量，避免未定义状态
• 使用参数化查询传递数据值，避免SQL注入并保证值正确传递
• 定义$date变量，确保日期字段有值

# 提前初始化变量，避免未定义状态
$status = $null
$msg = $null
$date = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
$ComputerName = $env:COMPUTERNAME

try {
    try {
        $path = "D:\$name"
        "$date:Folder Name : $name" | Out-File $LogFile -Append
        
        # 检查路径，若不存在则创建（添加-ErrorAction Stop触发终止异常）
        if (Test-Path -Path $path) {
            $status = 'already exists'
            "$date:Folder Already Exists" | Out-File $LogFile -Append
            Write-Output $status
            $msg = 'Already Existed'
        } else {
            New-Item -Path $path -ItemType Directory -ErrorAction Stop
            "$date:Folder Created" | Out-File $LogFile -Append
            $status = 'Success'
            $msg = 'Successfully Created'
        }
    } catch {
        $status = 'Failed'
        Write-Host "`nError Message: " $_.Exception.Message
        Write-Host "`nError in Line: " $_.InvocationInfo.Line
        Write-Host "`nError in Line Number: " $_.InvocationInfo.ScriptLineNumber
        Write-Host "`nError Item Name: " $_.Exception.ItemName
        "$date:$($_.Exception.Message)" | Out-File $LogFile -Append 
        $msg = $_.Exception.Message
    }

    # SQL操作使用参数化查询，避免注入和空值问题
    $connectionString = "Server=YOUR_SERVER_NAME;Database=$Database;Integrated Security=True;"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $connection.Open()

    # 检查表是否存在
    $checkQuery = "SELECT COUNT(1) AS TABLECOUNT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = @TableName"
    $checkCmd = $connection.CreateCommand()
    $checkCmd.CommandText = $checkQuery
    $checkCmd.Parameters.AddWithValue("@TableName", $name)
    $tableCount = $checkCmd.ExecuteScalar()

    # 不存在则创建表（表名无法参数化，需确保$name安全）
    if ($tableCount -eq 0) {
        $createQuery = "CREATE TABLE [dbo].[$name] (DATE NVARCHAR(MAX),HOST_NAME NVARCHAR(MAX),STATUS NVARCHAR(MAX),MESSAGE NVARCHAR(MAX))"
        $createCmd = $connection.CreateCommand()
        $createCmd.CommandText = $createQuery
        $createCmd.ExecuteNonQuery()
    }

    # 插入数据（参数化传递所有字段值）
    $insertQuery = "INSERT INTO [$Database].[dbo].[$name] ([DATE],[HOST_NAME],[STATUS],[MESSAGE]) VALUES (@Date, @ComputerName, @Status, @Message)"
    $insertCmd = $connection.CreateCommand()
    $insertCmd.CommandText = $insertQuery
    $insertCmd.Parameters.AddWithValue("@Date", $date)
    $insertCmd.Parameters.AddWithValue("@ComputerName", $ComputerName)
    $insertCmd.Parameters.AddWithValue("@Status", $status)
    $insertCmd.Parameters.AddWithValue("@Message", $msg)
    $insertCmd.ExecuteNonQuery()

    $connection.Close()
} catch {
    Write-Host "`nError Message: " $_.Exception.Message
    Write-Host "`nError in Line: " $_.InvocationInfo.Line
    Write-Host "`nError in Line Number: " $_.InvocationInfo.ScriptLineNumber
    Write-Host "`nError Item Name: " $_.Exception.ItemName
    "$date:$($_.Exception.Message)" | Out-File $LogFile -Append 
}

额外说明 #

• 表名无法通过参数化查询传递，需确保$name变量来自可信来源，避免SQL注入风险。
• 始终使用参数化查询传递数据值，这是处理SQL插入的最佳实践，能正确处理特殊字符、空值等场景。
• 提前初始化变量可以保证即使代码未进入任何分支，变量也不会处于未定义状态，避免插入空值。

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