Scanning for OWS SQL Injection Protection

A few months ago, I was reviewing some code that was flagged by Veracode for multiple CWE-89: Improper Neutralization of Special Elements used in an SQL Command (‘SQL Injection’) flaws. When working with the development team, they demonstrated that the SQL generation which triggers these flaws is part of OpenWebStudio (OWS) and is driven by configuration data. I said that if we can prove we are generating from safe input, I could mitigate, but I wanted an in-depth review of the code paths.

OWS does include the ability to Protect Against SQL Injection and even explicitly mentions to “ALWAYS check the box to protect against SQL Injection”. Here is a screenshot from the Variable documentation.

Since OWS is open source, I was able to search the code and find that the FormatQueryVariable implementation inside Render.Variable.vb works as expected.

  If EscapeQuotes Then
      Value = Value.Replace("'", "''")
      If (Left Is Nothing OrElse Left.Length = 0) AndAlso (Right Is Nothing OrElse Right.Length = 0) Then
          'NO LEFT AND RIGHT ARE PROVIDED, BUT SQL INJECTION IS CHECKED
          Firewall.Firewall(Value, False, Utilities.Firewall.FirewallDirectiveEnum.Any, False)
      End If
  End If

The configuration from that Variable UI Dialog above is serialized as JSON data into an XML based Microsoft ResX file.

<data name="Module.Load" xml:space="preserve">
  <value>
  <!-- OWS JSON configuration -->
  </value>
</data>

Inside that JSON configuration string is a block for each of the Variable configurations. The JSON sample below is representative of the UI Dialog above. Note that Protected is set to "true".

  "ChildActions": [{
      "Index": 66,
      "Level": 0,
      "Parameters": {
          "VariableType": "<Form>",
          "VariableDataType": "Any",
          "Formatters": "",
          "QuerySource": "frmContent",
          "QueryTarget": "@frmContent",
          "QueryTargetLeft": "'",
          "QueryTargetRight": "'",
          "QueryTargetEmpty": "NULL",
          "EscapeListX": "0",
          "Protected": "true",
          "EscapeHTML": "false"
      },
      "ActionType": "Template-Variable",
      "ChildActions": []
  }

I had asked the team to write a script to scan all .ascx.resx files in the project and verify SQL Protection is being set as expected. However, when I checked back with them, this was still on the backlog. I took this as an opportunity to help out and write the code myself. I usually use python for my utility scripts, but I decided to try out PowerShell for this since it would be more familiar to that .NET based team.

The first thing I wanted to do was initialize some counters and recursively find all the *.ascx.resx files. Since I kept seeing examples using | in my google results, I read up on How Pipelines Work in PowerShell, including the ForEach-Object function and the $_ automatic variable.

$FileCount = 0
$IndexCount = 0
$FilePathLength = (Get-Location).Path.Length + 1

Get-ChildItem *.ascx.resx -Recurse | ForEach-Object {
  $CurrentFile = $_.FullName.substring($FilePathLength)
  [String[]] $FileMessages = @()
  
  # continued below

I used Select-Xml with an XPath selector of //data[@name='Module.Load']/value, eventually converting the JSON string value into a PSCustomObject using ConvertFrom-Json. I then iterated over the ChildActions and called a ScanChildActions function I created to return an array of any violations.

  $obj = Select-Xml -Path $_.FullName -XPath "//data[@name='Module.Load']/value" |
  ForEach-Object {$_.node.InnerXML} |
  ConvertFrom-Json

  $obj.messageItems | ForEach-Object {
    $_.ChildActions | ForEach-Object {
      $FileMessages = ScanChildActions $_ $FileMessages
    }
  }
  
  # continued below

The ChildActions function is a recursive scan that looks for any ActionType of Template-Variable and inspects the Protected parameter. If "false", it appends an error message with the ️Index value as the key for finding the offending Variable later.

function ScanChildActions($ChildActions, [String[]]$ErrorMessages) {
  $ChildActions | ForEach-Object {
    if ($_.ActionType -eq "Template-Variable") {
      if ($_.Parameters.Protected -eq "false") {
        $message = "️Index=$($_.Index)  Protected=false"
        $ErrorMessages += $message
      }
    }

    $ErrorMessages = [String[]](ScanChildActions $_.ChildActions $ErrorMessages)
  }

  return $ErrorMessages
}

With all the error messages gathered for the file, it was time to output the results and increment $FileCount / $IndexCount and output either a success (✅) or failure (❌) line for that filename.

  # ... continued from above

  if ($FileMessages.length -eq 0) {
    Write-Output "✅  $($CurrentFile)"

  } else {
    $script:FileCount++
    $script:IndexCount += $FileMessages.Length

    Write-Output "❌  $($CurrentFile)"
    $FileMessages | ForEach-Object { Write-Output "️  ☒ $($_)" }
  }
}

Because I intended for this to be run as part of the build, I made sure to return a non-zero exit code so this script could be used to fail either the build or a pre-commit hook or whatever worked for the team’s development flow.

Write-Output "$($FileCount) files had $($IndexCount) instances of Variable SQL Injection"
if ($FileCount -gt 0) {
  exit -1
}
exit 0

I am early in my PowerShell script development journey, so there are likely improvements I could make to this code. However, since my goal was to improve security through automation, I feel good about the solution.