コード例 #1
0
ファイル: Db2Db_Tests.cs プロジェクト: dandraka/Zoro
        public void T06_Csv2Db_SqlServer()
        {
            string tblName  = $"T06_Csv2Db_{Guid.NewGuid().ToString().Substring(0, 8)}";
            string tblName2 = $"T06_Csv2Db_{Guid.NewGuid().ToString().Substring(0, 8)}";

            var connstr = Environment.GetEnvironmentVariable("SQLCONNSTRING");

            // skip if no db config found
            Skip.If(string.IsNullOrWhiteSpace(connstr), "No database connection info found, is the environment variable 'SQLCONNSTRING' set?");

            using (var utility = new Utility())
            {
                try
                {
                    utility.PrepareTestInstanceDir();
                    utility.PrepareSqlServerDb(tblName, connstr);

                    var config = new MaskConfig()
                    {
                        DataSource      = DataSource.CsvFile,
                        DataDestination = DataDestination.Database,
                        SqlCommand      = $"INSERT INTO {tblName2} (ID, Name, Bankaccount) VALUES (@ID, '@Name', '@BankAccount')",
                        InputFile       = Path.Combine(utility.TestInstanceDir, "data1.csv")
                    };
                    config.SetConnection(utility.TestDbConnection);
                    config.FieldMasks.Add(new FieldMask()
                    {
                        FieldName = "ID", MaskType = MaskType.None
                    });
                    config.FieldMasks.Add(new FieldMask()
                    {
                        FieldName = "Name", MaskType = MaskType.None
                    });
                    config.FieldMasks.Add(new FieldMask()
                    {
                        FieldName = "BankAccount", MaskType = MaskType.Asterisk
                    });

                    var cmdTbl = utility.TestDbConnection.CreateCommand();
                    cmdTbl.CommandText = $"CREATE TABLE {tblName2} (ID int, Name nvarchar(100), BankAccount varchar(50))";
                    cmdTbl.ExecuteNonQuery();
                    utility.TestTablesToDrop = tblName2;

                    var masker = new DataMasking(config);
                    masker.Mask();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    // error 40 - could not open connection to sql server
                    Skip.If(ex.Message.Contains("40"), $"Database seems not to respond, check if your SQL Server is running. {ex.Message}");
                }

                var cmdSel = utility.TestDbConnection.CreateCommand();
                cmdSel.CommandText = $"SELECT * FROM {tblName2}";
                var dt = new DataTable();
                dt.Load(cmdSel.ExecuteReader());

                Assert.Equal(4, dt.Rows.Count);
            }
        }
コード例 #2
0
        public void T02_Mask_DB_Test()
        {
            var config = new MaskConfig()
            {
                DataSource = DataSource.Database,
                SqlSelect  = $"SELECT * FROM {utility.TestTableName}",
                OutputFile = Path.Combine(utility.TestInstanceDir, "maskeddata_db_02.csv")
            };

            config.SetConnection(utility.TestDbConnection);
            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "Name", MaskType = MaskType.Similar
            });
            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "BankAccount", MaskType = MaskType.Asterisk
            });
            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "Country", MaskType = MaskType.None
            });
            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "Address", MaskType = MaskType.List
            });
            config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement()
            {
                Selector = "country=CH", ReplacementList = "Bahnhofstrasse 41,Hauptstrasse 8,Berggasse 4"
            });
            config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement()
            {
                Selector = "country=GR", ReplacementList = "Evangelistrias 22,Thessalias 47,Eparhiaki Odos Lefkogion 6"
            });
            config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement()
            {
                Selector = "", ReplacementList = "Main Street 9,Fifth Avenue 104,Ranch rd. 1"
            });

            var masker = new DataMasking(config);

            try
            {
                masker.Mask();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                // error 40 - could not open connection to sql server
                Skip.If(ex.Message.Contains("40"), $"Database seems not to respond, check if your SQL Server is running. {ex.Message}");
            }

            Assert.True(File.Exists(config.OutputFile));
            var contents = new List <string>(File.ReadLines(config.OutputFile));

            Console.WriteLine($"Contents of {config.OutputFile}");
            contents.ForEach(x => Console.WriteLine(x));
            Assert.Equal(5, contents.Count);
        }
