PowerShell中为SQL的BETWEEN子句使用变量的问题求助

问题原因 #

你的代码直接将$starttime和$endtime变量插入SQL语句，但生成的SQL里日期值没有被单引号包裹，导致SQL解析时无法识别为日期类型，进而执行失败。而写死'2023-05-01'这类带单引号的日期时，SQL能正确识别，所以可以正常运行。另外，直接拼接字符串到SQL语句存在SQL注入风险，更安全的方式是使用参数化查询。

解决方案 #

方案1：临时修复（字符串拼接时添加单引号） #

在拼接SQL语句时，给日期变量加上单引号，确保SQL能识别为日期常量：

$starttime = '2023-05-01'
$endtime = '2023-05-10'

$SQLServer = "HostName"
$SQLDBName = "Historian"

# 给日期变量添加单引号，确保SQL解析为日期值
$SqlQuery = "SELECT CONVERT(VARCHAR(10),DATEADD(mi,$GMTOffset,EventDate),103) As DATE, 
                    CONVERT(VARCHAR(20),DATEADD(mi,$GMTOffset,EventDate),114) As TIME
                    FROM [Historian].[dbo].[S2KEventReport_View] 
                    WHERE (DATEADD(mi,330,EventDate) BETWEEN '$starttime' AND '$endtime')"

# 后续连接执行代码不变
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$Dataset = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$EventExportPath = "临时导出路径.csv" # 确保该变量已定义有效路径
$DataSet.Tables[0] | Export-Csv -Path $EventExportPath -NoTypeInformation
$filenameFormat = (Get-Date -Format "dd-MM-yyyy_HH.mm.ss_") + " S2KEvents.csv"
Rename-Item -Path $EventExportPath -NewName $filenameFormat

方案2：推荐使用参数化查询（更安全可靠） #

通过SqlParameter传递参数，既避免SQL注入，又确保日期类型被正确解析：

$starttime = '2023-05-01'
$endtime = '2023-05-10'

$SQLServer = "HostName"
$SQLDBName = "Historian"

# 定义带参数占位符的SQL语句
$SqlQuery = "SELECT CONVERT(VARCHAR(10),DATEADD(mi,@GMTOffset,EventDate),103) As DATE, 
                    CONVERT(VARCHAR(20),DATEADD(mi,@GMTOffset,EventDate),114) As TIME
                    FROM [Historian].[dbo].[S2KEventReport_View] 
                    WHERE (DATEADD(mi,330,EventDate) BETWEEN @StartDate AND @EndDate)"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

# 添加参数，指定类型和值，确保SQL正确解析
$SqlCmd.Parameters.Add("@GMTOffset", [System.Data.SqlDbType]::Int).Value = $GMTOffset
$SqlCmd.Parameters.Add("@StartDate", [System.Data.SqlDbType]::Date).Value = $starttime
$SqlCmd.Parameters.Add("@EndDate", [System.Data.SqlDbType]::Date).Value = $endtime

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$Dataset = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$EventExportPath = "临时导出路径.csv" # 确保该变量已定义有效路径
$DataSet.Tables[0] | Export-Csv -Path $EventExportPath -NoTypeInformation
$filenameFormat = (Get-Date -Format "dd-MM-yyyy_HH.mm.ss_") + " S2KEvents.csv"
Rename-Item -Path $EventExportPath -NewName $filenameFormat

额外注意点 #

• 确保$GMTOffset变量已正确定义并赋值，否则SQL中的DATEADD函数会报错。
• 确保$EventExportPath变量指定了有效输出路径，否则Export-Csv会执行失败。
• 参数化查询是数据库操作的最佳实践，既能避免语法错误，又能有效防止SQL注入攻击。

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