[jira] [Commented] (FINERACT-1338) SQL Injection - While "runreports" api is trying to load report parameters

["Joseph Makara (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/FINERACT-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17314418#comment-17314418 ] 

Joseph Makara commented on FINERACT-1338:
-----------------------------------------

Hi,

FINERACT-1336 is in the right direction. 

To solve FINERACT-1338  - The Short answer = Use Prepared Statements!

How to reproduce: Apply the script in FINERACT-1336. This solves below issues while running [https://localhost:8443/fineract-provider/api/v1/runreports/FullParameterList?R_reportListing=%27Active+Clients%27&parameterType=true] 

 
{code:java}
{"developerMessage":"The requested resource is not available.","httpStatusCode":"404","defaultUserMessage":"The requested resource is not available.","userMessageGlobalisationCode":"error.msg.resource.not.found","errors":[{"developerMessage":"Reporting meta-data entry not found.","defaultUserMessage":"Reporting meta-data entry not found.","userMessageGlobalisationCode":"error.msg.report.name.not.found","parameterName":"id","value":null,"args":[{"value":"Report Name: FullParameterList"}]}]}
{code}
The next issues comes up whilst running [https://localhost:8443/fineract-provider/api/v1/runreports/OfficeIdSelectOne?parameterType=true]

 

 
{code:java}
{"developerMessage":"The request was invalid. This typically will happen due to validation errors which are provided.","httpStatusCode":"400","defaultUserMessage":"Unexpected SQL Commands found","userMessageGlobalisationCode":"error.msg.found.sql.injection"}
{code}
This comes after running SQL 

 

 
{code:java}
SELECT x.* FROM (
  SELECT 
    sp.parameter_name, sp.parameter_variable, sp.parameter_label, sp.parameter_displayType,  
    sp.parameter_FormatType, sp.parameter_default, sp.selectOne,  sp.selectAll, spp.parameter_name AS  parentParameterName 
  FROM stretchy_parameter sp 
  LEFT JOIN stretchy_parameter spp ON spp.id = sp.parent_id 
  WHERE sp.special IS NULL AND EXISTS (
    SELECT 'f'   
    FROM stretchy_report sr  
    JOIN stretchy_report_parameter srp ON srp.report_id = sr.id   AND sr.self_service_user_report = '0'   
    WHERE sr.report_name IN('Active Clients')
   AND srp.parameter_id = sp.id  
  ) ORDER BY sp.id
) x;
{code}
Which returns

 
{code:java}
+------------------------+--------------------+-----------------+-----------------------+
| parameter_name         | parameter_variable | parameter_label | parameter_displayType |
+------------------------+--------------------+-----------------+-----------------------+
| OfficeIdSelectOne      | officeId           | Office          | select                |
| loanOfficerIdSelectAll | loanOfficerId      | Loan Officer    | select                |
+------------------------+--------------------+-----------------+-----------------------+
{code}
ParameterName `OfficeIdSelectOne` will not pass `validateReportName` with `REPORT_NAME_REGEX_PATTERN`.  These other parameters in the same table are impacted. 
h6. {{'currencyIdSelectAll', 'cycleXSelect', 'cycleYSelect', 'endDateSelect', 'fromXSelect', 'fundIdSelectAll', 'loanOfficerIdSelectAll', 'loanProductIdSelectAll', 'loanPurposeIdSelectAll', 'obligDateTypeSelect', 'OfficeIdSelectOne', 'overdueXSelect', 'overdueYSelect', 'parTypeSelect', 'savingsProductIdSelectAll', 'selectAccount', 'selectCenterId', 'SelectGLAccountNO', 'SelectLoanType', 'startDateSelect', 'toYSelect'}}

I see that history of things. The SQL Injection validation workaround should be removed and fix this with parameter queries for the `inputSQL` shown below. 

 

This has been identified by OWASP ZAP Scan as fix candidate in  `ReadReportingServiceImpl.java` in Jira FINERACT-969 

See how it is doing string concatenations `where " + type + "_name = '" + name + "'"`

 
{code:java}
    private String getSql(final String name, final String type) {
        final String inputSql = "select " + type + "_sql as the_sql from stretchy_" + type + " where " + type + "_name = '" + name + "'";
{code}
Instead of REGEX validation for report names here it requires changing to use parameterised query with `?`

 

 

 

> SQL Injection - While "runreports" api is trying to load report parameters
> --------------------------------------------------------------------------
>
>                 Key: FINERACT-1338
>                 URL: https://issues.apache.org/jira/browse/FINERACT-1338
>             Project: Apache Fineract
>          Issue Type: Bug
>            Reporter: Francis Guchie
>            Assignee: Francis Guchie
>            Priority: Major
>         Attachments: image-2021-03-31-15-53-00-571.png
>
>
> After solving the error at FINERACT-1336 a new error shows up. 
> while api - runreports
> fineract-provider/api/v1/runreports/OfficeIdSelectOne?parameterType=true
> is spooling the report parameters, user will not see any error on the UI 
> !image-2021-03-31-15-53-00-571.png!
> but looking through the console OR postman you see error below
> {
>     "developerMessage": "The request was invalid. This typically will happen due to validation errors which are provided.",
>     "httpStatusCode": "400",
>     "defaultUserMessage": "Unexpected SQL Commands found",
>     *"userMessageGlobalisationCode": "error.msg.found.sql.injection"*
> }



--
This message was sent by Atlassian Jira
(v8.3.4#803005)