コード例 #3
0
ファイル: Db2Db_Tests.cs プロジェクト: dandraka/Zoro
        public void T05_Db2Csv_SqlServer()
        {
            string tblName = $"T05_Db2Csv_{Guid.NewGuid().ToString().Substring(0, 8)}";

            var connstr = Environment.GetEnvironmentVariable("SQLCONNSTRING");

            // skip if no db config found
            Skip.If(string.IsNullOrWhiteSpace(connstr), "No database connection info found, is the environment variable 'SQLCONNSTRING' set?");

            using (var utility = new Utility())
            {
                try
                {
                    utility.PrepareTestInstanceDir();
                    utility.PrepareSqlServerDb(tblName, connstr);

                    var config = new MaskConfig()
                    {
                        DataSource      = DataSource.Database,
                        DataDestination = DataDestination.CsvFile,
                        SqlSelect       = $"SELECT * FROM {utility.TestTableName}",
                        OutputFile      = Path.Combine(utility.TestInstanceDir, "T01_Db2Csv.csv")
                    };
                    config.SetConnection(utility.TestDbConnection);
                    config.FieldMasks.Add(new FieldMask()
                    {
                        FieldName = "Name", MaskType = MaskType.None
                    });
                    config.FieldMasks.Add(new FieldMask()
                    {
                        FieldName = "BankAccount", MaskType = MaskType.Asterisk
                    });
                    config.FieldMasks.Add(new FieldMask()
                    {
                        FieldName = "Country", MaskType = MaskType.None
                    });
                    config.FieldMasks.Add(new FieldMask()
                    {
                        FieldName = "Address", MaskType = MaskType.None
                    });

                    var masker = new DataMasking(config);
                    masker.Mask();

                    Assert.True(File.Exists(config.OutputFile));
                    var contents = new List <string>(File.ReadLines(config.OutputFile));
                    Console.WriteLine($"Contents of {config.OutputFile}");
                    contents.ForEach(x => Console.WriteLine(x));
                    Assert.Equal(5, contents.Count);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    // error 40 - could not open connection to sql server
                    Skip.If(ex.Message.Contains("40"), $"Database seems not to respond, check if your SQL Server is running. {ex.Message}");
                }
            }
        }
コード例 #4
0
        public void T01_Mask_CSV_Test()
        {
            var config = MaskConfig.ReadConfig(utility.TestInstanceConfigfile);
            //Console.WriteLine($"Config: InputFile = {config.InputFile}");
            //Console.WriteLine($"Config: OutputFile = {config.OutputFile}");
            var masker = new DataMasking(config);

            masker.Mask();

            Assert.True(File.Exists(config.OutputFile));
            var contents = new List <string>(File.ReadLines(config.OutputFile));

            Assert.Equal(5, contents.Count);
        }
コード例 #5
0
        public void T06_Mask_MaskType_List_Test()
        {
            // Arrange
            string csvContent       = "id;name\r\n1;Carol Danvers\r\n2;Bruce Banner\r\n3;Peter Parker\r\n";
            string csvMaskedContent = "id;name\r\n1;Charles Xavier\r\n2;Jean Grey\r\n3;Charles Xavier\r\n";
            string csvFilename      = this.utility.CreateFileInTestInstanceDir(csvContent, "csv");
            var    config           = new MaskConfig()
            {
                InputFile  = csvFilename,
                OutputFile = csvFilename.Replace(".csv", "_out.csv")
            };

            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "id", MaskType = MaskType.None
            });
            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "name", MaskType = MaskType.List
            });
            config.FieldMasks[1].ListOfPossibleReplacements.Add(new Replacement()
            {
                Selector = "id=2", ReplacementList = "Jean Grey"
            });
            config.FieldMasks[1].ListOfPossibleReplacements.Add(new Replacement()
            {
                Selector = "", ReplacementList = "Charles Xavier"
            });

            // Act
            var masker = new DataMasking(config);

            masker.Mask();

            // Assert
            Assert.True(File.Exists(config.OutputFile));
            var maskContents = new List <string>(csvMaskedContent.Split("\r\n"));
            var contents     = new List <string>(File.ReadLines(config.OutputFile));

            Assert.Equal(4, contents.Count);
            for (int i = 0; i < contents.Count; i++)
            {
                Assert.Equal(maskContents[i], contents[i]);
            }
        }
