FORMATENCRYPT generates different values for numeric values when masking a Database table and CSV File
Article ID: 370975
Updated On:
Products
Issue/Introduction
When using the FORMATENCRYPT function to mask an Oracle table, and a CSV flat file, which contain identical data, we are getting different results for any field that is a numeric data type.
Environment
All Supported releases of FDM
Cause
When masking flat files, FDM does not use the Numeric, Date, and Character dropdown settings in the UI to define the data types of the fields from the flat file. We use the definition file, which only allows for Character (varchar) and Date data types.
The FORMATENCRYPT function processes numeric data types and character data types differently. See Masking Functions and Parameters for more information.
For numeric data types, we ignore the first digit. This is to prevent the algorithm from using a zero as the leading digit, which can cause problems for many types of databases. However, when processing character data types, we mask all characters, including the first character.
Resolution
As a workaround, it's recommended that we ignore the first digit, when masking any numeric fields in the flat file, to ensure we get the same results as we are seeing in the database tables. So when masking the CSV file, where you have numeric data types, we need to set Parm1 (Ignore first nn characters) to 1.
Example for Flat File masking:
Below are the confirmed masked results when testing the workaround. We get the same masked results in the Oracle table and the CSV file, as seen below:
Oracle Table Notes: Masked_Number = number data type, Masked_Integer = integer data type, and Masked_Character = the varchar2 data type
Oracle masked results:
| Original_Value | Masked_Number | Masked_Integer | Masked_Character |
| 552835916 | 520747251 | 520747251 | 231958362 |
| 512345689 | 580257924 | 580257924 | 291468035 |
| 543219876 | 511121111 | 511121111 | 222332222 |
| 529876120 | 597788465 | 597788465 | 208999576 |
| 567098213 | 535900558 | 535900558 | 246111669 |
| 507835298 | 575747533 | 575747533 | 286958644 |
| 598765432 | 566677777 | 566677777 | 277888888 |
CSV File masked results:
| Original_Value | Masked_Number | Masked_Integer | Masked_Character |
| 552835916 | 520747251 | 520747251 | 231958362 |
| 512345689 | 580257924 | 580257924 | 291468035 |
| 543219876 | 511121111 | 511121111 | 222332222 |
| 529876120 | 597788465 | 597788465 | 208999576 |
| 567098213 | 535900558 | 535900558 | 246111669 |
| 507835298 | 575747533 | 575747533 | 286958644 |
| 598765432 | 566677777 | 566677777 | 277888888 |
Feedback
