Summary

Pre-existing SQL injection vulnerabilities identified in src/main/webapp/billing/CA/ON/billingONNewReport.jsp during review of PR #366. These vulnerabilities exist in the current codebase and were not introduced by the PR, but need to be tracked and fixed.

Vulnerability Details

Affected File

src/main/webapp/billing/CA/ON/billingONNewReport.jsp

Vulnerable Code Locations

All four billing report actions concatenate user-controlled request parameters directly into SQL queries without parameterization:

1. Line 97-100 (unbilled action):

sql = "select * from appointment where provider_no='" + providerview + "' and appointment_date >='" + xml_vdate
        + "' and appointment_date<='" + xml_appointment_date
        + "' and (BINARY status NOT LIKE 'B%' AND BINARY status NOT LIKE 'C%' AND BINARY status NOT LIKE 'N%')"
        + " and demographic_no != 0 order by appointment_date , start_time ";

2. Line 134-136 (billed action):

sql = "select * from billing_on_cheader1 where provider_no='" + providerview + "' and billing_date >='" + xml_vdate
        + "' and billing_date<='" + xml_appointment_date + "' and (status<>'D' and status<>'S' and status<>'B')"
        + " order by billing_date , billing_time ";

3. Line 195-198 (settled action):

sql = "select billing_no,total from billing where provider_no='" + providerview
        + "' and billing_date>='" + xml_vdate + "' and billing_date<='" + xml_appointment_date
        + "' and status ='S' order by billing_date, billing_time";

4. Line 293-295 (all action):

sql = "select * from billing where provider_no='" + providerview + "' and billing_date >='" + xml_vdate
        + "' and billing_date<='" + xml_appointment_date + "' and (status<>'D' and status<>'S')"
        + " order by billing_date , billing_time ";

User-Controlled Parameters

• providerview - from request.getParameter("providerview") (Line 67)
• xml_vdate - from request.getParameter("xml_vdate") (Line 75)
• xml_appointment_date - from request.getParameter("xml_appointment_date") (Line 76)

Security Impact

• Severity: Critical
• Type: SQL Injection (CWE-89)
• OWASP: A03:2021 – Injection
• Data at Risk: Patient Health Information (PHI), billing records, appointment data
• Compliance: Violates HIPAA/PIPEDA requirements

An attacker could craft malicious values for these parameters to:

• Extract sensitive patient data
• Modify billing records
• Bypass access controls
• Perform unauthorized database operations

Remediation

Replace all string concatenation SQL queries with parameterized queries (PreparedStatement).

Example Fix

// BEFORE (vulnerable):
sql = "select * from appointment where provider_no='" + providerview + "' and appointment_date >='" + xml_vdate + "'";

// AFTER (secure):
String sql = "select * from appointment where provider_no=? and appointment_date >=?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, providerview);
ps.setString(2, xml_vdate);
ResultSet rs = ps.executeQuery();

References

• Identified during code review of PR fix: restyle billing report and day sheet JSPs with Bootstrap/searchB… #366
• CARLOS security guidelines (CLAUDE.md): "Parameterized queries ONLY - never string concatenation"
• OWASP SQL Injection Prevention: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

Related Issues

Closes #366 review finding (pre-existing vulnerability)