コード例 #6
0
        public void T07_Mask_MaskType_Similar_Test()
        {
            // ===== Arrange
            string csvContent  = "id;name\r\n1;Carol Danvers\r\n2;Bruce Banner\r\n3;Peter Parker\r\n";
            string csvFilename = this.utility.CreateFileInTestInstanceDir(csvContent, "csv");
            var    config      = new MaskConfig()
            {
                InputFile  = csvFilename,
                OutputFile = csvFilename.Replace(".csv", "_out.csv")
            };

            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "id", MaskType = MaskType.None
            });
            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "name", MaskType = MaskType.Similar
            });

            // ===== Act
            var masker = new DataMasking(config);

            masker.Mask();

            // ===== Assert
            Assert.True(File.Exists(config.OutputFile));
            var contents = new List <string>(File.ReadLines(config.OutputFile));

            Assert.Equal(4, contents.Count);
            for (int i = 1; i < contents.Count; i++)
            {
                var items = contents[i].Split(';');
                // id
                Assert.Equal(i.ToString(), items[0]);
            }
        }
コード例 #7
0
        public void T05_Mask_MaskType_Asterisk_Test()
        {
            // Arrange
            string csvContent       = "id;name\r\n1;Carol Danvers\r\n2;Bruce Banner\r\n3;Peter Parker\r\n";
            string csvMaskedContent = "id;name\r\n1;***** *******\r\n2;***** ******\r\n3;***** ******\r\n";
            string csvFilename      = this.utility.CreateFileInTestInstanceDir(csvContent, "csv");
            var    config           = new MaskConfig()
            {
                InputFile  = csvFilename,
                OutputFile = csvFilename.Replace(".csv", "_out.csv")
            };

            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "id", MaskType = MaskType.None
            });
            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "name", MaskType = MaskType.Asterisk
            });

            // Act
            var masker = new DataMasking(config);

            masker.Mask();

            // Assert
            Assert.True(File.Exists(config.OutputFile));
            var maskContents = new List <string>(csvMaskedContent.Split("\r\n"));
            var contents     = new List <string>(File.ReadLines(config.OutputFile));

            Assert.Equal(4, contents.Count);
            for (int i = 0; i < contents.Count; i++)
            {
                Assert.Equal(maskContents[i], contents[i]);
            }
        }
