Parameterized queries

Convert all database queries to use java sql
parameterized queries to reduce risk of sql
injection attack.

Change-Id: I15876ce3a2f2e2dfbd6578f5141367deed75d097
Signed-off-by: Patrick Brady <patrick.brady@att.com>
Issue-ID: OJSI-25

5 files changed, 136 insertions, 42 deletions

diff --git a/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/DBServiceTest.java b/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/DBServiceTest.java
index 2ca39bc73..c71f56d11 100644
--- a/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/DBServiceTest.java
+++ b/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/DBServiceTest.java
@@ -2,7 +2,7 @@
  * ============LICENSE_START=======================================================
  * ONAP : APPC
  * ================================================================================
- * Copyright (C) 2017-2018 AT&T Intellectual Property. All rights reserved.
+ * Copyright (C) 2017-2019 AT&T Intellectual Property. All rights reserved.
  * ================================================================================
  * Copyright (C) 2017 Amdocs
  * =============================================================================
@@ -31,6 +31,7 @@ import org.junit.Rule;
 import org.junit.Test;
 import org.junit.rules.ExpectedException;
 import org.onap.appc.artifact.handler.utils.SdcArtifactHandlerConstants;
+import org.onap.ccsdk.sli.core.dblib.DbLibService;
 import org.onap.ccsdk.sli.core.sli.SvcLogicContext;
 import org.onap.ccsdk.sli.core.sli.SvcLogicException;
 import org.onap.ccsdk.sli.core.sli.SvcLogicResource.QueryStatus;
@@ -38,6 +39,12 @@ import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertNotNull;
 import static org.junit.Assert.assertTrue;
 
