From 066fc4828dd8e0dd9e6c0e9cc7e7dd705f02f7c1 Mon Sep 17 00:00:00 2001 From: Patrick Brady Date: Wed, 19 Jun 2019 12:35:08 -0700 Subject: [PATCH] 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 Issue-ID: OJSI-25 --- .../dbservices/CtxParameterizedResolver.java | 229 +++++++++++++++++ .../artifact/handler/dbservices/DBService.java | 273 ++++++++++++--------- .../handler/dbservices/DbLibServiceQueries.java | 154 ++++++++++++ .../artifact/handler/dbservices/DBServiceTest.java | 98 ++++++-- .../artifact/handler/dbservices/MockDBService.java | 11 +- ...cResource.java => MockDbLibServiceQueries.java} | 33 ++- ...re.java => MockDbLibServiceQueriesFailure.java} | 32 ++- .../dbservices/TestDBServiceExceptions.java | 4 +- 8 files changed, 671 insertions(+), 163 deletions(-) create mode 100644 appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/CtxParameterizedResolver.java create mode 100644 appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/DbLibServiceQueries.java rename appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/{MockSvcLogicResource.java => MockDbLibServiceQueries.java} (65%) rename appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/{MockSvcLogicResourceFailure.java => MockDbLibServiceQueriesFailure.java} (61%) diff --git a/appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/CtxParameterizedResolver.java b/appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/CtxParameterizedResolver.java new file mode 100644 index 000000000..d44ba066b --- /dev/null +++ b/appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/CtxParameterizedResolver.java @@ -0,0 +1,229 @@ +/*- + * ============LICENSE_START======================================================= + * ONAP : APPC + * ================================================================================ + * Copyright (C) 2019 AT&T Intellectual Property. All rights reserved. + * ================================================================================ + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * + * ============LICENSE_END========================================================= + */ + + +package org.onap.appc.artifact.handler.dbservices; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.util.ArrayList; + +import javax.sql.rowset.CachedRowSet; + +import org.apache.commons.lang3.StringUtils; +import org.onap.ccsdk.sli.core.dblib.DbLibService; +import org.onap.ccsdk.sli.core.sli.SvcLogicContext; + +import com.att.eelf.configuration.EELFLogger; +import com.att.eelf.configuration.EELFManager; + +public class CtxParameterizedResolver { + + private static String CRYPT_KEY = "QtfJMKggVk"; + private static final EELFLogger log = EELFManager.getInstance().getLogger(CtxParameterizedResolver.class); + + protected static String resolveCtxVars(String key, SvcLogicContext ctx, ArrayList arguments) { + if (key == null) { + return (null); + } + + if (key.startsWith("'") && key.endsWith("'")) { + key = key.substring(1, key.length() - 1); + log.debug("Stripped outer single quotes - key is now [" + key + "]"); + } + + String[] keyTerms = key.split("\\s+"); + + StringBuffer sqlBuffer = new StringBuffer(); + + for (int i = 0; i < keyTerms.length; i++) { + sqlBuffer.append(resolveTerm(keyTerms[i], ctx, arguments)); + sqlBuffer.append(" "); + } + + return (sqlBuffer.toString()); + } + + private static String resolveTerm(String term, SvcLogicContext ctx, ArrayList arguments) { + if (term == null) { + return (null); + } + + log.trace("resolveTerm: term is " + term); + + if (term.startsWith("$") && (ctx != null)) { + // Resolve any index variables. + term = resolveCtxVariable(term.substring(1), ctx); + // Escape single quote + if (term != null) { + term = term.replaceAll("'", "''"); + } + arguments.add(term); + return "?"; + } else { + return (term); + } + + } + + private static String resolveCtxVariable(String ctxVarName, SvcLogicContext ctx) { + + if (ctxVarName.indexOf('[') == -1) { + // Ctx variable contains no arrays + if ("CRYPT_KEY".equals(ctxVarName)) { + // Handle crypt key as special case. If it's set as a context + // variable, use it. Otherwise, use + // configured crypt key. + String cryptKey = ctx.getAttribute(ctxVarName); + if ((cryptKey != null) && (cryptKey.length() > 0)) { + return (cryptKey); + } else { + return (CRYPT_KEY); + } + } + return (ctx.getAttribute(ctxVarName)); + } + + // Resolve any array references + StringBuffer sbuff = new StringBuffer(); + String[] ctxVarParts = ctxVarName.split("\\["); + sbuff.append(ctxVarParts[0]); + for (int i = 1; i < ctxVarParts.length; i++) { + if (ctxVarParts[i].startsWith("$")) { + int endBracketLoc = ctxVarParts[i].indexOf("]"); + if (endBracketLoc == -1) { + // Missing end bracket ... give up parsing + log.warn("Variable reference " + ctxVarName + " seems to be missing a ']'"); + return (ctx.getAttribute(ctxVarName)); + } + + String idxVarName = ctxVarParts[i].substring(1, endBracketLoc); + String remainder = ctxVarParts[i].substring(endBracketLoc); + + sbuff.append("["); + sbuff.append(ctx.getAttribute(idxVarName)); + sbuff.append(remainder); + + } else { + // Index is not a variable reference + sbuff.append("["); + sbuff.append(ctxVarParts[i]); + } + } + + return (ctx.getAttribute(sbuff.toString())); + } + + protected static void saveCachedRowSetToCtx(CachedRowSet results, SvcLogicContext ctx, String prefix, DbLibService dblibSvc) + throws SQLException { + if (ctx != null) { + if ((prefix != null) && prefix.endsWith("[]")) { + // Return an array. + String pfx = prefix.substring(0, prefix.length() - 2); + int idx = 0; + do { + ResultSetMetaData rsMeta = results.getMetaData(); + int numCols = rsMeta.getColumnCount(); + + for (int i = 0; i < numCols; i++) { + String colValue = null; + String tableName = rsMeta.getTableName(i + 1); + if (rsMeta.getColumnType(i + 1) == java.sql.Types.VARBINARY) { + colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1), + dblibSvc); + } else { + colValue = results.getString(i + 1); + } + log.debug("Setting " + pfx + "[" + idx + "]." + + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue); + ctx.setAttribute(pfx + "[" + idx + "]." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), + colValue); + } + idx++; + } while (results.next()); + log.debug("Setting " + pfx + "_length = " + idx); + ctx.setAttribute(pfx + "_length", "" + idx); + } else { + ResultSetMetaData rsMeta = results.getMetaData(); + int numCols = rsMeta.getColumnCount(); + + for (int i = 0; i < numCols; i++) { + String colValue = null; + String tableName = rsMeta.getTableName(i + 1); + if ("VARBINARY".equalsIgnoreCase(rsMeta.getColumnTypeName(i + 1))) { + colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1), + dblibSvc); + } else { + colValue = results.getString(i + 1); + } + if (prefix != null) { + log.debug("Setting " + prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + + colValue); + ctx.setAttribute(prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue); + } else { + log.debug("Setting " + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue); + ctx.setAttribute(rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue); + } + } + } + } + } + + private static String decryptColumn(String tableName, String colName, byte[] colValue, DbLibService dblibSvc) { + String strValue = new String(colValue); + + if (StringUtils.isAsciiPrintable(strValue)) { + + // If printable, not encrypted + return (strValue); + } else { + ResultSet results = null; + try (Connection conn = dblibSvc.getConnection(); + PreparedStatement stmt = conn.prepareStatement("SELECT CAST(AES_DECRYPT(?, ?) AS CHAR(50)) FROM DUAL")) { + + stmt.setBytes(1, colValue); + stmt.setString(2, CRYPT_KEY); + results = stmt.executeQuery(); + + if ((results != null) && results.next()) { + strValue = results.getString(1); + log.debug("Decrypted value is " + strValue); + } else { + log.warn("Cannot decrypt " + tableName + "." + colName); + } + } catch (Exception e) { + log.error("Caught exception trying to decrypt " + tableName + "." + colName, e); + }finally { + if (results != null) { + try { + results.close(); + } catch (SQLException se) { + log.error("Caught exception trying to close ResultSet",se); + } + } + } + } + return (strValue); + } +} diff --git a/appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/DBService.java b/appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/DBService.java index 758869f24..471b0b019 100644 --- a/appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/DBService.java +++ b/appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/DBService.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,11 +27,15 @@ package org.onap.appc.artifact.handler.dbservices; import com.att.eelf.configuration.EELFLogger; import com.att.eelf.configuration.EELFManager; + +import java.util.ArrayList; + import org.apache.commons.configuration.ConfigurationException; import org.apache.commons.configuration.PropertiesConfiguration; import org.apache.commons.lang.StringUtils; import org.onap.appc.artifact.handler.utils.SdcArtifactHandlerConstants; import org.onap.ccsdk.sli.adaptors.resource.sql.SqlResource; +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; @@ -42,29 +46,25 @@ public class DBService { private static final EELFLogger log = EELFManager.getInstance().getLogger(DBService.class); private static final String FAILURE_PARAM = "FAILURE"; private static final String RECEIVED_AS = "Internal Version received as1 : "; - private static final String SET_DOWNLOAD_CONFIG_QUERY_STR = " set DOWNLOAD_CONFIG_DG = $"; - private static final String WHERE_VNF_TYPE_QUERY_STR = " where VNF_TYPE = $"; - private static final String ACTION_QUERY_STR = " , ACTION = $"; - private static final String VNF_TYPE_QUERY_STR = " , VNF_TYPE = $"; - private static final String INSERT_INTO_QUERY_STR = "insert into "; - private static final String AND_ACTION_QUERY_STR = " and ACTION = $"; - private static final String AND_FILE_CAT_QUERY_STR = " and FILE_CATEGORY = $"; - private static final String AND_VNF_TYPE_QUERY_STR = " and VNF_TYPE = $"; - private static final String UPDATE_QUERY_STR = "update "; - private static final String AND_VNFC_TYPE_QUERY_STR = " and VNFC_TYPE = $"; - - private SvcLogicResource serviceLogic; + + private DbLibServiceQueries dblib; private static DBService dgGeneralDBService = null; private DBService() { - if (serviceLogic == null) { - serviceLogic = new SqlResource(); + if (dblib == null) { + dblib = new DbLibServiceQueries(); } } - protected DBService(SqlResource svcLogic) { - if (serviceLogic == null) { - serviceLogic = svcLogic; + protected DBService(DbLibService dbLibService) { + if (dblib == null) { + dblib = new DbLibServiceQueries(dbLibService); + } + } + + protected DBService(DbLibServiceQueries dbLibServiceQueries) { + if (dblib == null) { + dblib = dbLibServiceQueries; } } @@ -79,11 +79,12 @@ public class DBService { throws SvcLogicException { QueryStatus status; String artifactInternalVersion = null; - if (serviceLogic != null && ctx != null) { - String key = "select max(internal_version) as maximum from ASDC_ARTIFACTS WHERE ARTIFACT_NAME = '" - + artifactName + "'"; + if (dblib != null && ctx != null) { + String key = "select max(internal_version) as maximum from ASDC_ARTIFACTS WHERE ARTIFACT_NAME = ?"; + ArrayList arguments = new ArrayList<>(); + arguments.add(artifactName); log.info("Getting internal Version :" + key); - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, ctx, arguments); if (status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error - getting internal Artifact Number"); } @@ -100,11 +101,11 @@ public class DBService { public String getArtifactID(SvcLogicContext ctx, String artifactName) throws SvcLogicException { QueryStatus status; String artifactID = null; - if (serviceLogic != null && ctx != null) { - String key = "select max(ASDC_ARTIFACTS_ID) as id from ASDC_ARTIFACTS WHERE ARTIFACT_NAME = '" - + artifactName + "'"; + if (dblib != null && ctx != null) { + String key = "select max(ASDC_ARTIFACTS_ID) as id from ASDC_ARTIFACTS WHERE ARTIFACT_NAME = ?"; + ArrayList arguments = new ArrayList<>(); log.info("Getting Artifact ID String :" + key); - status = serviceLogic.query("SQL", false, null, key, null, null, ctx); + status = dblib.query(key, ctx, arguments); if (status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error - getting Artifact ID from database"); } @@ -116,7 +117,7 @@ public class DBService { public QueryStatus saveArtifacts(SvcLogicContext ctx, int intversion) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "INSERT INTO ASDC_ARTIFACTS " + "SET SERVICE_UUID = $service-uuid , " + " DISTRIBUTION_ID = $distribution-id ," + " SERVICE_NAME = $service-name ," + " SERVICE_DESCRIPTION = $service-description ," + " RESOURCE_UUID = $resource-uuid ," @@ -124,10 +125,10 @@ public class DBService { + " RESOURCE_VERSION = $resource-version ," + " RESOURCE_TYPE = $resource-type ," + " ARTIFACT_UUID = $artifact-uuid ," + " ARTIFACT_TYPE = $artifact-type ," + " ARTIFACT_VERSION = $artifact-version ," - + " ARTIFACT_DESCRIPTION = $artifact-description ," + " INTERNAL_VERSION = " + intversion - + "," + " ARTIFACT_NAME = $artifact-name ," + " ARTIFACT_CONTENT = $artifact-contents "; - - status = serviceLogic.save("SQL", false, false, key, null, null, ctx); + + " ARTIFACT_DESCRIPTION = $artifact-description ," + " INTERNAL_VERSION = $internal-version" + + " ," + " ARTIFACT_NAME = $artifact-name ," + " ARTIFACT_CONTENT = $artifact-contents "; + ctx.setAttribute("internal-version", Integer.toString(intversion)); + status = dblib.save(key, ctx); if (status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error While processing storing Artifact: " + ctx.getAttribute(SdcArtifactHandlerConstants.ARTIFACT_NAME)); @@ -138,10 +139,10 @@ public class DBService { public QueryStatus logData(SvcLogicContext ctx, String prefix) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "INSERT INTO CONFIG_TRANSACTION_LOG " + " SET request_id = $request-id , " + " message_type = $log-message-type , " + " message = $log-message ;"; - status = serviceLogic.save("SQL", false, false, key, null, prefix, ctx); + status = dblib.save(key, ctx); if (status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error while logging data"); } @@ -165,39 +166,39 @@ public class DBService { if (isUpdate && context.getAttribute(SdcArtifactHandlerConstants.FILE_CATEGORY) .equals(SdcArtifactHandlerConstants.CAPABILITY)) { log.info("Updating capability artifact in ASDC_REFERENCE"); - key = UPDATE_QUERY_STR + SdcArtifactHandlerConstants.DB_SDC_REFERENCE + " set ARTIFACT_NAME = $" + key = "update " + SdcArtifactHandlerConstants.DB_SDC_REFERENCE + " set ARTIFACT_NAME = $" + SdcArtifactHandlerConstants.ARTIFACT_NAME + " where " + "FILE_CATEGORY = $" - + SdcArtifactHandlerConstants.FILE_CATEGORY + AND_VNF_TYPE_QUERY_STR + + SdcArtifactHandlerConstants.FILE_CATEGORY + " and VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE; } else if (isUpdate) { - key = UPDATE_QUERY_STR + SdcArtifactHandlerConstants.DB_SDC_REFERENCE + " set ARTIFACT_NAME = $" + key = "update " + SdcArtifactHandlerConstants.DB_SDC_REFERENCE + " set ARTIFACT_NAME = $" + SdcArtifactHandlerConstants.ARTIFACT_NAME + " where VNFC_TYPE = $" - + SdcArtifactHandlerConstants.VNFC_TYPE + AND_FILE_CAT_QUERY_STR - + SdcArtifactHandlerConstants.FILE_CATEGORY + AND_ACTION_QUERY_STR + SdcArtifactHandlerConstants.ACTION - + AND_VNF_TYPE_QUERY_STR + SdcArtifactHandlerConstants.VNF_TYPE; + + SdcArtifactHandlerConstants.VNFC_TYPE + " and FILE_CATEGORY = $" + + SdcArtifactHandlerConstants.FILE_CATEGORY + " and ACTION = $" + SdcArtifactHandlerConstants.ACTION + + " and VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE; if (StringUtils.isNotBlank(modelId)) { - key += createQueryListForTemplateIds(modelId); + key += createQueryListForTemplateIds(modelId, context); } } else { if (context.getAttribute(SdcArtifactHandlerConstants.FILE_CATEGORY) .equals(SdcArtifactHandlerConstants.CAPABILITY)) { log.info("Inserting new record for capability artifact in ASDC_REFERENCE"); - key = INSERT_INTO_QUERY_STR + SdcArtifactHandlerConstants.DB_SDC_REFERENCE + " set VNFC_TYPE = null " - + " , FILE_CATEGORY = $" + SdcArtifactHandlerConstants.FILE_CATEGORY + VNF_TYPE_QUERY_STR + key = "insert into " + SdcArtifactHandlerConstants.DB_SDC_REFERENCE + " set VNFC_TYPE = null " + + " , FILE_CATEGORY = $" + SdcArtifactHandlerConstants.FILE_CATEGORY + " , VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE + " , ACTION = null " + " , ARTIFACT_TYPE = null " + " , ARTIFACT_NAME = $" + SdcArtifactHandlerConstants.ARTIFACT_NAME; } else { - key = INSERT_INTO_QUERY_STR + SdcArtifactHandlerConstants.DB_SDC_REFERENCE + " set VNFC_TYPE = $" + key = "insert into " + SdcArtifactHandlerConstants.DB_SDC_REFERENCE + " set VNFC_TYPE = $" + SdcArtifactHandlerConstants.VNFC_TYPE + " , FILE_CATEGORY = $" - + SdcArtifactHandlerConstants.FILE_CATEGORY + VNF_TYPE_QUERY_STR - + SdcArtifactHandlerConstants.VNF_TYPE + ACTION_QUERY_STR + SdcArtifactHandlerConstants.ACTION + + SdcArtifactHandlerConstants.FILE_CATEGORY + " , VNF_TYPE = $" + + SdcArtifactHandlerConstants.VNF_TYPE + " , ACTION = $" + SdcArtifactHandlerConstants.ACTION + " , ARTIFACT_TYPE = $" + SdcArtifactHandlerConstants.ARTIFACT_TYPE + " , ARTIFACT_NAME = $" + SdcArtifactHandlerConstants.ARTIFACT_NAME; } } - if (serviceLogic != null) { + if (dblib != null) { log.info("Insert Key: " + key); - status = serviceLogic.save("SQL", false, false, key, null, null, context); + status = dblib.save(key, context); if (status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error While processing sdc_reference table "); } @@ -223,7 +224,7 @@ public class DBService { //if templates are present - there might be multiple records, so validate if( db.equals(SdcArtifactHandlerConstants.DB_SDC_REFERENCE) && StringUtils.isNotBlank(modelId)) { log.info("ModelId is sent!!"); - String queryPart = createQueryListForTemplateIds(modelId); + String queryPart = createQueryListForTemplateIds(modelId, context); log.info("Querypart is = " + queryPart); if (isUpdateRequiredForTemplates(queryPart, context, db)) { log.info("Update is Required!!"); @@ -236,12 +237,12 @@ public class DBService { String whereClause; QueryStatus status; - whereClause = WHERE_VNF_TYPE_QUERY_STR + SdcArtifactHandlerConstants.VNF_TYPE; + whereClause = " where VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE; whereClause = resolveWhereClause(context, db, whereClause); if (validate(db)) { String key = "select COUNT(*) from " + db + whereClause; log.info("SELECT String : " + key); - status = serviceLogic.query("SQL", false, null, key, null, null, context); + status = dblib.query(key, context); checkForFailure(db, status); String count = context.getAttribute("COUNT(*)"); log.info("Number of row Returned : " + count + ": " + status + ":"); @@ -260,7 +261,7 @@ public class DBService { } private boolean validate(String db) { - return db != null && serviceLogic != null; + return db != null && dblib != null; } private boolean keyExists(PropertiesConfiguration conf, String property) { @@ -289,10 +290,10 @@ public class DBService { private String resolveWhereClause(SvcLogicContext context, String db, String whereClause) { if (db != null) { if (hasValidAttributes(context, db)) { - return whereClause + AND_FILE_CAT_QUERY_STR + SdcArtifactHandlerConstants.FILE_CATEGORY; + return whereClause + " and FILE_CATEGORY = $" + SdcArtifactHandlerConstants.FILE_CATEGORY; } else if (db.equals(SdcArtifactHandlerConstants.DB_SDC_REFERENCE)) { - return whereClause + AND_VNFC_TYPE_QUERY_STR + SdcArtifactHandlerConstants.VNFC_TYPE - + AND_FILE_CAT_QUERY_STR + SdcArtifactHandlerConstants.FILE_CATEGORY + AND_ACTION_QUERY_STR + return whereClause + " and VNFC_TYPE = $" + SdcArtifactHandlerConstants.VNFC_TYPE + + " and FILE_CATEGORY = $" + SdcArtifactHandlerConstants.FILE_CATEGORY + " and ACTION = $" + SdcArtifactHandlerConstants.ACTION; } else if (db.equals(SdcArtifactHandlerConstants.DB_DOWNLOAD_DG_REFERENCE)) { return " where PROTOCOL = $" + SdcArtifactHandlerConstants.DEVICE_PROTOCOL; @@ -303,10 +304,10 @@ public class DBService { return whereClause + " AND PROTOCOL = $" + SdcArtifactHandlerConstants.DEVICE_PROTOCOL + " AND ACTION = $" + SdcArtifactHandlerConstants.ACTION; } else if (db.equals(SdcArtifactHandlerConstants.DB_CONFIG_ACTION_DG)) { - return whereClause + AND_ACTION_QUERY_STR + SdcArtifactHandlerConstants.ACTION; + return whereClause + " and ACTION = $" + SdcArtifactHandlerConstants.ACTION; } else if (db.equals(SdcArtifactHandlerConstants.DB_VNFC_REFERENCE)) { - return whereClause + AND_ACTION_QUERY_STR + SdcArtifactHandlerConstants.ACTION - + AND_VNFC_TYPE_QUERY_STR + SdcArtifactHandlerConstants.VNFC_TYPE + " and VNFC_INSTANCE = $" + return whereClause + " and ACTION = $" + SdcArtifactHandlerConstants.ACTION + + " and VNFC_TYPE = $" + SdcArtifactHandlerConstants.VNFC_TYPE + " and VNFC_INSTANCE = $" + SdcArtifactHandlerConstants.VNFC_INSTANCE + " and VM_INSTANCE = $" + SdcArtifactHandlerConstants.VM_INSTANCE; } @@ -326,20 +327,20 @@ public class DBService { String key; QueryStatus status; if (isUpdate) { - key = UPDATE_QUERY_STR + SdcArtifactHandlerConstants.DB_DEVICE_INTERFACE_PROTOCOL + " set PROTOCOL = $" + key = "update " + SdcArtifactHandlerConstants.DB_DEVICE_INTERFACE_PROTOCOL + " set PROTOCOL = $" + SdcArtifactHandlerConstants.DEVICE_PROTOCOL + " , DG_RPC = 'getDeviceRunningConfig' " - + " , MODULE = 'APPC' " + WHERE_VNF_TYPE_QUERY_STR + SdcArtifactHandlerConstants.VNF_TYPE; + + " , MODULE = 'APPC' " + " where VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE; } else { key = - INSERT_INTO_QUERY_STR + SdcArtifactHandlerConstants.DB_DEVICE_INTERFACE_PROTOCOL + " set VNF_TYPE = $" + "insert into " + SdcArtifactHandlerConstants.DB_DEVICE_INTERFACE_PROTOCOL + " set VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE + " , PROTOCOL = $" + SdcArtifactHandlerConstants.DEVICE_PROTOCOL + " , DG_RPC = 'getDeviceRunningConfig' " + " , MODULE = 'APPC' "; } - if (serviceLogic != null && context != null) { + if (dblib != null && context != null) { - status = serviceLogic.save("SQL", false, false, key, null, null, context); + status = dblib.save(key, context); if (status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error While processing DEVICE_INTERFACE_PROTOCOL table "); } @@ -359,9 +360,11 @@ public class DBService { if (StringUtils.isBlank(port)) { port = "0"; + context.setAttribute(SdcArtifactHandlerConstants.PORT_NUMBER, port); } if (StringUtils.isBlank(user)) { user = ""; + context.setAttribute(SdcArtifactHandlerConstants.USER_NAME, user); } if (isInvalidInput(SdcArtifactHandlerConstants.DEVICE_PROTOCOL, SdcArtifactHandlerConstants.ACTION, SdcArtifactHandlerConstants.VNF_TYPE)) { @@ -374,35 +377,40 @@ public class DBService { String key; QueryStatus status; if (isUpdate) { - key = UPDATE_QUERY_STR + SdcArtifactHandlerConstants.DB_DEVICE_AUTHENTICATION + " set USER_NAME = '" - + user + "' , PORT_NUMBER = " + port + ""; + key = "update " + SdcArtifactHandlerConstants.DB_DEVICE_AUTHENTICATION + " set USER_NAME = $" + + SdcArtifactHandlerConstants.USER_NAME + + " , PORT_NUMBER = $" + SdcArtifactHandlerConstants.PORT_NUMBER + ""; if (context.getAttributeKeySet().contains(SdcArtifactHandlerConstants.URL)) { String url = context.getAttribute(SdcArtifactHandlerConstants.URL); if (StringUtils.isBlank(url)) { url = "" ; + context.setAttribute(SdcArtifactHandlerConstants.URL, url); } - key = key + ", URL = '" + url + "' "; + key = key + " , URL = $" + SdcArtifactHandlerConstants.URL + " "; } - key = key + WHERE_VNF_TYPE_QUERY_STR + SdcArtifactHandlerConstants.VNF_TYPE + " AND PROTOCOL = $" + key = key + " where VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE + " AND PROTOCOL = $" + SdcArtifactHandlerConstants.DEVICE_PROTOCOL + " AND ACTION = $" + SdcArtifactHandlerConstants.ACTION; } else { - key = "insert into DEVICE_AUTHENTICATION set VNF_TYPE = '" + vnftype + "' , PROTOCOL = '" + protocol - + "' , " + "ACTION = '" + action + "' , USER_NAME = '" + user + "' , PORT_NUMBER = '" + port - + "'"; + key = "insert into DEVICE_AUTHENTICATION set VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE + + " , PROTOCOL = $" + SdcArtifactHandlerConstants.DEVICE_PROTOCOL + + " , " + "ACTION = $" + SdcArtifactHandlerConstants.ACTION + + " , USER_NAME = $" + SdcArtifactHandlerConstants.USER_NAME + + " , PORT_NUMBER = $" + SdcArtifactHandlerConstants.PORT_NUMBER; if (context.getAttributeKeySet().contains(SdcArtifactHandlerConstants.URL)) { String url = context.getAttribute(SdcArtifactHandlerConstants.URL); if (StringUtils.isBlank(url)) { url = ""; + context.setAttribute(SdcArtifactHandlerConstants.URL, url); } - key = key + ", URL = '" + url + "' "; + key = key + " , URL = $" + SdcArtifactHandlerConstants.URL + " "; } } log.info("Query forDevice authentication " + key); - if (serviceLogic != null && context != null) { + if (dblib != null && context != null) { - status = serviceLogic.save("SQL", false, false, key, null, null, context); + status = dblib.save(key, context); if (status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error While processing DEVICE_AUTHENTICATION table "); } @@ -439,19 +447,19 @@ public class DBService { QueryStatus status; if (isUpdate) { - key = UPDATE_QUERY_STR + SdcArtifactHandlerConstants.DB_VNFC_REFERENCE + " set VM_INSTANCE = " + vmInstance + key = "update " + SdcArtifactHandlerConstants.DB_VNFC_REFERENCE + " set VM_INSTANCE = " + vmInstance + " , VNFC_INSTANCE = " + vnfcInstance + " , VNFC_TYPE = $" + SdcArtifactHandlerConstants.VNFC_TYPE + " , VNFC_FUNCTION_CODE = $" + SdcArtifactHandlerConstants.VNFC_FUNCTION_CODE + " , GROUP_NOTATION_TYPE = $" + SdcArtifactHandlerConstants.GROUP_NOTATION_TYPE + " , GROUP_NOTATION_VALUE = $" + SdcArtifactHandlerConstants.GROUP_NOTATION_VALUE + " , IPADDRESS_V4_OAM_VIP = $" + SdcArtifactHandlerConstants.IPADDRESS_V4_OAM_VIP - + WHERE_VNF_TYPE_QUERY_STR + SdcArtifactHandlerConstants.VNF_TYPE + AND_ACTION_QUERY_STR - + SdcArtifactHandlerConstants.ACTION + AND_VNFC_TYPE_QUERY_STR + SdcArtifactHandlerConstants.VNFC_TYPE + + " where VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE + " and ACTION = $" + + SdcArtifactHandlerConstants.ACTION + " and VNFC_TYPE = $" + SdcArtifactHandlerConstants.VNFC_TYPE + " and VNFC_INSTANCE = $" + SdcArtifactHandlerConstants.VNFC_INSTANCE + " and VM_INSTANCE = $" + SdcArtifactHandlerConstants.VM_INSTANCE; } else { - key = INSERT_INTO_QUERY_STR + SdcArtifactHandlerConstants.DB_VNFC_REFERENCE + " set VNF_TYPE = $" - + SdcArtifactHandlerConstants.VNF_TYPE + ACTION_QUERY_STR + SdcArtifactHandlerConstants.ACTION + key = "insert into " + SdcArtifactHandlerConstants.DB_VNFC_REFERENCE + " set VNF_TYPE = $" + + SdcArtifactHandlerConstants.VNF_TYPE + " , ACTION = $" + SdcArtifactHandlerConstants.ACTION + " , VM_INSTANCE = $" + SdcArtifactHandlerConstants.VM_INSTANCE + " , VNFC_INSTANCE = $" + SdcArtifactHandlerConstants.VNFC_INSTANCE + " , VNFC_TYPE = $" + SdcArtifactHandlerConstants.VNFC_TYPE + " , VNFC_FUNCTION_CODE = $" @@ -462,8 +470,8 @@ public class DBService { + SdcArtifactHandlerConstants.GROUP_NOTATION_VALUE; } - if (serviceLogic != null) { - status = serviceLogic.save("SQL", false, false, key, null, null, context); + if (dblib != null) { + status = dblib.save(key, context); if (status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error While processing VNFC_REFERENCE table "); } @@ -479,18 +487,18 @@ public class DBService { if (isUpdate) { key = - UPDATE_QUERY_STR + SdcArtifactHandlerConstants.DB_DOWNLOAD_DG_REFERENCE + SET_DOWNLOAD_CONFIG_QUERY_STR + "update " + SdcArtifactHandlerConstants.DB_DOWNLOAD_DG_REFERENCE + " set DOWNLOAD_CONFIG_DG = $" + SdcArtifactHandlerConstants.DOWNLOAD_DG_REFERENCE + " where PROTOCOL = $" + SdcArtifactHandlerConstants.DEVICE_PROTOCOL; } else { - key = INSERT_INTO_QUERY_STR + SdcArtifactHandlerConstants.DB_DOWNLOAD_DG_REFERENCE - + SET_DOWNLOAD_CONFIG_QUERY_STR + key = "insert into " + SdcArtifactHandlerConstants.DB_DOWNLOAD_DG_REFERENCE + + " set DOWNLOAD_CONFIG_DG = $" + SdcArtifactHandlerConstants.DOWNLOAD_DG_REFERENCE + " , PROTOCOL = $" + SdcArtifactHandlerConstants.DEVICE_PROTOCOL; } - if (serviceLogic != null && context != null) { - status = serviceLogic.save("SQL", false, false, key, null, null, context); + if (dblib != null && context != null) { + status = dblib.save(key, context); } if ((status == null) || status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error While processing DOWNLOAD_DG_REFERENCE table "); @@ -506,19 +514,19 @@ public class DBService { if (context.getAttribute(SdcArtifactHandlerConstants.DOWNLOAD_DG_REFERENCE) != null && context.getAttribute(SdcArtifactHandlerConstants.DOWNLOAD_DG_REFERENCE).length() > 0) { if (isUpdate) { - key = UPDATE_QUERY_STR + SdcArtifactHandlerConstants.DB_CONFIG_ACTION_DG + SET_DOWNLOAD_CONFIG_QUERY_STR + key = "update " + SdcArtifactHandlerConstants.DB_CONFIG_ACTION_DG + " set DOWNLOAD_CONFIG_DG = $" + SdcArtifactHandlerConstants.DOWNLOAD_DG_REFERENCE + " where ACTION = $" - + SdcArtifactHandlerConstants.ACTION + AND_VNF_TYPE_QUERY_STR + + SdcArtifactHandlerConstants.ACTION + " and VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE; } else { - key = INSERT_INTO_QUERY_STR + SdcArtifactHandlerConstants.DB_CONFIG_ACTION_DG - + SET_DOWNLOAD_CONFIG_QUERY_STR - + SdcArtifactHandlerConstants.DOWNLOAD_DG_REFERENCE + ACTION_QUERY_STR - + SdcArtifactHandlerConstants.ACTION + VNF_TYPE_QUERY_STR + SdcArtifactHandlerConstants.VNF_TYPE; + key = "insert into " + SdcArtifactHandlerConstants.DB_CONFIG_ACTION_DG + + " set DOWNLOAD_CONFIG_DG = $" + + SdcArtifactHandlerConstants.DOWNLOAD_DG_REFERENCE + " , ACTION = $" + + SdcArtifactHandlerConstants.ACTION + " , VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE; } - if (serviceLogic != null) { - status = serviceLogic.save("SQL", false, false, key, null, null, context); + if (dblib != null) { + status = dblib.save(key, context); } if ((status == null) || status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error While processing Configure DG Action table "); @@ -535,12 +543,12 @@ public class DBService { String key; QueryStatus status; key = - "select VNF_TYPE, VNFC_TYPE, ACTION, FILE_CATEGORY, ARTIFACT_TYPE from ASDC_REFERENCE where ARTIFACT_NAME = " - + artifactName; - - if (serviceLogic != null) { + "select VNF_TYPE, VNFC_TYPE, ACTION, FILE_CATEGORY, ARTIFACT_TYPE from ASDC_REFERENCE where ARTIFACT_NAME = ?"; + ArrayList arguments = new ArrayList(); + arguments.add(artifactName); + if (dblib != null) { log.info(fn + "select Key: " + key); - status = serviceLogic.query("SQL", false, null, key, null, null, con); + status = dblib.query(key, con, arguments); if (status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error While processing is ArtifactUpdateRequiredforPD table "); } @@ -558,11 +566,14 @@ public class DBService { String key; QueryStatus status = null; - key = "update ASDC_ARTIFACTS " + " set ARTIFACT_CONTENT = '" + yangContents + "'" - + " where ASDC_ARTIFACTS_ID = " + artifactId; + key = "update ASDC_ARTIFACTS " + " set ARTIFACT_CONTENT = ?" + + " where ASDC_ARTIFACTS_ID = ?"; + ArrayList arguments = new ArrayList(); + arguments.add(yangContents); + arguments.add(artifactId); - if (serviceLogic != null && context != null) { - status = serviceLogic.save("SQL", false, false, key, null, null, context); + if (dblib != null && context != null) { + status = dblib.save(key, context, arguments); } if ((status == null) || status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error While processing Configure DG Action table "); @@ -579,11 +590,17 @@ public class DBService { QueryStatus status = null; key = "insert into PROTOCOL_REFERENCE (ACTION, VNF_TYPE, PROTOCOL, UPDATED_DATE, TEMPLATE, ACTION_LEVEL)" - + " values (" + "'" + action + "', '" + vnfType + "', '" + protocol + "', now(),'" + template + "', '" - + actionLevel + "')"; - - if (serviceLogic != null && context != null) { - status = serviceLogic.save("SQL", false, false, key, null, null, context); + + " values (" + "?, ?, ?, now(), ?, " + + "?)"; + ArrayList arguments = new ArrayList(); + arguments.add(action); + arguments.add(vnfType); + arguments.add(protocol); + arguments.add(template); + arguments.add(actionLevel); + + if (dblib != null && context != null) { + status = dblib.save(key, context, arguments); } if ((status == null) || status.toString().equals(FAILURE_PARAM)) { throw new SvcLogicException("Error While processing insertProtocolReference "); @@ -597,9 +614,13 @@ public class DBService { String fn = "DBService.isProtocolReferenceUpdateRequired"; log.info(fn + "Starting DB operation for isProtocolReferenceUpdateRequired"); - String key = "select COUNT(*) from PROTOCOL_REFERENCE where ACTION='" + action + "' and ACTION_LEVEL='" + actionLevel - + "' and VNF_TYPE='" + vnfType + "'"; - serviceLogic.query("SQL", false, null, key, null, null, localContext); + String key = "select COUNT(*) from PROTOCOL_REFERENCE where ACTION=? and ACTION_LEVEL=?" + + " and VNF_TYPE=?"; + ArrayList arguments = new ArrayList(); + arguments.add(action); + arguments.add(actionLevel); + arguments.add(vnfType); + dblib.query(key, localContext, arguments); String countStr = localContext.getAttribute("COUNT(*)"); int count = Integer.parseInt(countStr); @@ -614,10 +635,15 @@ public class DBService { String key; QueryStatus status; - key = "update PROTOCOL_REFERENCE set UPDATED_DATE=now(), template='" + template + "', protocol ='" + protocol - + "' where ACTION='" + action + "' and ACTION_LEVEL='" + actionLevel + "' and VNF_TYPE='" + vnfType - + "'"; - status = serviceLogic.save("SQL", false, false, key, null, null, context); + key = "update PROTOCOL_REFERENCE set UPDATED_DATE=now(), template=?, protocol =?" + + " where ACTION=? and ACTION_LEVEL=? and VNF_TYPE=?"; + ArrayList arguments = new ArrayList(); + arguments.add(template); + arguments.add(protocol); + arguments.add(action); + arguments.add(actionLevel); + arguments.add(vnfType); + status = dblib.save(key, context, arguments); if (status == QueryStatus.FAILURE) { log.info("updateProtocolReference:: Error updating protocol reference"); throw new SvcLogicException("Error - updating PROTOCOL_REFERENCE_TABLE in updateProtocolReference"); @@ -638,9 +664,11 @@ public class DBService { throw new ConfigurationException(fn + ":: Protocol is Blank!! Returning without querying DB"); } key = "select download_config_dg from " + SdcArtifactHandlerConstants.DB_DOWNLOAD_DG_REFERENCE - + " where protocol = '" + protocol + "'"; + + " where protocol = ?"; + ArrayList arguments = new ArrayList(); + arguments.add(protocol); SvcLogicContext localContext = new SvcLogicContext(); - status = serviceLogic.query("SQL", false, null, key, null, null, localContext); + status = dblib.query(key, localContext, arguments); if (status == QueryStatus.FAILURE) { log.info(fn + ":: Error retrieving download_config_dg"); throw new SvcLogicException("Error retrieving download_config_dg"); @@ -665,8 +693,8 @@ public class DBService { log.debug("vnfType: " + context.getAttribute(SdcArtifactHandlerConstants.VNF_TYPE)); QueryStatus status; log.info("cleanUpVnfcReferencesForVnf()::Query:" + key1); - if (serviceLogic != null) { - status = serviceLogic.save("SQL", false, false, key1, null, null, context); + if (dblib != null) { + status = dblib.save(key1, context); if (status.toString().equals(FAILURE_PARAM)) { log.debug("Error deleting from VNFC_REFERENCE table"); throw new SvcLogicException("Error While processing VNFC_REFERENCE table "); @@ -689,14 +717,14 @@ public class DBService { log.info(""); String whereClause; QueryStatus status; - whereClause = WHERE_VNF_TYPE_QUERY_STR + SdcArtifactHandlerConstants.VNF_TYPE ; + whereClause = " where VNF_TYPE = $" + SdcArtifactHandlerConstants.VNF_TYPE ; whereClause = resolveWhereClause(context, db, whereClause); whereClause += queryPart; if (validate(db)) { if (!db.equals(SdcArtifactHandlerConstants.DB_DEVICE_AUTHENTICATION)) { String key = "select COUNT(*) from " + db + whereClause; log.info("SELECT String : " + key); - status = serviceLogic.query("SQL", false, null, key, null, null, context); + status = dblib.query(key, context); checkForFailure(db, status); String count = context.getAttribute("COUNT(*)"); log.info("Number of row Returned : " + count + ": " + status + ":"); @@ -711,8 +739,11 @@ public class DBService { } } - public String createQueryListForTemplateIds(String modelId) { - String queryPart = " AND ARTIFACT_NAME like '%_" + modelId + ".%'"; + public String createQueryListForTemplateIds(String modelId, SvcLogicContext context) { + String parameter = modelId.replace("%", "!%").replace("_","!_").replace("[","![").replace("]", "!]").replace("!","!!"); + parameter = "%_" + parameter + ".%"; + context.setAttribute("model-id", parameter); + String queryPart = " AND ARTIFACT_NAME like $model-id "; return queryPart; } } diff --git a/appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/DbLibServiceQueries.java b/appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/DbLibServiceQueries.java new file mode 100644 index 000000000..ad4242ae0 --- /dev/null +++ b/appc-inbound/appc-artifact-handler/provider/src/main/java/org/onap/appc/artifact/handler/dbservices/DbLibServiceQueries.java @@ -0,0 +1,154 @@ +/*- + * ============LICENSE_START======================================================= + * ONAP : APPC + * ================================================================================ + * Copyright (C) 2019 AT&T Intellectual Property. All rights reserved. + * ================================================================================ + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * + * ============LICENSE_END========================================================= + */ + +package org.onap.appc.artifact.handler.dbservices; + +import java.sql.SQLException; +import java.util.ArrayList; + +import javax.sql.rowset.CachedRowSet; + +import org.onap.ccsdk.sli.core.dblib.DBResourceManager; +import org.onap.ccsdk.sli.core.dblib.DbLibService; +import org.onap.ccsdk.sli.core.sli.SvcLogicContext; +import org.onap.ccsdk.sli.core.sli.SvcLogicResource.QueryStatus; +import org.onap.ccsdk.sli.core.sli.provider.SvcLogicService; +import org.osgi.framework.Bundle; +import org.osgi.framework.BundleContext; +import org.osgi.framework.FrameworkUtil; +import org.osgi.framework.ServiceReference; + +import com.att.eelf.configuration.EELFLogger; +import com.att.eelf.configuration.EELFManager; + +public class DbLibServiceQueries { + + private static final String DBLIB_SERVICE = "org.onap.ccsdk.sli.core.dblib.DbLibService"; + private static final EELFLogger log = EELFManager.getInstance().getLogger(DbLibServiceQueries.class); + + DbLibService dbLibService; + + public DbLibServiceQueries() { + this.dbLibService = getDbLibService(); + if(this.dbLibService == null) { + throw new NullPointerException("DbLibService reference not found"); + } + } + + public DbLibServiceQueries(DbLibService dbLibService) { + this.dbLibService = dbLibService; + if(this.dbLibService == null) { + throw new NullPointerException("Provided DbLibService is null"); + } + } + + public DbLibServiceQueries(DbLibService dbLibService, boolean allowNull) { + this.dbLibService = dbLibService; + if(this.dbLibService == null && !allowNull) { + throw new NullPointerException("Provided DbLibService is null"); + } + } + + public QueryStatus query(String query, SvcLogicContext ctx) { + ArrayList arguments = new ArrayList<>(); + query = CtxParameterizedResolver.resolveCtxVars(query, ctx, arguments); + return query(query, ctx, arguments); + } + + public QueryStatus query(String query, SvcLogicContext ctx, ArrayList arguments) { + + CachedRowSet result = null; + try { + result = dbLibService.getData(query, arguments, null); + if (!result.next()) { + log.debug("No data found"); + return QueryStatus.NOT_FOUND; + } else { + CtxParameterizedResolver.saveCachedRowSetToCtx(result, ctx, null, dbLibService); + } + } catch (SQLException e) { + log.error("Exception in query()",e); + return QueryStatus.FAILURE; + } + return QueryStatus.SUCCESS; + + } + public QueryStatus save(String query, SvcLogicContext ctx) { + ArrayList arguments = new ArrayList<>(); + query = CtxParameterizedResolver.resolveCtxVars(query, ctx, arguments); + return save(query,ctx,arguments); + } + + public QueryStatus save(String query, SvcLogicContext ctx, ArrayList arguments) { + boolean success = false; + try { + success = dbLibService.writeData(query, arguments, null); + } catch (SQLException e) { + log.error("Exception in save()",e); + success = false; + } + if(success) { + return QueryStatus.SUCCESS; + } + return QueryStatus.FAILURE; + } + + private static DbLibService getDbLibService() { + + DbLibService dbLibService = null; + BundleContext bundleContext = null; + ServiceReference serviceRef = null; + + Bundle bundle = FrameworkUtil.getBundle(SvcLogicService.class); + + if (bundle != null) { + bundleContext = bundle.getBundleContext(); + } + + if (bundleContext != null) { + log.debug("Getting bundle Context"); + serviceRef = bundleContext.getServiceReference(DBLIB_SERVICE); + } + + if (serviceRef == null) { + log.warn("Could not find service reference for DBLib service"); + + } else { + dbLibService = (DbLibService)bundleContext.getService(serviceRef); + if (dbLibService == null) { + log.warn("DBLIB_SERVICE is null"); + } + } + if (dbLibService == null) { + try { + dbLibService = new DBResourceManager(System.getProperties()); + } catch (Exception e) { + log.error("Caught exception trying to create db service", e); + } + + if (dbLibService == null) { + log.warn("Could not create new DBResourceManager"); + } + } + return dbLibService; + } + +} 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 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 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 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 @@ -215,12 +268,6 @@ public class DBServiceTest { assertEquals("TestDG", dbService.getDownLoadDGReference(ctx)); } - @Test - public void testInitialise() { - DBService dbService = DBService.initialise(); - assertNotNull(dbService); - } - @Test public void testGetInternalVersionNumberException() throws SvcLogicException { MockDBService dbService = MockDBService.initialise(true); @@ -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 similarity index 65% rename from appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockSvcLogicResource.java rename to 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 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 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 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 similarity index 61% rename from appc-inbound/appc-artifact-handler/provider/src/test/java/org/onap/appc/artifact/handler/dbservices/MockSvcLogicResourceFailure.java rename to 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 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 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 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(); } -- 2.16.6