DataMasker

A free data masking/anonymizer library for Sql Server written in .NET

If you've ever needed to pull down databases from a live environment to stage or even dev you'll need to think about masking any personal information. There are options out there paid and free, however the free ones I've found do not provide genuine data and the paid options are too pricey when it's only a few tables.

Data generation is provided by https://github.com/bchavez/Bogus

Bogus

Hello. I'm your host Brian Chavez (twitter). Bogus is a simple and sane fake data generator for .NET languages like C#, F# and VB.NET. Bogus is fundamentally a C# port of faker.js and inspired by FluentValidation's syntax sugar.

Bogus will help you load databases, UI and apps with fake data for your testing needs. If you like Bogus star ⭐️ the repository and show your friends! 😄

Nuget

DataMasker is available as a library over on nuget.org should you need to customize any part of the process. Alternatively just ask for a new feature and I'll see what I can do.

install-package DataMasker

Configuration

All configuration is done via a .json file, of which an example can be seen below.

Example Config

{
  "dataSource": {
    "type": "SqlServer",
    "config": {
       "name": "databasename",
       "userName": "databaseUserName",
       "password": "databasePassword",
       "server": "databaseServer"
    }   
  },  
  "tables": [    
    {
      "name": "Users",
      "schema": "dbo",
      "primaryKeyColumn": "UserId",
      "columns": [
        {
          "name": "FirstName",
          "type": "FirstName",
          "useGenderColumn": "Gender"
        },
        {
          "name": "LastName",
          "type": "LastName",
          "retainEmptyStringValues": true,
          "useGlobalValueMappings": true,
          "valueMappings": {
            "oldValue": "Jones",
            "newValue": "Smith"
          }
        },
        {
          "name": "Password",
          "type": "None",
          "retainNullValues": false,
          "useValue": "PasswordForDevEnvironment"
        },
        {
          "name": "DOB",
          "type": "DateOfBirth",
          "min" :"1901-01-01",
          "max": "2000-0101"
        },
        {
          "name": "EmailAddress",
          "type": "Bogus",
          "unique": true,
          "stringFormatPattern": "{{internet.email}}"
        },
        {
          "name": "Gender",
          "ignore": true,
          "type": "None"
        },
        {
          "name": "Address",
          "type": "Bogus",
          "retainNullValues": false,
          "stringFormatPattern": "{{address.fullAddress}}"
        },
        {
          "name": "ContactNumber",
          "type": "PhoneNumber",
          "retainNullValues": false,
          "stringFormatPattern": "+447#########"
        },
        {
          "name": "CompanyName",
          "type": "Sql",
          "sqlValue": {
            "query":"SELECT Name FROM SampleCompanyNames WHERE UserId = @UserId",
            "valueHandling": "Null"
          }
        }
      ]
    }
  ]
}

Table configuration is supplied by either the "tables" property or "tablesConfigPath" property.

If you want to keep your table configuration external to the main .json file this can be done by supplying "tablesConfigPath" with a file path. An example of this can be found in the example-configs directory in the example project.

External table config json

{
    "dataSource": {
    "type": "SqlServer",
    "config": {
       "name": "databasename",
       "userName": "databaseUserName",
       "password": "databasePassword",
       "server": "databaseServer"
    }   
  }, 
  "tablesConfigPath": "example-configs\\config-example2-table-config.json",
}

or embedded

{
    "dataSource": {
    "type": "SqlServer",
    "config": {
       "name": "databasename",
       "userName": "databaseUserName",
       "password": "databasePassword",
       "server": "databaseServer"
    }   
  }, 
  "tables": [... insert config here ...],
}

If both tables and tablesConfigPath is supplied then tablesConfigPath wins.

Column Configuration

