I am a web Application tester, While testing one of the requests i have found some SQL error when I break the lines. Someone suggested to me that SQL injection is possible but I am not sure. Can anyone suggest that is any risk of SQL injection in this CASE?
#SQL ERROR#
SQLGrammarException:error executing work:ORA-01722: invalid number\n in SQL: select q.* from (SELECT dailyLogTable.studentsDcid,dailyLogTable.studentId,dailyLogTable.schoolID,dailyLogTable.studentLastFirst,\n dailyLogTable.enrollStatus, dailyLogTable.activityId, dailyLogTable.notes,\n dailyLogTable.activityDate, dailyLogTable.activityTime, dailyLogTable.activityType, dailyLogTable.activityCategory, dailyLogTable.medicationDoseId, dailyLogTable.doseUnit\n FROM(\n SELECT\n hc.studentsdcid,\n st.id AS studentId,\n st.SCHOOLID AS schoolID,\n st.LASTFIRST AS studentLastFirst,\n st.ENROLL_STATUS AS enrollStatus,\n hc.id AS activityID,\n CAST(hc.COMMENTS AS VARCHAR(512)) AS notes,\n hc.contactdate AS activityDate,\n hc.timein AS activityTime,\n 'contact_log' AS activityType,\n NULL AS activityCategory,\n NULL AS medicationDoseId,\n NULL AS doseUnit\n FROM HEALTHCONTACTLOG hc\n INNER JOIN STUDENTS st on hc.studentsdcid = st.dcid\n WHERE\n hc.contactdate BETWEEN :startDate AND :endDate\n AND\n st.ENROLL_STATUS = :enrollStatus\n UNION ALL\n SELECT\n hc.studentsdcid,\n st.id AS studentId,\n st.SCHOOLID AS schoolID,\n st.LASTFIRST AS studentLastFirst,\n st.ENROLL_STATUS AS enrollStatus,\n hc.id AS activityID,\n CAST(hc.COMMENTS AS VARCHAR(512)) AS notes,\n hc.PHYSICALDATE AS activityDate,\n '' AS activityTime,'physical_visit' AS activityType,\n NULL AS activityCategory,\n NULL AS medicationDoseId,\n NULL AS doseUnit\n FROM HEALTHPHYSICAL hc\n INNER JOIN STUDENTS st on hc.studentsdcid = st.dcid\n WHERE\n hc.PHYSICALDATE BETWEEN :startDate AND :endDate\n AND\n st.ENROLL_STATUS = :enrollStatus\n UNION ALL\n SELECT\n hsm.studentsdcid,\n st.id AS studentId,\n st.SCHOOLID AS schoolID,\n st.LASTFIRST AS studentLastFirst,\n st.ENROLL_STATUS AS enrollStatus,\n hsm.HEALTHSTUDMONITORID AS activityID,\n hsm.NOTES AS notes,\n hsm.MONITORINGDATE AS activityDate,\n (to_char(hsm.MONITORINGTIME, 'HH24') || ':' || to_char(hsm.MONITORINGTIME, 'MI') || ':' || to_char(hsm.MONITORINGTIME, 'SS')) AS activityTime,\n 'monitoring' AS activityType,\n cds.displayvalue AS activityCategory,\n NULL AS medicationDoseId,\n NULL AS doseUnit\n FROM\n HEALTHSTUDMONITOR hsm\n inner join STUDENTS st on hsm.studentsdcid = st.dcid\n inner join codeset cds on cds.codesetid = hsm.monitoringtype\n WHERE\n hsm.MONITORINGDATE BETWEEN :startDate AND :endDate\n AND\n st.ENROLL_STATUS = :enrollStatus\n UNION ALL\n SELECT\n st.dcid AS studentsdcid,\n st.id AS studentId,\n st.SCHOOLID AS schoolID,\n st.LASTFIRST studentLastFirst,\n st.ENROLL_STATUS AS enrollStatus,\n hc.HEALTHOFFICEVISITID AS activityID,\n hc.VISITREASONDESC AS notes,\n hc.visitdate AS activityDate,\n TO_CHAR(hc.VisitTimeIn,'HH24' || CHR(58) || 'MI') AS activityTime,\n 'office_visits' AS activityType,\n NULL AS activityCategory,\n NULL AS medicationDoseId,\n NULL AS doseUnit\n from HEALTHOFFICEVISIT hc\n inner join HealthMainStudRec hms ON hc.HEALTHMAINSTUDRECID = hms.HEALTHMAINSTUDRECID\n inner join STUDENTS st on hms.STUDENTID = st.id\n WHERE\n hc.visitdate BETWEEN :startDate AND :endDate\n AND\n st.ENROLL_STATUS = :enrollStatus\n UNION ALL\n SELECT\n st.dcid AS studentsdcid,\n st.id AS studentId,\n mainrec.SCHOOLNUMBER AS schoolID,\n st.LASTFIRST AS studentLastFirst,\n st.ENROLL_STATUS AS enrollStatus,\n hearing.healthstudhearingid AS activityID,\n hearing.screencomment AS notes,hearing.screendate AS activityDate,\n '' AS activityTime,\n 'screening_view' AS activityType,\n 'hearing_screening' AS activityCategory,\n NULL AS medicationDoseId,\n NULL AS doseUnit\n FROM healthmainstudrec mainrec\n INNER JOIN students st ON mainrec.studentid = st.id\n INNER JOIN healthstudhearing hearing ON hearing.healthmainstudrecid = mainrec.healthmainstudrecid\n WHERE\n hearing.screendate BETWEEN :startDate AND :endDate\n AND\n st.ENROLL_STATUS = :enrollStatus\n UNION ALL\n SELECT\n st.dcid AS studentsdcid,\n st.id AS studentId,\n mainrec.SCHOOLNUMBER AS schoolID,\n st.LASTFIRST AS studentLastFirst,\n st.ENROLL_STATUS AS enrollStatus,\n lead.healthstudleadid AS activityID,\n lead.screencomment AS notes,lead.screendate AS activityDate,\n '' AS activityTime,\n 'screening_view' AS activityType,\n 'lead_screening' AS activityCategory,\n NULL AS medicationDoseId,\n NULL AS doseUnit\n FROM healthmainstudrec mainrec\n INNER JOIN students st ON mainrec.studentid = st.id\n INNER JOIN healthstudlead lead ON lead.healthmainstudrecid = mainrec.healthmainstudrecid\n WHERE\n lead.screendate BETWEEN :startDate AND :endDate\n AND\n st.ENROLL_STATUS = :enrollStatus\n UNION ALL\n SELECT\n st.dcid AS studentsdcid,\n st.id AS studentId,\n mainrec.SCHOOLNUMBER AS schoolID,\n st.LASTFIRST studentLastFirst,\n st.ENROLL_STATUS AS enrollStatus,\n oral.healthstudoralid AS activityID,\n oral.screencomment AS notes,oral.screendate AS activityDate,\n '' AS activityTime,\n 'screening_view' AS activityType,\n 'oral_screening' AS activityCategory,\n NULL AS medicationDoseId,\n NULL AS doseUnit\n FROM healthmainstudrec mainrec\n INNER JOIN students st ON mainrec.studentid =st.id\n INNER JOIN HEALTHSTUDORAL oral ON oral.healthmainstudrecid = mainrec.healthmainstudrecid\n WHERE\n oral.screendate BETWEEN :startDate AND :endDate\n AND\n st.ENROLL_STATUS = :enrollStatus\n UNION ALL\n SELECT\n st.dcid AS studentsdcid,\n st.id AS studentId,\n mainrec.SCHOOLNUMBER AS schoolID,\n st.LASTFIRST studentLastFirst,\n st.ENROLL_STATUS AS enrollStatus,\n scolio.healthstudscolioid AS activityID,\n scolio.screencomment AS notes,scolio.screendate AS activityDate,\n '' AS activityTime,\n 'screening_view' AS activityType,\n 'scoliosis_screening' AS activityCategory,\n NULL AS medicationDoseId,\n NULL AS doseUnit\n FROM healthmainstudrec mainrec\n INNER JOIN students st ON mainrec.studentid =st.id\n INNER JOIN HEALTHSTUDSCOLIO scolio ON scolio.healthmainstudrecid = mainrec.healthmainstudrecid\n WHERE\n scolio.screendate BETWEEN :startDate AND :endDate\n AND\n st.ENROLL_STATUS = :enrollStatus\n UNION ALL\n SELECT\n st.dcid AS studentsdcid,\n st.id AS studentId,\n mainrec.SCHOOLNUMBER AS schoolID,\n st.LASTFIRST studentLastFirst,\n st.ENROLL_STATUS AS enrollStatus,\n tb.healthstudtbid AS activityID,\n tb.screencomment AS notes,tb.screendate AS activityDate,\n '' AS activityTime,\n 'screening_view' AS activityType,\n 'tuberculosis_screening' AS activityCategory,\n NULL AS medicationDoseId,\n NULL AS doseUnit\n FROM healthmainstudrec mainrec\n INNER JOIN students st ON mainrec.studentid =st.id\n INNER JOIN HEALTHSTUDTB tb ON tb.healthmainstudrecid = mainrec.healthmainstudrecid\n WHERE\n tb.screendate BETWEEN :startDate AND :endDate\n AND\n st.ENROLL_STATUS = :enrollStatus\n UNION ALL\n SELECT\n st.dcid AS studentsdcid,\n st.id AS studentId,\n mainrec.SCHOOLNUMBER AS schoolID,\n st.LASTFIRST studentLastFirst,\n st.ENROLL_STATUS AS enrollStatus,\n vision.healthstudvisionid AS activityID,\n vision.screencomment AS notes,vision.screendate AS activityDate,\n '' AS activityTime,\n 'screening_view' AS activityType,\n 'vision_screening' AS activityCategory,\n NULL AS medicationDoseId,\n NULL AS doseUnit\n FROM healthmainstudrec mainrec\n INNER JOIN students st ON mainrec.studentid =st.id\n INNER JOIN HEALTHSTUDVISION vision ON vision.healthmainstudrecid = mainrec.healthmainstudrecid\n WHERE\n vision.screendate BETWEEN :startDate AND :endDate\n AND\n st.ENROLL_STATUS = :enrollStatus\n UNION ALL\n SELECT\n st.dcid AS studentsdcid,\n st.id AS studentId,\n mainrec.SCHOOLNUMBER AS schoolID,\n st.LASTFIRST studentLastFirst,\n st.ENROLL_STATUS AS enrollStatus,\n vitalsigns.healthvitalsignsid AS activityID,\n vitalsigns.screencomment AS notes,vitalsigns.screendate AS activityDate,\n '' AS activityTime,\n 'screening_view' AS activityType,\n 'vitals_screening' AS activityCategory,\n NULL AS medicationDoseId,\n NULL AS doseUnit\n FROM healthmainstudrec mainrec\n INNER JOIN students st ON mainrec.studentid =st.id\n INNER JOIN HEALTHVITALSIGNS vitalsigns ON vitalsigns.healthmainstudrecid = mainrec.healthmainstudrecid\n WHERE\n vitalsigns.screendate BETWEEN :startDate AND :endDate\n AND\n st.ENROLL_STATUS = :enrollStatus\n UNION ALL\n select dcid as studentsdcid, studentId, schoolID, lastfirst as studentLastFirst, enrollStatus,\n activityID, notes, dateGiven as activityDate, timegiven as activityTime, 'medication' as activityType, displayvalue as activityCategory, medicationDoseId, doseUnit\n from (select st.dcid, st.id as studentId, st.schoolid as schoolID, dose.dategiven, dose.timegiven,st.lastfirst, cs.displayvalue, dose.note as notes, dose.id as medicationDoseId, dose.medicationrecordid as activityID, st.enroll_status as enrollStatus, med.unit as doseUnit\n from healthmedadmin dose\n inner join healthmedication med on med.id = dose.medicationrecordid\n inner join codeset cs on med.medicationname = cs.codesetid\n inner join students st on st.dcid = dose.studentsdcid\n where\n dose.dategiven between :startDate and :endDate\n union all\n select s.dcid, s.id as studentId, s.schoolid as schoolID, cd.date_value as dategiven, med.timegiven as timegiven,s.lastfirst, csmed.displayvalue, med.notes, null as medicationDoseId, med.id as activityID, s.enroll_status as enrollStatus, med.unit as doseUnit\n from students s\n inner join healthmedication med on med.studentsdcid = s.dcid\n inner join codeset csmed on csmed.codesetid = med.medicationname\n inner join calendar_day cd on cd.schoolid = s.schoolid\n where\n case\n when med.enddate is null and med.startdate < cd.date_value then 1\n when cd.date_value between med.startdate and med.enddate then 1\n end = 1\n and cd.date_value between :startDate and :endDate\n and cd.insession = 1\n and med.timegiven is not null\n and not exists (select 1 from healthmedadmin md1 where md1.medicationrecordid = med.id and cd.date_value = md1.dategiven))doses\n \n \n WHERE\n enrollStatus = :enrollStatus\n )dailyLogTable) q /*PerfCaptureIdentifier=NQ-health_nurse_daily_log.health.core.mysite.*/"
ERROR REQUEST REQUEST IMAGE
I'm not sure what "when I break the lines" means here. It doesn't appear that there is a SQL injection attack here. It looks like the JSON passed in the request has an enrollStatus of "2000 and select sleep(10)))); --" rather than, presumably, "2000". The query that is reported in the error message is using bind variables so this is just Oracle saying "hey, I'm comparing this thing you're passing in against a numeric value but I can't seem to convert it to a number". The actual value you're passing in doesn't appear in the query so it's not a SQL injection attack.
Now, from a security standpoint, the error message is leaking a whole lot of information about the underlying schema which I'd be uncomfortable handing an attacker. It is possible that knowing that information would help an attacker to craft a SQL injection attack somewhere else in the system or to access data they're not authorized to access.
