[jira] [Created] (FINERACT-2676) Named PPI survey score overview query fails because survey table name is bound as JDBC parameter

["Abhishek Chaudhary (Jira)" <ji...@apache.org>]

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)