Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

PowerShell Parameters Part II - ValidateSet and ValidatePattern


By:   |   Last Updated: 2016-04-19   |   Comments (2)   |   Related Tips: More > PowerShell

Problem

We've been passing many of our PowerShell scripts to developers and DBAs to use and wanted to know some of the basics for parameter validation to limit ranges as well as enforce the standards in our environment.

Solution

In the previous tip on PowerShell parameter validation, we looked at parameters limits on strings, integers and validating scripts with valid file locations.

In this tip, we'll look at some other enhancements to validating parameters, which will allow us to pass scripts to DBAs and/or developers, to prevent errors or mistakes when running scripts. We'll look at restricting numbers and strings to a set, along with restricting both to follow certain regular expression patterns, and wrapping it up with allowing some parameters to be mandatory while others can be optional.

PowerShell ValidateSet

Whether using strings or integers, we can limit what values enter functions by using ValidateSet. Let's first look at using it with integers:

Function Pass-Set {
    Param(
        [ValidateSet(2801,2823)][int]$specificinteger
    )
    Process
    {
        Write-Host "It must be one of two numbers; in this case $specificinteger."
    }
}

When we try to call the function, notice how PowerShell's intellisense tries to limit what we can do:

PowerShell intellisense tries to limit what parameters can be passed

Still, we can call the function with an invalid integer - in this case 1234 and we receive the error, Pass-Set : Cannot validate argument on parameter 'specificinteger'. The argument "1234" does not belong to the set "2801,2823" specified by the ValidateSet attribute. Supply an argument that is in the set and then try the command again. We can throttle string ranges too using the same ValidateSet:

Function Pass-Set {
    Param(
        [ValidateSet("oro","plata")][string]$specificstring
    )
    Process
    {
        Write-Host "It must be one of two words; in this case $specificstring."
    }
}

Intellisense will also try to prevent us from entering an invalid value when we call the function and if we pass in an invalid parameter - in this case "word" - we'll get a similar error, Pass-Set : Cannot validate argument on parameter 'specificstring'. The argument "word" does not belong to the set "oro,plata" specified by the ValidateSet attribute. Supply an argument that is in the set and then try the command again.

PowerShell ValidatePattern

PowerShell also allows us to add more complexity for validating parameters for our scripts with ValidatePattern, which looks at regular expressions that enter the script and checks if they are valid. With this, let's start with a simple example where all our development servers must start with DV- and then carry at least seven digits.

Function Pass-Pattern {
    Param(
        [ValidateLength(10,10)][ValidatePattern("[D][V][-]\d{7}")][string]$pattern
    )
    Process
    {
        Write-Host "You entered $pattern."
    }
}

Which of the below will pass?

Pass-Pattern -pattern "DV-123"
Pass-Pattern -pattern "DV-7814698"
Pass-Pattern -pattern "A1"
Pass-Pattern -pattern "DVx1234567"
Pass-Pattern -pattern "DV-VeryCool"
Pass-Pattern -pattern "ABCDV-1475135"

Only the second call meets the requirement of the regular expression pattern and length of the string - 10 character. This also highlights that we can combine parameter validation; for instance, if I pass in the below parameter, I get the length error:

Pass-Pattern -pattern "DV-78146981"

Using this validation can be very useful for object validation and enforcing naming convention. We can take our parameter validation a step further, requiring some parameters, while not requiring others. In the below script, server is mandatory, while database is not; a practical example of this is a loop with the SQL management objects for every database versus one database:

Function Pass-Server {
    Param(
        [Parameter(Mandatory=$true)][ValidatePattern("^DV-\d{7}")][string]$server
        , [Parameter(Mandatory=$false)][ValidateLength(2,30)][string]$database
    )
    Process
    {
        if ($database -ne "")
        {
            Write-Host "You entered $server and $database."
        }
        else
        {
            Write-Host "You entered $server."
        }
    }
}


Pass-Server -server "DV-7814698"
Pass-Server -server "DV-7814698" -database "LCNRatings"

This script doesn't allow us to pass in something that's incorrect according to our validation:

Pass-Server -server "ABCDEFG"
Pass-Server -server "DV-7814698" -database "H"

The first throws an error indicating that we violated the regular expression pattern, while the other throws an error stating that we passed in a database value that's too small. This allows us to stack further validation if we need for certain development situations.

The above parameter validation approaches help restrict anything from entering a script, though be careful about being too script in a manner that doesn't match your environment. For example, while environments tend to have a naming convention for servers, many of them don't follow their own naming convention, so validate that it's enforced before applying these, as you may miss out on events for a server you should be monitoring.

Next Steps
  • For pieces of our application or database that follow strict guidelines, we can use restrictions in these parameters to pass off scripts to developers and DBAs that will follow the guidelines.
  • For regular expressions, touch up on some regular expression patterns to ensure that the validation is correct.
  • Check out this previous tip Validating PowerShell Parameters - Part I


Last Updated: 2016-04-19


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, April 26, 2016 - 9:21:15 AM - Tim Back To Top

@Linda for security purposes, in some cases, I may strategize how/if I validate parameters (for tight security, I would prefer using Add-Type with a compiled DLL).  As an example of one way that you can limit what's returned as far as options using the first example from this tip:

Function Pass-Set {

    Param(

        [ValidateScript({ 

            if ($_ -in "2801","2823","0386") { $_ }

            else {throw "We apologize for the inconvenience, but $_ is not found."}

        })][string]$etfset

    )

    Process

    {

        Write-Host "$etfset passed."

    }

}

 

Pass-Set -etfset 2823
Pass-Set -etfset 3011
Pass-Set -etfset 1123


Monday, April 25, 2016 - 6:29:46 PM - Linda Back To Top

 Hello Tim,

What happens if you do not want the error message that is returned to provide the valid list of acceptable values?  This could be a security breach in many environments.  While it would be fine for the script to provide a message that said it didn't fit the list of values, providing those values would not be acceptable.

 


Learn more about SQL Server tools