[
https://issues.apache.org/jira/browse/FINERACT-2676?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Abhishek Chaudhary updated FINERACT-2676:
-----------------------------------------
Fix Version/s: 1.15.0
> 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
> Priority: Major
> Labels: Survey, ppi, sql
> Fix For: 1.15.0
>
>
> 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)
