AdCommandTextParser: Parsing SQL Statements

March 12, 2011

AdCommandTextParser: Parsing SQL Statements

Filed under: Active Directory,RegEx,VS.Net — Larry Steinle @ 11:48 pm
Tags: Active Directory, Data Access, Parse, Parsing, VB.Net

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.

First Post: Active Directory Data Access Layer
Second Post: Active Directory Connection Strings
Third Post: AdConnection: Enforcing Active Directory Communication Best Practices
Fourth Post: AdCommandTextParser: Parsing SQL Statements
Fifth Post: AdCommand: Running Active Directory Queries
Sixth Post: AdDataReader: Providing Controlled Access to AD Values
Seventh Post: AdDataAdapter: Managing Active Directory Data
Eighth Post: AD Query: Putting it All Together

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