| Property Name | Values | | ------------- | ------ | | type | None, Bogus, FirstName, lastName, DateOfBirth, Rant, StringFormat, FullAddress, PhoneNumber | | name | Database column name | | schema | Name of the schema in which the tables lives, defaults to dbo | valueMappings | Object with value mappings, e.g map "James" to "John" | useGenderColumn | Name of the database column to use as for the gender | | ignore | true/false | min | Minimum value to use for the given data type | | max | Maxiumum value to use for the given data type | | stringFormatPattern | From Bogus, numbers #, letters ?, or * random number or letter | | useValue | A hardcoded value to use for every row | | retainNullValues | true/false | | retainEmptyStringValues | true/false - when true if the existing value is null or empty (whitespace) then it will use the original value | | useLocalValueMappings | true/false | | useGlobalValueMappings | true/false | | unique | true/false - when true it will attempt to generate a unique value for this column| | sqlValue | Used when type is set to sql

Sql Value Configuration

When using data type Sql this allows you to get values from other tables within the same database. The configuration object is made up of the following properties

| Property Name | Values | | ------------- | ------ | | query | The query to use for the lookup, the current row will be passed into the query as parameters for use, see the example config above that uses @UserId. Columns are passed back into the query as parameters, any columns with spaces in their name, will be replaced with '_' . e.g. A column name "User Id" would become "User_Id" | | valueHandling | "Null" or "KeepValue". If the query executes and no data is returned, this tells the masker what to do, null will set the value to Null while KeepValue will keep the existing value on that row |

Data types

None

To use None you must specify either valueMappings or useValue, no data will be generated for this type. If you specify only valueMappings and the target value is not found, an error will be thrown.

{
  "name": "Title",
  "type": "None",
  "valueMappings": {    
    "Mr": "Master"
  },
  "ignore":"true/false",
  "useValue": "Miss",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false"
}

Bogus

Bogus is a type that when specified requires the stringFormatPattern option, which is passed directly to the Bogus API, see here for available options

{
  "name": "PhoneNumber",
  "type": "Bogus",
  "valueMappings": {    
    "+555-555-555": "+444-555-555-55"
  },
  "ignore":"true/false",
  "useValue": "+50559-5-5-555",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "stringFormatPattern": "{{phonenumbers.phonenumber}}"
}

FirstName

{
  "name": "FirstName",
  "type": "FirstName",
  "valueMappings": {    
    "James": "Bob"
  },
  "ignore":"true/false",
  "useValue": "Steve",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "useGenderColumn": "Gender"
}

LastName

{
  "name": "Surname",
  "type": "LastName",
  "valueMappings": {    
    "Smith": "Jojnes"
  },
  "ignore":"true/false",
  "useValue": "Timms",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false"
}

DateOfBirth

min will default to 1901-01-01
max will default to current date
date format is fullyear-month-day

{
  "name": "DOB",
  "type": "DateOfBirth",
  "valueMappings": {    
    "1990-01-02": "1990-02-02"
  },
  "ignore":"true/false",
  "useValue": "1940-02-02",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "min": "1901-12-25",
  "max": "2000-11-20"
}

Rant

max will default to 25

{
  "name": "Comments",
  "type": "Rant",
  "valueMappings": {    
    "A comment": "Becomes this"
  },
  "ignore":"true/false",
  "useValue": "A really important comment",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "max": 15
}

Lorem

min will default to 5
max will default to 30

{
  "name": "Comments",
  "type": "Lorem",
  "valueMappings": {    
    "A comment": "Becomes this"
  },
  "ignore":"true/false",
  "useValue": "A really important comment",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "min": 5,
  "max": 15
}

StringFormat

Check out the Bogus API for supported values

{
  "name": "Comments",
  "type": "StringFormat",
  "valueMappings": {    
    "A comment": "Becomes this"
  },
  "ignore":"true/false",
  "useValue": "A really important comment",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings"

DataMasker

Install / Use

README

DataMasker

Bogus

Nuget

Configuration

Column Configuration

Sql Value Configuration

Data types

None

Bogus

FirstName

LastName

DateOfBirth

Rant

Lorem

StringFormat