How to Prevent SQL Injection in OneStream

Prevent OneStream SQL Injection

SQL injection in OneStream becomes a risk when business rules concatenate user-controlled values directly into a database query. Parameterized queries keep the SQL statement separate from its values, allowing the database driver to treat input as data rather than executable code and helping applications pass security reviews.

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. Create a DbParamInfo object and add your variables 

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. 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. Pass the SQL query and DbParamInfo object together

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.

Contact

MindStream Analytics

To learn more about OneStream and how MindStream Analytics can help you improve your planning, reporting, and analytics, please fill out the form below.

Secure Pattern for SQL Injection in OneStream

To reduce SQL injection in OneStream, define placeholders in the SQL statement and create a corresponding list of DbParamInfo objects. Each object pairs a parameter name with its value. Pass both the statement and parameter list to the supported BRApi database method instead of interpolating names, filters, or dashboard input into the query string.

Validate the expected type, length, format, and business meaning of every input even when parameters are used. Parameterization protects values, but object names such as table or column identifiers may still require an allowlist because they generally cannot be supplied as ordinary query parameters.

A complete review for SQL injection in OneStream should cover dashboard extender rules, data adapters, scheduled processes, custom tables, integrations, and any shared helper that builds SQL. Centralizing safe query methods reduces repeated mistakes and makes security-audit evidence easier to produce.

Test the business rule with normal values, apostrophes, empty values, unexpected Unicode, oversized input, and known injection payloads. Log failures without exposing credentials or sensitive query data. Apply least-privilege database permissions so a defect cannot perform operations beyond the rule’s legitimate purpose.

Teams should treat SQL injection in OneStream as a coding-standard concern, not only an audit finding. Add a parameterization check to pull-request reviews, flag string-built SQL during static analysis, and keep approved examples available for developers. Preventing SQL injection in OneStream is easier when secure helpers are the default. Regular reviews for SQL injection in OneStream also protect older rules as requirements change.

Related OneStream Security and Development Resources

For more guidance on preventing SQL injection in OneStream, explore what OneStream is, OneStream software, OneStream consulting, saving data units in OneStream, OneStream business rules. Also review the official OneStream documentation for parameterized database queries.