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