103 lines
4.0 KiB
PowerShell
103 lines
4.0 KiB
PowerShell
function Assert-SqlQueryIsSafe {
|
|
<#
|
|
.SYNOPSIS
|
|
This function will verify that a Sql Query does not violate the expected Alkami key phrases that can not be run via this script.
|
|
Queries that violate this will need to be authorized or run in a different manner.
|
|
|
|
.PARAMETER QueryString
|
|
The query to be validated
|
|
|
|
.OUTPUTS
|
|
Returns true or false, and writes errors to the error stream (can cause the function to early-exit based on ErrorPreference)
|
|
#>
|
|
[CmdletBinding()]
|
|
[OutputType([bool])]
|
|
param (
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateNotNullOrEmpty()]
|
|
[Alias('Sql')]
|
|
[Alias('Query')]
|
|
[string]$QueryString
|
|
)
|
|
|
|
$logLead = Get-LogLeadName
|
|
|
|
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.SqlParser") | Out-Null
|
|
$parseOptions = New-Object Microsoft.SqlServer.Management.SqlParser.Parser.ParseOptions
|
|
|
|
$restrictedPhrases = @(
|
|
'DROP FULLTEXT INDEX ON'
|
|
'DROP PROCEDURE'
|
|
'DROP TABLE'
|
|
'DROP VIEW'
|
|
'DROP INDEX'
|
|
'DROP TRIGGER'
|
|
'DROP XML SCHEMA COLLECTION'
|
|
'DROP TYPE'
|
|
'CREATE FULLTEXT INDEX ON TABLE'
|
|
'CREATE FUNCTION'
|
|
'CREATE PROCEDURE'
|
|
'CREATE TABLE'
|
|
'CREATE VIEW'
|
|
'CREATE TRIGGER'
|
|
'CREATE XML SCHEMA COLLECTION'
|
|
'CREATE TYPE'
|
|
'CREATE UNIQUE CLUSTERED INDEX'
|
|
'CREATE UNIQUE NONCLUSTERED INDEX'
|
|
'CREATE UNIQUE INDEX'
|
|
'CREATE CLUSTERED INDEX'
|
|
'CREATE NONCLUSTERED INDEX'
|
|
'CREATE INDEX'
|
|
'CREATE PRIMARY XML INDEX'
|
|
'CREATE XML INDEX'
|
|
'ALTER FUNCTION'
|
|
'ALTER PROCEDURE'
|
|
'ALTER TABLE'
|
|
'ALTER VIEW'
|
|
)
|
|
|
|
$parsedScript = [Microsoft.SqlServer.Management.SqlParser.Parser.Parser]::Parse($Query, $parseOptions)
|
|
|
|
if (-not (Test-IsCollectionNullOrEmpty -Collection $parsedScript.Errors)) {
|
|
Write-Error "$logLead : Script is invalid. See errors: `n$($script.Errors.Message -join "`n")"
|
|
return $false
|
|
}
|
|
|
|
# convert the query to a lexed string then join with spaces so we can do regex magic below
|
|
$parsedTokens = @($parsedScript.Script.Tokens.Type)
|
|
$parsedTokensAsString = $parsedTokens -join ' '
|
|
|
|
$errors = @()
|
|
|
|
foreach ($phrase in $restrictedPhrases) {
|
|
# convert each phrase into a lexed string, then rejoin with spaces, and use the power of regex to count the number of occurrences
|
|
$parsedPhrase = [Microsoft.SqlServer.Management.SqlParser.Parser.Parser]::Parse($phrase, $parseOptions)
|
|
$parsedPhraseTokens = @($parsedPhrase.Script.Tokens.Type)
|
|
$parsedPhraseTokensAsString = $parsedPhraseTokens -join ' '
|
|
$parsedPhraseCount = ([regex]::Matches($parsedTokensAsString, $parsedPhraseTokensAsString )).Count
|
|
|
|
if ($parsedPhraseCount -gt 0) {
|
|
$errors += $phrase
|
|
}
|
|
}
|
|
|
|
if (-not (Test-IsCollectionNullOrEmpty -Collection $errors)) {
|
|
Write-Error "$logLead : Your script violated the constraints on containing the following restricted keywords: `n `n$($errors -join "`n")`n `nYour script can not be run."
|
|
return $false
|
|
}
|
|
|
|
# use the power of regex to count the number of occurrences
|
|
$beginTransactionCount = ([regex]::Matches($parsedTokensAsString, "TOKEN_BEGIN LEX_WHITE TOKEN_TRANSACTION" )).Count
|
|
$commitTransactionCount = ([regex]::Matches($parsedTokensAsString, "TOKEN_COMMIT LEX_WHITE TOKEN_TRANSACTION" )).Count
|
|
$rollbackTransactionCount = ([regex]::Matches($parsedTokensAsString, "TOKEN_ROLLBACK LEX_WHITE TOKEN_TRANSACTION" )).Count
|
|
|
|
# You should have as many or more end-transaction statements as begin statements. This allows for an IF .. ROLLBACK .. ELSE .. COMMIT type scenario
|
|
$endTransactionCount = $commitTransactionCount + $rollbackTransactionCount
|
|
if ($beginTransactionCount -lt $endTransactionCount) {
|
|
Write-Error "$logLead : Your statement has a mismatched number of BEGIN TRANSACTION and either COMMIT TRANSACTION or ROLLBACK TRANSACTION statements. Please resolve this issue."
|
|
return $false
|
|
}
|
|
|
|
return $true
|
|
}
|