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