+import static org.mockito.Mockito.*;
+
+import java.util.ArrayList;
+
+import javax.sql.rowset.CachedRowSet;
+
 public class DBServiceTest {
 
     @Rule
@@ -142,7 +149,7 @@ public class DBServiceTest {
         SvcLogicContext ctx = new SvcLogicContext();
         ctx.setAttribute("test", "test");
         ctx.setAttribute("url", "");
-        String expectedKey ="update DEVICE_AUTHENTICATION set USER_NAME = '' , PORT_NUMBER = 0, URL = ''  where VNF_TYPE = $vnf-type  AND PROTOCOL = $device-protocol AND  ACTION = $action";
+        String expectedKey ="update DEVICE_AUTHENTICATION set USER_NAME = $user-name , PORT_NUMBER = $port-number , URL = $url  where VNF_TYPE = $vnf-type  AND PROTOCOL = $device-protocol AND  ACTION = $action";
         boolean isUpdate = true;
         dbService.processDeviceAuthentication(ctx, isUpdate);
         assertEquals(expectedKey,ctx.getAttribute("keys"));
@@ -161,11 +168,22 @@ public class DBServiceTest {
 
     @Test
     public void testProcessDeviceInterfaceProtocol() throws Exception {
-        MockDBService dbService = MockDBService.initialise();
+        DbLibService mockDbLibService = mock(DbLibService.class);
+        DBService dbService = new DBService(mockDbLibService);
         SvcLogicContext ctx = new SvcLogicContext();
-        ctx.setAttribute("test", "test");
+        ctx.setAttribute(SdcArtifactHandlerConstants.DEVICE_PROTOCOL, "testDeviceProtocol");
+        ctx.setAttribute(SdcArtifactHandlerConstants.VNF_TYPE, "testVnfType");
         boolean isUpdate = true;
+        String expectedStatement = "update DEVICE_INTERFACE_PROTOCOL set PROTOCOL = ?"
+                +" , DG_RPC = 'getDeviceRunningConfig'"
+                + " , MODULE = 'APPC' " + "where VNF_TYPE = ? ";
+        ArrayList<String> expectedArguments = new ArrayList<>();
+        expectedArguments.add("testDeviceProtocol");
+        expectedArguments.add("testVnfType");
+        when(mockDbLibService.writeData(any(), any(), any())).thenReturn(true);
         dbService.processDeviceInterfaceProtocol(ctx, isUpdate);
+        verify(mockDbLibService,times(1)).writeData(expectedStatement, expectedArguments, null);
+        
     }
 
     @Test
@@ -180,21 +198,56 @@ public class DBServiceTest {
 
     @Test
     public void testProcessSdcReferences() throws Exception {
-        MockDBService dbService = MockDBService.initialise();
-        SvcLogicContext ctx = new SvcLogicContext();
-        ctx.setAttribute("test", "test");
-        ctx.setAttribute(SdcArtifactHandlerConstants.FILE_CATEGORY, "testCategory");
-        boolean isUpdate = true;
-        dbService.processSdcReferences(ctx, isUpdate);
+        DbLibService mockDbLibService = mock(DbLibService.class);
+        DBService dbService = new DBService(mockDbLibService);
+        SvcLogicContext ctx = new SvcLogicContext();
+        ctx.setAttribute(SdcArtifactHandlerConstants.ARTIFACT_NAME, "testArtifactName");
+        ctx.setAttribute(SdcArtifactHandlerConstants.VNF_TYPE, "testVnfType");
+        ctx.setAttribute(SdcArtifactHandlerConstants.VNFC_TYPE, "testVnfcType");
+        ctx.setAttribute(SdcArtifactHandlerConstants.FILE_CATEGORY, "testFileCategory");
+        ctx.setAttribute(SdcArtifactHandlerConstants.ACTION, "testAction");
+        String expectedStatement = "update ASDC_REFERENCE set ARTIFACT_NAME = ? where VNFC_TYPE = ? "
+                + "and FILE_CATEGORY = ? and ACTION = ? and VNF_TYPE = ? AND ARTIFACT_NAME like ? ";
+        ArrayList<String> expectedArguments = new ArrayList<>();
+        expectedArguments.add("testArtifactName");
+        expectedArguments.add("testVnfcType");
+        expectedArguments.add("testFileCategory");
+        expectedArguments.add("testAction");
+        expectedArguments.add("testVnfType");
+        expectedArguments.add("%_testModelId.%");
+        when(mockDbLibService.writeData(any(), any(), any())).thenReturn(true);
+        CachedRowSet crs = mock(CachedRowSet.class);
+        when(crs.next()).thenReturn(false);
+        when(mockDbLibService.getData(any(), any(), any())).thenReturn(crs);
+        dbService.processSdcReferences(ctx, true, "testModelId");
+        verify(mockDbLibService,times(1)).writeData(expectedStatement, expectedArguments, null);
     }
 
     @Test
     public void testIsArtifactUpdateRequired() throws Exception {
-        MockDBService dbService = MockDBService.initialise();
-        SvcLogicContext ctx = new SvcLogicContext();
-        ctx.setAttribute("test", "test");
-        String db = "db";
-        dbService.isArtifactUpdateRequired(ctx, db);
+        DbLibService mockDbLibService = mock(DbLibService.class);
+        DBService dbService = new DBService(mockDbLibService);
+        SvcLogicContext ctx = new SvcLogicContext();
+        ctx.setAttribute(SdcArtifactHandlerConstants.DEVICE_PROTOCOL, "testDeviceProtocol");
+        ctx.setAttribute(SdcArtifactHandlerConstants.VNF_TYPE, "testVnfType");
+        ctx.setAttribute(SdcArtifactHandlerConstants.VNFC_TYPE, "testVnfcType");
+        ctx.setAttribute(SdcArtifactHandlerConstants.FILE_CATEGORY, "testFileCategory");
+        ctx.setAttribute(SdcArtifactHandlerConstants.ACTION, "testAction");
+        String db = SdcArtifactHandlerConstants.DB_SDC_REFERENCE;
+        String expectedStatement = "select COUNT(*) from ASDC_REFERENCE where VNF_TYPE = ? and VNFC_TYPE = ?"
+                + " and FILE_CATEGORY = ? and ACTION = ? AND ARTIFACT_NAME like ? ";
+        ArrayList<String> expectedArguments = new ArrayList<>();
+        expectedArguments.add("testVnfType");
+        expectedArguments.add("testVnfcType");
+        expectedArguments.add("testFileCategory");
+        expectedArguments.add("testAction");
+        expectedArguments.add("%_testModelId.%");
+        when(mockDbLibService.writeData(any(), any(), any())).thenReturn(true);
+        CachedRowSet crs = mock(CachedRowSet.class);
+        when(crs.next()).thenReturn(false);
+        when(mockDbLibService.getData(any(), any(), any())).thenReturn(crs);
+        dbService.isArtifactUpdateRequired(ctx, db, "testModelId");
+        verify(mockDbLibService,times(1)).getData(expectedStatement, expectedArguments, null);
     }
 
     @Test
@@ -216,12 +269,6 @@ public class DBServiceTest {
     }
 
     @Test
-    public void testInitialise() {
-        DBService dbService = DBService.initialise();
-        assertNotNull(dbService);
-    }
-
-    @Test
     public void testGetInternalVersionNumberException() throws SvcLogicException {
         MockDBService dbService = MockDBService.initialise(true);
         SvcLogicContext ctx = new SvcLogicContext();
@@ -362,9 +409,12 @@ public class DBServiceTest {
     @Test
     public void testcreateQueryListForTemplateIds() {
         MockDBService dbService = MockDBService.initialise(true);
-        String queryPart = dbService.createQueryListForTemplateIds("modelId");
-        String expected = " AND ARTIFACT_NAME like '%_modelId.%'";
-        assertEquals(expected, queryPart);
+        SvcLogicContext ctx = new SvcLogicContext();
+        String queryPart = dbService.createQueryListForTemplateIds("modelId", ctx);
+        String expectedQuery = " AND ARTIFACT_NAME like $model-id ";
+        String expectedAttribute = "%_modelId.%";
+        assertEquals(expectedQuery, queryPart);
+        assertEquals(expectedAttribute,ctx.getAttribute("model-id"));
     }
     
     @Test
diff --git a/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockDBService.java b/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockDBService.java
index 374f6b16e..0ea689b79 100644
--- a/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockDBService.java
+++ b/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockDBService.java
@@ -2,7 +2,7 @@
  * ============LICENSE_START=======================================================
  * ONAP : APPC
  * ================================================================================
- * Copyright (C) 2017-2018 AT&T Intellectual Property. All rights reserved.
+ * Copyright (C) 2017-2019 AT&T Intellectual Property. All rights reserved.
  * ================================================================================
  * Copyright (C) 2017 Amdocs
  * ================================================================================
@@ -28,8 +28,8 @@ package org.onap.appc.artifact.handler.dbservices;
 public class MockDBService extends DBService {
     private static MockDBService mockDgGeneralDBService = null;
     private static MockDBService mockDgGeneralDBServiceFailure = null;
-    private static MockSvcLogicResource serviceLogic = new MockSvcLogicResource();
-    private static MockSvcLogicResourceFailure serviceLogicFailure = new MockSvcLogicResourceFailure();
+    private static MockDbLibServiceQueries serviceLogic = new MockDbLibServiceQueries();
+    private static MockDbLibServiceQueriesFailure serviceLogicFailure = new MockDbLibServiceQueriesFailure();
 
 
     public MockDBService() {
@@ -39,15 +39,16 @@ public class MockDBService extends DBService {
         }
     }
 
-    public MockDBService(MockSvcLogicResource serviceLogic2) {
+    public MockDBService(MockDbLibServiceQueries serviceLogic2) {
         super(serviceLogic);
     }
 
-    public MockDBService(MockSvcLogicResourceFailure serviceLogic2) {
+    public MockDBService(MockDbLibServiceQueriesFailure serviceLogic2) {
         super(serviceLogicFailure);
     }
 
     public static MockDBService initialise() {
+        System.out.println("tesateas");
         if (mockDgGeneralDBService == null) {
             mockDgGeneralDBService = new MockDBService(serviceLogic);
         }
diff --git a/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockSvcLogicResource.java b/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockDbLibServiceQueries.java
index d516c4359..0d02d369d 100644
--- a/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockSvcLogicResource.java
+++ b/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockDbLibServiceQueries.java
@@ -2,7 +2,7 @@
  * ============LICENSE_START=======================================================
  * ONAP : APPC
  * ================================================================================
- * Copyright (C) 2017-2018 AT&T Intellectual Property. All rights reserved.
+ * Copyright (C) 2017-2019 AT&T Intellectual Property. All rights reserved.
  * ================================================================================
  * Copyright (C) 2017 Amdocs
  * =============================================================================
@@ -27,17 +27,33 @@
 
 package org.onap.appc.artifact.handler.dbservices;
 
+import java.util.ArrayList;
 import java.util.Map;
 
 import org.onap.ccsdk.sli.core.sli.SvcLogicContext;
 import org.onap.ccsdk.sli.core.sli.SvcLogicException;
+import org.onap.ccsdk.sli.core.sli.SvcLogicResource.QueryStatus;
 import org.onap.ccsdk.sli.adaptors.resource.sql.SqlResource;
 
-public class MockSvcLogicResource extends SqlResource {
+public class MockDbLibServiceQueries extends DbLibServiceQueries {
 
+    public MockDbLibServiceQueries() {
+        super(null,true);
+    }
     @Override
-    public QueryStatus query(String resource, boolean localOnly, String select, String key, String prefix,
-            String orderBy, SvcLogicContext ctx) throws SvcLogicException {
+    public QueryStatus query(String key, SvcLogicContext ctx) {
+        QueryStatus status = QueryStatus.SUCCESS;
+        ctx.setAttribute("keys",key);
+        ctx.setAttribute("id", "testId");
+        ctx.setAttribute("VNF_TYPE", "testvnf");
+        ctx.setAttribute("maximum", "1");
+        ctx.setAttribute("COUNT(*)", "1");
+        ctx.setAttribute("download-config-dg", "TestDG");
+        return status;
+    }
+    
+    @Override
+    public QueryStatus query(String key, SvcLogicContext ctx, ArrayList<String> arguments) {
         QueryStatus status = QueryStatus.SUCCESS;
         ctx.setAttribute("keys",key);
         ctx.setAttribute("id", "testId");
@@ -50,8 +66,13 @@ public class MockSvcLogicResource extends SqlResource {
 
 
     @Override
-    public QueryStatus save(String resource, boolean force, boolean localOnly, String key, Map<String, String> parms,
-            String prefix, SvcLogicContext ctx) throws SvcLogicException {
+    public QueryStatus save(String key, SvcLogicContext ctx) {
+        ctx.setAttribute("keys", key);
+        return QueryStatus.SUCCESS;
+    }
+    
+    @Override
+    public QueryStatus save(String key, SvcLogicContext ctx, ArrayList<String> arguments) {
         ctx.setAttribute("keys", key);
         return QueryStatus.SUCCESS;
     }
diff --git a/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockSvcLogicResourceFailure.java b/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockDbLibServiceQueriesFailure.java
index 221511416..2723ba6f1 100644
--- a/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockSvcLogicResourceFailure.java
+++ b/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockDbLibServiceQueriesFailure.java
@@ -21,17 +21,34 @@
 
 package org.onap.appc.artifact.handler.dbservices;
 
+import java.util.ArrayList;
 import java.util.Map;
 
 import org.onap.ccsdk.sli.core.sli.SvcLogicContext;
 import org.onap.ccsdk.sli.core.sli.SvcLogicException;
+import org.onap.ccsdk.sli.core.sli.SvcLogicResource.QueryStatus;
 import org.onap.ccsdk.sli.adaptors.resource.sql.SqlResource;
 
-public class MockSvcLogicResourceFailure extends SqlResource {
+public class MockDbLibServiceQueriesFailure extends DbLibServiceQueries {
+    
+    public MockDbLibServiceQueriesFailure() {
+        super(null,true);
+    }
 
     @Override
-    public QueryStatus query(String resource, boolean localOnly, String select, String key, String prefix,
-            String orderBy, SvcLogicContext ctx) throws SvcLogicException {
+    public QueryStatus query(String key, SvcLogicContext ctx) {
+        QueryStatus status = QueryStatus.FAILURE;
+        ctx.setAttribute("keys",key);
+        ctx.setAttribute("id", "testId");
+        ctx.setAttribute("VNF_TYPE", "testvnf");
+        ctx.setAttribute("maximum", "1");
+        ctx.setAttribute("COUNT(*)", "1");
+        ctx.setAttribute("download-config-dg", "TestDG");
+        return status;
+    }
+    
+    @Override
+    public QueryStatus query(String key, SvcLogicContext ctx, ArrayList<String> arguments) {
         QueryStatus status = QueryStatus.FAILURE;
         ctx.setAttribute("keys",key);
         ctx.setAttribute("id", "testId");
@@ -43,8 +60,13 @@ public class MockSvcLogicResourceFailure extends SqlResource {
     }
 
     @Override
-    public QueryStatus save(String resource, boolean force, boolean localOnly, String key, Map<String, String> parms,
-            String prefix, SvcLogicContext ctx) throws SvcLogicException {
+    public QueryStatus save(String key, SvcLogicContext ctx) {
+        ctx.setAttribute("keys", key);
+        return QueryStatus.FAILURE;
+    }
+    
+    @Override
+    public QueryStatus save(String key, SvcLogicContext ctx, ArrayList<String> arguments) {
         ctx.setAttribute("keys", key);
         return QueryStatus.FAILURE;
     }
diff --git a/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/TestDBServiceExceptions.java b/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/TestDBServiceExceptions.java
index 8067439e1..02d5553e8 100644
--- a/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/TestDBServiceExceptions.java
+++ b/appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/TestDBServiceExceptions.java
@@ -39,14 +39,14 @@ public class TestDBServiceExceptions {
 
     private MockDBService dbService;
 
-    private MockSvcLogicResource mockSVCLogicResource;
+    private MockDbLibServiceQueries mockSVCLogicResource;
 
     private SvcLogicContext ctx ;
 
     @Before
     public void setup(){
            dbService =  MockDBService.initialise();
-           mockSVCLogicResource = Mockito.spy(MockSvcLogicResource.class);
+           mockSVCLogicResource = Mockito.spy(MockDbLibServiceQueries.class);
            ctx = new SvcLogicContext();
         }