Larry Steinle

SQL Code Injection: How it Works

Before we can detect and block SQL Code Injection attacks we first need to understand how SQL Code Injection works. SQL Code Injection relies on the sql text block character (apostrophe) and statement divider character (semi-colon).

Take the following code as an example. Here we are performing a simple search for a list of products that is filtered based on text supplied from the end user. The value from the web page will be passed in using a parameter name called @Name.

SELECT ProductID, Name FROM Products WHERE Name LIKE '%@Name%'

We expect the user to enter a product name so that the list can be filtered by the value entered. In the event that the user enters say, “pencils”, in the Name text box we expect the parameter name, @Name, in the query to be replaced as follows:

SELECT ProductID, Name FROM Products WHERE Name LIKE '%pencils%'

But what happens if our user enters the following value in the Name text box of our web page?

'; SELECT * FROM sysobjects ;--

Now our sql statement looks like the following:

SELECT ProductID, Name FROM Products WHERE Name LIKE ''; SELECT * FROM sysobjects ;--%'

What just happened? The apostrophe ended our sql statement, the semi-colon told the sql engine that a new statement has begun (; SELECT * FROM sysobjects). And, the value ends with a double-dash which tells the sql engine to treat all following characters as a comment.

Voila! The user now has access to a list of all the tables, views, procedures, functions and definitions from our database instead of the intended search. The page returns the name of our precious users table that stores user id’s and passwords. Now the intruder has full access to the entire site and perhaps other systems as well!

The user has injected a sql statement into our query with the aid of a closing text block character and statement break character.

Caution

Some sites will detect the attempted attack and are capable of tracing the call back to the client’s machine. If you want to play with sql code injection to better understand how it works do so in a proper test environment and not on some government agency website.

Risk Mitigation Strategy

To eliminate the risk of executing injected sql code analyze all sql statements for unsupported behavior prior to executing the statements. Unsupported behavior is an unexpected statement type. In our example we are executing a select statement so if we find an insert, update or delete we know code has been injected. Another unsupported behavior is multiple statement execution generated by statement breaks (semi-colons). So if we find more than one statement we know code has been injected. But what if our hacker is a bit more savvy and tricky and uses a UNION statement? We don’t have a multi-statement break and we don’t have an unexpected statement type. So the union would also qualify as an unexpected statement type.

Regular Expression

We will use three regular expressions to help parse and validate the sql statement:

1. A regular expression to identify text blocks.
2. A regular expression to identify statement breaks.
3. A regular expression to identify sql statements.

Text Blocks

We will recycle our text block regular expression from the previous article, Use Regular Expressions to Clean SQL Statements:

'(''|[^'])*'

Statement Breaks

This regular expression is very simple to write. It is simply a semi-colon. If we find a semi-colon outside of a text block then we have multiple statements.

SQL Statements

Now all we need is an expression that can find SQL Statements. Once we know what statements are in the query we can check for both authorized and unauthorized actions:

\b(ALTER|CREATE|DELETE|DROP|EXEC(UTE){0,1}|INSERT( +INTO){0,1}|MERGE|SELECT|UPDATE|UNION( +ALL){0,1})\b

The above regular expression says to search for the word, ALTER, CREATE, DELETE, DROP, EXEC, EXECUTE, INSERT, INSERT INTO, MERGE, SELECT, UPDATE, UNION or UNION ALL. The \b at the beginning and ending tell the regular expression engine to search for whole word matches. That is, the word CREATED should not be treated as a match because it contains the letters that make the word CREATE.

CommandTextValidator

Create a routine that checks the sql command text value for authorized sql statements. If an unauthorized statement is discovered throw an UnauthorizedAccessException message.

To identify what statements are authorized use the following enumerator. Note the numbering scheme will permit selecting more than one value as a valid option.

Public Enum StatementTypes
  None = 0
  Procedure = 0
  Alter = 1
  Create = 2
  Delete = 4
  Drop = 8
  Execute = 16
  Insert = 32
  [Select] = 64
  Update = 128
  Union = 256
  Batch = 512
  Merge = 1024 Or Delete Or Insert Or [Select] Or Update
End Enum

Create a new class called, CommandTextValidator. We only need one, simple routine to validate our sql statements. The routine will be called, ValidateStatement.

Begin by constructing the regular expression. Combine all three regular expressions in order of the text block, statement break, and statements regular expressions. The text block expression must be first. If the other matches occur inside the text block it doesn’t count because it is a string value and not a command.

Remove any authorized statements from the regular expression. The code should only find unauthorized actions. Once the authorized actions are removed clean the expression by removing extra opening and closing parenthesis and extra pipe characters.

Finally search for matches ignoring text blocks and throw a meaningful error.

Public Class CommandTextValidator
  Public Shared Sub ValidateStatement(ByVal commandText As String, ByVal authorizedStatements As StatementTypes)
  'Construct Regular Expression To Find Text Blocks, Statement Breaks & SQL Statement Headers
  Dim regExText As String = "('(''|[^'])*')|(;)|(\b(ALTER|CREATE|DELETE|DROP|EXEC(UTE){0,1}|INSERT( +INTO){0,1}|MERGE|SELECT|UPDATE|UNION( +ALL){0,1})\b)"

  'Remove Authorized Options
  If (authorizedStatements And StatementTypes.Batch) = StatementTypes.Batch Then regExText = regExText.Replace("(;)", 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 separators
  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
      Throw New System.UnauthorizedAccessException("Batch statements not authorized:" & vbCrLf & commandText)
    Else
      Throw New System.UnauthorizedAccessException(value.Substring(0, 1).ToUpper & value.Substring(1).ToLower & " statements not authorized:" & vbCrLf & commandText)
    End If
  Next
 End Sub
End Class

And, a simple test to demonstrate that the code works:

Public Sub Test
  Dim commandText As String

  commandText = "SELECT ProductID, Name FROM Products WHERE Name LIKE '; SELECT * FROM sysobjects ;--%'"
  Try
    CommandTextValidator.ValidateStatement(commandText, StatementTypes.Select)
    System.Diagnostics.Debug.WriteLine("Validation worked. The semicolon and statement are inside a text block.")
  Catch ex As System.Exception
    System.Diagnostics.Debug.WriteLine("Validation failed. The following error was thrown: " & ex.message)
  End Try

  commandText = "SELECT ProductID, Name FROM Products WHERE Name LIKE ''; SELECT * FROM sysobjects ;--%'"
  Try
    CommandTextValidator.ValidateStatement(commandText, StatementTypes.Select)
    System.Diagnostics.Debug.WriteLine("Validation failed. An error should have been thrown.")
  Catch ex As System.Exception
    System.Diagnostics.Debug.WriteLine("Validation worked. The following error was thrown: " & ex.message)
  End Try
End Sub

Summary

Today we learned how to use regular expressions to find unauthorized sql statements in our code prior to executing the sql statement. To make the code more robust I would recommend stripping comments, line breaks, spaces and tabs using the code from the previous post, Use Regular Expressions to Clean SQL Statements, prior to calling our new ValidateStatement routine.

When using inline sql statements always check the sql command prior to execution to ensure that your system remains safe and secure from hackers!