MaskSQL

MaskSQL is a command-line tool to mask sensitive values in a SQL file.

Installation

Add this line to your application's Gemfile:

gem 'mask_sql'

And then execute:

$ bundle

Or install it yourself as:

$ gem install mask_sql

Usage

Mask sensitive values in a SQL file

$ mask_sql --in dump.sql --out masked_dump.sql --config mask_config.yml

Generate a config file

$ mask_sql init

Command Options

Option	Alias	Description	Default
`--in`	`-i`	Input file path (Required).
`--out`	`-o`	Output file path (Required).
`--config`	`-c`	Config YAML file path.	`.mask.yml` in the working directory.
`--insert`		`true` if mask `INSERT` SQL.	`false`, but `true` if `--insert`, `--replace`, and `--copy` options are not given.
`--replace`		`true` if mask `REPLACE` SQL.	`false`, but `true` if `--insert`, `--replace`, and `--copy` options are not given.
`--copy`		`true` if mask `COPY` SQL.	`false`, but `true` if `--insert`, `--replace`, and `--copy` options are not given.

Config

The following keys are available in the config YAML file.

Top level keys

Key	Description	Type
`mark`	Replacement text.	String
`targets`	Array of targets.	Array

Keys for `targets`

Key	Description	Type
`table`	Target table name.	String
`columns`	Columns count of the table.	Integer
`dummy_values`	Target column index (zero-based) and dummy text.	Hash
`group_indexes`	Array of column indexes (zero-based). Records that have the same values in these indexes are considered as the same numbering group.	Array

Examples

Input file (includes sensitive values):

INSERT INTO `people` (`id`, `code`, `name`, `email`) VALUES (1,'01','坂本龍馬','[email protected]'),(2,'02','高杉晋作','[email protected]'),(3,'03','沖田総司','[email protected]');
INSERT INTO `cats` (`code`, `name`) VALUES ('01','Sora'),('02','Hana'),('03','Leo');
INSERT INTO `dogs` (`code`, `name`, `house_id`, `room_id`) VALUES ('01','Pochi',1,1),('02','Rose',2,1),('03','Momo',1,1),('04','Sakura',1,2);

REPLACE INTO `people` (`id`, `code`, `name`, `email`) VALUES (1,'01','坂本龍馬','[email protected]'),(2,'02','高杉晋作','[email protected]'),(3,'03','沖田総司','[email protected]');
REPLACE INTO `cats` (`code`, `name`) VALUES ('01','Sora'),('02','Hana'),('03','Leo');
REPLACE INTO `dogs` (`code`, `name`, `house_id`, `room_id`) VALUES ('01','Pochi',1,1),('02','Rose',2,1),('03','Momo',1,1),('04','Sakura',1,2);

COPY people (id, code, name, email) FROM stdin;
1   01  坂本龍馬    [email protected]
2   02  高杉晋作    [email protected]
3   03  沖田総司    [email protected]
\.
COPY cats (code, name) FROM stdin;
01  Sora
02  Hana
03  Leo
\.
COPY dogs (code, name, house_id, room_id) FROM stdin;
01  Pochi   1   1
02  Rose    2   1
03  Momo    1   1
04  Sakura  1   2
\.

Output file (the sensitive values are masked):

INSERT INTO `people` (`id`, `code`, `name`, `email`) VALUES (1,'01','氏名1','[email protected]'),(2,'02','氏名2','[email protected]'),(3,'03','氏名3','[email protected]');
INSERT INTO `cats` (`code`, `name`) VALUES ('code-1','Cat name 1'),('code-2','Cat name 2'),('code-3','Cat name 3');
INSERT INTO `dogs` (`code`, `name`, `house_id`, `room_id`) VALUES ('code-1','Dog name 1',1,1),('code-1','Dog name 1',2,1),('code-2','Dog name 2',1,1),('code-1','Dog name 1',1,2);

REPLACE INTO `people` (`id`, `code`, `name`, `email`) VALUES (1,'01','氏名1','[email protected]'),(2,'02','氏名2','[email protected]'),(3,'03','氏名3','[email protected]');
REPLACE INTO `cats` (`code`, `name`) VALUES ('code-1','Cat name 1'),('code-2','Cat name 2'),('code-3','Cat name 3');
REPLACE INTO `dogs` (`code`, `name`, `house_id`, `room_id`) VALUES ('code-1','Dog name 1',1,1),('code-1','Dog name 1',2,1),('code-2','Dog name 2',1,1),('code-1','Dog name 1',1,2);

COPY people (id, code, name, email) FROM stdin;
1   01  氏名1 [email protected]
2   02  氏名2 [email protected]
3   03  氏名3 [email protected]
\.
COPY cats (code, name) FROM stdin;
code-1  Cat name 1
code-2  Cat name 2
code-3  Cat name 3
\.
COPY dogs (code, name, house_id, room_id) FROM stdin;
code-1  Dog name 1  1   1
code-1  Dog name 1  2   1
code-2  Dog name 2  1   1
code-1  Dog name 1  1   2
\.

Config file:

mark: '[mask]'
targets:
  - table: people
    columns: 4
    dummy_values:
      2: 氏名[mask]
      3: email-[mask]@example.com
  - table: cats
    columns: 2
    dummy_values:
      0: code-[mask]
      1: Cat name [mask]
  - table: dogs
    columns: 4
    dummy_values:
      0: code-[mask]
      1: Dog name [mask]
    group_indexes:
      - 2
      - 3

Supported Ruby Versions

Ruby 2.0.0
Ruby 2.1
Ruby 2.2
Ruby 2.3
Ruby 2.4

Contributing

Bug reports and pull requests are welcome.

License

MIT