dbatools

1.0.125

internal/functions/Convert-DbaMaskingValue.ps1

function Convert-DbaMaskingValue {

    <#
    .SYNOPSIS
        Convert a value to a TSQL format that be used in queries
 
    .DESCRIPTION
        The command will take a value and with the data type return it in a format
        that can be used in queries.
 
        For instance, a value "this is text" with data type 'varchar' will be returned as
        'this is text' with the single quotes
 
        It returns an object with the following properties
        - OriginalValue
        - NewValue
        -DataType
        -ErrorMessage
 
    .PARAMETER Value
        The value to be converted
 
    .PARAMETER DataType
        Data type the value needs to be converted to
 
    .PARAMETER Nullable
        It's possible to send a null value. It will then be converted to 'NULL' for SQL Server
 
    .PARAMETER WhatIf
        If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.
 
    .PARAMETER Confirm
        If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.
 
    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
 
    .NOTES
        Tags: Masking, DataMasking
        Author: Sander Stad (@sqlstad, sqlstad.nl)
 
        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT
 
    .LINK
        https://dbatools.io/Invoke-DbaDbDataMasking
 
    .EXAMPLE
        Convert-DbaMaskingValue -Value "please convert this" -DataType varchar
 
        Will convert ""please convert this" to "'please convert this'"
 
    .EXAMPLE
        Convert-DbaMaskingValue -Value $null -DataType varchar -Nullable
 
        Will convert null to "NULL"
    #>

    param(
        [Parameter(ValueFromPipeline)]
        [AllowEmptyString()]
        [object[]]$Value,
        [string]$DataType,
        [switch]$Nullable,
        [switch]$EnableException
    )

    begin {
        if (-not $Nullable -and -not $Value) {
            Stop-Function -Message "Please enter a value" -Target $Value -Continue
        }

        if (-not $Nullable -and -not $DataType) {
            Stop-Function -Message "Please enter a data type" -Target $DataType -Continue
        }

        if ($Value.Count -eq 0 -and $Nullable) {
            [PSCustomObject]@{
                OriginalValue = '$null'
                NewValue      = 'NULL'
                DataType      = $DataType
                ErrorMessage  = $null
            }
        }
    }

    process {
        if (Test-FunctionInterrupt) { return }

        foreach ($item in $Value) {

            $originalValue = $item

            [string]$newValue = $null
            [string]$errorMessage = $null

            if ($null -eq $item -or -not $item) {
                $originalValue = '$null'
                $newValue = "NULL"
            } else {
                switch ($DataType.ToLower()) {
                    { $_ -in 'bit', 'bool' } {
                        if ($item -match "([0-1])") {

                            $newValue = "$item"
                        } else {
                            $errorMessage = "Value '$($item)' is not valid BIT or BOOL"
                        }
                    }
                    { $_ -like '*int*' -or $_ -in 'decimal', 'numeric', 'float', 'money', 'smallmoney', 'real' } {
                        if ($item -match "\b\d+([\.,]\d+)?") {
                            $newValue = "$item"
                        } else {
                            $errorMessage = "Value '$($item)' is not valid integer/decimal format"
                        }
                    }
                    { $_ -in 'uniqueidentifier' } {
                        $newValue = "'$item'"
                    }
                    { $_ -eq 'datetime' } {
                        if ($item -match "(\d{4})-(\d{2})-(\d{2})") {
                            $item = ([datetime]$item).Tostring("yyyy-MM-dd HH:mm:ss.fff")
                            $newValue = "'$item'"
                        } else {
                            $errorMessage = "Value '$($item)' is not valid DATE or DATETIME format (yyyy-MM-dd)"
                        }
                    }
                    { $_ -eq 'datetime2' } {
                        if ($item -match "(\d{4})-(\d{2})-(\d{2})") {
                            $item = ([datetime]$item).Tostring("yyyy-MM-dd HH:mm:ss.fffffff")
                            $newValue = "'$item'"
                        } else {
                            $errorMessage = "Value '$($item)' is not valid DATE or DATETIME format (yyyy-MM-dd)"
                        }
                    }
                    { $_ -eq 'date' } {
                        if ($item -match "(\d{4})-(\d{2})-(\d{2})") {
                            $item = ([datetime]$item).Tostring("yyyy-MM-dd")
                            $newValue = "'$item'"
                        } else {
                            $errorMessage = "Value '$($item)' is not valid DATE or DATETIME format (yyyy-MM-dd)"
                        }
                    }
                    { $_ -like 'smalldatetime' } {
                        if ($item -match "(\d{4})-(\d{2})-(\d{2})") {
                            $item = ([datetime]$item).Tostring("yyyy-MM-dd HH:mm:ss")
                            $newValue = "'$item'"
                        } else {
                            $errorMessage = "Value '$($item)' is not valid DATE or DATETIME format (yyyy-MM-dd)"
                        }
                    }
                    { $_ -eq 'time' } {
                        if ($item -match "(\d{2}):(\d{2}):(\d{2})") {
                            $item = ([datetime]$item).Tostring("HH:mm:ss.fffffff")
                            $newValue = "'$item'"
                        } else {
                            $errorMessage = "Value '$($item)' is not valid TIME format (HH:mm:ss)"
                        }
                    }
                    { $_ -eq 'xml' } {
                        # nothing, unsure how i'll handle this
                    }
                    default {
                        $item = ($item).Tostring().Replace("'", "''")
                        $newValue = "'$item'"
                    }
                }
            }

            [PSCustomObject]@{
                OriginalValue = $originalValue
                NewValue      = $newValue
                DataType      = $DataType
                ErrorMessage  = $errorMessage
            }
        }
    }
}