Last active
August 29, 2015 14:20
-
-
Save hpmmatuska/6f0eae4ac80916ccf0c3 to your computer and use it in GitHub Desktop.
The function validate input file (SQL script) for potential dangerous commands. Optional is report and trigger execution.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Function ExecuteSQLscript { | |
| <# | |
| .Synopsis | |
| Execute SQL script | |
| .Description | |
| will trigger sqlexec.exe and display output | |
| .INPUTS | |
| -SQLscript <[System.IO.FileInfo]string> | |
| -SQLServer <[System.IO.FileInfo]string> | |
| -SQLDB <[System.IO.FileInfo]string> | |
| .OUTPUTS | |
| Console output, not suitable for redirect. | |
| ExecuteResults <Int> - errorcode from the sqlexec.exe | |
| .Example | |
| PS C:\ ExecuteSQLscript -SQLscript <file> -SQLServer <instance> -SQLDB <database> | |
| .Notes | |
| Last Updated: April 27, 2015 | |
| Version : 1.0 | |
| .Link | |
| #> | |
| [cmdletbinding()] | |
| Param( | |
| [Parameter(Position=0, ValueFromPipeline=$true, ValueFromPipelineByPropertyName, Mandatory=$true)] | |
| [ValidateNotNullorEmpty()] | |
| [String]$SQLscript, | |
| [parameter(Mandatory=$true)] | |
| [ValidateNotNullorEmpty()] | |
| [String]$SQLServer, | |
| [parameter(Mandatory=$true)] | |
| [ValidateNotNullorEmpty()] | |
| [String]$SQLDB | |
| ) | |
| invoke-expression ('sqlcmd -i "' + $SQLScript + '" -S "' + $SQLServer + '" -d "' + $SQLDB + '" -E -b -I') | Tee-object -variable ExecuteResults | |
| # here you can put the code for various "$ExecuteResults"... | |
| } | |
| Function ValidateSQL { | |
| <# | |
| .Synopsis | |
| The function validate input file (SQL script) for potential dangerous commands. | |
| .Description | |
| Validate, create report and execute the commands | |
| .INPUTS | |
| -SQLscript <[System.IO.FileInfo]string> | |
| -IllegalCommands <[String[]]> | |
| -SaveReport [Switch] | |
| -ExecuteSQL [Switch] | |
| -SQLServer <[System.IO.FileInfo]string> | |
| -SQLDB <[System.IO.FileInfo]string> | |
| .OUTPUTS | |
| Interactive Console output, not suitable for redirect. | |
| .Example | |
| PS C:\> ValidateSQL | |
| find all *.sql in current directory and will try to validate them | |
| .Example | |
| PS C:\> ValidateSQL c:\sample.sql -SaveReport | |
| verify specified script and create a result report in current directory | |
| The report is created just in case some issue found | |
| .Example | |
| PS C:\> c:\sample.sql | ValidateSQL -ExecuteSQL | |
| validate and execute the script | |
| .Example | |
| PS C:\> ValidateSQL -SQLscript .\SQL_Validation.ps1, C:\BOOTNXT -IllegalCommands Begin, End -Verbose | |
| validate defined scripts with overwritten the default validation phrases | |
| enabled verbouse console output | |
| .Example | |
| PS C:\> ValidateSQL -SQLscript (Get-ChildItem -path c:\sql -Filter *.sql) -SQLServer "Server/Instance" -SQLDB "Test_DB" -ExecuteSQL -Verbose | |
| Pipeline all filter query for SQL scripts to validator function | |
| With option to run all scripts again specified DB | |
| .Notes | |
| Last Updated: April 27, 2015 | |
| Version : 1.0 | |
| .Link | |
| #> | |
| [cmdletbinding(DefaultParameterSetName = "SQLscript")] | |
| Param( | |
| [Parameter(Position=0, ValueFromPipeline=$true, ValueFromPipelineByPropertyName, ParameterSetName="Path")] | |
| [String[]]$SQLscript = $null, | |
| [parameter()] | |
| [String[]]$IllegalCommands = @( | |
| #Object Level illegal commands | |
| "GRANT SELECT","GRANT DELETE","GRANT INSERT","GRANT UPDATE","GRANT EXEC", | |
| "GRANT EXECUTE","DENY SELECT","DENY DELETE","DENY INSERT","DENY UPDATE","DENY EXEC", | |
| "REVOKE SELECT","REVOKE DELETE","REVOKE INSERT", | |
| #Database Level Illegal Commands | |
| “CREATE USER",“ALTER USER",“DROP USER", | |
| “CREATE ROLE",“ALTER ROLE",“DROP ROLE", | |
| “CREATE APPLICATION ROLE",“ALTER APPLICATION ROLE",“DROP APPLICATION ROLE", | |
| “CREATE DATABASE",“ALTER DATABASE",“DROP DATABASE", | |
| “CREATE RULE",“ALTER RULE",“DROP RULE", | |
| “CREATE TRIGGER",“ALTER TRIGGER",“DROP TRIGGER", | |
| #Server Level Illegal Commands | |
| “CREATE LOGIN",“ALTER LOGIN",“DROP LOGIN", | |
| “CREATE SERVER ROLE",“ALTER SERVER ROLE",“DROP SERVER ROLE", | |
| "SP_CONFIGURE", "SP_RECONFIGURE", | |
| "sp_add_job","sp_add_jobstep","sp_add_schedule","sp_attach_schedule","sp_add_jobserver", | |
| "BACKUP DATABASE","BACKUP LOG", "RESTORE DATABASE","RESTORE LOG", | |
| #Soon to be depreciated statements | |
| "sp_addsrvrolemember","sp_addrolemember","sp_dropsrvrolemember","sp_addapprole","sp_addlogin","sp_addrole", | |
| "sp_adduser","sp_denylogin","sp_dropapprole","sp_droplogin","sp_droprole","sp_droprolemember", | |
| "sp_dropuser","sp_grantdbaccess","sp_grantlogin","sp_revokelogin" | |
| ), | |
| [parameter()] | |
| [Switch]$SaveReport = $false, | |
| [parameter()] | |
| [Switch]$ExecuteSQL = $false, | |
| [parameter()] | |
| [String]$SQLServer = $null, | |
| [parameter()] | |
| [String]$SQLDB = $null | |
| ) | |
| BEGIN { | |
| Write-Output '' | |
| Write-Verbose 'Looking for SQL scripts' | |
| if (!$SQLscript) { $SQLscript = Get-ChildItem -Attributes !Directory -Include *.sql -Force } #if no param, look for *.sql | |
| if ($SQLscript) { [System.Collections.ArrayList]$a = $SQLscript; $SQLscript | %{ if (!(Test-Path $_ -PathType Leaf)) {$a.Remove($_)}}; $SQLscript = $a; Remove-Variable a} #check if all files exists | |
| if (!$SQLscript) { | |
| Write-Warning "Uh-Oh, nothing to validate found" | |
| Write-Warning "Entered SQLscript paramater does not exists or" | |
| Write-Warning ("No SQL script found at " + (Get-Location) + " path.") | |
| break | |
| } | |
| else { | |
| Write-Output ('Found ' + $SQLscript.count + ' for validation.') | |
| $SQLscript | %{Write-Verbose ("File to be validated: " + $_)} | |
| } | |
| } #check of input paramaters | |
| PROCESS { | |
| $IssuesFound = select-string -path $SQLscript -Pattern $IllegalCommands | |
| if ($IssuesFound) { | |
| Write-Output '' | |
| Write-Warning "Illegal SQL commands are used in the scripts. For more details use verbose paramter or read the log." | |
| $IssuesFound | Write-Verbose | |
| if ($SaveReport) { | |
| $IssuesFound | Export-Clixml SQL_Validation_Report.xml -Force | |
| $IssuesFound | Export-Csv SQL_Validation_Report.csv -Force | |
| } | |
| } # this is the validation report for all illegal matches - just due to logging purposes. | |
| else {Write-Output ''; Write-Output "No illegals commands detected."} | |
| } #Main body of the script, Illegal commands validation | |
| END { | |
| if ($ExecuteSQL) { | |
| If (!$SQLServer) { | |
| Write-Warning "The Target SQL server \ instance is missing. " | |
| $SQLServer = Read-Host -Prompt "Enter SQLServer\Instance or keep blank to specify later: " | |
| } | |
| If (!$SQLDB) { | |
| Write-Warning "The Target Database is missing. " | |
| $SQLDB = Read-Host -Prompt "Enter Database or keep blank to specify later: " | |
| } | |
| Write-Output '' | |
| Write-Verbose "Execution switch detected." | |
| Write-Output '' | |
| $SQLscript | %{ | |
| $scriptname = $_ | |
| if ($IssuesFound.Filename -contains (split-path $_ -leaf -resolve)) { | |
| Write-Warning ('The file ' + $_ + ' contains dangerous SQL commands.') | |
| $IssuesFound | ?{$IssuesFound.Filename -contains (split-path $scriptname -leaf -resolve)} | %{Write-Warning ('At Line: ' + $_.LineNumber + '`t Error: ' + $_.Line)} | |
| Write-Output '' | |
| $title = "Execute SQL scripts" | |
| $message = "Do you want to execute the SQL Script $scriptname even the unallowed commands were detected?" | |
| $yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Hell Yeah", "Hell yeah, no risk - no fun..." | |
| $no = New-Object System.Management.Automation.Host.ChoiceDescription "&No", "No, are you crazy?" | |
| $show = New-Object System.Management.Automation.Host.ChoiceDescription "&Show", "I want to review the script!" | |
| $options = [System.Management.Automation.Host.ChoiceDescription[]]($yes, $no, $show) | |
| $result = $host.ui.PromptForChoice($title, $message, $options, 1) | |
| switch ($result) { | |
| 0 {Write-Verbose ("Executing " + $scriptname); ExecuteSQLscript -SQLscript $scriptname -SQLServer $SQLServer -SQLDB $SQLDB} # Selected YES | |
| 1 {Write-Verbose ("Skipping the script " + $scriptname)} # Selected NO | |
| 2 { | |
| notepad.exe ($scriptname) | |
| $options = [System.Management.Automation.Host.ChoiceDescription[]]($yes, $no) | |
| $result = $host.ui.PromptForChoice($title, $message, $options, 1) | |
| switch ($result) { | |
| 0 {Write-Verbose ("Executing " + $scriptname); ExecuteSQLscript -SQLscript $scriptname -SQLServer $SQLServer -SQLDB $SQLDB} # Selected YES | |
| 1 {Write-Verbose ("Skipping the script " + $scriptname)} # Selected NO | |
| } #switch | |
| } # Selected SHOW | |
| } | |
| } # write-host $_ " has Issue" | |
| else { | |
| Write-Output '' | |
| Write-Output ('The file ' + $_ + ' is safe to execute.') | |
| $title = "Execute SQL scripts" | |
| $message = "SQL Script $scriptname is safe to execute, would you?" | |
| $yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes", "Execute the SQL command" | |
| $no = New-Object System.Management.Automation.Host.ChoiceDescription "&No", "Skip the script" | |
| $options = [System.Management.Automation.Host.ChoiceDescription[]]($yes, $no) | |
| $result = $host.ui.PromptForChoice($title, $message, $options, 0) | |
| switch ($result) { | |
| 0 {Write-Verbose ("Executing " + $scriptname); ExecuteSQLscript -SQLscript $scriptname -SQLServer $SQLServer -SQLDB $SQLDB} # Selected YES | |
| 1 {Write-Verbose ("Skipping the script " + $scriptname)} # Selected NO | |
| } # switch | |
| } # write-host $_ " has no Issue" | |
| } # for each sql script | |
| } # Execution switch | |
| } # final return & execution (END) | |
| } # function ValidateSQL |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment