public bool CheckPolicy(string script, List <Match> commentBlockMatches, out string message) { try { message = string.Empty; Regex selectStar = new Regex(@"(SELECT\s*\*)|(SELECT\s*.*\.\*)", RegexOptions.IgnoreCase | RegexOptions.Compiled); // Finds "SELECT *" and also "SELECT abc.*" List <string> regStrings = new List <string>(); var tmpRegStrings = (from a in this.arguments select a.Value); if (tmpRegStrings.Count() > 0) { regStrings = tmpRegStrings.ToList(); } List <Regex> selectStarExceptions = new List <Regex>(); foreach (string regStr in regStrings) { selectStarExceptions.Add(new Regex(regStr, RegexOptions.IgnoreCase)); } MatchCollection starMatches = selectStar.Matches(script); if (starMatches.Count == 0) { return(true); } foreach (Match star in starMatches) { if (ScriptHandlingHelper.IsInComment(star.Index, commentBlockMatches)) { continue; } bool foundException = false; foreach (Regex regExcept in selectStarExceptions) { MatchCollection exceptionMatches = regExcept.Matches(script, star.Index); if (exceptionMatches.Count == 0) { int lineNumber = PolicyHelper.GetLineNumber(script, star.Index); message = this.ErrorMessage.Replace(PolicyHelper.LineNumberToken, lineNumber.ToString()); } else if (exceptionMatches[0].Index == star.Index) { //this "star" match is an exception case, so it's ok. foundException = true; break; } } if (!foundException) { int lineNumber = PolicyHelper.GetLineNumber(script, star.Index); message = this.ErrorMessage.Replace(PolicyHelper.LineNumberToken, lineNumber.ToString()); return(false); } } message = string.Empty; return(true); } catch (Exception exe) { message = "Error processing script policy. See application log file for details"; log.LogError(exe, message); return(false); } }
public bool CheckPolicy(string script, List <Match> commentBlockMatches, out string message) { try { message = string.Empty; Dictionary <int, bool> rulesLine = new Dictionary <int, bool>(); foreach (p.IScriptPolicyArgument argument in this.arguments) { Regex syntaxCheck = new Regex(argument.Value, RegexOptions.IgnoreCase); MatchCollection syntaxMatches = syntaxCheck.Matches(script); if (syntaxMatches.Count == 0) { continue; } //Check match and add each line to a collection. A true value means it passes with an exception. foreach (Match syn in syntaxMatches) { if (!argument.IsGlobalException && ScriptHandlingHelper.IsInComment(syn.Index, commentBlockMatches)) //don't care about matches in comments unless it's a global exception. { continue; } if (argument.IsGlobalException) //found a global exception, so pass the test. { return(true); } if (rulesLine.ContainsKey(syn.Index)) { if (argument.IsLineException) { rulesLine[syn.Index] = true; } } else { if (argument.IsLineException) { rulesLine.Add(syn.Index, true); } else { rulesLine.Add(syn.Index, false); } } } } //Don't have any matches, so we must pass :-) if (rulesLine.Count == 0) { return(true); } //See if we have any that are set to false... var f = from r in rulesLine where r.Value == false select r.Key; if (f.Any()) { List <int> line = f.ToList(); //Return an error for the first line... int lineNumber; if (line.Count() == 0) { lineNumber = 1; } else { lineNumber = PolicyHelper.GetLineNumber(script, line[0]); } message = this.ErrorMessage.Replace(PolicyHelper.LineNumberToken, lineNumber.ToString()); return(false); } else { return(true); } } catch (Exception exe) { message = "Error processing script policy. See application log file for details"; log.LogError(exe, message); return(false); } }
public bool CheckPolicy(string script, List <Match> commentBlockMatches, out string message) { bool passed = true; try { List <string> badReferenceObjs = new List <string>(); string rawScript = script; string subStr; int lengthToWhere; Match current; Match next; Regex regTokens = new Regex(@"(\bINNER JOIN\b)|(\bOUTER JOIN\b) |(\bFROM\b)|(\bWHERE\b)|(\bON\b)|(WITH *\(NOLOCK\))|(\bLEFT JOIN\b)|(\bRIGHT JOIN\b)|(\bINTO\b)|(\bJOIN\b)|(\bGROUP BY\b)|(\bDELETE FROM\b)|(\bUPDATE\b)|(\bset\b)|(\binserted\b)|(\bdeleted\b)|(\bAS\b)|(\bNO ACTION\b)", RegexOptions.IgnoreCase | RegexOptions.Compiled); Regex regSelects = new Regex(@"(\bINNER JOIN\b)|(\bOUTER JOIN\b) |(\bFROM\b)|(\bLEFT JOIN\b)|(\bRIGHT JOIN\b)|(\bJOIN\b)|(\bDELETE FROM\b)|(\bUPDATE\b)", RegexOptions.IgnoreCase | RegexOptions.Compiled); Regex regWheres = new Regex(@"(\bWHERE\b)|(\bON\b)|(\bINNER JOIN\b)|(\bOUTER JOIN\b)|(\bLEFT JOIN\b)|(\bRIGHT JOIN\b)|(\bJOIN\b)|(\bset\b)", RegexOptions.IgnoreCase | RegexOptions.Compiled); Regex regFrom = new Regex(@"(\bFROM\b)|(\bDELETE FROM\b)|(\bUPDATE\b)", RegexOptions.IgnoreCase | RegexOptions.Compiled); Regex regFromWithTableName = new Regex(@"(\bFROM\b\s*.*\s*)", RegexOptions.IgnoreCase | RegexOptions.Compiled); Regex regNoLock = new Regex(@"(WITH *\(NOLOCK\))", RegexOptions.IgnoreCase | RegexOptions.Compiled); Regex regCursorInto = new Regex(@"(\bINTO\b)", RegexOptions.IgnoreCase | RegexOptions.Compiled); Regex regTriggerTables = new Regex(@"(\binserted\b)|(\bdeleted\b)", RegexOptions.IgnoreCase | RegexOptions.Compiled); Regex regTriggerUpdateAs = new Regex(@"(\bAS\b)", RegexOptions.IgnoreCase | RegexOptions.Compiled); Regex regForeignKeyAction = new Regex(@"(\bNO ACTION\b)", RegexOptions.IgnoreCase | RegexOptions.Compiled); MatchCollection coll = regTokens.Matches(rawScript); int textIndex = 0; for (int i = 0; i < coll.Count; i++) { current = coll[i]; if (i == coll.Count - 1 && !regFrom.Match(current.Value).Success) { break; } else if (i == coll.Count - 1) // the last match is an unpaired FROM { next = current; //give it a fake value } else { next = coll[i + 1]; } //Ignore trigger selectes "FROM inserted" or "FROM deleted" if (regFrom.Match(current.Value).Success&& regTriggerTables.Match(next.Value).Success) { continue; } //Ignore trigger declaration "FOR UPDATE AS" if (regFrom.Match(current.Value).Success&& regTriggerUpdateAs.Match(next.Value).Success) { continue; } //Ignore foreign key action "UPDATE NO ACTION" if (regFrom.Match(current.Value).Success&& regForeignKeyAction.Match(next.Value).Success) { continue; } if (!ScriptHandlingHelper.IsInComment(current.Index, commentBlockMatches) && regSelects.Match(current.Value).Success&& (regWheres.Match(next.Value).Success || regNoLock.Match(next.Value).Success)) // we have found our FROM .. WHERE or INNER JOIN ... ON limits { lengthToWhere = next.Index - textIndex; int start = current.Index + current.Value.Length; int length = next.Index - start; string sub = rawScript.Substring(start, length); if (sub.IndexOf('.') == -1 && !sub.Trim().StartsWith("@") && !sub.Trim().StartsWith("#")) { string regString = "(WITH *" + sub.Trim().Replace(")", "\\)").Replace("(", "\\(") + @"\s*\()"; try { Regex regCTE = new Regex(regString); //Check for a Common Table Entity (CTE) declaration for this item. If it is declared as a CTE, don't fail the check, otherwise, fail it. if (regCTE.Matches(script).Count == 0) { passed = false; if (!badReferenceObjs.Contains(sub.Trim())) { int line = PolicyHelper.GetLineNumber(rawScript, start); badReferenceObjs.Add(sub.Trim() + " (line: " + line.ToString() + ")"); } } } catch (ArgumentException exe) { int line = PolicyHelper.GetLineNumber(rawScript, start); log.LogWarning(exe, $"Error validating QualifiedNamesPolicy. Issue on line {line.ToString()}. Problem with generated RegularExpression: {regString}"); message = "Error running Qualified Named Policy. This script will need to be manually checked. (See log file for details)"; return(false); } } } else if (!ScriptHandlingHelper.IsInComment(current.Index, commentBlockMatches) && regFrom.Match(current.Value).Success&& !regNoLock.Match(next.Value).Success&& !regCursorInto.Match(next.Value).Success) //handle the FROM that doesn't have a following WHERE or INNER or OUTER JOINS { if (regFromWithTableName.Match(rawScript, current.Index).Value.Length > 0) { Match subMatch = regFromWithTableName.Match(rawScript, current.Index); if (subMatch != null & subMatch.Value.Length > 0) { subStr = subMatch.Value.Substring(4); if (subStr.IndexOf('.') == -1 && !subStr.Trim().StartsWith("@") && !subStr.Trim().StartsWith("#") && !ScriptHandlingHelper.IsInComment(current.Index, commentBlockMatches)) { passed = false; if (!badReferenceObjs.Contains(subStr.Trim())) { int line = PolicyHelper.GetLineNumber(rawScript, subMatch.Index); badReferenceObjs.Add(subStr.Trim() + " (line: " + line.ToString() + ")"); } } } } } } if (passed) { message = string.Empty; } else { message = "Missing schema qualifier on: " + String.Join(", ", badReferenceObjs.ToArray()); } } catch (Exception exe) { message = "Error processing script policy. See application log file for details"; log.LogError(exe, message); passed = false; } return(passed); }
public bool CheckPolicy(string script, string targetDatabase, List <Match> commentBlockMatches, out string message) { try { string parentRegex = string.Empty; string policyTargetDatabase = string.Empty; List <p.IScriptPolicyArgument> childPairRegexList = new List <p.IScriptPolicyArgument>(); List <p.IScriptPolicyArgument> childDontPairRegexList = new List <p.IScriptPolicyArgument>(); //Parse out the arguments foreach (p.IScriptPolicyArgument argument in this.arguments) { switch (argument.Name) { case parentRegexKey: if (parentRegex.Length > 0) { log.LogWarning($"The ScriptSyntaxPairingCheckPolicy \"{this.ShortDescription}\" already has a {ScriptSyntaxPairingCheckPolicy.parentRegexKey} value of \"{parentRegex}\". This is being overwritten by a value of \"{argument.Value}\""); } parentRegex = argument.Value; break; case childPairRegexKey: childPairRegexList.Add(argument); break; case childDontPairRegexKey: childDontPairRegexList.Add(argument); break; case targetDatabaseKey: policyTargetDatabase = argument.Value; break; default: log.LogWarning($"The ScriptSyntaxPairingCheckPolicy \"{this.ShortDescription}\" has an unrecognized argument. Name: {argument.Name}; Value:{argument.Value}"); break; } } //Check to make sure we have some values set if (parentRegex.Length == 0) { message = String.Format("The ScriptSyntaxPairingCheckPolicy \"{0}\" does not have a {1} argument/value. Unable to process policy.", this.ShortDescription, ScriptSyntaxPairingCheckPolicy.parentRegexKey); log.LogWarning(message); return(true); } if (childDontPairRegexList.Count == 0 && childPairRegexList.Count == 0) { message = String.Format("The ScriptSyntaxPairingCheckPolicy \"{0}\" does not have any {1} or {2} argument/values. Unable to process policy.", this.ShortDescription, ScriptSyntaxPairingCheckPolicy.childPairRegexKey, ScriptSyntaxPairingCheckPolicy.childDontPairRegexKey); log.LogWarning(message); return(true); } //Does this policy apply to the current script? if (policyTargetDatabase.Trim().Length > 0 && targetDatabase.ToLower().Trim() != policyTargetDatabase.ToLower().Trim()) { message = String.Format("Script target database {0} does not match policy target database {1}. Policy passes.", targetDatabase, policyTargetDatabase); return(true); } //Match the parent regex Regex parentCheck = new Regex(parentRegex, RegexOptions.IgnoreCase); MatchCollection syntaxMatches = parentCheck.Matches(script); //No matches? Nevermind then... if (syntaxMatches.Count == 0) { message = "No parent match found. Policy passes."; return(true); } //make sure at least one of the parent matches is not in a comment bool foundParent = false; foreach (Match parentMatch in syntaxMatches) { if (ScriptHandlingHelper.IsInComment(parentMatch.Index, commentBlockMatches)) { continue; } else { foundParent = true; } } if (!foundParent) { message = "No uncommented parent match found. Policy passes."; return(true); } //If we have a match.. let's make sure we have the child matches foreach (p.IScriptPolicyArgument child in childPairRegexList) { Regex childPair = new Regex(child.Value, RegexOptions.IgnoreCase); MatchCollection childPairMatches = childPair.Matches(script); if (childPairMatches.Count == 0) { if (child.FailureMessage.Length > 0) { message = child.FailureMessage; } else { message = String.Format("No child pair found with regular expression {0}", child.Value); } return(false); } else { //If matches are found, make sure they are not in comments. bool foundUncommentedChild = false; foreach (Match cM in childPairMatches) { if (!ScriptHandlingHelper.IsInComment(cM.Index, commentBlockMatches)) { foundUncommentedChild = true; } } if (!foundUncommentedChild) { if (child.FailureMessage.Length > 0) { message = child.FailureMessage; } else { message = String.Format("No child pair found with regular expression {0}", child.Value); } return(false); } } } //If we have a match.. let's make sure we have the child matches foreach (p.IScriptPolicyArgument child in childDontPairRegexList) { Regex childPair = new Regex(child.Value, RegexOptions.IgnoreCase); MatchCollection childPairMatches = childPair.Matches(script); if (childPairMatches.Count > 0) { //If matches are found, make sure they are not in comments. bool hadUnCommentedChild = true; foreach (Match cM in childPairMatches) { if (!ScriptHandlingHelper.IsInComment(cM.Index, commentBlockMatches)) { hadUnCommentedChild = true; } } if (hadUnCommentedChild) { if (child.FailureMessage.Length > 0) { message = child.FailureMessage; } else { message = String.Format("A child match was found for regular expression \"{0}\". This script pairing is not allowed.", child.Value); } return(false); } } } message = string.Empty; return(true); } catch (Exception exe) { message = String.Format("Error processing script policy {0}. See application log file for details", this.ShortDescription); log.LogError(exe, message); return(false); } }