用 PowerShell 安全優雅地跑 SQL - 連線字串加密與類 Dapper 寫法
0 |
在 PowerShell 可以引用 .NET 程式庫,因此就算想要用它連資料庫跑 SQL 指令,也不是什麼難事。建立 SqlConnection、SqlCommand,ExecuteReader() 讀結果,ExecuteNonQuery() 跑更新,三兩下就能搞定。
但在 .NET 習慣了 Dapper 的簡潔輕巧,建 SqlParameter 傳參數讓人煩躁,但實在也不想再寫「串參數組 SQL 字串」的低級程式。。 (擔心小朋友有樣學樣搞出 SQL Injection。參考:你的網站正在裸奔嗎?)
想在 PowerShell 引用 Dapper,載入 DLL 不難,但 Dapper 跟 LINQ 重度依賴擴充方法,PowerShell 要引用也是可以,但寫法複雜到想吐,失去簡潔易用的原意。參考:PowerShell 裡可以寫 .NET LINQ 嗎?
為此,我試著在 PowerShell 實現類似 Dapper 用物件屬性傳參數的簡潔寫法,甚至能支援 WHERE Id IN @ids 傳參數陣列,感覺是不是很酷。
query "SELECT * FROM PSTEST WHERE Id IN `@ids" ([PSCustomObject]@{ ids = (1, 3) })
另外,關於連線字串,每次輸入帳號密碼不方便,但我也無法接受明碼儲存。PowerShell SecureString 限定主機及使用者使用,加密內容外流也難以解密,若沒有更安全的自訂連線字串儲存機制,不失為保存連線字串的簡易選擇。參考:淺談 PowerShell 中的密碼字串加密處理
綜合以上,這篇將展示如何使用 SecureString 儲存 SQL 連線字串,以及在 PowerShell 用類似 Dapper 的寫法查詢及更新資料庫。
Encrypt-ConnString.ps1 是一支小程式,輸入 Data Source、Initial Catalog、User Id、Password (或指定 Integrated Security=SSPI) 後加密儲存成檔案供重複使用。連線字串使用 SecureString / DPAPI 加密,在同一台主機使用相同帳號登入才能解開,方便將帳號密碼儲存起來重複使用。
$dataSource = Read-Host "Data Source"
$initCatalog = Read-Host "Initial Catalog"
$sspi = (Read-Host "Use Integrated Security? (Y/N)") -ieq 'y'
function secStrToClearText($secString) {
$BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($secString)
return [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)
}
if (!$sspi) {
$userId = Read-Host "User Id"
$passwd = secStrToClearText (Read-Host "Password" -AsSecureString)
}
$path = Read-Host "Please input the file name of encrypted connection string"
$cnStr = "data source=$dataSource;initial catalog=$initCatalog;"
if ($sspi) {
$cnStr += "integrated security=sspi"
}
else {
$cnStr += "user id=$userId;password=$passwd"
}
ConvertTo-SecureString -String $cnStr -AsPlainText -Force | ConvertFrom-SecureString | Out-File $path
在 PowerShell 仿效 Dapper 以匿名型別物件傳 SQL 參數沒想像來得困難,以 PSCustomObject 定義匿名型別,跑迴圈逐一將屬性轉成 SqlParameter。若資料值為陣列,遇到 WHERE Id IN @ids 則跑迴圈加入 @id1、@id2、@id3 等 SqlParameter,再將 @ids 換成 (@id1, @id2, @id3)。前面提到的參數會用 AddParameterWithValue() 讓 ADO.NET 自動對映參數資料型別,若有特殊需求,亦可自建 SqlParameter 傳入。
另外,函式也能共用外部傳入的 SqlConnection,並可啟用 Transaction 交易,支援 Rollback。多說無益,直接看程式吧!
param (
[Parameter(Mandatory = $true)][string]$encConnStrFile
)
$ErrorActionPreference = 'STOP'
try {
$secString = Get-Content $encConnStrFile | ConvertTo-SecureString
$BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($secString)
$cnStr = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)
}
catch {
Write-Host "Failed to read connection string - $($error[0].Exception.Message)"
return
}
function createConn() {
return New-Object System.Data.SqlClient.SqlConnection -ArgumentList $cnStr
}
function addParams($cmd, [PSCustomObject]$params) {
if ($params -ne $null) {
$params.PSObject.Properties.Name | ForEach-Object {
$paramName = $_
if (-not ($paramName -match '^[0-9A-Za-z]+$')) {
throw "Invalid parameter name: $paramName"
}
$paramVal = $params.$paramName
if ($paramVal -is [System.Data.SqlClient.SqlParameter]) {
$cmd.Parameters.Add($paramVal) | Out-Null
}
elseif ($paramVal -is [array]) {
# support "WHERE Col IN @array"
$idx = 1
$paramNameList = ''
$paramVal | ForEach-Object {
$cmd.Parameters.AddWithValue('@' + $paramName + $idx, $_) | Out-Null
$paramNameList += '@' + $paramName + $idx + ','
$idx++
}
$cmd.CommandText = $cmd.CommandText.Replace('@' + $paramName, "($($paramNameList.TrimEnd(',')))")
}
else {
$cmd.Parameters.AddWithValue('@' + $paramName, $paramVal) | Out-Null
}
}
}
}
function execute([string]$cmdText, [PSCustomObject]$params, $extConn, $trans) {
if ($null -eq $extConn) {
$cn = createConn
$cn.Open()
}
else {
$cn = $extConn
}
$cmd = $cn.CreateCommand()
if ($null -ne $trans) { $cmd.Transaction = $trans }
$cmd.CommandText = $cmdText
addParams $cmd $params
$cmd.ExecuteNonQuery() | Out-Null
if ($null -eq $extConn) { $cn.Dispose() }
}
function query([string]$cmdText, [PSCustomObject]$params, $extConn, $trans) {
if ($null -eq $extConn) {
$cn = createConn
$cn.Open()
}
else {
$cn = $extConn
}
$cmd = $cn.CreateCommand()
if ($null -ne $trans) { $cmd.Transaction = $trans }
$cmd.CommandText = $cmdText
addParams $cmd $params
$dr = $cmd.ExecuteReader()
$dt = New-Object System.Data.DataTable
$dt.load($dr)
$dr.Dispose()
if ($null -eq $extConn) { $cn.Dispose() }
return $dt
}
# 範例一 無參數SQL指令
execute @"
IF OBJECT_ID('PSTEST','U') IS NULL
BEGIN
CREATE TABLE PSTEST (ID INTEGER, NAME NVARCHAR(32))
END
ELSE
BEGIN
TRUNCATE TABLE PSTEST
END
"@
execute "INSERT INTO PSTEST VALUES (1,'Jeffrey')"
execute "INSERT INTO PSTEST VALUES (2,'darkthread')"
execute "INSERT INTO PSTEST VALUES (3,'Ironman')"
# 結果以 Format-Table 方式呈現
Write-Host "簡單查詢" -ForegroundColor Yellow
query "SELECT * FROM PSTEST" | Format-Table
# 類似 LINQ Where()/Select() 概念
Write-Host "結合 Where-Object / ForEach-Object" -ForegroundColor Yellow
query 'SELECT * FROM PSTEST' | Where-Object { $_.Id -le 2 } |
ForEach-Object { "* $($_.Name)" }
# 範例二 使用自訂資料物件傳參數
Write-Host "使用自訂物件傳參數" -ForegroundColor Yellow
$users = @( @{ id = 4; name = 'Clone-1' }, @{ id = 5; name = 'Clone-2'} )
$users | ForEach-Object {
execute 'INSERT INTO PSTEST VALUES (@id, @name)' ([PSCustomObject]$_)
}
query "SELECT * FROM PSTEST WHERE Id = `@id OR Name LIKE `@name + '%'" `
([PSCustomObject]@{ id = 1; name = 'Clone' }) | Format-Table
Write-Host "WHERE Id IN @ids 陣列參數" -ForegroundColor Yellow
query "SELECT * FROM PSTEST WHERE Id IN `@ids" ([PSCustomObject]@{ ids = (1, 3) }) | Format-Table
# 範例三 指定特殊參數型別
Write-Host "指定 SqlParameter SqlDbType" -ForegroundColor Yellow
$guidParam = New-Object System.Data.SqlClient.SqlParameter -ArgumentList "@guid",[System.Data.SqlDbType]::UniqueIdentifier
$guidParam.Value = New-Guid
$params = [PSCustomObject]@{
guid = $guidParam
str = 'Param Type Test'
}
query 'SELECT @str AS S, @guid as G' $params | Format-Table
# 範例四 啟動 Transaction
Write-Host "啟動 Transaction" -ForegroundColor Yellow
Write-Host "測試前筆數: $((query 'SELECT COUNT(1) AS C FROM PSTEST').C)"
$cn = createConn
$cn.Open()
$trans = $cn.BeginTransaction()
execute 'DELETE FROM PSTEST' -extConn $cn -trans $trans
$cnt = (query 'SELECT COUNT(1) AS C FROM PSTEST' -extConn $cn -trans $trans).C
Write-Host "交易過程筆數:$cnt"
$trans.Rollback()
$trans.Dispose()
$cn.Dispose()
Write-Host "Rollback 後筆數:$((query 'SELECT COUNT(1) AS C FROM PSTEST').C)"
測試成功!
Example of how to save conneciton securely and run SQL command in Dapper-style with PowerShell.
Comments
Be the first to post a comment