SQL Injection – How to Avoid It in Your OneStream Business Rules

This came up at a client the other day. They were performing a security audit on the OneStream Business Rules that had been written in their application. The audit was failing due to SQL Injection. So, what is SQL Injection and how do you write code to avoid it (and pass the Security Audit)?

SQL Injection is a security vulnerability that allows an attacker to modify SQL queries by injecting malicious code into the user input sections of a SQL query. Including concatenated User Input in the SQL Query allows SQL Injection.

The following code sample is from a OneStream Business Rule. It shows an example of a SQL Database query that is open to SQL Injection. The query is based on the user input of 2 variables, Sales Person First Name and Sales Person Last Name.

 Dim dt as New DataTable
 Dim strSalesPersonFirstName As String = "John"
 Dim strSalesPersonLastName As String = "Doe"
 Dim strCity As String = Nothing
 using dbconn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
        Dim Sql As String = String.Empty
        sql = $"SELECT City from XFC_TableView_SalesPerson WHERE SalesPersonFirstName = '{strSalesPersonFirstName}' AND SalesPersonLastName = '{strSalesPersonLastName}';"
        dt = BRApi.Database.ExecuteSqlUsingReader(dbConn,sql.ToString,False)
        If Not dt is Nothing Then
           For Each RowNotInTableException As DataRow In dt.Rows
                    strCity = RowNotInTableException.Item("City")
           Next
        End If
        brapi.ErroLog.LogMessage(si,"City=" & strCity.ToString)
 End Using

The SQL Query is interpreted by VB.Net, which would replace the 2 string variables in the SQL query and pass that complete query to the database. It would look like “SELECT City from XFC_TableView_SalesPerson WHERE SalesPersonFirstName = ‘John’ AND SalesPersonLastName = ‘Doe’;” This opens the opportunity for an attacker to replace the 2 user inputted variables with other SQL Code that would get passed, concatenated into the query by VB.Net and run by the database.

So how to avoid this? One way is to separate the user input from the SQL Query using Parameterized Queries. Parameterized queries keep the User Input separate in the code from the SQL Query. Then the parameterized user input is passed separately from the SQL Query to the database.

In practice it goes as follows.

You will still use BRApi.Database.ExecuteSql or BRApi.Database.ExecuteSqlUsingReader. What you will do differently is use the version that takes a dbParamInfo object as a parameter.

Syntax
Public Function ExecuteSqlUsingReader(ByVal dbConn As DbConnInfo, ByVal sqlStatement As String, ByVal dbParaminfos As List(Of DbParaminfo),
ByVal useCommandTimeoutLarge As Boolean) As DataTable

dbParamInfo is a List(Of DbParamInfo). Basically, you load this list with the parameters and their values for your parameterized SQL query and pass this into your BRApi.Database.ExecuteSql or BRApi.Database.ExecuteSqlUsingReader.

1. In your Business Rule you create a DbParamInfo object and then add your variables and their values to it using “.Add”.

 Dim dt as New DataTable
 Dim strSalesPersonFirstName As String = "John"
 Dim strSalesPersonLastName As String = "Doe"
 Dim strCity As String = Nothing
 Dim dbParamInfos As New List(Of DbParamInfo)
 dbParamInfos.Add(New DbParamInfo("strSalesPersonFirstName", strSalesPersonFirstName))
 dbParamInfos.Add(New DbParamInfo("strSalesPersonLastName", strSalesPersonLastName))

2. Then you parameterize your SQL query.

Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDBConnInfo(si)
Dim Sql As String = String.Empty
 sql = $"SELECT City from XFC_TableView_SalesPerson WHERE SalesPersonFirstName = @strSalesPersonFirstName AND SalesPersonLastName = @strSalesPersonLastName;"

3. Finally, you pass both the SQL Query and the separate DbParamInfo object to your BRApi.Database.ExecuteSql or BRApi.Database.ExecuteSqlUsingReader call. Keeping the parameters separate from the SQL query. This way avoids SQL Injection.

The query in its entirety.

 Dim dt as New DataTable
 Dim strSalesPersonFirstName As String = "John"
 Dim strSalesPersonLastName As String = "Doe"
 Dim strCity As String = Nothing
  Dim dbParamInfos As New List(Of DbParamInfo)
 dbParamInfos.Add(New DbParamInfo("strSalesPersonFirstName", strSalesPersonFirstName))
 dbParamInfos.Add(New DbParamInfo("strSalesPersonLastName", strSalesPersonLastName))
 using dbconn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
        Dim Sql As String = String.Empty
        sql = $"SELECT City from XFC_TableView_SalesPerson WHERE SalesPersonFirstName = @strSalesPersonFirstName AND SalesPersonLastName = @strSalesPersonLastName;"
        dt = BRApi.Database.ExecuteSqlUsingReader(dbConn,sql.ToString,dbParamInfors,False)
        If Not dt is Nothing Then
           For Each RowNotInTableException As DataRow In dt.Rows
                    strCity = RowNotInTableException.Item("City")
           Next
        End If
        brapi.ErroLog.LogMessage(si,"City=" & strCity.ToString)
 End Using

It's not a large change to the way you are used to writing SQL queries in OneStream and it will help to avoid SQL injections (and pass security audits).