How to prevent ‘Select *’ : The elegant way
- by Dave Ballantyne
I’ve been doing a lot of work with the “Microsoft SQL Server 2012 Transact-SQL Language Service” recently, see my post here and article here for more details on its use and some uses. An obvious use is to interrogate sql scripts to enforce our coding standards. In the SQL world a no-brainer is SELECT *, all apologies must now be given to Jorge Segarra and his post “How To Prevent SELECT * The Evil Way” as this is a blatant rip-off IMO, the only true way to check for this particular evilness is to parse the SQL as if we were SQL Server itself. The parser mentioned above is ,pretty much, the best tool for doing this. So without further ado lets have a look at a powershell script that does exactly that : cls
#Load the assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.SqlParser") | Out-Null
$ParseOptions = New-Object Microsoft.SqlServer.Management.SqlParser.Parser.ParseOptions
$ParseOptions.BatchSeparator = 'GO'
#Create the object
$Parser = new-object Microsoft.SqlServer.Management.SqlParser.Parser.Scanner($ParseOptions)
$SqlArr = Get-Content "C:\scripts\myscript.sql"
$Sql = ""
foreach($Line in $SqlArr){
$Sql+=$Line
$Sql+="`r`n"
}
$Parser.SetSource($Sql,0)
$Token=[Microsoft.SqlServer.Management.SqlParser.Parser.Tokens]::TOKEN_SET
$IsEndOfBatch = $false
$IsMatched = $false
$IsExecAutoParamHelp = $false
$Batch = ""
$BatchStart =0
$Start=0
$End=0
$State=0
$SelectColumns=@();
$InSelect = $false
$InWith = $false;
while(($Token = $Parser.GetNext([ref]$State ,[ref]$Start, [ref]$End, [ref]$IsMatched, [ref]$IsExecAutoParamHelp ))-ne [Microsoft.SqlServer.Management.SqlParser.Parser.Tokens]::EOF) {
$Str = $Sql.Substring($Start,($End-$Start)+1)
try{
($TokenPrs =[Microsoft.SqlServer.Management.SqlParser.Parser.Tokens]$Token) | Out-Null
#Write-Host $TokenPrs
if($TokenPrs -eq [Microsoft.SqlServer.Management.SqlParser.Parser.Tokens]::TOKEN_SELECT){
$InSelect =$true
$SelectColumns+=""
}
if($TokenPrs -eq [Microsoft.SqlServer.Management.SqlParser.Parser.Tokens]::TOKEN_FROM){
$InSelect =$false
#Write-Host $SelectColumns -BackgroundColor Red
foreach($Col in $SelectColumns){
if($Col.EndsWith("*")){
Write-Host "select * is not allowed"
exit
}
}
$SelectColumns =@()
}
}catch{
#$Error
$TokenPrs = $null
}
if($InSelect -and $TokenPrs -ne [Microsoft.SqlServer.Management.SqlParser.Parser.Tokens]::TOKEN_SELECT){
if($Str -eq ","){
$SelectColumns+=""
}else{
$SelectColumns[$SelectColumns.Length-1]+=$Str
}
}
}
OK, im not going to pretend that its the prettiest of powershell scripts, but if our parsed script file “C:\Scripts\MyScript.SQL” contains SELECT * then “select * is not allowed” will be written to the host.
So, where can this go wrong ? It cant ,or at least shouldn’t , go wrong, but it is lacking in functionality. IMO, Select * should be allowed in CTEs, views and Inline table valued functions at least and as it stands they will be reported upon.
Anyway, it is a start and is more reliable that other methods.
