Abhishek Chaudhary created FINERACT-2676:
--------------------------------------------
Summary: Named PPI survey score overview query fails because
survey table name is bound as JDBC parameter
Key: FINERACT-2676
URL: https://issues.apache.org/jira/browse/FINERACT-2676
Project: Apache Fineract
Issue Type: Bug
Components: Surveys
Reporter: Abhishek Chaudhary
The PPI survey score overview endpoint for a specific survey builds invalid SQL
by using a JDBC bind parameter for the datatable name.
*Problem*
In {{ReadSurveyServiceImpl.retrieveClientSurveyScoreOverview(String surveyName,
Long clientId)}}, the SQL contains:
{quote}
SELECT tz.id, lkh.name, lkh.code, poverty_line, tz.date, tz.score FROM ? tz ...
{quote}
and passes {{surveyName}} as the first bind parameter.
JDBC bind parameters can be used for values, but not for SQL identifiers such
as table names. As a result, retrieving score overview for a named survey is
expected to fail with SQL syntax/grammar errors.
The all-surveys variant in the same class builds the query differently by
inserting the registered survey table name into the SQL, so this appears to
affect only the named-survey path.
*Steps to reproduce*
# Create/register a PPI survey datatable.
# Add a survey entry for a client.
# Call the API path that retrieves score overview for that specific survey and
client.
# Observe that the request fails due to invalid SQL generated with {{FROM ?
tz}}.
*Expected result*
The endpoint should return the client score overview for the requested survey.
*Actual result*
The endpoint builds invalid SQL because the table name is passed as a JDBC
parameter.
*Code reference*
{{fineract-provider/src/main/java/org/apache/fineract/infrastructure/survey/service/ReadSurveyServiceImpl.java}}
Method:
{{retrieveClientSurveyScoreOverview(String surveyName, Long clientId)}}
*Suggested fix*
Validate that {{surveyName}} is a registered PPI datatable, escape/quote it as
an identifier using the existing database SQL generator or SQL injection
prevention service, and build the {{FROM}} clause with the validated identifier
instead of a JDBC placeholder.
Add test coverage
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
