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 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")); }
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 TestCreateSqlRewriteRuleService() { using var conn = new SqlConnection(Settings.Default.ConnectionString); using var service = new SqlRewriteRuleService(new SqlRewriteDbRepository(conn)); Assert.IsNotNull(service); }