public void IsInLargeCommentHeaderTest_InCommentHeader()
{
string rawScript = @"This is my script
and the results of my search
will be in the comment header
/****************
* I'm here!!
*****************/";
int index = 85;
bool expected = true;
bool actual;
actual = ScriptHandlingHelper.IsInLargeCommentHeader(rawScript, index);
Assert.AreEqual(expected, actual);
}
public void IsInLargeCommentHeaderTest_NotInCommentHeader()
{
string rawScript = @"This is my script
and the results of my search
will not be in a comment header
/****************
* not here
*****************/";
int index = 24;
bool expected = false;
bool actual;
actual = ScriptHandlingHelper.IsInLargeCommentHeader(rawScript, index);
Assert.AreEqual(expected, actual);
}
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)
{
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;
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, 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);
}
}
