Larry Steinle

March 12, 2011

AdCommandTextParser: Parsing SQL Statements

Filed under: Active Directory,RegEx,VS.Net — Larry Steinle @ 11:48 pm
Tags: , , , ,

The most difficult task in the construction of the Active Directory Data Access Layer was parsing the SQL Statements so that the information would be easily accessible to populate the DirectorySearcher object. In today’s post we will review the capabilities of the parser followed by the sql statement parsing code.

Active Directory Data Access Layer Series

This is the fourth post of an eight part series about the Active Directory Data Access Layer. As each post builds on the previous it may be helpful to review older posts prior to reading this one. If you would like to download a working copy of the AD DAL please refer to the download on the Code Share page.

SQL Statement Parsing Scope Definition

The Active Directory Data Access Layer was created with the intention of making it easier to manage data in Active Directory. It was not meant to replicate all of the mathematical, conditional and string manipulation found in most sql engines.

By focusing on the primary purpose of the Active Directory Data Access Layer we simplify the code requirements and reduce the complexity of the parser.

For clarification lets review the supported syntax that can be parsed for each sql statement.

Select Statement Syntax

SELECT attributeName1, attributeName2, ..., attributeName[n]
FROM ['RootPath'.]objectCategory
WHERE condition1 [AND|OR] condition2 [AND|OR] condition[n]
ORDER BY attributeName1, attributeName2, ..., attributeName[n]

SELECT attributeName1, attributeName2, ..., attributeName[n]
FROM ['RootPath'.]objectCategory
WHERE 'adsiFilter'
ORDER BY attributeName1, attributeName2, ..., attributeName[n]

Insert Statement Syntax

INSERT INTO ['RootPath'.]objectCategory (name, attributeName1, attributeName2, ..., attributeName[n])
VALUES (nameValue, attributeValue1, attributeValue2, ..., attributeValue[n])

Update Statement Syntax

UPDATE ['RootPath'.]objectCategory
SET [+-]attributeName1 = attributeValue1,
[+-]attributeName2 = attributeValue2,
    ...,
[+-]attributeName[n] = attributeValue[n]
WHERE condition1 [AND|OR] condition2 [AND|OR] condition[n]
ORDER BY attributeName1, attributeName2, ..., attributeName[n]

UPDATE ['RootPath'.]objectCategory
SET [+-]attributeName1 = attributeValue1,
[+-]attributeName2 = attributeValue2,
    ...,
[+-]attributeName[n] = attributeValue[n]
WHERE 'adsiFilter'
ORDER BY attributeName1, attributeName2, ..., attributeName[n]

Delete Statement Syntax

DELETE FROM ['RootPath'.]objectCategory
WHERE condition1 [AND|OR] condition2 [AND|OR] condition[n]

DELETE FROM 'RootPath'.objectCategory
WHERE 'adsiFilter'
DELETETREE FROM 'RootPath'.objectCategory
WHERE condition1 [AND|OR] condition2 [AND|OR] condition[n]

DELETETREE FROM 'RootPath'.objectCategory
WHERE 'adsiFilter'

Syntax Summary

Notice that conditions are only supported in the where clause. They are not supported in the select, insert or update clause. The where clause may be a sql statement or an adsi filter but not both. The conditions in the where clause may be organized using opening and closing parens.

In the update statement the leading addition or subtraction sign is used to manage multivalued attributes. A plus indicates that the value is to be appended while a negative indicates that the value is to be removed. Please note limitations exist with multivalued attributes in older domain controllers. So test multivalued attribute management where the value count exceeds the maximum number of records returned per page.

All statements support a root path with an object category. The object category represents the object to query (the table name in sql). The optional root path indicates where the search scope should begin.

The insert statement requires at least a name attribute assignment. Depending on the object being created other attributes may be required.

A new statement called, DeleteTree, has been introduced. This operator specifically indicates that any objects found that match the search criteria should be deleted even when they contain child objects. The DeleteTree statement requires administrative permissions on an object to execute. Use with extreme caution!

Supported Operators and Commands

The following operators will be supported in the where clause:

  • Equal operator (=)
  • Not operator (!)
  • Not equal (!=)
  • Less than or equal to (<=)
    Note: Less than is not supported in ADSI and therefore will be translated to less than or equal to.
  • Greater than or equal to (>=)
    Note: Greater than is not supported in ADSI and therefore will be translated to greater than or equal to.
  • Approximately equal to (~=)
  • Bitwise and operator (&&)
  • Bitwise or operator (||)

The bitwise operators are very useful with the userAccountControl attribute making it very easy to find disabled accounts for example.

The following commands will be supported in the where clause:

  • NOT (the not command can be applied to any condition)
  • Is
  • Exists
  • IsExactly
  • Contains
  • StartsWith
  • EndsWith
  • In
  • Like
  • ChainDN

The ChainDN allows the system to locate users who are members of groups even when the user is not directly assigned to the group. It works with any distinguished name attribute type.

CommandTextParser

We begin with the basic functionality required for any command text statement.

To make the statements easier to parse we will recycle the code from the post, Use Regular Expressions to Clean SQL Statements. Next the parser will support splitting the statement into segments to make it easier to work with. A segment is the individual parts of the statement. For example, in a select statement a segment would exist for the select, the from, the where and the order by clauses. The command text parser class will host the regular expression pattern constants.

Imports System.Text.RegularExpressions

