protected internal Dictionary <string, StoredProcMetadata> GetFilteredStoredProcNames(StoredProxSearchCriteria filterOnNamesLike) { var allStoredProx = Sorting.AllStoredProx; if (allStoredProx == null || allStoredProx.Count <= 0) { return(null); } //apply filter if any if (filterOnNamesLike == null) { return(allStoredProx); } var matchedProx = new Dictionary <string, StoredProcMetadata>(); List <string> matchedKeys = null; if (!string.IsNullOrWhiteSpace(filterOnNamesLike.ExactName)) { matchedKeys = allStoredProx.Keys.Where( x => string.Equals(x, filterOnNamesLike.ExactName, StringComparison.OrdinalIgnoreCase)).ToList(); var exactNameEntries = allStoredProx.Keys.Where(matchedKeys.Contains) .ToDictionary(pName => pName, pName => allStoredProx[pName]); foreach (var k in exactNameEntries.Keys.Where(k => !matchedProx.ContainsKey(k))) { matchedProx.Add(k, exactNameEntries[k]); } } if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyStartingWith)) { matchedKeys = allStoredProx.Keys.Where( x => Regex.IsMatch(x, string.Format("^{0}.*", filterOnNamesLike.AnyStartingWith), RegexOptions.IgnoreCase)).ToList(); var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains) .ToDictionary(pName => pName, pName => allStoredProx[pName]); foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k))) { matchedProx.Add(k, nameEntries[k]); } } if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyNameEndingWith)) { matchedKeys = allStoredProx.Keys.Where( x => Regex.IsMatch(x, string.Format(".*{0}$", filterOnNamesLike.AnyStartingWith), RegexOptions.IgnoreCase)).ToList(); var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains) .ToDictionary(pName => pName, pName => allStoredProx[pName]); foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k))) { matchedProx.Add(k, nameEntries[k]); } } if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyNameContaining)) { matchedKeys = allStoredProx.Keys.Where( x => Regex.IsMatch(x, string.Format(".*{0}.*", filterOnNamesLike.AnyStartingWith), RegexOptions.IgnoreCase)).ToList(); var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains) .ToDictionary(pName => pName, pName => allStoredProx[pName]); foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k))) { matchedProx.Add(k, nameEntries[k]); } } allStoredProx = matchedProx; if (filterOnNamesLike.SqlInjOnParamNamesLike == null || filterOnNamesLike.SqlInjOnParamNamesLike.Count <= 0) { return(allStoredProx); } foreach (var param in allStoredProx.SelectMany(x => x.Value.Parameters)) { foreach (var namedLike in filterOnNamesLike.SqlInjOnParamNamesLike) { if (!param.ParamName.Contains(namedLike)) { continue; } param.IsOpenToSqlInj = true; break; } } return(allStoredProx); }
/// <summary> /// The synchronous version of the function which gets /// the returned datasets from stored prox at the location specified /// at <see cref="connectionString"/>. /// </summary> /// <param name="filterOnNamesLike"> A proc(s) search criteria. </param> /// <param name="connectionString"> /// The calling assembly is forced to specify what the connection string is. /// This value should bare a semblance to the current connection values at /// <see cref="NfConfig.SqlServer"/> and /// <see cref="NfConfig.SqlCatalog"/> /// but its value is not inspected verifying this. /// </param> /// <remarks> /// The excessive level of indirection here is an attempt to gain control /// over the fact that any stored proc which itself calls 'sp_executesql' does /// not honor the timeout value assigned on the <see cref="System.Data.SqlClient.SqlCommand"/>. /// Since every proc is invoked with random values, a stored proc /// may run endlessly and comsume the entire machines resources. /// Placing all this under the control of an autonomous process allows for the process, as a whole, /// to be shutdown, and SqlServer has to clean up the mess. /// </remarks> public void GetSpResultSetXsd(StoredProxSearchCriteria filterOnNamesLike, string connectionString) { if (string.IsNullOrWhiteSpace(_nfHbmInvokeProxExePath) && !string.IsNullOrWhiteSpace(NfConfig.BinDirectories.Root)) { _nfHbmInvokeProxExePath = Path.Combine(NfConfig.BinDirectories.Root, "NoFuture.Hbm.InvokeStoredProc"); } //blow up for this if (!string.IsNullOrWhiteSpace(_nfHbmInvokeProxExePath) || !File.Exists(_nfHbmInvokeProxExePath)) { throw new ItsDeadJim(string.Format("The required console app is missing at '{0}'", _nfHbmInvokeProxExePath)); } BroadcastProgress(new ProgressMessage { Activity = "Loading current connection", Status = Status }); Settings.LoadOutputPathCurrentSettings(); BroadcastProgress(new ProgressMessage { Activity = "Getting list of stored proc targets", Status = Status }); //copy into local copy the global sql inj param names if (Settings.SqlInjParamNames.Any()) { filterOnNamesLike = filterOnNamesLike ?? new StoredProxSearchCriteria(); filterOnNamesLike.SqlInjOnParamNamesLike.AddRange(Settings.SqlInjParamNames); } //check the current connection has any procs present var allStoredProx = GetFilteredStoredProcNames(filterOnNamesLike); if (allStoredProx == null || allStoredProx.Count <= 0) { BroadcastProgress(new ProgressMessage { Activity = "There are no prox at NoFuture.Hbm.Sorting.AllStoredProx", Status = Status }); return; } //begin async listening for messages from autonomous processes var socketListeningTask = _taskFactory.StartNew(BeginReceiveMsgFromProcess); var counter = 0; foreach (var spItemKey in allStoredProx.Keys) { //check for the name being on the black list if (Settings.DoNotReference.Contains(spItemKey)) { counter += 1; continue; } //assign to instance level _currentSp = allStoredProx[spItemKey]; //serialize to disk - invoked process will read from this location _currentSp.SerializeToDisk(); //start a P\Invoke to NoFuture.Hbm.InvokeStoredProc.exe using (_currentProcess = new Process { StartInfo = new ProcessStartInfo(_nfHbmInvokeProxExePath, ConstructCmdLineArgs(_currentSp, connectionString)) { CreateNoWindow = true, UseShellExecute = false, RedirectStandardOutput = true, RedirectStandardError = true } }) { BroadcastProgress(new ProgressMessage { Activity = string.Format("At sp '{0}'", _currentSp.ProcName), Status = Status, ProgressCounter = Etc.CalcProgressCounter(counter, allStoredProx.Count), ProcName = _currentSp.ProcName }); try { Settings.WriteToStoredProcLog(string.Format("working '{0}'", _currentSp.ProcName)); //fire off the exe _currentProcess.Start(); //wait for it to finish or timeout _currentProcess.WaitForExit((Settings.HbmStoredProcXsdTimeOut + PInvokePaddingInSeconds) * 1000); //presumes it timed out if (!_currentProcess.HasExited) { //the only sure way to end some p.o.s. proc _currentProcess.Kill(); Settings.WriteToStoredProcLog(string.Format("Stored proc '{0}' is not responding is will be shutdown", _currentSp.ProcName)); Sorting.KilledProx.Add(_currentSp.ProcName); } } catch (Exception ex) { Settings.WriteToStoredProcLog(ex, string.Format("unknown error at '{0}'", _currentSp.ProcName)); } counter += 1; } } //communicate that the has completed BroadcastProgress(new ProgressMessage { Activity = "GetSpResultSetXsd has completed", Status = COMPLETED_STATUS_STRING, ProgressCounter = 100, }); if (socketListeningTask.IsCompleted) { socketListeningTask.Dispose(); } }
/// <summary> /// The asynchronous version of the function which gets /// the returned datasets from stored prox at the location specified /// </summary> /// <param name="filterOnNamesLike"></param> /// <param name="connectionString"></param> /// <remarks> /// See the details on <see cref="GetSpResultSetXsd"/> for /// more information on the manner an purpose of this function. /// </remarks> public void BeginGetSpResultSetXsd(StoredProxSearchCriteria filterOnNamesLike, string connectionString) { GetSpResultSetXsdTask = _taskFactory.StartNew(() => GetSpResultSetXsd(filterOnNamesLike, connectionString)); }
protected internal Dictionary<string, StoredProcMetadata> GetFilteredStoredProcNames(StoredProxSearchCriteria filterOnNamesLike) { var allStoredProx = Sorting.AllStoredProx; if (allStoredProx == null || allStoredProx.Count <= 0) return null; //apply filter if any if (filterOnNamesLike == null) { return allStoredProx; } var matchedProx = new Dictionary<string, StoredProcMetadata>(); List<string> matchedKeys = null; if (!string.IsNullOrWhiteSpace(filterOnNamesLike.ExactName)) { matchedKeys = allStoredProx.Keys.Where( x => string.Equals(x, filterOnNamesLike.ExactName, StringComparison.OrdinalIgnoreCase)).ToList(); var exactNameEntries = allStoredProx.Keys.Where(matchedKeys.Contains) .ToDictionary(pName => pName, pName => allStoredProx[pName]); foreach (var k in exactNameEntries.Keys.Where(k => !matchedProx.ContainsKey(k))) matchedProx.Add(k, exactNameEntries[k]); } if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyStartingWith)) { matchedKeys = allStoredProx.Keys.Where( x => Regex.IsMatch(x, string.Format("^{0}.*", filterOnNamesLike.AnyStartingWith), RegexOptions.IgnoreCase)).ToList(); var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains) .ToDictionary(pName => pName, pName => allStoredProx[pName]); foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k))) matchedProx.Add(k, nameEntries[k]); } if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyNameEndingWith)) { matchedKeys = allStoredProx.Keys.Where( x => Regex.IsMatch(x, string.Format(".*{0}$", filterOnNamesLike.AnyStartingWith), RegexOptions.IgnoreCase)).ToList(); var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains) .ToDictionary(pName => pName, pName => allStoredProx[pName]); foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k))) matchedProx.Add(k, nameEntries[k]); } if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyNameContaining)) { matchedKeys = allStoredProx.Keys.Where( x => Regex.IsMatch(x, string.Format(".*{0}.*", filterOnNamesLike.AnyStartingWith), RegexOptions.IgnoreCase)).ToList(); var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains) .ToDictionary(pName => pName, pName => allStoredProx[pName]); foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k))) matchedProx.Add(k, nameEntries[k]); } allStoredProx = matchedProx; if (filterOnNamesLike.SqlInjOnParamNamesLike == null || filterOnNamesLike.SqlInjOnParamNamesLike.Count <= 0) { return allStoredProx; } foreach (var param in allStoredProx.SelectMany(x => x.Value.Parameters)) { foreach (var namedLike in filterOnNamesLike.SqlInjOnParamNamesLike) { if (!param.ParamName.Contains(namedLike)) { continue; } param.IsOpenToSqlInj = true; break; } } return allStoredProx; }
/// <summary> /// The synchronous version of the function which gets /// the returned datasets from stored prox at the location specified /// at <see cref="connectionString"/>. /// </summary> /// <param name="filterOnNamesLike"> A proc(s) search criteria. </param> /// <param name="connectionString"> /// The calling assembly is forced to specify what the connection string is. /// This value should bare a semblance to the current connection values at /// <see cref="NfConfig.SqlServer"/> and /// <see cref="NfConfig.SqlCatalog"/> /// but its value is not inspected verifying this. /// </param> /// <remarks> /// The excessive level of indirection here is an attempt to gain control /// over the fact that any stored proc which itself calls 'sp_executesql' does /// not honor the timeout value assigned on the <see cref="System.Data.SqlClient.SqlCommand"/>. /// Since every proc is invoked with random values, a stored proc /// may run endlessly and comsume the entire machines resources. /// Placing all this under the control of an autonomous process allows for the process, as a whole, /// to be shutdown, and SqlServer has to clean up the mess. /// </remarks> public void GetSpResultSetXsd(StoredProxSearchCriteria filterOnNamesLike, string connectionString) { //blow up for this if(!File.Exists(_nfHbmInvokeProxExePath)) throw new ItsDeadJim(string.Format("The required console app is missing at '{0}'", _nfHbmInvokeProxExePath)); BroadcastProgress(new ProgressMessage { Activity = "Loading current connection", Status = Status }); Settings.LoadOutputPathCurrentSettings(); BroadcastProgress(new ProgressMessage { Activity = "Getting list of stored proc targets", Status = Status }); //copy into local copy the global sql inj param names if (Settings.SqlInjParamNames.Any()) { filterOnNamesLike = filterOnNamesLike ?? new StoredProxSearchCriteria(); filterOnNamesLike.SqlInjOnParamNamesLike.AddRange(Settings.SqlInjParamNames); } //check the current connection has any procs present var allStoredProx = GetFilteredStoredProcNames(filterOnNamesLike); if (allStoredProx == null || allStoredProx.Count <= 0) { BroadcastProgress(new ProgressMessage { Activity = "There are no prox at NoFuture.Hbm.Sorting.AllStoredProx", Status = Status }); return; } //begin async listening for messages from autonomous processes var socketListeningTask = _taskFactory.StartNew(BeginReceiveMsgFromProcess); var counter = 0; foreach (var spItemKey in allStoredProx.Keys) { //check for the name being on the black list if (Settings.DoNotReference.Contains(spItemKey)) { counter += 1; continue; } //assign to instance level _currentSp = allStoredProx[spItemKey]; //serialize to disk - invoked process will read from this location _currentSp.SerializeToDisk(); //start a P\Invoke to NoFuture.Hbm.InvokeStoredProc.exe using (_currentProcess = new Process { StartInfo = new ProcessStartInfo(_nfHbmInvokeProxExePath, ConstructCmdLineArgs(_currentSp, connectionString)) { CreateNoWindow = true, UseShellExecute = false, RedirectStandardOutput = true, RedirectStandardError = true } }) { BroadcastProgress(new ProgressMessage { Activity = string.Format("At sp '{0}'", _currentSp.ProcName), Status = Status, ProgressCounter = Etc.CalcProgressCounter(counter, allStoredProx.Count), ProcName = _currentSp.ProcName }); try { Settings.WriteToStoredProcLog(string.Format("working '{0}'", _currentSp.ProcName)); //fire off the exe _currentProcess.Start(); //wait for it to finish or timeout _currentProcess.WaitForExit((Settings.HbmStoredProcXsdTimeOut + PInvokePaddingInSeconds) * 1000); //presumes it timed out if (!_currentProcess.HasExited) { //the only sure way to end some p.o.s. proc _currentProcess.Kill(); Settings.WriteToStoredProcLog(string.Format("Stored proc '{0}' is not responding is will be shutdown", _currentSp.ProcName)); Sorting.KilledProx.Add(_currentSp.ProcName); } } catch (Exception ex) { Settings.WriteToStoredProcLog(ex, string.Format("unknown error at '{0}'", _currentSp.ProcName)); } counter += 1; } } //communicate that the has completed BroadcastProgress(new ProgressMessage { Activity = "GetSpResultSetXsd has completed", Status = COMPLETED_STATUS_STRING, ProgressCounter = 100, }); if(socketListeningTask.IsCompleted) socketListeningTask.Dispose(); }
/// <summary> /// The asynchronous version of the function which gets /// the returned datasets from stored prox at the location specified /// </summary> /// <param name="filterOnNamesLike"></param> /// <param name="connectionString"></param> /// <remarks> /// See the details on <see cref="GetSpResultSetXsd"/> for /// more information on the manner an purpose of this function. /// </remarks> public void BeginGetSpResultSetXsd(StoredProxSearchCriteria filterOnNamesLike, string connectionString) { GetSpResultSetXsdTask = _taskFactory.StartNew(() => GetSpResultSetXsd(filterOnNamesLike, connectionString)); }