From 7d9ba5eea9377ab15f359bd32306bdb9c276f638 Mon Sep 17 00:00:00 2001 From: efiacor Date: Wed, 9 Mar 2022 11:48:35 +0000 Subject: [PATCH] [DMAAP-DR] Fix sql injection bug Signed-off-by: efiacor Change-Id: Icccc65b3b90c553dea74d95bf247b08ae6f78506 Issue-ID: DMAAP-1623 --- .../datarouter/provisioning/StatisticsServlet.java | 155 +++++++++++---------- .../provisioning/StatisticsServletTest.java | 4 +- 2 files changed, 85 insertions(+), 74 deletions(-) diff --git a/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java b/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java index 4bc39771..964ef037 100755 --- a/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java +++ b/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java @@ -163,33 +163,33 @@ public class StatisticsServlet extends BaseServlet { map.put(OUTPUT_TYPE, JSON_OUTPUT_TYPE); } if (req.getParameter(START_TIME) != null) { - String start_time = req.getParameter(START_TIME); - try{ - Long.parseLong(start_time); - map.put(START_TIME, start_time); + String startTime = req.getParameter(START_TIME); + try { + Long.parseLong(startTime); + map.put(START_TIME, startTime); } - catch (NumberFormatException e){ + catch (NumberFormatException e) { eventlogger.error("Invalid start time StatisticsServlet.doGet: " + e.getMessage(), e); } } if (req.getParameter(END_TIME) != null) { - String end_time = req.getParameter(END_TIME); - try{ - Long.parseLong(end_time); - map.put(END_TIME, end_time); + String endTime = req.getParameter(END_TIME); + try { + Long.parseLong(endTime); + map.put(END_TIME, endTime); } - catch (NumberFormatException e){ + catch (NumberFormatException e) { eventlogger.error("Invalid end time StatisticsServlet.doGet: " + e.getMessage(), e); } } if (req.getParameter("time") != null) { String time = req.getParameter("time"); - try{ + try { Long.parseLong(time); map.put(START_TIME, time); map.put(END_TIME, null); } - catch (NumberFormatException e){ + catch (NumberFormatException e) { eventlogger.error("Invalid end time StatisticsServlet.doGet: " + e.getMessage(), e); } } @@ -201,11 +201,6 @@ public class StatisticsServlet extends BaseServlet { } - private boolean validateDateInput(String date){ - - return true; - } - /** * rsToJson - Converting RS to JSON object. * @@ -310,13 +305,18 @@ public class StatisticsServlet extends BaseServlet { * * @param map as key value pare of all user input fields */ - private String queryGeneretor(Map map) throws ParseException { + private PreparedStatement queryGeneretor(Map map) throws ParseException, SQLException { String sql; String feedids = null; String startTime = null; String endTime = null; + long compareTime = 0; + long startInMillis = 0; + long endInMillis = 0; String subid = " "; + String compareType = null; + PreparedStatement ps = null; if (map.get(FEEDIDS) != null) { feedids = map.get(FEEDIDS); @@ -331,49 +331,56 @@ public class StatisticsServlet extends BaseServlet { subid = map.get(SUBID); } - eventlogger.info("Generating sql query to get Statistics resultset. "); - if (endTime == null && startTime == null) { - - sql = SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB - + SQL_SELECT_SUM - + feedids + SQL_PUBLISH_LENGTH - + SQL_SUBSCRIBER_URL + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS - + feedids + ") " + subid - + SQL_STATUS_204 + SQL_GROUP_SUB_ID; - - return sql; + sql = SQL_SELECT_NAME + "?" + SQL_FEED_ID + SQL_SELECT_COUNT + "?" + SQL_TYPE_PUB + SQL_SELECT_SUM + + "?" + SQL_PUBLISH_LENGTH + SQL_SUBSCRIBER_URL + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + + SQL_JOIN_RECORDS + "?" + ") " + "?" + SQL_STATUS_204 + + SQL_GROUP_SUB_ID; + compareType = "default"; } else if (startTime != null && endTime == null) { - long inputTimeInMilli = 60000 * Long.parseLong(startTime); Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT")); long currentTimeInMilli = cal.getTimeInMillis(); - long compareTime = currentTimeInMilli - inputTimeInMilli; - - sql = SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB - + SQL_SELECT_SUM - + feedids + SQL_PUBLISH_LENGTH - + SQL_SUBSCRIBER_URL + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS - + feedids + ") " + subid - + SQL_STATUS_204 + " and e.event_time>=" + compareTime + SQL_GROUP_SUB_ID; - return sql; - + compareTime = currentTimeInMilli - inputTimeInMilli; + sql = SQL_SELECT_NAME + "?" + SQL_FEED_ID + SQL_SELECT_COUNT + "?" + SQL_TYPE_PUB + SQL_SELECT_SUM + + "?" + SQL_PUBLISH_LENGTH + SQL_SUBSCRIBER_URL + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + + SQL_JOIN_RECORDS + "?" + ") " + "?" + SQL_STATUS_204 + + " and e.event_time>=" + "?" + SQL_GROUP_SUB_ID; + compareType = "start"; } else { SimpleDateFormat inFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss"); Date startDate = inFormat.parse(startTime); Date endDate = inFormat.parse(endTime); - - long startInMillis = startDate.getTime(); - long endInMillis = endDate.getTime(); - - sql = SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB - + SQL_SELECT_SUM - + feedids + SQL_PUBLISH_LENGTH + SQL_SUBSCRIBER_URL - + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS + feedids + ")" + subid + SQL_STATUS_204 - +" and e.event_time between " + startInMillis + " and " + endInMillis + SQL_GROUP_SUB_ID; - - return sql; + startInMillis = startDate.getTime(); + endInMillis = endDate.getTime(); + sql = SQL_SELECT_NAME + "?" + SQL_FEED_ID + SQL_SELECT_COUNT + "?" + SQL_TYPE_PUB + SQL_SELECT_SUM + + "?" + SQL_PUBLISH_LENGTH + SQL_SUBSCRIBER_URL + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + + SQL_JOIN_RECORDS + "?" + ") " + "?" + SQL_STATUS_204 + + " and e.event_time between " + "?" + " and " + "?" + SQL_GROUP_SUB_ID; + compareType = "startAndEnd"; + } + try (Connection conn = ProvDbUtils.getInstance().getConnection()) { + eventlogger.debug("SQL Query for Statistics resultset. " + sql); + intlogger.debug(sql); + ps = conn.prepareStatement(sql); + ps.setString(1, feedids); + ps.setString(2, feedids); + ps.setString(3, feedids); + ps.setString(4, feedids); + ps.setString(5, subid); + if (compareType.equals("start")) { + ps.setLong(6, compareTime); + } + if (compareType.equals("startAndEnd")) { + ps.setLong(6, startInMillis); + ps.setLong(7, endInMillis); + } + } finally { + if (ps != null) { + ps.close(); + } } + return ps; } @@ -459,7 +466,7 @@ public class StatisticsServlet extends BaseServlet { return map; } map.put("statusSQL", sql); - map.put("resultSQL", sql.replaceAll("STATUS", "RESULT")); + map.put("resultSQL", sql.replace("STATUS", "RESULT")); } str = req.getParameter("expiryReason"); @@ -531,29 +538,14 @@ public class StatisticsServlet extends BaseServlet { private void getRecordsForSQL(Map map, String outputType, ServletOutputStream out, HttpServletResponse resp) { + eventlogger.info("Generating sql query to get Statistics resultset. "); try { - String filterQuery = this.queryGeneretor(map); - eventlogger.debug("SQL Query for Statistics resultset. " + filterQuery); - intlogger.debug(filterQuery); + PreparedStatement ps = this.queryGeneretor(map); long start = System.currentTimeMillis(); - try (Connection conn = ProvDbUtils.getInstance().getConnection(); - PreparedStatement ps = conn.prepareStatement(filterQuery); - ResultSet rs = ps.executeQuery()) { - if (CSV_OUTPUT_TYPE.equals(outputType)) { - resp.setContentType("application/octet-stream"); - DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yyyy HH:mm:ss"); - resp.setHeader("Content-Disposition", - "attachment; filename=\"result:" + LocalDateTime.now().format(formatter) + ".csv\""); - eventlogger.info("Generating CSV file from Statistics resultset"); - rsToCSV(rs, out); - } else { - eventlogger.info("Generating JSON for Statistics resultset"); - this.rsToJson(rs, out); - } - } catch (SQLException e) { - eventlogger.error("SQLException:" + e); - } + executeQuery(outputType, out, resp, ps); intlogger.debug("Time: " + (System.currentTimeMillis() - start) + " ms"); + } catch (SQLException e) { + eventlogger.error("SQLException:" + e); } catch (IOException e) { eventlogger.error("IOException - Generating JSON/CSV:" + e); } catch (JSONException e) { @@ -562,5 +554,24 @@ public class StatisticsServlet extends BaseServlet { eventlogger.error("ParseException - executing SQL query:" + e); } } + + private void executeQuery(String outputType, ServletOutputStream out, HttpServletResponse resp, + PreparedStatement ps) throws IOException { + try (ResultSet rs = ps.executeQuery()) { + if (CSV_OUTPUT_TYPE.equals(outputType)) { + resp.setContentType("application/octet-stream"); + DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yyyy HH:mm:ss"); + resp.setHeader("Content-Disposition", + "attachment; filename=\"result:" + LocalDateTime.now().format(formatter) + ".csv\""); + eventlogger.info("Generating CSV file from Statistics resultset"); + rsToCSV(rs, out); + } else { + eventlogger.info("Generating JSON for Statistics resultset"); + this.rsToJson(rs, out); + } + } catch (SQLException e) { + eventlogger.error("SQLException:" + e); + } + } } diff --git a/datarouter-prov/src/test/java/org/onap/dmaap/datarouter/provisioning/StatisticsServletTest.java b/datarouter-prov/src/test/java/org/onap/dmaap/datarouter/provisioning/StatisticsServletTest.java index 1fe8d9b1..b6686b0e 100755 --- a/datarouter-prov/src/test/java/org/onap/dmaap/datarouter/provisioning/StatisticsServletTest.java +++ b/datarouter-prov/src/test/java/org/onap/dmaap/datarouter/provisioning/StatisticsServletTest.java @@ -119,7 +119,7 @@ public class StatisticsServletTest { ServletOutputStream outStream = mock(ServletOutputStream.class); when(response.getOutputStream()).thenReturn(outStream); statisticsServlet.doGet(request, response); - verify(response).setStatus(eq(HttpServletResponse.SC_OK)); + verify(response).setStatus(HttpServletResponse.SC_OK); } @Test @@ -130,7 +130,7 @@ public class StatisticsServletTest { ServletOutputStream outStream = mock(ServletOutputStream.class); when(response.getOutputStream()).thenReturn(outStream); statisticsServlet.doGet(request, response); - verify(response).setStatus(eq(HttpServletResponse.SC_OK)); + verify(response).setStatus(HttpServletResponse.SC_OK); } private void buildRequestParameters() { -- 2.16.6