コード例 #8
0
ファイル: Program.cs プロジェクト: dandraka/Zoro
        static void Main(string[] args)
        {
            if (args.Length == 0)
            {
                Console.WriteLine(@"Usage: Zoro.exe <path to config file>");
                Console.WriteLine(@"E.g. Zoro.exe c:\temp\mask.xml");
                Console.WriteLine(@"Sample config file:");
                Console.WriteLine("<?xml version=\"1.0\"?>");
                Console.WriteLine("<MaskConfig xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">");
                Console.WriteLine(@"  <FieldMasks>");
                Console.WriteLine(@"    <FieldMask>");
                Console.WriteLine(@"      <FieldName>Name</FieldName>");
                Console.WriteLine(@"      <MaskType>Similar</MaskType>");
                Console.WriteLine(@"    </FieldMask>");
                Console.WriteLine(@"    <FieldMask>");
                Console.WriteLine(@"      <FieldName>MainPhone</FieldName>");
                Console.WriteLine(@"      <MaskType>Similar</MaskType>");
                Console.WriteLine(@"      <RegExMatch>^(\+\d\d)?(.*)$</RegExMatch>");
                Console.WriteLine(@"      <RegExGroupToReplace>2</RegExGroupToReplace>");
                Console.WriteLine(@"    </FieldMask>");
                Console.WriteLine(@"    <FieldMask>");
                Console.WriteLine(@"      <FieldName>Fax</FieldName>");
                Console.WriteLine(@"      <MaskType>Asterisk</MaskType>");
                Console.WriteLine(@"      <Asterisk>9</Asterisk>");
                Console.WriteLine(@"      <RegExMatch>^(\+\d\d)?(.*)$</RegExMatch>");
                Console.WriteLine(@"      <RegExGroupToReplace>2</RegExGroupToReplace>");
                Console.WriteLine(@"    </FieldMask>");
                Console.WriteLine(@"    <FieldMask>");
                Console.WriteLine(@"      <FieldName>BankAccount</FieldName>");
                Console.WriteLine(@"      <MaskType>Asterisk</MaskType>");
                Console.WriteLine(@"      <Asterisk>9</Asterisk>");
                Console.WriteLine(@"    </FieldMask>");
                Console.WriteLine(@"    <FieldMask>");
                Console.WriteLine(@"      <FieldName>Street</FieldName>");
                Console.WriteLine(@"      <MaskType>List</MaskType>");
                Console.WriteLine(@"      <ListOfPossibleReplacements>");
                Console.WriteLine(@"        <Replacement Selector=""Country=Netherlands"" List=""Nootdorpstraat,Nolensstraat,Statensingel"" />");
                Console.WriteLine(@"        <Replacement Selector=""Country=Germany"" List=""Bahnhofstraße,Freigaße,Hauptstraße"" />");
                Console.WriteLine(@"        <Replacement Selector=""Country=France"" List=""Rue Nationale,Boulevard Vauban,Rue des Stations"" />");
                Console.WriteLine(@"        <Replacement Selector="""" List=""Bedford Gardens,Sheffield Terrace,Kensington Palace Gardens"" />");
                Console.WriteLine(@"      </ListOfPossibleReplacements>");
                Console.WriteLine(@"    </FieldMask>");
                Console.WriteLine(@"  </FieldMasks>");
                Console.WriteLine(@"  <InputFile>C:\\temp\\Zorotests\\data.csv</InputFile>");
                Console.WriteLine(@"  <OutputFile>C:\\temp\\Zorotests\\maskeddata.csv</OutputFile>");
                Console.WriteLine(@"  <Delimiter>;</Delimiter>");
                Console.WriteLine(@"</MaskConfig>");

                return;
            }

            // cross platform
            configfile = Path.GetFullPath(args[0]
                                          .Replace('/', Path.DirectorySeparatorChar)
                                          .Replace('\\', Path.DirectorySeparatorChar));

            if (!File.Exists(configfile))
            {
                Console.WriteLine($"WARNING: Config file {configfile} was not found, exiting.");
                Console.WriteLine(@"Usage: Zoro.exe <path to config file>");
                return;
            }

            try
            {
                var config = MaskConfig.ReadConfig(configfile);
                var masker = new DataMasking(config);
                masker.Mask();
            }
            catch (Exception ex)
            {
                Console.WriteLine("An error occured:");
                Console.WriteLine("{0}: {1}\r\n{2}", ex.GetType(), ex.Message, ex.StackTrace);
            }
        }
コード例 #9
0
ファイル: Db2Db_Tests.cs プロジェクト: dandraka/Zoro
        public void T07_Db2Db_SqlServer()
        {
            string tblName = $"T03_Db2Db_{Guid.NewGuid().ToString().Substring(0, 8)}";

            var connstr = Environment.GetEnvironmentVariable("SQLCONNSTRING");

            // skip if no db config found
            Skip.If(string.IsNullOrWhiteSpace(connstr), "No database connection info found, is the environment variable 'SQLCONNSTRING' set?");

            using (var utility = new Utility())
            {
                try
                {
                    utility.PrepareTestInstanceDir();
                    utility.PrepareSqlServerDb(tblName, connstr);

                    // debug: get list of tables

                    /*
                     * var testCmd = utility.TestDbConnection.CreateCommand();
                     * testCmd.CommandText = "SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%'";
                     * var tbl = new DataTable();
                     * using (var rd = testCmd.ExecuteReader())
                     * {
                     *  tbl.Load(rd);
                     * }
                     * string tablesStr = Utility.DumpDataTable(tbl);
                     * Console.Write($"========== List of tables: {tablesStr} ==========");
                     */

                    var config = new MaskConfig()
                    {
                        DataSource      = DataSource.Database,
                        DataDestination = DataDestination.Database,
                        SqlSelect       = $"SELECT * FROM {utility.TestTableName}",
                        SqlCommand      = $"UPDATE {utility.TestTableName} SET Name=@Name, Bankaccount=@Bankaccount, Address=@Address WHERE ID = @ID",
                        OutputFile      = Path.Combine(utility.TestInstanceDir, "T01_Db2Csv.csv")
                    };
                    config.SetConnection(utility.TestDbConnection);
                    config.FieldMasks.Add(new FieldMask()
                    {
                        FieldName = "ID", MaskType = MaskType.None
                    });
                    config.FieldMasks.Add(new FieldMask()
                    {
                        FieldName = "Name", MaskType = MaskType.None
                    });
                    config.FieldMasks.Add(new FieldMask()
                    {
                        FieldName = "BankAccount", MaskType = MaskType.Asterisk
                    });
                    config.FieldMasks.Add(new FieldMask()
                    {
                        FieldName = "Address", MaskType = MaskType.List
                    });
                    config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement()
                    {
                        Selector = "country=CH", ReplacementList = "Bahnhofstrasse 41,Hauptstrasse 8,Berggasse 4"
                    });
                    config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement()
                    {
                        Selector = "country=GR", ReplacementList = "Evangelistrias 22,Thessalias 47,Eparhiaki Odos Lefkogion 6"
                    });
                    config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement()
                    {
                        Selector = "", ReplacementList = "Main Street 9,Fifth Avenue 104,Ranch rd. 1"
                    });

                    var masker = new DataMasking(config);
                    masker.Mask();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    // error 40 - could not open connection to sql server
                    Skip.If(ex.Message.Contains("40"), $"Database seems not to respond, check if your SQL Server is running. {ex.Message}");
                }
            }
        }
コード例 #10
0
        public void T08_Mask_MaskType_Query_Test()
        {
            // ===== Arrange
            string csvContent  = "ID;Name;City;Country\r\n1;Roche;Basel;CH\r\n2;ABB;Baden;CH\r\n3;BMW;München;DE\r\n4;Barilla;Parma;IT\r\n5;FAGE;Athens;GR";
            string csvFilename = this.utility.CreateFileInTestInstanceDir(csvContent, "csv");
            var    config      = new MaskConfig()
            {
                InputFile  = csvFilename,
                OutputFile = csvFilename.Replace(".csv", "_out.csv")
            };

            config.SetConnection(utility.TestDbConnection);
            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "ID", MaskType = MaskType.None
            });
            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "Name", MaskType = MaskType.Asterisk
            });
            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "City", MaskType = MaskType.Query
            });
            config.FieldMasks.Add(new FieldMask()
            {
                FieldName = "Country", MaskType = MaskType.None
            });

            config.FieldMasks[2].QueryReplacement = new QueryReplacement()
            {
                Query         = "SELECT city, country FROM cities",
                GroupDbField  = "country",
                ValueDbField  = "city",
                SelectorField = "Country"
            };

            // prepare lookup table
            var cmdTbl = utility.TestDbConnection.CreateCommand();

            cmdTbl.CommandText = @"CREATE TABLE cities AS 
                SELECT 'Geneva'   AS city, 'CH' as country UNION ALL 
                SELECT 'Bern'     AS city, 'CH' as country UNION ALL 
                SELECT 'Thun'     AS city, 'CH' as country UNION ALL 
                SELECT 'Köln'     AS city, 'DE' as country UNION ALL                 
                SELECT 'Berlin'   AS city, 'DE' as country UNION ALL 
                SELECT 'Hamburg'  AS city, 'DE' as country UNION ALL 
                SELECT 'Roma'     AS city, 'IT' as country UNION ALL 
                SELECT 'Venezia'  AS city, 'IT' as country UNION ALL 
                SELECT 'Milano'   AS city, 'IT' as country UNION ALL 
                SELECT 'Rethimno' AS city, 'GR' as country UNION ALL 
                SELECT 'Trikala'  AS city, 'GR' as country UNION ALL 
                SELECT 'Patra'    AS city, 'GR' as country";
            cmdTbl.ExecuteNonQuery();
            utility.TestTablesToDrop = "cities";

            // ===== Act
            var masker = new DataMasking(config);

            masker.Mask();

            // ===== Assert
            Assert.True(File.Exists(config.OutputFile));
            var contents = new List <string>(File.ReadLines(config.OutputFile));

            Assert.Equal(6, contents.Count);
            for (int i = 1; i < contents.Count; i++)
            {
                var items = contents[i].Split(';');

                string id      = items[0];
                string city    = items[2];
                string country = items[3];

                // id
                Assert.Equal(i.ToString(), id);

                // city
                switch (country)
                {
                case "CH":
                    Assert.Contains <string>(city, new string[] { "Geneva", "Bern", "Thun" });
                    break;

                case "DE":
                    Assert.Contains <string>(city, new string[] { "Köln", "Berlin", "Hamburg" });
                    break;

                case "IT":
                    Assert.Contains <string>(city, new string[] { "Roma", "Venezia", "Milano" });
                    break;

                case "GR":
                    Assert.Contains <string>(city, new string[] { "Rethimno", "Trikala", "Patra" });
                    break;

                default:
                    throw new NotSupportedException($"Unexpected country {country} found");
                }
            }
        }