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, 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); } }