public void TestBasicSqlRewriteRuleService()
{
using var conn = new SqlConnection(Settings.Default.ConnectionString);
var repo = new SqlRewriteDbRepository(conn);
var rule = new SqlRewriteRule
{
DatabaseRegEx = ".*", QueryMatchRegEx = Stm, QueryReplacementString = "SELECT GETDATE()"
};
repo.SaveSqlRewriteRule(rule);
using var service = new SqlRewriteRuleService(repo, true);
using var con = new SqlConnection(Settings.Default.ConnectionString);
con.Open();
using var cmd = new SqlCommand(Stm, con);
var version = cmd.ExecuteScalar().ToString();
Assert.IsFalse(version.Contains("Microsoft"));
service.RemoveRule(rule.Id);
service.RefreshRulesFromRepository();
using var con2 = new SqlConnection(Settings.Default.ConnectionString);
con2.Open();
using var cmd2 = new SqlCommand(Stm, con2);
version = cmd2.ExecuteScalar().ToString();
Assert.IsTrue(version.Contains("Microsoft"));
}
public void TestPerformanceImpact()
{
using var repo = new SqlRewriteDbRepository(Settings.Default.ConnectionString);
using var service = new SqlRewriteRuleService(repo, true);
using var conn = new SqlConnection(Settings.Default.ConnectionString);
conn.Open();
var stopWatch = new Stopwatch();
stopWatch.Start();
Assert.AreNotEqual(0, service.AddRule(".*", Stm, "SELECT GETDATE()"));
service.RefreshRulesFromRepository();
for (var i = 0; i < 10000; i++)
{
using var cmd = new SqlCommand(Stm, conn);
var version = cmd.ExecuteScalar().ToString();
Assert.IsNotNull(version.Contains("Microsoft"));
}
stopWatch.Stop();
var intervalFullInjection = stopWatch.Elapsed;
stopWatch.Reset();
stopWatch.Start();
service.Enabled = false;
for (var i = 0; i < 10000; i++)
{
using var cmd = new SqlCommand(Stm, conn);
var version = cmd.ExecuteScalar().ToString();
Assert.IsNotNull(version.Contains("Microsoft"));
}
stopWatch.Stop();
Assert.IsTrue(Math.Abs(intervalFullInjection.TotalMilliseconds / stopWatch.Elapsed.TotalMilliseconds) < 3, $"{intervalFullInjection.TotalMilliseconds} vs {stopWatch.Elapsed.TotalMilliseconds}");
}
public void TestBasicSqlRewriteRuleServiceWithAutoRefreshTimer()
{
using var repo = new SqlRewriteDbRepository(Settings.Default.ConnectionString);
using var service = new SqlRewriteRuleService(repo, true);
service.AutoRefreshTimerInterval = 1000;
service.AutoRefreshRulesAndSettingsEnabled = true;
using var con = new SqlConnection(Settings.Default.ConnectionString);
con.Open();
using (var cmd = new SqlCommand(Stm, con))
{
var version = cmd.ExecuteScalar().ToString();
Assert.IsTrue(version.Contains("Microsoft"));
}
Assert.AreNotEqual(0, service.AddRule(".*", "NO MATCH", "SELECT GETDATE()"));
Thread.Sleep(1100);
using (var cmd = new SqlCommand(Stm, con))
{
var version = cmd.ExecuteScalar().ToString();
Assert.IsTrue(version.Contains("Microsoft"));
}
Assert.AreNotEqual(0, service.AddRule(".*", Stm, "SELECT GETDATE()"));
Thread.Sleep(1100);
using (var cmd = new SqlCommand(Stm, con))
{
var version = cmd.ExecuteScalar().ToString();
Assert.IsFalse(version.Contains("Microsoft"));
}
}
public void TestSaveAndRemoveItem()
{
var repository = new SqlRewriteDbRepository(new SqlConnection(Settings.Default.ConnectionString));
var item = new SqlRewriteRule
{
Id = 0, // This will cause insertion
DatabaseRegEx = ".*",
QueryMatchRegEx = "SELECT @@VERSION",
QueryReplacementString = "SELECT DBDATE()",
RegExOptions = 0
};
repository.SaveSqlRewriteRule(item);
try
{
Assert.AreNotEqual(0, item.Id);
var items = repository.LoadSqlRewriteRules();
ItemExists(items, item);
repository.RemoveSqlRewriteRule(item.Id);
items = repository.LoadSqlRewriteRules();
Assert.ThrowsException <Exception>(() => { ItemExists(items, item); });
}
finally
{
try
{
repository.RemoveSqlRewriteRule(item.Id);
}
catch (Exception)
{
// Ignore exceptions trying to remove to cleanup
}
}
}
public void TestCreateEnsureTableExists()
{
var conn = new SqlConnection(Settings.Default.ConnectionString);
conn.Open();
using (var dropTable = new SqlCommand("DROP TABLE SqlRewriteRegistry", conn))
{
try
{
dropTable.ExecuteNonQuery();
}
catch (Exception)
{
// Ignore exception attempting to drop table if it doesn't exist
}
}
using (var cmdCheckTable = new SqlCommand("SELECT OBJECT_ID (N'SqlRewriteRegistry', N'U') obj_id", conn))
{
var tableId = cmdCheckTable.ExecuteScalar();
Assert.IsTrue(tableId is DBNull);
var repository = new SqlRewriteDbRepository(conn);
Assert.IsNotNull(repository);
tableId = cmdCheckTable.ExecuteScalar();
Assert.IsFalse(tableId is DBNull);
}
}
public void TestSaveAndRemoveSettings()
{
var repository = new SqlRewriteDbRepository(new SqlConnection(Settings.Default.ConnectionString));
var settings = new SqlRewriteSettings
{
Id = 0, // This will cause insertion
ProcessNameRegEx = ".*",
MachineRegEx = ".*",
StackFrameIgnorePrefixes = "[mscorlib].\r\nSqlInterceptorsTests."
};
repository.SaveSqlRewriteSettings(settings);
try
{
Assert.AreNotEqual(0, settings.Id);
var items = repository.LoadSqlRewriteSettings();
SettingsExists(items, settings);
repository.RemoveSqlRewriteSettings(settings.Id);
items = repository.LoadSqlRewriteSettings();
Assert.ThrowsException <Exception>(() => { SettingsExists(items, settings); });
}
finally
{
try
{
repository.RemoveSqlRewriteSettings(settings.Id);
}
catch (Exception)
{
// Ignore exception
}
}
}
private static void Main()
{
Console.TreatControlCAsInput = true;
using var repo = new SqlRewriteDbRepository(Settings.Default.ConnectionString);
repo.RemoveAllSqlRewriteRules();
var lastRefreshFromRepo = DateTime.Now;
using var svc = new SqlRewriteRuleService(repo, true)
{
AutoRefreshRulesAndSettingsEnabled = true
};
svc.AutoRefreshEvent += () => lastRefreshFromRepo = DateTime.Now;
using var conn = new SqlConnection(Settings.Default.ConnectionString);
conn.Open();
SqlRewriteRule rule = null;
var input = "";
do
{
Console.Clear();
if (input != "")
{
rule ??= new SqlRewriteRule()
{
DatabaseRegEx = ".*",
QueryMatchRegEx = "SELECT @@VERSION"
};
rule.QueryReplacementString = $"{input} /*x*/";
repo.SaveSqlRewriteRule(rule);
}
PrintCurrentRuleSet(repo);
using var cmd = CreateSqlCommand(conn);
Console.WriteLine($"Current datetime: {DateTime.Now}");
Console.WriteLine($"Last refresh from repository: {lastRefreshFromRepo}");
Console.WriteLine($"Auto-refresh interval (milliseconds): {svc.AutoRefreshTimerInterval}");
Console.WriteLine($"Query:\r\n{cmd.CommandText}\r\n");
Console.WriteLine($"Result:\r\n{cmd.ExecuteScalar()}\r\n");
Console.WriteLine("Press Enter to continue, enter a replacement SQL and press Enter or press Ctrl-C to finish execution");
input = "";
do
{
var keyPressed = Console.ReadKey();
if (keyPressed.Key == ConsoleKey.C && keyPressed.Modifiers == ConsoleModifiers.Control)
{
return;
}
if (keyPressed.Key == ConsoleKey.Enter)
{
break;
}
input += keyPressed.KeyChar;
} while (true);
Console.WriteLine();
} while (true);
}
public void TestApplySettings()
{
using var conn = new SqlConnection(Settings.Default.ConnectionString);
var repo = new SqlRewriteDbRepository(conn);
repo.RemoveAllSqlRewriteSettings();
var settings = new SqlRewriteSettings()
{
MachineRegEx = Settings.Default.MachineNameMatchString,
ProcessNameRegEx = "Program Files",
Enabled = true,
HashInjectionEnabled = false,
RegExInjectionEnabled = false,
StackFrameInjectionEnabled = false,
StackFrameIgnorePrefixes = "Microsoft.VisualStudio.TestTools.UnitTesting"
};
repo.SaveSqlRewriteSettings(settings);
using var service = new SqlRewriteRuleService(repo);
Assert.IsFalse(service.Enabled);
Assert.IsTrue(SqlCommandRegExProcessor.RegExInjectionEnabled);
Assert.IsTrue(SqlCommandTextStackTraceInjector.HashInjectionEnabled);
Assert.IsTrue(SqlCommandTextStackTraceInjector.StackInjectionEnabled);
Assert.AreEqual("", SqlCommandTextStackTraceInjector.StackFrameIgnorePrefixes);
service.Enabled = true;
service.ApplySettingsFromRepository();
Assert.IsTrue(service.Enabled);
Assert.IsFalse(SqlCommandRegExProcessor.RegExInjectionEnabled);
Assert.IsFalse(SqlCommandTextStackTraceInjector.HashInjectionEnabled);
Assert.IsFalse(SqlCommandTextStackTraceInjector.StackInjectionEnabled);
Assert.AreEqual("Microsoft.VisualStudio.TestTools.UnitTesting", SqlCommandTextStackTraceInjector.StackFrameIgnorePrefixes);
repo.RemoveSqlRewriteSettings(settings.Id);
settings.Enabled = false;
settings.HashInjectionEnabled = true;
settings.RegExInjectionEnabled = true;
settings.StackFrameInjectionEnabled = true;
settings.StackFrameIgnorePrefixes = "";
repo.SaveSqlRewriteSettings(settings);
service.ApplySettingsFromRepository();
Assert.IsFalse(service.Enabled);
Assert.IsTrue(SqlCommandRegExProcessor.RegExInjectionEnabled);
Assert.IsTrue(SqlCommandTextStackTraceInjector.HashInjectionEnabled);
Assert.IsTrue(SqlCommandTextStackTraceInjector.StackInjectionEnabled);
Assert.AreEqual("", SqlCommandTextStackTraceInjector.StackFrameIgnorePrefixes);
service.RemoveSettings(settings.Id);
var id = service.StoreCurrentSettings(settings.MachineRegEx, settings.ProcessNameRegEx);
Assert.AreNotEqual(settings.Id, id);
service.ApplySettingsFromRepository();
Assert.IsFalse(service.Enabled);
Assert.IsTrue(SqlCommandRegExProcessor.RegExInjectionEnabled);
Assert.IsTrue(SqlCommandTextStackTraceInjector.HashInjectionEnabled);
Assert.IsTrue(SqlCommandTextStackTraceInjector.StackInjectionEnabled);
Assert.AreEqual("", SqlCommandTextStackTraceInjector.StackFrameIgnorePrefixes);
service.RemoveSettings(id);
// ReSharper disable once AccessToDisposedClosure
Assert.ThrowsException <SqlRewriteRuleDbRepositoryException>(() => { service.RemoveSettings(id); });
}
public void TestBasicSqlRewriteRuleServiceWithAutoRefreshTimerCausingExceptionByDroppingTable()
{
using var repo = new SqlRewriteDbRepository(Settings.Default.ConnectionString);
using var service = new SqlRewriteRuleService(repo, true);
Exception throwException = null;
service.ExceptionEvent += e => { throwException = e; };
using var conn = new SqlConnection(Settings.Default.ConnectionString);
conn.Open();
using var cmd = new SqlCommand("DROP TABLE SqlRewriteRegistry", conn);
cmd.ExecuteNonQuery();
var exceptionThrown = false;
service.ExceptionEvent += e => exceptionThrown = true;
service.AutoRefreshTimerInterval = 500;
service.AutoRefreshRulesAndSettingsEnabled = true;
Thread.Sleep(1000);
Assert.IsNotNull(throwException);
Assert.IsTrue(service.Enabled);
Assert.IsTrue(exceptionThrown);
}
public void TestBasicSqlRewriteRuleServiceWithAutoRefreshTimerCausingExceptionByInsertingBadRegEx()
{
using var repo = new SqlRewriteDbRepository(Settings.Default.ConnectionString);
using var service = new SqlRewriteRuleService(repo, true);
Assert.IsTrue(service.Enabled);
Exception throwException = null;
service.ExceptionEvent += e => { throwException = e; };
using var conn = new SqlConnection(Settings.Default.ConnectionString);
conn.Open();
using var cmd = new SqlCommand($"INSERT INTO SqlRewriteRegistry (DatabaseRegEx, QueryMatchRegEx, QueryReplacementString) VALUES ('.*)', '{Stm}', 'hello')", conn);
cmd.ExecuteNonQuery();
var exceptionThrown = false;
service.ExceptionEvent += e => exceptionThrown = true;
service.AutoRefreshTimerInterval = 500;
service.AutoRefreshRulesAndSettingsEnabled = true;
Thread.Sleep(1000);
Assert.IsNotNull(throwException);
Assert.IsTrue(service.Enabled);
Assert.IsTrue(exceptionThrown);
}
public void TestCreateRepositoryWithConnectionString()
{
using (var repository = new SqlRewriteDbRepository(Settings.Default.ConnectionString))
Assert.IsNotNull(repository);
}
