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);
}
}
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);
}
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}");
}
}
}
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);
}
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]);
}
}
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]);
}
}
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]);
}
}
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);
}
}
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}");
}
}
}
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");
}
}
}
