PowerShell向SQL传值时如何转义字符串中的单引号？

方法一：快速转义单引号 #

SQL语法中，字符串内的单引号需要用两个单引号表示实际的单引号，修改变量赋值逻辑即可解决语法错误：

$PathArowCount=0
$filelist = Get-ChildItem -Path $localPath -Recurse | Where-Object {!($_.PSIsContainer)} | Select DirectoryName, Name, Length, LastWriteTime 
ForEach ($file in $filelist)
{
    # 转义字符串中的单引号，数值类型Length无需加单引号
    $insFolder = "`'$($file.DirectoryName.Replace("'", "''"))`'"
    $insFile = "`'$($file.Name.Replace("'", "''"))`'"
    $insLength = $file.Length
    $insLastWrite = "`'$($file.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss"))`'"
    
    write-host "$insFolder`n$insFile`n$insLength`n$insLastWrite`n---"
    
    $sqlCommand.CommandText = "INSERT INTO [$dbCompare].[dbo].[PathA] (FilePath,FileName,FileLength,LastWriteTime) VALUES ($insFolder,$insFile,$insLength,$insLastWrite)"
    $PathArowCount = $PathArowCount + $sqlCommand.ExecuteNonQuery()
} 
write-output "$(Get-Date) - $PathArowCount rows (eg files found) inserted into table PathA"

注意：Length是数值类型，不需要用单引号包裹，之前的写法会引发类型不匹配问题，此处一并修正。

方法二：参数化查询（推荐方案） #

直接拼接SQL字符串不仅容易触发语法错误，还存在SQL注入风险，参数化查询能让SQL Server自动处理特殊字符，彻底规避这类问题：

$PathArowCount=0
$filelist = Get-ChildItem -Path $localPath -Recurse | Where-Object {!($_.PSIsContainer)} | Select DirectoryName, Name, Length, LastWriteTime 

# 预先定义参数化SQL模板，避免循环中重复生成
$sqlCommand.CommandText = @"
INSERT INTO [$dbCompare].[dbo].[PathA] (FilePath,FileName,FileLength,LastWriteTime)
VALUES (@FilePath, @FileName, @FileLength, @LastWriteTime)
"@

ForEach ($file in $filelist)
{
    # 清除上一次循环的参数，避免累积冲突
    $sqlCommand.Parameters.Clear()
    
    # 添加对应参数，无需手动处理特殊字符
    $sqlCommand.Parameters.AddWithValue("@FilePath", $file.DirectoryName)
    $sqlCommand.Parameters.AddWithValue("@FileName", $file.Name)
    $sqlCommand.Parameters.AddWithValue("@FileLength", $file.Length)
    $sqlCommand.Parameters.AddWithValue("@LastWriteTime", $file.LastWriteTime)
    
    write-host "$($file.DirectoryName)`n$($file.Name)`n$($file.Length)`n$($file.LastWriteTime)`n---"
    $PathArowCount += $sqlCommand.ExecuteNonQuery()
} 
write-output "$(Get-Date) - $PathArowCount rows (eg files found) inserted into table PathA"

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