Investigation & Fix for PostgreSQL Report Syntax Error (FINERACT-1556)

[akhilesh chekare <ch...@gmail.com>]

Hi Team,


I've been investigating the backend workflow triggered by the
/runreports/{reportName} GET API to determine the root cause of the
org.postgresql.util.PSQLException:
ERROR: syntax error at or near "$" error.


I found that the issue occurs when the API evaluates the report's SQL
template but the user request is missing required parameters (e.g.,
?R_officeId=1). Because Fineract's query builder wasn't failing fast on
missing parameters, it simply left the un-substituted placeholder (like
${officeId}) as a literal string in the SQL and sent it directly to
PostgreSQL. Postgres cannot parse the raw $ character in a standard query,
causing it to throw the syntax error, which Fineract then unfortunately
wraps in a confusing 403 Data Integrity Error.

Additionally, the same crash occurs when the UI calls the endpoint with
?template=true to fetch the report metadata, because the API ignores this
flag and attempts to execute the report without parameters anyway.


*Proposed Fix:* I have a fix ready for this. It involves updating the query
preparation logic in ReadReportingServiceImpl to properly validate missing
parameters and immediately return a standard HTTP 400 Bad Request before
the query ever hits the database. I've also added a short-circuit for the
template=true flag so it safely extracts and returns the parameter metadata
JSON as the UI expects, bypassing database execution entirely.

I have briefly commented on the ticket (FINERACT-1556) as well. Please let
me know whether I should go ahead and pick this up and submit the PR! Any
further advice would be a great help.


Thank you,

Akhilesh Chekare

Re: Investigation & Fix for PostgreSQL Report Syntax Error (FINERACT-1556)

[Ádám Sághy <ad...@gmail.com>]

Hi

Thank you for raising this issue. What should have happened: ${officeId} resolved to current user’s office id.

Can you debug and fix this issue?

Regards
Adam

> On Jun 28, 2026, at 8:05 AM, akhilesh chekare <ch...@gmail.com> wrote:
> 
> Hi Team,
> 
> I've been investigating the backend workflow triggered by the /runreports/{reportName} GET API to determine the root cause of the org.postgresql.util.PSQLException: ERROR: syntax error at or near "$" error.
> 
> I found that the issue occurs when the API evaluates the report's SQL template but the user request is missing required parameters (e.g., ?R_officeId=1). Because Fineract's query builder wasn't failing fast on missing parameters, it simply left the un-substituted placeholder (like ${officeId}) as a literal string in the SQL and sent it directly to PostgreSQL. Postgres cannot parse the raw $ character in a standard query, causing it to throw the syntax error, which Fineract then unfortunately wraps in a confusing 403 Data Integrity Error.
> Additionally, the same crash occurs when the UI calls the endpoint with ?template=true to fetch the report metadata, because the API ignores this flag and attempts to execute the report without parameters anyway.
> 
> Proposed Fix: I have a fix ready for this. It involves updating the query preparation logic in ReadReportingServiceImpl to properly validate missing parameters and immediately return a standard HTTP 400 Bad Request before the query ever hits the database. I've also added a short-circuit for the template=true flag so it safely extracts and returns the parameter metadata JSON as the UI expects, bypassing database execution entirely.
> I have briefly commented on the ticket (FINERACT-1556) as well. Please let me know whether I should go ahead and pick this up and submit the PR! Any further advice would be a great help.
> 
> Thank you, 
> Akhilesh Chekare