Metadata security causing SQL syntax error

Database Support
, , , , ,
#1

Hello,

Using open source BI helical insight enterprise edition 5.2.2

I have created several reports using the hiadmin user. These reports are working correctly when accessed directly via Helical Insight.

We have implemented metadata-level security using a company filter, so users from different tenants/organizations can only see their respective data. Everything works as expected when accessed directly via Helical Insight.

However, when we embed these reports into our application and users from different companies log in to view their respective reports, we encounter issues. The embedded reports throw a SQL syntax error.

Upon inspecting the generated SQL in Helical Insight, we notice that an extra, incorrect syntax is being added—likely due to how the security filter is being applied in the embedded context.

How can we fix this issue?

Thanks.

#2

Hello,

This issue arises due to how the security filters are applied when reports are embedded. In version 4.1, we used to handle such cases by setting a custom filter expression to bypass the outer WHERE clause. From version 5.0 onwards, Helical Insight introduced an option in the frontend to manage this using Filter Advanced Settings.

When users log in, an additional security WHERE clause is appended automatically. If this clause isn’t properly formatted or clashes with existing report filters, it can cause SQL syntax errors.

To fix this:

Please follow these steps to add a filter expression override in your reports:

  1. Open the report in edit mode.

  2. Click on the three dots (options menu) and select “Operations”.

  3. In the “Pre Fetch” section, inject the following line of code:

setFilterExpression((“1=1”))

image

  1. Click Apply and Save the report.

This code forces a neutral filter expression and prevents malformed SQL from being generated when security clauses are appended.

Now, try accessing the embedded report as a logged-in user from a different organization. The report should render correctly without syntax errors.

Thank you.