Pre/Post SQL Option

This option can perform an SQL function on a column value immediately prior and/or immediately after a DataVeil mask is performed.

This provides great flexibility because column values and masked output can be reformatted or even cast to different types as needed as shown in the examples below.

For further convenience, a mask that has been customized with the Pre/Post SQL option can be saved as a Mask Component. This means that the customized mask can be created and edited in only one place and be referenced from many columns and projects.

 

 

 

#_value

This is a system macro.

In the Pre-Mask SQL panel it represents a reference to the original value that would be passed as input to the DataVeil masking function. Therefore, the result of the Pre-Mask SQL code shall become the new input to the DataVeil masking function.

In the Post-Mask SQL panel it represents a reference to the value returned from the DataVeil masking function. Therefore, the result of the Post-Mask SQL code shall become the new masked value.

 

 

Example: Using Randomize DateTime Mask to mask dates stored as integers.

Ordinarily the Randomize DateTime mask operates on date type columns. However, some users store dates as integers in INT data type columns such as 19820331 in the format of YYYYMMDD.

Using the Pre-Mask SQL option, as shown in the SQL Server T-SQL example above, the user can define how the original INT value is to be converted into a DATE type value so that the Randomize DateTime mask can use it.

The Post-Mask SQL shows that the date type value returned from the mask is converted back to an INT data type before it is written to the table's INT type column.

This example can be implemented for each of the supported DBMS types using the relevant SQL code:

SQL Server:

Pre-Mask SQL:   CONVERT(DATE, CONVERT(VARCHAR(8), #_value, 112))

Post-Mask SQL:   CONVERT(INT, CONVERT(VARCHAR(8), #_value, 112))

Oracle:

Pre-Mask SQL:   TO_DATE(#_value, 'YYYYMMDD')

Post-Mask SQL:   TO_CHAR(#_value, 'YYYYMMDD')

MySQL:

Pre-Mask SQL:   STR_TO_DATE(#_value, '%Y%m%d')

Post-Mask SQL:   DATE_FORMAT(#_value, '%Y%m%d')

 

 

Example: Changing the format of a masked value.

Another usage is that values can be reformatted or manipulated any any other way required by the user - either before and/or after being masked by the DataVeil masking function.

For instance, the Person Full Name mask can generate names in the format of family name, followed by a comma, followed by a space, followed by given names, such as "Smith, John". If a DataVeil mask does not produce output in a desired format then the Post-Mask SQL can be used to adjust the format as required. In this case, suppose that there must be no space after the comma. Therefore the Post-Mask SQL could simply replace ', ' (comma and space) with just ',' (comma only) as shown in the example below:
 

 

 

Example: Using Random Number mask on formated numeric strings.

The Random Number mask is designed to operate on numeric data types. However it can also accept and return string values if the string can be implicitly converted to a numeric type by the DBMS, such as the value "1999.99" in a column defined as VARCHAR(40). The implicit string to numeric conversion works only for very simple formats, typically those that contain only digits and a decimal point. Other characters, such as currency symbols and commas (depending on localization) can cause the implicit conversion to fail. For example, an implicit conversion of "$1,999.99" would fail; specifically, the "$" and the "," characters would be the problem.

In such a case, the Pre-Mask SQL code can be used to explicitly convert from the string value to a numeric value, and the Post-Mask SQL code can be used to explicitly convert the numeric value returned by the Random Number mask back into a string value and in the format required in the user's VARCHAR column.

  

 

 

The above example can be implemented for each of the DBMS types as follows:

SQL Server:

Pre-Mask SQL:   CAST(#_value AS MONEY)

Post-Mask SQL:   FORMAT(CAST(#_value AS MONEY), 'C')

Oracle:

Pre-Mask SQL:   TO_NUMBER(REPLACE(REPLACE(#_value, '$', ''), ',', ''))

Post-Mask SQL:   '$' || TRIM(TO_CHAR(#_value, '999,999,999.99'))

 

Unsupported Masks

Please note that not all masks support the Pre/Post SQL option, such as:

* Dataset

* DateTime (use Randomize DateTime instead)

* JSON and its paths

* Number Sequence

* Preserve

* Splitter and its paths

* Shuffle

* User SQL Value

* XML and its paths

 

Caution

Please be aware that when using Pre/Post SQL this causes DataVeil to inject this code into its SQL masking queries. If there is an error in this code then it can result in ambiguous errors, such as SQL syntax errors or others.

For example:  Incorrect syntax near the keyword 'AS'