[DMAAP-DR] Fix sql injection bug

Signed-off-by: efiacor <fiachra.corcoran@est.tech>
Change-Id: Icccc65b3b90c553dea74d95bf247b08ae6f78506
Issue-ID: DMAAP-1623

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<String, String> map) throws ParseException {

+    private PreparedStatement queryGeneretor(Map<String, String> 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<String, String> 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() {