Namespace Data
  ''' <summary>
  ''' Primary SQL commands
  ''' </summary>
  Public Enum StatementTypes
    ''' <summary>
    ''' No SQL commands supported.
    ''' </summary>
    ''' <remarks></remarks>
    None = 0

    ''' <summary>
    ''' Indicates that there are no SQL commands as the
    ''' text will be the name of a stored procedure.
    ''' </summary>
    ''' <remarks></remarks>
    Procedure = 0

    ''' <summary>
    ''' Alter statement type.
    ''' </summary>
    ''' <remarks></remarks>
    Alter = 1

    ''' <summary>
    ''' Create statement type.
    ''' </summary>
    ''' <remarks></remarks>
    Create = 2

    ''' <summary>
    ''' Delete statement type.
    ''' </summary>
    ''' <remarks></remarks>
    Delete = 4

    ''' <summary>
    ''' Drop statement type.
    ''' </summary>
    ''' <remarks></remarks>
    Drop = 8

    ''' <summary>
    ''' Execute statement type.
    ''' </summary>
    ''' <remarks></remarks>
    Execute = 16

    ''' <summary>
    ''' Insert statement type.
    ''' </summary>
    ''' <remarks></remarks>
    Insert = 32

    ''' <summary>
    ''' Select statement type.
    ''' </summary>
    ''' <remarks></remarks>
    [Select] = 64

    ''' <summary>
    ''' Update statement type.
    ''' </summary>
    ''' <remarks></remarks>
    Update = 128

    ''' <summary>
    ''' Union statement type.
    ''' </summary>
    ''' <remarks></remarks>
    Union = 256

    ''' <summary>
    ''' Indicates that multiple SQL Statements may be executed within a single server call.
    ''' Each set of statements must be seperated by a semicolon.
    ''' </summary>
    ''' <remarks></remarks>
    Batch = 512

    ''' <summary>
    ''' Merge statement type.
    ''' </summary>
    ''' <remarks></remarks>
    Merge = 1024 Or Insert Or Update Or Delete Or [Select]
  End Enum

  ''' <summary>
  ''' SQL statement parser.
  ''' </summary>
  ''' <remarks>
  ''' Intended for use with case-insensitive, multi-line regular expression options.
  ''' As there is no not logic in the expressions it may be necessary
  ''' to check that results are not part of a comment or text block.
  ''' <example>
  ''' A semicolon inside a text block should not be treated as a SQL
  ''' End-of-Statement Break.
  ''' </example>
  ''' </remarks>
  Public Class CommandTextParser
    ''' <summary>
    ''' Regular expression to locate SQL End-of-Statement Break.
    ''' </summary>
    ''' <remarks></remarks>
    Friend Const BATCH_BREAK As String = ";"

    ''' <summary>
    ''' Regular expression to locate primary sql commands.
    ''' </summary>
    ''' <remarks></remarks>
    Friend Const STATEMENTS As String = "\b(ALTER|CREATE|DELETE|DELETETREE|DROP|EXEC(UTE){0,1}|INSERT( +INTO){0,1}|MERGE|SELECT|UPDATE)\b"

    ''' <summary>
    ''' Regular expression to locate secondary sql commands.
    ''' </summary>
    ''' <remarks></remarks>
    Friend Const CLAUSES As String = "\b(SET|FROM|PIVOT|(CROSS +|LEFT +|RIGHT +|)(INNER +|OUTER +|)JOIN|WHEN +MATCHED|WHEN +NOT +MATCHED +BY|WHERE|OUTPUT|VALUES|USING|ORDER +BY|GROUP +BY|HAVING)\b"

    ''' <summary>
    ''' Regular expression to locate text blocks.
    ''' </summary>
    ''' <remarks></remarks>
    Friend Const TEXT_BLOCK As String = "'(''|[^'])*'"

    ''' <summary>
    ''' Regular expression to locate single line comments.
    ''' </summary>
    ''' <remarks></remarks>
    Friend Const SINGLE_ROW_COMMENT_BLOCK As String = "--[^\r\n]*"

    ''' <summary>
    ''' Regular expression to locate multiple line comment blocks.
    ''' </summary>
    ''' <remarks></remarks>
    Friend Const MULTI_ROW_COMMENT_BLOCK As String = "/\*[\w\W]*?(?=\*/)\*/"

    ''' <summary>
    ''' Regular expression to locate tabs, returns and line feeds.
    ''' </summary>
    ''' <remarks></remarks>
    Friend Const TAB_CARRIAGE_RETURN_LINE_FEED As String = "[\t\r\n]"

    ''' <summary>
    ''' Locates Recognized SQL Statements.
    ''' </summary>
    ''' <remarks>
    ''' The following list of ADSI statements is recognized adsi format in parens:
    ''' <list>
    ''' <item>Is (=___)</item>
    ''' <item>Is Not (!=___)</item>
    ''' <item>IsExactly (=___)</item>
    ''' <item>Not IsExactly (!=___)</item>
    ''' <item>Contains (=*__*)</item>
    ''' <item>Not Contains (!=*__*)</item>
    ''' <item>StartsWith (=__*)</item>
    ''' <item>Not StartsWith (!=__*)</item>
    ''' <item>EndsWith (=*___)</item>
    ''' <item>Not EndsWith (!=*___)</item>
    ''' <item>ChainDN (=*___)</item>
    ''' <item>Not ChainDN (!(=*___))</item>
    ''' <item>Exists (=*)</item>
    ''' <item>Not Exists (!=*)</item>
    ''' <item>Approximately Equal To (~___) (~=___) (=~____)</item>
    ''' <item>Less Than or Equal To (&lt;___) (&lt;=___) (=&lt;___)</item>
    ''' <item>Greater Than or Equal To (&gt;___) (&gt;=___) (=&gt;___)</item>
    ''' <item>BitWiseAnd ( &amp;&amp;___)</item>
    ''' <item>BitWiseOr (||___)</item>
    ''' </list>
    ''' </remarks>
    Friend Const SQL_OPERATORS As String = "\b((Not +){0,1}IS( +Not){0,1}|(Not +){0,1}Contains|(Not +){0,1}StartsWith|(Not +){0,1}EndsWith|(Not +){0,1}ChainDN|(Not +){0,1}Exists|(Not +){0,1}Like|(Not +){0,1}In)\b|(= *\*|= *!\*|!=|<>|<=|=<|>=|=>|<|>|~=|=~|~|=|&{2,2}|[|]{2,2})"

    ''' <summary>
    ''' Matchs on FieldName [not] CommandName (Values)
    ''' </summary>
    ''' <remarks></remarks>
    Friend Const CONDITION_FIELD_COMMAND_VALUE As String = "(\b\w*\b)? +(Not +){0,1}(ChainDN|Contains|EndsWith|In|Like|StartsWith) *\({0,1}(('(''|[^'])*')|[, ]*)*\){0,1}"

    ''' <summary>
    ''' Matches on [not] CommandName(FieldName)
    ''' </summary>
    ''' <remarks></remarks>
    Friend Const CONDITION_COMMAND_FIELD As String = "(Not +){0,1}(Exists) *\((('(''|[^'])*')|[^\r\n \)]*)\)"

    ''' <summary>
    ''' Matches on [not] FieldName Operator ComparisonValue
    ''' </summary>
    ''' <remarks></remarks>
    Friend Const CONDITION_FIELD_OPERATOR_VALUE As String = "(\b\w*\b)? *(= *\*|= *!\*|!=|<>|<=|=<|>=|=>|<|>|~=|=~|~|=|&{2,2}|[|]{2,2}) *(('(''|[^'])*')|[^\r\n \)]*)"

    ''' <summary>
    ''' Locates any remaining conditions that need the not operator applied to them prior to translating and/or logic.
    ''' </summary>
    ''' <remarks></remarks>
    Friend Const NOT_CONDITIONS_WITH_PARENS As String = " *NOT *(\([^/(/)]*\))"
    Friend Const NOT_CONDITIONS_WITHOUT_PARENS As String = "NOT( +|[^\w])((\[[^\[\]]*\])|[^\)]*)"

    ''' <summary>
    ''' Returns the name of the first operator found in the SQL Statement.
    ''' </summary>
    ''' <param name="commandText">
    ''' The SQL Statement.
    ''' </param>
    ''' <returns>
    ''' Returns the first operator in the SQL Statement if found, otherwise an empty string.
    ''' </returns>
    ''' <remarks>
    ''' It is recommended that ToRaw used to clean up the statement prior to calling GetFirstOperator.
    ''' </remarks>
    Protected Shared Function GetFirstOperator(ByVal commandText As String) As String
      Dim regExOptions As RegexOptions = regExOptions.IgnoreCase Or regExOptions.Multiline
      Dim regExPattern As String = "(" & TEXT_BLOCK & ")|(" & SINGLE_ROW_COMMENT_BLOCK & ")|(" & MULTI_ROW_COMMENT_BLOCK & ")|(" & STATEMENTS & ")|(" & CLAUSES & ")"
      Dim matches As MatchCollection = Regex.Matches(commandText, regExPattern, regExOptions)
      For Each matchItem As Match In matches
        Dim value As String = matchItem.Value.Trim
        If Not value.StartsWith("--") AndAlso Not value.StartsWith("/*") AndAlso Not value.StartsWith("'") Then
          Return value
        End If
      Next
      Return String.Empty
    End Function

    ''' <summary>
    ''' Get an array of strings representing each clause part in the SQL Statement.
    ''' </summary>
    ''' <param name="commandText">
    ''' The SQL Statement to split.
    ''' </param>
    ''' <returns>
    ''' Returns an array of string values representing each clause part in the SQL Statement.
    ''' </returns>
    ''' <remarks>
    ''' It is recommended that ToRaw used to clean up the statement prior to calling SplitSQL.
    ''' </remarks>
    Protected Shared Function SplitSQL(ByVal commandText As String) As String()
      Dim regExOptions As RegexOptions = regExOptions.IgnoreCase Or regExOptions.Multiline
      Dim regExPattern As String = "(" & TEXT_BLOCK & ")|(" & SINGLE_ROW_COMMENT_BLOCK & ")|(" & MULTI_ROW_COMMENT_BLOCK & ")|(" & STATEMENTS & ")|(" & CLAUSES & ")"
      Dim matches As MatchCollection = Regex.Matches(commandText, regExPattern, regExOptions)
      Dim segmentList As New System.Collections.ArrayList
      Dim startIndex As Integer = 0

      For Each matchItem As Match In matches
        If Not matchItem.Value.Trim.StartsWith("'") AndAlso matchItem.Index > startIndex Then
          Dim value As String = commandText.Substring(startIndex, matchItem.Index - startIndex).Trim
          If value.Length > 0 Then segmentList.Add(value)
          startIndex = matchItem.Index
        End If
      Next

      If startIndex < commandText.Length Then
        segmentList.Add(commandText.Substring(startIndex).Trim)
      End If

      Return DirectCast(segmentList.ToArray(GetType(String)), String())
    End Function

    ''' <summary>
    ''' Removes carriage returns, tabs, comments and extra spacing from the SQL Statement.
    ''' </summary>
    ''' <param name="commandText">A SQL Statement</param>
    ''' <returns>
    ''' A raw, formatless SQL Statement.
    ''' </returns>
    ''' <remarks>
    ''' While it is fairly simple to create a regular expression to find text,
    ''' it is more difficult to create a regular expression to ignore text.
    ''' This routine takes advantage of the regular expression's ease to locate
    ''' text but then uses standard code to ignore the matches we don't want.
    ''' As such the regular expression will include an instruction set to match
    ''' on both the wanted and unwanted conditions. The code will then iterate
    ''' thru the returned collection ignoring the unwanted matches which is
    ''' easier in code than in a regular expression.
    ''' </remarks>
    Public Shared Function ToRaw(ByVal commandText As String) As String
      Dim regExOptions As RegexOptions = regExOptions.IgnoreCase Or regExOptions.Multiline
      Dim rawText As String = commandText
      Dim regExText As String = "(" & TEXT_BLOCK & ")|(" & TAB_CARRIAGE_RETURN_LINE_FEED & ")|(" & SINGLE_ROW_COMMENT_BLOCK & ")|(" & MULTI_ROW_COMMENT_BLOCK & ")"

      'Replace Tab, Carriage Return, Line Feed, Single-row Comments and
      'Multi-row Comments with a space when not included inside a text block.
      Dim patternMatchList As MatchCollection = Regex.Matches(rawText, regExText, regExOptions)
      For patternIndex As Integer = patternMatchList.Count - 1 To 0 Step -1
        With patternMatchList.Item(patternIndex)
          If Not .Value.StartsWith("'") And Not .Value.EndsWith("'") Then
            rawText = rawText.Substring(0, .Index) & " " & rawText.Substring(.Index + .Length)
          End If
        End With
      Next

      'Remove extra spacing that is not contained inside text qualifers.
      patternMatchList = Regex.Matches(rawText, "'([^']|'')*'|[ ]{2,}", regExOptions)
      For patternIndex As Integer = patternMatchList.Count - 1 To 0 Step -1
        With patternMatchList.Item(patternIndex)
          If Not .Value.StartsWith("'") And Not .Value.EndsWith("'") Then
            rawText = rawText.Substring(0, .Index) & rawText.Substring(.Index + .Length - 1)
          End If
        End With
      Next

      'Return value without leading and trailing spaces.
      Return rawText.Trim
    End Function

    ''' <summary>
    ''' Throws an UnauthorizedAccessException when commandText contains unauthorized SQL Statements.
    ''' </summary>
    ''' <param name="commandText">
    ''' The SQL Statement to validate.
    ''' </param>
    ''' <param name="authorizedStatements">
    ''' The authorized actions.
    ''' </param>
    ''' <remarks>
    ''' Use care when authorizing batch commands and union commands!
    ''' While most examples demonstrate SQL Code Injection using a batch seperator, it may
    ''' be accomplished with a union statement as well. While the union approach may be more
    ''' difficult to work with and does not provide insert, update, delete capabilities it
    ''' does offer the option to extract valuable data from the target.
    ''' </remarks>
    Public Shared Sub ValidateStatement(ByVal commandText As String, ByVal authorizedStatements As StatementTypes)
      'Remove Comments, Tabs and Line Breaks
      Dim testText As String = ToRaw(commandText)

      'Construct Regular Expression
      Dim regExText As String = String.Format("({0})|({1})|({2})|(\bUNION( +ALL){0,1}\b)", TEXT_BLOCK, BATCH_BREAK, STATEMENTS)

      'Remove Authorized Options
      If (authorizedStatements And StatementTypes.Batch) = StatementTypes.Batch Then regExText = regExText.Replace(String.Format("({0})", BATCH_BREAK), String.Empty)
      If (authorizedStatements And StatementTypes.Alter) = StatementTypes.Alter Then regExText = regExText.Replace("ALTER", String.Empty)
      If (authorizedStatements And StatementTypes.Create) = StatementTypes.Create Then regExText = regExText.Replace("CREATE", String.Empty)
      If (authorizedStatements And StatementTypes.Delete) = StatementTypes.Delete Then regExText = regExText.Replace("DELETE", String.Empty)
      If (authorizedStatements And StatementTypes.Delete) = StatementTypes.Delete Then regExText = regExText.Replace("DELETETREE", String.Empty)
      If (authorizedStatements And StatementTypes.Drop) = StatementTypes.Drop Then regExText = regExText.Replace("DROP", String.Empty)
      If (authorizedStatements And StatementTypes.Execute) = StatementTypes.Execute Then regExText = regExText.Replace("EXEC(UTE){0,1}", String.Empty)
      If (authorizedStatements And StatementTypes.Insert) = StatementTypes.Insert Then regExText = regExText.Replace("INSERT( +INTO){0,1}", String.Empty)
      If (authorizedStatements And StatementTypes.Merge) = StatementTypes.Merge Then regExText = regExText.Replace("MERGE", String.Empty)
      If (authorizedStatements And StatementTypes.Select) = StatementTypes.Select Then regExText = regExText.Replace("SELECT", String.Empty)
      If (authorizedStatements And StatementTypes.Union) = StatementTypes.Union Then regExText = regExText.Replace("UNION", String.Empty)
      If (authorizedStatements And StatementTypes.Update) = StatementTypes.Update Then regExText = regExText.Replace("UPDATE", String.Empty)

      'Remove extra seperators
      Dim regExOptions As RegexOptions = regExOptions.IgnoreCase Or regExOptions.Multiline
      regExText = Regex.Replace(regExText, "\(\|", "(", regExOptions)
      regExText = Regex.Replace(regExText, "\|{2,}", "|", regExOptions)
      regExText = Regex.Replace(regExText, "\|\)", ")", regExOptions)

      'Check for errors
      Dim patternMatchList As MatchCollection = Regex.Matches(testText, regExText, regExOptions)

      For patternIndex As Integer = patternMatchList.Count - 1 To 0 Step -1
        Dim value As String = patternMatchList.Item(patternIndex).Value.Trim
        If String.IsNullOrWhiteSpace(value) Then
          'Continue - Not an error.
        ElseIf value.StartsWith("'") AndAlso value.EndsWith("'") Then
          'Continue - Text Block
        ElseIf value.Trim = ";" Then
          ErrorValidator.ThrowErrorOnAudit("Batch statements not authorized:" & vbCrLf & commandText)
        Else
          ErrorValidator.ThrowErrorOnAudit(value.Substring(0, 1).ToUpper & value.Substring(1).ToLower & " statements not authorized:" & vbCrLf & commandText)
        End If
      Next
    End Sub
  End Class
End Namespace

Multiple Statement Support

While inner statements are outside of the scope of the parser the library will be capable of handling multiple statements. The class AdCommandTextSets will be responsible for splitting multiple statements and assigning each statement to an AdCommandTextParser class instance.

Imports System.Text.RegularExpressions

Namespace Data.ActiveDirectory
  ''' <summary>
  ''' Splits batched commands into one or more AdCommandTextParser instances.
  ''' </summary>
  ''' <remarks></remarks>
  Public Class AdCommandTextSets
    Inherits System.Collections.Generic.List(Of AdCommandTextParser)

    ''' <summary>
    ''' Instantiate a new AdCommandTextSets instance.
    ''' </summary>
    ''' <param name="commandText">
    ''' The AD Command to parse.
    ''' </param>
    ''' <remarks></remarks>
    Public Sub New(ByVal commandText As String)
      Dim commandList As New System.Collections.ArrayList
      Dim startIndex As Integer = 0

      commandText = CommandTextParser.ToRaw(commandText)

      For Each commandMatch As Match In Regex.Matches(commandText, CommandTextParser.TEXT_BLOCK & "|" & CommandTextParser.BATCH_BREAK, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
        If Not commandMatch.Value.StartsWith("'") AndAlso Not commandMatch.Value.EndsWith("'") Then
          Add(New AdCommandTextParser(commandText.Substring(startIndex, commandMatch.Index - startIndex)))
          startIndex = commandMatch.Index + 1
        End If
      Next

      If startIndex < commandText.Length Then Add(New AdCommandTextParser(commandText.Substring(startIndex)))
    End Sub
  End Class
End Namespace

AdCommandTextParser

Finally we are ready to parse the individual sql statements so that the AdCommand class can use the information to instantiate and execute the DirectorySearcher object.

Note that the AdCommandTextParser uses the code from the post, String.Split on Steroids.

Imports System.Text.RegularExpressions
Imports LarrySteinle.Library.Extensions.FullSplitStringExtension

Namespace Data.ActiveDirectory
  ''' <summary>
  ''' Parses the commandText for the necessary lookup information.
  ''' </summary>
  ''' <remarks>
  ''' Supports simple SQL-like:
  ''' <list>
  ''' <item>SELECT FieldNames FROM 'SearchPath'.objectCategory WHERE ...</item>
  ''' <item>INSERT INTO 'TargetPath'.objectCategory (Fields...) VALUES (Values...)</item>
  ''' <item>UPDATE 'SearchPath'. objectCategory SET Field = Value WHERE...</item>
  ''' <item>DELETE 'SearchPath'.objectCategory WHERE...</item>
  ''' </list>
  ''' <para>
  ''' Does not support constants in the select clause.
  ''' Does not support joins of any kind in any of the statements.
  ''' Does not support inner queries in any part of the statements.
  ''' </para>
  ''' <para>
  ''' The WHERE clause can use either SQL syntax or an adsi filter enclosed in apostraphes.
  ''' If apostraphes are necessary inside the adsi filter they must be escaped with double
  ''' apostraphes.
  ''' </para>
  ''' The WHERE clause supports the following SQL syntax options:
  ''' <list>
  ''' <item>Use of AND and OR to combine conditionals.</item>
  ''' <item>Use of parenthesis to group conditional logic. When using and's and or's in the same clause this is strongly recommended.</item>
  ''' <item>The less than and greater than operator are not supported in Active Directory. They will be replaced with less than or equal to, and greater than or equal to respectivelly.</item>
  ''' <item>Inner selects are not supported in the where clause just as joins are not supported in the from clause.</item>
  ''' </list>
  ''' The following operators are supported:
  ''' <list>
  ''' <item>Equal (=)</item>
  ''' <item>Not Equal (!= or &lt;&gt;)</item>
  ''' <item>Less Than or Equal (&lt;= or =&lt; or &lt;)</item>
  ''' <item>Greater Than or Equal (&gt;= or =&gt; or &gt;)</item>
  ''' </list>
  ''' These additional operators are supported:
  ''' <list>
  ''' <item>Approximately Equal To (~___) (~=___) (=~____)</item>
  ''' <item>BitWiseAnd ( &amp;&amp;___)</item>
  ''' <item>BitWiseOr (||___)</item>
  ''' </list>
  ''' <para>
  ''' The BitWise operators provide the power to query fields like
  ''' userAccountControl allowing you to get Disabled accounts.
  ''' They are equivilant to LDAP_MATCHING_RULE_BIT_AND and LDAP_MATCHING_RULE_BIT_OR constants.
  ''' </para>
  ''' The following SQL keywords are supported:
  ''' <list>
  ''' <item>IS (is translated to Equal)</item>
  ''' <item>IS NOT (is translated to Not Equal)</item>
  ''' <item>Exists (=*)</item>
  ''' <item>Not Exists (!=*)</item>
  ''' </list>
  ''' These additional keywords are supported:
  ''' <list>
  ''' <item>IsExactly (is translated to Equal)</item>
  ''' <item>Not IsExactly (is translated to Not Equal)</item>
  ''' <item>Contains (=*__*)</item>
  ''' <item>Not Contains (!=*__*)</item>
  ''' <item>StartsWith (=__*)</item>
  ''' <item>Not StartsWith (!=__*)</item>
  ''' <item>EndsWith (=*___)</item>
  ''' <item>Not EndsWith (!=*___)</item>
  ''' <item>ChainDN (=*___)</item>
  ''' <item>Not ChainDN (!(=*___))</item>
  ''' </list>
  ''' The ChainDN function instructs the system to match a distinguished
  ''' name attribute beginning with the root up to the specified child. Any
  ''' object found within the DN at any sub-level is treated as a match. For
  ''' example, if you wanted to get all user objects in the AdminOU you could
  ''' write a conditional as follows:
  ''' <code>
  ''' distinguishedName ChainDN('OU=Admin,OU=Management,DC=company,DC=name')
  ''' </code>
  ''' The above condition will return any object that is a child of the specified
  ''' path no matter how many nodes below the path it exists. The ChainDN is
  ''' knowns as the LDAP_MATCHING_RULE_IN_CHAIN constant.
  ''' </remarks>
  Public Class AdCommandTextParser
    Inherits CommandTextParser

#Region "Constructor Section"
    ''' <summary>
    ''' Instantiates a new AdCommandTextParser instance.
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub New()
    End Sub

    ''' <summary>
    ''' Instantiates a new AdCommandTextParser instance.
    ''' </summary>
    ''' <param name="commandText"></param>
    ''' <remarks></remarks>
    Public Sub New(ByVal commandText As String)
      Me.CommandText = commandText.Trim
    End Sub
#End Region

#Region "Property Section"
    ''' <summary>
    ''' Provides access to the raw SQL statement.
    ''' </summary>
    ''' <remarks>
    ''' AdCommand only supports Delete, Insert, Select and Update statements.
    ''' Batch and union statements are not supported.
    ''' </remarks>
    Public Property CommandText As String
      Get
        Return _CommandText
      End Get
      Set(ByVal value As String)
        ValidateStatement(value, StatementTypes.Delete Or StatementTypes.Insert Or StatementTypes.Select Or StatementTypes.Update)
        Dim textValue As String = ToRaw(value)
        _CommandText = textValue
        _Segments = Nothing

        SetStatementType()
        Select Case CommandType
          Case StatementTypes.Delete : ParseDelete()
          Case StatementTypes.Insert : ParseInsert()
          Case StatementTypes.Select : ParseSelect()
          Case StatementTypes.Update : ParseUpdate()
        End Select
      End Set
    End Property
    Private _CommandText As String

    ''' <summary>
    ''' Get a value that indicates the type of statement.
    ''' </summary>
    ''' <remarks></remarks>
    Public ReadOnly Property CommandType As StatementTypes
      Get
        Return _CommandType
      End Get
    End Property
    Private _CommandType As StatementTypes

    ''' <summary>
    ''' Get a value that represents the table name.
    ''' </summary>
    ''' <remarks></remarks>
    Public ReadOnly Property TableName As String
      Get
        Return _TableName
      End Get
    End Property
    Private _TableName As String

    ''' <summary>
    ''' The adsi filter extracted from the where clause.
    ''' </summary>
    ''' <remarks></remarks>
    Public ReadOnly Property Filter As String
      Get
        If String.IsNullOrWhiteSpace(ObjectCategory) Then
          Return _Filter
        Else
          Return String.Format("(&(objectCategory={0}){1})", ObjectCategory.Trim, _Filter)
        End If
      End Get
    End Property
    Private _Filter As String

    ''' <summary>
    ''' Identifies the OU to begin the search inside.
    ''' </summary>
    ''' <remarks>
    ''' First Part From Clause
    ''' </remarks>
    Public ReadOnly Property SearchRoot As String
      Get
        Return _SearchRoot
      End Get
    End Property
    Private _SearchRoot As String

    ''' <summary>
    ''' Get a value that indicates the object to read.
    ''' </summary>
    ''' <remarks>
    ''' Second Part From Clause.
    ''' Required for Insert, Update and Delete statements.
    ''' Optional value for select statements but may impact how fast results are returned.
    ''' </remarks>
    Public ReadOnly Property ObjectCategory As String
      Get
        Return _ObjectCategory
      End Get
    End Property
    Private _ObjectCategory As String

    ''' <summary>
    ''' Get a value that stores the field names for a select statement,
    ''' or the field names and values for insert and update statements.
    ''' </summary>
    ''' <remarks></remarks>
    Public ReadOnly Property Parameters As AdParameterCollection
      Get
        If _Parameters Is Nothing Then _Parameters = New AdParameterCollection
        Return _Parameters
      End Get
    End Property
    Private _Parameters As AdParameterCollection

    ''' <summary>
    ''' Get a value that indicates the sort order for the results.
    ''' </summary>
    ''' <remarks>
    ''' AdDataReader supports sorting on only one column.
    ''' </remarks>
    Public ReadOnly Property OrderBy As AdSorterCollection
      Get
        If _OrderBy Is Nothing Then _OrderBy = New AdSorterCollection
        Return _OrderBy
      End Get
    End Property
    Private _OrderBy As AdSorterCollection

    ''' <summary>
    ''' Get the command text split up into managable segments.
    ''' </summary>
    ''' <remarks></remarks>
    Public ReadOnly Property Segments As String()
      Get
        If _Segments Is Nothing OrElse _Segments.Length = 0 Then
          _Segments = SplitSQL(CommandText)
        End If
        Return _Segments
      End Get
    End Property
    Private _Segments() As String

    ''' <summary>
    ''' Get a value that indicates that a node tree should be deleted.
    ''' </summary>
    ''' <remarks></remarks>
    Public ReadOnly Property DeleteTree As Boolean
      Get
        Return _DeleteTree
      End Get
    End Property
    Private _DeleteTree As Boolean = False
#End Region

#Region "Supporting Behaviors"
    ''' <summary>
    ''' Throws an NotSupportedException when a join is found in the statement.
    ''' </summary>
    ''' <param name="commandText">
    ''' The statement to test for authorized clauses.
    ''' </param>
    ''' <remarks></remarks>
    Private Sub ThrowErrorOnUnsupportedClauses(ByVal commandText As String)
      Dim regExOptions As RegexOptions = regExOptions.IgnoreCase Or regExOptions.Multiline
      Dim regExPattern As String = "((CROSS +|LEFT +|RIGHT +|)(INNER +|OUTER +|)JOIN|GROUP +BY|HAVING)"
      If Regex.IsMatch(commandText, regExPattern, regExOptions) Then
        Throw New NotSupportedException("Join and Grouping clauses are not supported.")
      End If
    End Sub

    ''' <summary>
    ''' Throws an AdException for parsing errors.
    ''' </summary>
    ''' <param name="message">
    ''' Optional message details.
    ''' </param>
    ''' <remarks></remarks>
    Private Sub ThrowParseError(Optional ByVal message As String = "")
      If String.IsNullOrWhiteSpace(message) Then
        message = String.Empty
      Else
        message = " " & message.Trim
      End If
      Throw New AdException("Invalid or unsupported SQL Syntax." & message)
    End Sub
#End Region

#Region "Parsing Routines"
    ''' <summary>
    ''' Parses the CommandText to identify the statement type.
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub SetStatementType()
      'Since all comments, line breaks and extra spaces are removed we can get the
      'primary command from the beginning of the string. The only commands supported
      'conveniently all have a length of six characters.
      Dim commandName As String
      If CommandText.Length > 6 Then
        commandName = CommandText.Substring(0, 6)
      Else
        commandName = CommandText
      End If

      Select Case commandName.ToUpper
        Case "SELECT" : _CommandType = StatementTypes.Select
        Case "INSERT" : _CommandType = StatementTypes.Insert
        Case "UPDATE" : _CommandType = StatementTypes.Update
        Case "DELETE" : _CommandType = StatementTypes.Delete
        Case Else : ThrowParseError(String.Format("System does not support {0} SQL command.", commandName))
      End Select
    End Sub

    ''' <summary>
    ''' Parses the CommandText to identify the various parts of a Delete statement.
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub ParseDelete()
      Dim segmentParts() As String
      Dim segmentPart As String

      For Each segmentItem As String In Segments
        segmentItem = segmentItem.Trim
        Select Case GetFirstOperator(segmentItem).ToUpper
          Case "DELETE" : _DeleteTree = False
          Case "DELETETREE" : _DeleteTree = True
          Case "FROM"
            segmentPart = segmentItem.Substring(segmentItem.ToUpper.IndexOf("FROM") + 4).Trim
            segmentParts = segmentPart.FullSplit("."c, "'"c)
            If segmentParts.Length > 1 Then
              _SearchRoot = segmentParts(0)
              _ObjectCategory = segmentParts(1)
            Else
              _ObjectCategory = segmentParts(0)
            End If
          Case "WHERE" : _Filter = ParseWhereClause(segmentItem)
        End Select
      Next

      Parameters.Add(New AdParameter("ADsPath"))
    End Sub

    ''' <summary>
    ''' Parses the CommandText to identify the various parts of an Insert statement.
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub ParseInsert()
      Dim operatorName As String
      Dim segmentParts() As String
      Dim segmentPart As String

      For Each segmentItem As String In Segments
        segmentItem = segmentItem.Trim
        operatorName = GetFirstOperator(segmentItem).ToUpper
        operatorName = Regex.Replace(operatorName, "INSERT( +INTO){0,1}", "INSERT INTO", RegexOptions.Multiline Or RegexOptions.IgnoreCase)
        Select Case operatorName
          Case "INSERT INTO"
            segmentPart = segmentItem.Substring(segmentItem.ToUpper.IndexOf("INSERT INTO") + 11).Trim

            If segmentPart.IndexOf("(") > 0 Then
              'Get Parent OU Path and Object Type
              segmentParts = segmentPart.Substring(0, segmentPart.IndexOf("(") - 1).Trim.FullSplit("."c, "'"c)
              If segmentParts.Length > 1 Then
                _SearchRoot = segmentParts(0)
                _ObjectCategory = segmentParts(1)
              Else
                _ObjectCategory = segmentParts(0)
              End If

              'Get Field Names
              segmentPart = segmentPart.Substring(segmentPart.IndexOf("(")).Trim
              If segmentPart.StartsWith("(") AndAlso segmentPart.EndsWith(")") Then segmentPart = segmentPart.Substring(1, segmentPart.Length - 2).Trim

              segmentParts = segmentPart.FullSplit(","c, "'"c)
              For Each segmentPart In segmentParts
                Parameters.Add(New AdParameter(segmentPart))
              Next
            Else
              ThrowParseError("Insert statement missing field names.")
            End If
          Case "VALUES"
            segmentPart = segmentItem.Substring(segmentItem.ToUpper.IndexOf("VALUES") + 6).Trim
            If segmentPart.StartsWith("(") AndAlso segmentPart.EndsWith(")") Then segmentPart = segmentPart.Substring(1, segmentPart.Length - 2).Trim
            segmentParts = segmentPart.FullSplit(","c, "'"c)

            If segmentParts.Count <> Parameters.Count Then
              ThrowParseError("Field count doesn't match value count.")
            Else
              For intIndex As Integer = 0 To segmentParts.Count - 1
                'If named parameter make sure it is trimmed to remove any leading or trailing spaces.
                'If not a named parameter use value exactly as it is.
                If segmentParts(intIndex).Trim.StartsWith("@") Then
                  segmentParts(intIndex) = segmentParts(intIndex).Trim
                End If
                Parameters(intIndex).Value = segmentParts(intIndex)
              Next
            End If
          Case "WHERE" : _Filter = ParseWhereClause(segmentItem)
        End Select
      Next

      For Each adParm As AdParameter In Parameters
        If String.Compare(adParm.ParameterName, "ADsPath", True) = 0 _
        OrElse String.Compare(adParm.ParameterName, "userAccountControl", True) = 0 _
        OrElse String.Compare(adParm.ParameterName, "distinguishedName", True) = 0 Then
          ThrowParseError(String.Format("The field, {0}, may not be set with an insert statement.", adParm.ParameterName))
        End If
      Next
    End Sub

    ''' <summary>
    ''' Parses the CommandText to identify the various parts of a Select statement.
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub ParseSelect()
      Dim segmentParts() As String
      Dim segmentPart As String

      For Each segmentItem As String In Segments
        segmentItem = segmentItem.Trim
        Select Case GetFirstOperator(segmentItem).ToUpper
          Case "SELECT"
            segmentPart = segmentItem.Substring(segmentItem.ToUpper.IndexOf("SELECT") + 6).Trim
            For Each parameterName As String In segmentPart.FullSplit(","c, "'"c)
              parameterName = parameterName.Trim
              If parameterName.Length > 0 AndAlso parameterName <> "*" Then
                Parameters.Add(New AdParameter(parameterName))
              End If
            Next
          Case "FROM"
            segmentPart = segmentItem.Substring(segmentItem.ToUpper.IndexOf("FROM") + 4).Trim
            segmentParts = segmentPart.FullSplit("."c, "'"c)
            If segmentParts.Length > 1 Then
              _SearchRoot = segmentParts(0)
              _ObjectCategory = segmentParts(1)
            Else
              _ObjectCategory = segmentParts(0)
            End If
          Case "WHERE"
            _Filter = ParseWhereClause(segmentItem)
          Case "ORDER BY"
            segmentPart = segmentItem.Substring(segmentItem.ToUpper.IndexOf("ORDER BY") + 8).Trim
            segmentParts = segmentPart.FullSplit(","c, "'"c)
            OrderBy.Clear()
            For Each segment As String In segmentParts
              OrderBy.Add(New AdSorter(segment))
            Next
        End Select
      Next

      If Parameters.Count = 0 AndAlso String.IsNullOrWhiteSpace(_ObjectCategory) Then
        ThrowParseError("When properties are not specified a class name must be provided as part of the from clause ('dn'.className).")
      End If
    End Sub

    ''' <summary>
    ''' Parses the CommandText to identify the various parts of an Update statement.
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub ParseUpdate()
      Dim segmentParts() As String
      Dim segmentPart As String

      For Each segmentItem As String In Segments
        segmentItem = segmentItem.Trim
        Select Case GetFirstOperator(segmentItem).ToUpper
          Case "UPDATE"
            segmentPart = segmentItem.Substring(segmentItem.ToUpper.IndexOf("UPDATE") + 6).Trim
            segmentParts = segmentPart.FullSplit("."c, "'"c)
            If segmentParts.Length > 1 Then
              _SearchRoot = segmentParts(0)
              _ObjectCategory = segmentParts(1)
            Else
              _ObjectCategory = segmentParts(0)
            End If
          Case "SET"
            segmentPart = segmentItem.Substring(segmentItem.ToUpper.IndexOf("SET") + 3).Trim
            For Each parmItem As DictionaryEntry In segmentPart.FullSplit(","c, "'"c, "="c)
              'If named parameter make sure it is trimmed to remove any leading or trailing spaces.
              'If not a named parameter use value exactly as it is.
              If parmItem.Value.ToString.Trim.StartsWith("@") Then
                parmItem.Value = parmItem.Value.ToString.Trim
              End If
              Parameters.Add(New AdParameter(parmItem.Key.ToString, parmItem.Value))
            Next
          Case "WHERE" : _Filter = ParseWhereClause(segmentItem)
        End Select
      Next

      For Each adParm As AdParameter In Parameters
        If String.Compare(adParm.ParameterName, "ADsPath", True) = 0 Then
          ThrowParseError(String.Format("The field, {0}, may not be set with an update statement.", adParm.ParameterName))
        End If
      Next

      Parameters.Add(New AdParameter("ADsPath"))
    End Sub

    ''' <summary>
    ''' Parses the where clause statement.
    ''' </summary>
    ''' <param name="whereClause">
    ''' The where clause to parse.
    ''' </param>
    ''' <remarks>
    ''' Returns an ADSI Filter statement.
    ''' </remarks>
    Private Function ParseWhereClause(ByVal whereClause As String) As String
      If ClauseUsesSQLSyntax(whereClause) Then
        whereClause = SQLSyntaxToAdSyntax(whereClause)
      Else
        whereClause = whereClause.Trim

        'Remove WHERE statement
        If whereClause.ToUpper.StartsWith("WHERE ") Then
          whereClause = whereClause.Substring(6).Trim
        End If

        'Remove leading and trailing apostraphes
        If whereClause.StartsWith("'") AndAlso whereClause.EndsWith("'") Then
          whereClause = whereClause.Substring(1, whereClause.Length - 2)
        End If

        'Replace double apostraphes with single apostraphes
        whereClause = Regex.Replace(whereClause, "''", "'", RegexOptions.IgnoreCase Or RegexOptions.Multiline)
      End If
      Return whereClause
    End Function
#End Region

#Region "Parse Where Clause Helper Routines"
    ''' <summary>
    ''' Analayzes the Clause to determine when it uses SQL Syntax.
    ''' </summary>
    ''' <param name="clause">
    ''' The WHERE clause to analyze.
    ''' </param>
    ''' <returns>
    ''' Returns true when the clause uses SQL Syntax and
    ''' returns false when the clause uses ADSI filter syntax.
    ''' </returns>
    ''' <remarks></remarks>
    Private Function ClauseUsesSQLSyntax(ByVal clause As String) As Boolean
      clause = clause.Trim.ToUpper
      If clause.StartsWith("WHERE ") Then clause = clause.Substring(6).Trim
      Return Not (clause.StartsWith("'") AndAlso clause.EndsWith("'"))
    End Function

    ''' <summary>
    ''' Translates a SQL where clause into an ADSI filter.
    ''' </summary>
    ''' <param name="clause">
    ''' The SQL clause to translate.
    ''' </param>
    ''' <returns>
    ''' An ADSI filter.
    ''' </returns>
    ''' <remarks></remarks>
    Private Function SQLSyntaxToAdSyntax(ByVal clause As String) As String
      'Encode the (, ) characters
      clause = clause.Replace("[", OpenBracketSub).Replace("]", CloseBracketSub)

      'Remove the Where clause from the statement
      clause = "(" & Regex.Replace(clause, "((^|[\r\n\t ])+(WHERE)[\r\n\t ]+)", String.Empty, RegexOptions.IgnoreCase Or RegexOptions.Multiline) & ")"

      clause = ConvertOperators(clause)
      clause = ConvertNotOperators(clause)
      clause = ConvertComplexConditions(clause)

      'Decode the [, ], (, ) characters
      clause = clause.Replace("[", "(").Replace("]", ")")
      clause = clause.Trim.Replace(OpenBracketSub, "[").Replace(CloseBracketSub, "]")

      'Remove Extra Spaces
      clause = Regex.Replace(clause, " *\([|] +", "(|", RegexOptions.IgnoreCase Or RegexOptions.Multiline)
      clause = Regex.Replace(clause, " *\([&] +", "(&", RegexOptions.IgnoreCase Or RegexOptions.Multiline)
      clause = Regex.Replace(clause, " *\([!] +", "(!", RegexOptions.IgnoreCase Or RegexOptions.Multiline)
      clause = Regex.Replace(clause, " *\( +", "(", RegexOptions.IgnoreCase Or RegexOptions.Multiline)

      Return clause.Trim
    End Function

    ''' <summary>
    ''' Replace SQL Conditions with ADSI Filter Conditions
    ''' </summary>
    ''' <param name="clause">
    ''' The SQL conditional clause to translate.
    ''' </param>
    ''' <returns>
    ''' Returns a value where all operators and commands are translated to ADSI syntax.
    ''' </returns>
    ''' <remarks>
    ''' Use [] instead of () because in SQL the () are used for grouping comparisons.
    ''' </remarks>
    Private Function ConvertOperators(ByVal clause As String) As String
      Dim regExPatterns As String = String.Format("({0})|({1})|({2})", CONDITION_FIELD_COMMAND_VALUE, CONDITION_COMMAND_FIELD, CONDITION_FIELD_OPERATOR_VALUE)
      Dim matches As MatchCollection = Regex.Matches(clause, regExPatterns, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
      For intIndex As Integer = matches.Count - 1 To 0 Step -1
        Dim operatorMatch As Match = matches(intIndex)
        Dim value As String = ConditionToFilter(operatorMatch.Value.Trim)
        clause = clause.Substring(0, operatorMatch.Index) & value & clause.Substring(operatorMatch.Index + operatorMatch.Length)
      Next
      Return clause
    End Function

    ''' <summary>
    ''' Translate Not operators into ADSI filter syntax.
    ''' </summary>
    ''' <param name="clause">
    ''' The SQL conditional clause to translate.
    ''' </param>
    ''' <returns>
    ''' Returns a value where all not operators are translated to ADSI syntax.
    ''' </returns>
    ''' <remarks></remarks>
    Private Function ConvertNotOperators(ByVal clause As String) As String
      Dim regExPatterns() As String = {NOT_CONDITIONS_WITH_PARENS, NOT_CONDITIONS_WITHOUT_PARENS}
      Dim matches As MatchCollection

      For Each regExPattern In regExPatterns
        matches = Regex.Matches(clause, regExPattern, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
        Do
          For intIndex As Integer = matches.Count - 1 To 0 Step -1
            Dim operatorMatch As Match = matches(intIndex)

            'Remove NOT keywork
            Dim value As String = operatorMatch.Value.Trim.Substring(3).Trim

            'If is complex condition (AND|OR) then process prior to not
            If value.StartsWith("(") AndAlso value.EndsWith(")") Then
              value = ConvertComplexConditions(value)
            Else
              value = ConvertComplexConditions("(" & value & ")")
            End If

            'Remove opening and closing parens
            If value.StartsWith("(") AndAlso value.EndsWith(")") Then value = value.Substring(1, value.Length - 2).Trim

            'NOT the condition and wrap in parens
            If value.StartsWith("[") AndAlso value.EndsWith("]") Then
              value = " [!" & value & "]"
            Else
              value = " [![" & value & "]]"
            End If

            'Replace original value
            clause = clause.Substring(0, operatorMatch.Index) & value & clause.Substring(operatorMatch.Index + operatorMatch.Length)
          Next
          clause = clause.Trim
          matches = Regex.Matches(clause, regExPattern, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
        Loop Until matches.Count = 0
      Next
      Return clause
    End Function

    ''' <summary>
    ''' Convert sql conditionals to ADSI syntax.
    ''' </summary>
    ''' <param name="condition">
    ''' The SQL conditional or complex conditional to translate.
    ''' </param>
    ''' <returns>
    ''' Returns a value where conditions are translated to ADSI syntax.
    ''' </returns>
    ''' <remarks></remarks>
    Private Function ConvertComplexConditions(ByVal condition As String) As String
      Dim matches As MatchCollection
      Dim origClause As String
      Do
        origClause = condition
        matches = Regex.Matches(condition, "(" & TEXT_BLOCK & ")|(\([^/(/)]*\))", RegexOptions.IgnoreCase Or RegexOptions.Multiline)
        For intIndex As Integer = matches.Count - 1 To 0 Step -1
          Dim comparisonText As String = matches(intIndex).Value
          While comparisonText.StartsWith("(") AndAlso comparisonText.EndsWith(")")
            comparisonText = comparisonText.Substring(1, comparisonText.Length - 2)
          End While

          If Not (comparisonText.StartsWith("'") AndAlso comparisonText.EndsWith("'")) Then
            Dim startIndex As Integer = 0
            Dim operatorChangeCounter As Integer = 0
            Dim subFilter As String = String.Empty
            Dim conditionType As String = String.Empty
            For Each conditionalMatch As Match In Regex.Matches(comparisonText, "(\[[^\]]*)|((^|[\r\n\t ])*(AND|OR)[\r\n\t ]*)", RegexOptions.IgnoreCase Or RegexOptions.Multiline)
              If Not conditionalMatch.Value.StartsWith("[") AndAlso Not conditionalMatch.Value.EndsWith("]") Then
                If String.IsNullOrWhiteSpace(conditionType) _
                OrElse conditionType <> conditionalMatch.Value.Trim.ToUpper Then
                  operatorChangeCounter += 1
                  conditionType = conditionalMatch.Value.Trim.ToUpper
                  Select Case conditionType
                    Case "AND" : subFilter &= "[&"
                    Case "OR" : subFilter &= "[|"
                  End Select
                End If

                subFilter &= comparisonText.Substring(startIndex, conditionalMatch.Index - startIndex)
                startIndex = conditionalMatch.Index + conditionalMatch.Value.Length
              End If
            Next

            If startIndex < comparisonText.Length Then
              subFilter &= comparisonText.Substring(startIndex)
            End If

            For operatorChangeIndex As Integer = 0 To operatorChangeCounter - 1
              subFilter &= "]"
            Next

            With matches(intIndex)
              If .Index + .Length < condition.Length Then
                condition = condition.Substring(0, .Index) & subFilter & condition.Substring(.Index + .Length)
              Else
                condition = condition.Substring(0, .Index) & subFilter
              End If
            End With
          End If
        Next
      Loop Until matches Is Nothing OrElse matches.Count = 0 OrElse origClause = condition
      Return condition
    End Function

    ''' <summary>
    ''' Get a string value used to encode an open bracket character.
    ''' </summary>
    ''' <remarks></remarks>
    Private ReadOnly Property OpenBracketSub As String
      Get
        Return "&#" & Char.ConvertFromUtf32(168) & Char.GetNumericValue("["c) & Char.ConvertFromUtf32(168) & ";"
      End Get
    End Property

    ''' <summary>
    ''' Get a string value used to encode a close bracket character.
    ''' </summary>
    ''' <remarks></remarks>
    Private ReadOnly Property CloseBracketSub As String
      Get
        Return "&#" & Char.ConvertFromUtf32(168) & Char.GetNumericValue("]"c) & Char.ConvertFromUtf32(168) & ";"
      End Get
    End Property

    ''' <summary>
    ''' Converts a SQL Condition into an ADSI Filter
    ''' </summary>
    ''' <param name="condition">
    ''' A SQL Condition phrase.
    ''' </param>
    ''' <returns>
    ''' An ADSI filter.
    ''' </returns>
    ''' <remarks>
    ''' There is no greater than or less than.
    ''' Only greater than or equal to, and less than or equal to.
    ''' </remarks>
    Private Function ConditionToFilter(ByVal condition As String) As String
      Dim filter As String = condition.Trim
      Dim negated As Boolean = filter.ToUpper.StartsWith("NOT ")
      If negated Then filter = filter.Substring(3).Trim

      'Remove Begining and Ending Text Qualifier
      If filter.StartsWith("'") AndAlso filter.EndsWith("'") Then filter = filter.Substring(1, filter.Length - 2)

      Select Case GetOperator(filter).ToUpper
        Case "IS", "=" : filter = BuildStandardFilter(filter, "=")
        Case "!=", "<>" : filter = BuildStandardFilter(filter, "!=")
        Case "IS NOT", "NOT IS" : filter = "!" & BuildStandardFilter(filter, "=")
        Case "=~", "~", "~=" : filter = BuildStandardFilter(filter, "~=")
        Case "<", "=<", "<=" : filter = BuildStandardFilter(filter, "<=")
        Case ">", "=>", ">=" : filter = BuildStandardFilter(filter, ">=")
        Case "&&" : filter = BuildStandardFilter(filter, ":1.2.840.113556.1.4.803:=")
        Case "||" : filter = BuildStandardFilter(filter, ":1.2.840.113556.1.4.804:=")

        Case "CONTAINS", "NOT CONTAINS"
          filter = BuildFunctionFilter(filter, "=", "*", "*")
          If filter.ToUpper.StartsWith("NOT ") OrElse filter.ToUpper.IndexOf(" NOT ") > 0 Then filter = "!" & filter
        Case "STARTSWITH", "NOT STARTSWITH"
          filter = BuildFunctionFilter(filter, "=", "", "*")
          If filter.ToUpper.StartsWith("NOT ") OrElse filter.ToUpper.IndexOf(" NOT ") > 0 Then filter = "!" & filter
        Case "ENDSWITH", "NOT ENDSWITH"
          filter = BuildFunctionFilter(filter, "=", "*", "")
          If filter.ToUpper.StartsWith("NOT ") OrElse filter.ToUpper.IndexOf(" NOT ") > 0 Then filter = "!" & filter
        Case "CHAINDN", "NOT CHAINDN"
          filter = BuildFunctionFilter(filter, ":1.2.840.113556.1.4.1941:=", "", "")
          If filter.ToUpper.StartsWith("NOT ") OrElse filter.ToUpper.IndexOf(" NOT ") > 0 Then filter = "!" & filter

        Case "EXISTS" : filter = BuildExistsFilter(filter, False)
        Case "NOT EXISTS" : filter = BuildExistsFilter(filter, True)

        Case "IN" : filter = BuildInFilter(filter, False)
        Case "NOT IN" : filter = BuildInFilter(filter, True)

        Case "LIKE" : filter = BuildLikeFilter(filter, False)
        Case "NOT LIKE" : filter = BuildLikeFilter(filter, True)
      End Select

      filter = filter.Trim
      If negated Then filter = "![" & filter & "]"
      Return "[" & filter & "]"
    End Function

    ''' <summary>
    ''' Identifies the type of operator found in the condition.
    ''' </summary>
    ''' <param name="condition">
    ''' The condition to interpret.
    ''' </param>
    ''' <returns>
    ''' Returns the operator if one exists in the condition, an empty string otherwise.
    ''' </returns>
    ''' <remarks></remarks>
    Private Function GetOperator(ByVal condition As String) As String
      Dim matches As MatchCollection = Regex.Matches(condition, SQL_OPERATORS, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
      Dim operatorMatch As Match = Regex.Match(condition, SQL_OPERATORS, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
      If operatorMatch Is Nothing Then
        Return String.Empty
      Else
        Return operatorMatch.Value.Trim
      End If
    End Function

    ''' <summary>
    ''' Helper method to translate a SQL IN condition into an ADSI filter.
    ''' </summary>
    ''' <param name="condition">
    ''' The condition to translate.
    ''' </param>
    ''' <param name="negate">
    ''' When true builds a filter that passes if the value does not exist,
    ''' when false builds a filter that passes when the value does exist.
    ''' </param>
    ''' <returns>
    ''' A valid, escaped ADSI filter.
    ''' </returns>
    ''' <remarks></remarks>
    Private Function BuildInFilter(ByVal condition As String, ByVal negate As Boolean) As String
      Dim filter As String = String.Empty
      Dim key, values As String
      With Regex.Match(condition, SQL_OPERATORS, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
        key = condition.Substring(0, .Index).Trim
        values = condition.Substring(.Index + .Length).Trim
      End With

      If values.StartsWith("(") AndAlso values.EndsWith(")") Then values = values.Substring(1, values.Length - 2).Trim

      For Each value As String In values.FullSplit(","c, "'"c)
        'True and False constants must be uppercase to work correctly in an Active Directory Filter.
        If value.Trim.ToUpper = "TRUE" Then value = "TRUE"
        If value.Trim.ToUpper = "FALSE" Then value = "FALSE"
        filter &= "[" & key & IIf(negate, "!=", "=").ToString & EscapeFilterValue(value) & "]"
      Next

      'If IN then any condition can pass
      'If NOT IN then all conditions must fail
      Return IIf(negate, "&", "|").ToString & filter
    End Function

    ''' <summary>
    ''' Helper method to translate a SQL LIKE condition into an ADSI filter.
    ''' </summary>
    ''' <param name="condition">
    ''' The condition to translate.
    ''' </param>
    ''' <param name="negate">
    ''' When true builds a filter that passes if the value does not exist,
    ''' when false builds a filter that passes when the value does exist.
    ''' </param>
    ''' <returns>
    ''' A valid, escaped ADSI filter.
    ''' </returns>
    ''' <remarks>
    ''' The SQL % sign has the same meaning as an * in ADSI filters.
    ''' There is no corresponding symbol for the SQL underscore character in ADSI filters.
    ''' </remarks>
    Private Function BuildLikeFilter(ByVal condition As String, ByVal negate As Boolean) As String
      Dim key, value As String
      With Regex.Match(condition, SQL_OPERATORS, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
        key = condition.Substring(0, .Index).Trim
        value = condition.Substring(.Index + .Length).Trim
      End With

      If value.StartsWith("(") AndAlso value.EndsWith(")") Then value = value.Substring(1, value.Length - 2).Trim
      'True and False constants must be uppercase to work correctly in an Active Directory Filter.
      If value.Trim.ToUpper = "TRUE" Then value = "TRUE"
      If value.Trim.ToUpper = "FALSE" Then value = "FALSE"

      'Fortunately the SQL % sign is not an escaped character in ADSI making this easier to manage.
      'Just be careful to translate the % to an * after escaping the value.
      value = EscapeFilterValue(value)
      value = value.Replace("%", "*")

      If negate Then
        Return "!" & key & "=" & value
      Else
        Return key & "=" & value
      End If
    End Function

    ''' <summary>
    ''' Helper method to translate an operator condition into an ADSI filter.
    ''' </summary>
    ''' <param name="condition">
    ''' The condition to translate.
    ''' </param>
    ''' <param name="operatorValue">
    ''' The operator value to use for key value assignment.
    ''' </param>
    ''' <returns>
    ''' A valid, escaped ADSI filter.
    ''' </returns>
    ''' <remarks></remarks>
    Private Function BuildStandardFilter(ByVal condition As String, ByVal operatorValue As String) As String
      Dim key, value As String
      With Regex.Match(condition, SQL_OPERATORS, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
        key = condition.Substring(0, .Index).Trim
        value = condition.Substring(.Index + .Length).Trim
      End With

      If value.Trim.StartsWith("'") AndAlso value.Trim.EndsWith("'") Then
        value = value.Trim.Substring(1, value.Trim.Length - 2)
      End If

      'True and False constants must be uppercase to work correctly in an Active Directory Filter.
      If value.Trim.ToUpper = "TRUE" Then value = "TRUE"
      If value.Trim.ToUpper = "FALSE" Then value = "FALSE"

      Return key & operatorValue & EscapeFilterValue(value)
    End Function

    ''' <summary>
    ''' Helper method to translate a function condition into an ADSI filter.
    ''' </summary>
    ''' <param name="condition">
    ''' The condition to translate.
    ''' </param>
    ''' <param name="operatorValue">
    ''' The operator value to use for key value assignment.
    ''' </param>
    ''' <param name="beforeValueChar">
    ''' Any characters that belong in front of the value.
    ''' </param>
    ''' <param name="afterValueChar">
    ''' Any characters that belong behind the value.
    ''' </param>
    ''' <returns>
    ''' A valid, escaped ADSI filter.
    ''' </returns>
    ''' <remarks></remarks>
    Private Function BuildFunctionFilter(ByVal condition As String, ByVal operatorValue As String, ByVal beforeValueChar As String, ByVal afterValueChar As String) As String
      Dim key, value As String
      With Regex.Match(condition, SQL_OPERATORS, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
        key = condition.Substring(0, .Index).Trim
        value = condition.Substring(.Index + .Length).Trim
      End With

      If key.ToUpper.StartsWith("NOT ") Then key = key.Substring(4).Trim
      If value.StartsWith("(") AndAlso value.EndsWith(")") Then value = value.Substring(1, value.Length - 2).Trim
      If value.Trim.StartsWith("'") AndAlso value.Trim.EndsWith("'") Then value = value.Trim.Substring(1, value.Trim.Length - 2)

      'True and False constants must be uppercase to work correctly in an Active Directory Filter.
      If value.Trim.ToUpper = "TRUE" Then value = "TRUE"
      If value.Trim.ToUpper = "FALSE" Then value = "FALSE"

      Return key & operatorValue.ToString & beforeValueChar & EscapeFilterValue(value) & afterValueChar
    End Function

    ''' <summary>
    ''' Helper method to translate an exists condition into an ADSI filter.
    ''' </summary>
    ''' <param name="condition">
    ''' The condition to translate.
    ''' </param>
    ''' <param name="negate">
    ''' When true builds a filter that passes if the value does not exist,
    ''' when false builds a filter that passes when the value does exist.
    ''' </param>
    ''' <returns>
    ''' A valid, escaped ADSI filter.
    ''' </returns>
    ''' <remarks></remarks>
    Private Function BuildExistsFilter(ByVal condition As String, Optional ByVal negate As Boolean = False) As String
      Dim key As String
      With Regex.Match(condition, SQL_OPERATORS, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
        key = condition.Substring(.Index + .Length).Trim
      End With

      If key.StartsWith("(") AndAlso key.EndsWith(")") Then key = key.Substring(1, key.Length - 2).Trim
      If negate Then
        Return "!" & key & "=*"
      Else
        Return key & "=*"
      End If
    End Function

    ''' <summary>
    ''' Apply character code escaping to special characters
    ''' in compliance with ADSI filter encoding guidelines.
    ''' </summary>
    ''' <param name="value">The ADSI filter value to encode.</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function EscapeFilterValue(ByVal value As String) As String
      Dim buffer As String = value

      'Remove DistinguishedName Character Escape Encoding That Causes Problems With The Filter
      buffer = Replace(buffer, "\/", "/")

      'This must be done first or it will corrupt the formatting.
      buffer = Replace(buffer, "\", "\5c")

      'Replace Characters with AD Formatting
      buffer = Replace(buffer, "*", "\2a")
      buffer = Replace(buffer, "(", "\28")
      buffer = Replace(buffer, ")", "\29")

      'Convert Non-printable Characters to Escaped Hex Code Values
      Dim hexCode As String
      For ascIndex As Integer = 0 To 31
        hexCode = Convert.ToString(ascIndex, 16)
        If hexCode.Length = 1 Then hexCode = "0" & hexCode
        buffer = Regex.Replace(buffer, Chr(ascIndex), "\" & hexCode.ToUpper, RegexOptions.IgnoreCase Or RegexOptions.Multiline)

        'If we are escaping a DN we have to remove the double escaping. This only affects non-printable characters.
        'buffer = Regex.Replace(buffer, "\5c" & hexCode.ToUpper, "\" & hexCode.ToUpper, RegexOptions.IgnoreCase Or RegexOptions.Multiline)
      Next

      Return buffer
    End Function
#End Region
  End Class
End Namespace
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: