2 * ============LICENSE_START=======================================================
4 * ================================================================================
5 * Copyright (C) 2017 ONAP Intellectual Property. All rights
7 * ================================================================================
8 * Licensed under the Apache License, Version 2.0 (the "License");
9 * you may not use this file except in compliance with the License.
10 * You may obtain a copy of the License at
12 * http://www.apache.org/licenses/LICENSE-2.0
14 * Unless required by applicable law or agreed to in writing, software
15 * distributed under the License is distributed on an "AS IS" BASIS,
16 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 * See the License for the specific language governing permissions and
18 * limitations under the License.
19 * ============LICENSE_END=========================================================
22 package org.openecomp.sdnc.sli.resource.sql;
24 import java.sql.Connection;
25 import java.sql.PreparedStatement;
26 import java.sql.ResultSet;
27 import java.sql.ResultSetMetaData;
28 import java.sql.SQLException;
29 import java.util.ArrayList;
31 import java.util.Map.Entry;
33 import javax.sql.rowset.CachedRowSet;
35 import org.apache.commons.lang3.StringUtils;
36 import org.onap.ccsdk.sli.core.dblib.DBResourceManager;
37 import org.onap.ccsdk.sli.core.dblib.DbLibService;
38 import org.onap.ccsdk.sli.core.sli.SvcLogicContext;
39 import org.onap.ccsdk.sli.core.sli.SvcLogicException;
40 import org.onap.ccsdk.sli.core.sli.SvcLogicJavaPlugin;
41 import org.onap.ccsdk.sli.core.sli.SvcLogicResource;
42 import org.onap.ccsdk.sli.core.sli.SvcLogicResource.QueryStatus;
43 import org.osgi.framework.Bundle;
44 import org.osgi.framework.BundleContext;
45 import org.osgi.framework.FrameworkUtil;
46 import org.osgi.framework.ServiceReference;
47 import org.slf4j.Logger;
48 import org.slf4j.LoggerFactory;
50 public class SqlResource implements SvcLogicResource, SvcLogicJavaPlugin {
52 private static final Logger LOG = LoggerFactory.getLogger(SqlResource.class);
54 private static final String DBLIB_SERVICE = "org.openecomp.sdnc.sli.resource.dblib.DBResourceManager";
56 private static String CRYPT_KEY = "";
58 public SqlResource() {
61 // For sql-resource, is-available is the same as exists
63 public QueryStatus isAvailable(String resource, String key, String prefix, SvcLogicContext ctx)
64 throws SvcLogicException {
66 return (exists(resource, key, prefix, ctx));
71 public QueryStatus exists(String resource, String key, String prefix, SvcLogicContext ctx)
72 throws SvcLogicException {
74 DbLibService dblibSvc = getDbLibService();
75 if (dblibSvc == null) {
76 return (QueryStatus.FAILURE);
79 String theStmt = resolveCtxVars(key, ctx);
82 CachedRowSet results = dblibSvc.getData(theStmt, null, null);
84 if (!results.next()) {
85 return (QueryStatus.NOT_FOUND);
88 int numRows = results.getInt(1);
91 return (QueryStatus.SUCCESS);
93 return (QueryStatus.NOT_FOUND);
95 } catch (Exception e) {
96 LOG.error("Caught SQL exception", e);
97 return (QueryStatus.FAILURE);
102 public QueryStatus query(String resource, boolean localOnly, String select, String key, String prefix,
103 String orderBy, SvcLogicContext ctx) throws SvcLogicException {
105 DbLibService dblibSvc = getDbLibService();
107 if (dblibSvc == null) {
108 return (QueryStatus.FAILURE);
111 String sqlQuery = resolveCtxVars(key, ctx);
115 CachedRowSet results = dblibSvc.getData(sqlQuery, null, null);
117 QueryStatus retval = QueryStatus.SUCCESS;
119 if (!results.next()) {
120 retval = QueryStatus.NOT_FOUND;
121 LOG.debug("No data found");
123 saveCachedRowSetToCtx(results, ctx, prefix, dblibSvc);
126 } catch (Exception e) {
127 LOG.error("Caught SQL exception", e);
128 return (QueryStatus.FAILURE);
132 public void saveCachedRowSetToCtx(CachedRowSet results, SvcLogicContext ctx, String prefix, DbLibService dblibSvc)
133 throws SQLException {
135 if ((prefix != null) && prefix.endsWith("[]")) {
137 String pfx = prefix.substring(0, prefix.length() - 2);
140 ResultSetMetaData rsMeta = results.getMetaData();
141 int numCols = rsMeta.getColumnCount();
143 for (int i = 0; i < numCols; i++) {
144 String colValue = null;
145 String tableName = rsMeta.getTableName(i + 1);
146 if (rsMeta.getColumnType(i + 1) == java.sql.Types.VARBINARY) {
147 colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1),
150 colValue = results.getString(i + 1);
152 LOG.debug("Setting " + pfx + "[" + idx + "]."
153 + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue);
154 ctx.setAttribute(pfx + "[" + idx + "]." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"),
158 } while (results.next());
159 LOG.debug("Setting " + pfx + "_length = " + idx);
160 ctx.setAttribute(pfx + "_length", "" + idx);
162 ResultSetMetaData rsMeta = results.getMetaData();
163 int numCols = rsMeta.getColumnCount();
165 for (int i = 0; i < numCols; i++) {
166 String colValue = null;
167 String tableName = rsMeta.getTableName(i + 1);
168 if ("VARBINARY".equalsIgnoreCase(rsMeta.getColumnTypeName(i + 1))) {
169 colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1),
172 colValue = results.getString(i + 1);
174 if (prefix != null) {
175 LOG.debug("Setting " + prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = "
177 ctx.setAttribute(prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue);
179 LOG.debug("Setting " + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue);
180 ctx.setAttribute(rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue);
189 public QueryStatus reserve(String resource, String select, String key, String prefix, SvcLogicContext ctx)
190 throws SvcLogicException {
191 return (QueryStatus.SUCCESS);
196 public QueryStatus release(String resource, String key, SvcLogicContext ctx) throws SvcLogicException {
197 return (QueryStatus.SUCCESS);
200 private QueryStatus executeSqlWrite(String key, SvcLogicContext ctx) throws SvcLogicException {
201 QueryStatus retval = QueryStatus.SUCCESS;
203 DbLibService dblibSvc = getDbLibService();
205 if (dblibSvc == null) {
206 return (QueryStatus.FAILURE);
209 String sqlStmt = resolveCtxVars(key, ctx);
211 LOG.debug("key = [" + key + "]; sqlStmt = [" + sqlStmt + "]");
214 if (!dblibSvc.writeData(sqlStmt, null, null)) {
215 retval = QueryStatus.FAILURE;
217 } catch (Exception e) {
218 LOG.error("Caught SQL exception", e);
219 retval = QueryStatus.FAILURE;
226 private String resolveCtxVars(String key, SvcLogicContext ctx) {
231 if (key.startsWith("'") && key.endsWith("'")) {
232 key = key.substring(1, key.length() - 1);
233 LOG.debug("Stripped outer single quotes - key is now [" + key + "]");
236 String[] keyTerms = key.split("\\s+");
238 StringBuffer sqlBuffer = new StringBuffer();
240 for (int i = 0; i < keyTerms.length; i++) {
241 sqlBuffer.append(resolveTerm(keyTerms[i], ctx));
242 sqlBuffer.append(" ");
245 return (sqlBuffer.toString());
248 private String resolveTerm(String term, SvcLogicContext ctx) {
253 LOG.trace("resolveTerm: term is " + term);
255 if (term.startsWith("$") && (ctx != null)) {
256 // Resolve any index variables.
257 term = resolveCtxVariable(term.substring(1), ctx);
258 // Escape single quote
260 term = term.replaceAll("'", "''");
262 return ("'" + term + "'");
269 private String resolveCtxVariable(String ctxVarName, SvcLogicContext ctx) {
271 if (ctxVarName.indexOf('[') == -1) {
272 // Ctx variable contains no arrays
273 if ("CRYPT_KEY".equals(ctxVarName)) {
274 // Handle crypt key as special case. If it's set as a context
275 // variable, use it. Otherwise, use
276 // configured crypt key.
277 String cryptKey = ctx.getAttribute(ctxVarName);
278 if ((cryptKey != null) && (cryptKey.length() > 0)) {
284 return (ctx.getAttribute(ctxVarName));
287 // Resolve any array references
288 StringBuffer sbuff = new StringBuffer();
289 String[] ctxVarParts = ctxVarName.split("\\[");
290 sbuff.append(ctxVarParts[0]);
291 for (int i = 1; i < ctxVarParts.length; i++) {
292 if (ctxVarParts[i].startsWith("$")) {
293 int endBracketLoc = ctxVarParts[i].indexOf("]");
294 if (endBracketLoc == -1) {
295 // Missing end bracket ... give up parsing
296 LOG.warn("Variable reference " + ctxVarName + " seems to be missing a ']'");
297 return (ctx.getAttribute(ctxVarName));
300 String idxVarName = ctxVarParts[i].substring(1, endBracketLoc);
301 String remainder = ctxVarParts[i].substring(endBracketLoc);
304 sbuff.append(ctx.getAttribute(idxVarName));
305 sbuff.append(remainder);
308 // Index is not a variable reference
310 sbuff.append(ctxVarParts[i]);
314 return (ctx.getAttribute(sbuff.toString()));
318 public QueryStatus save(String resource, boolean force, boolean localOnly, String key, Map<String, String> parms,
319 String prefix, SvcLogicContext ctx) throws SvcLogicException {
320 return (executeSqlWrite(key, ctx));
323 private DbLibService getDbLibService() {
324 // Try to get dblib as an OSGI service
325 DbLibService dblibSvc = null;
326 BundleContext bctx = null;
327 ServiceReference sref = null;
329 Bundle bundle = FrameworkUtil.getBundle(SqlResource.class);
331 if (bundle != null) {
332 bctx = bundle.getBundleContext();
336 sref = bctx.getServiceReference(DBLIB_SERVICE);
340 LOG.warn("Could not find service reference for DBLIB service (" + DBLIB_SERVICE + ")");
342 dblibSvc = (DbLibService) bctx.getService(sref);
343 if (dblibSvc == null) {
344 LOG.warn("Could not find service reference for DBLIB service (" + DBLIB_SERVICE + ")");
348 if (dblibSvc == null) {
349 // Must not be running in an OSGI container. See if you can load it
353 dblibSvc = DBResourceManager.create(System.getProperties());
354 } catch (Exception e) {
355 LOG.error("Caught exception trying to create dblib service", e);
358 if (dblibSvc == null) {
359 LOG.warn("Could not create new DBResourceManager");
367 public QueryStatus notify(String resource, String action, String key, SvcLogicContext ctx)
368 throws SvcLogicException {
369 if (LOG.isDebugEnabled()) {
370 LOG.debug("SqlResource.notify called with resource=" + resource + ", action=" + action);
372 return QueryStatus.SUCCESS;
376 public QueryStatus delete(String resource, String key, SvcLogicContext ctx) throws SvcLogicException {
377 return (executeSqlWrite(key, ctx));
380 public QueryStatus update(String resource, String key, Map<String, String> parms, String prefix,
381 SvcLogicContext ctx) throws SvcLogicException {
382 return (executeSqlWrite(key, ctx));
385 private String decryptColumn(String tableName, String colName, byte[] colValue, DbLibService dblibSvc) {
386 String strValue = new String(colValue);
388 if (StringUtils.isAsciiPrintable(strValue)) {
390 // If printable, not encrypted
393 PreparedStatement stmt = null;
394 Connection conn = null;
395 ResultSet results = null;
397 // CachedRowSet results =
398 // dblibSvc.getData("SELECT
399 // CAST(AES_DECRYPT('"+strValue+"','"+CRYPT_KEY+"') AS CHAR(50))
402 conn = ((DBResourceManager) dblibSvc).getConnection();
404 stmt = conn.prepareStatement("SELECT CAST(AES_DECRYPT(?, ?) AS CHAR(50)) FROM DUAL");
406 stmt.setBytes(1, colValue);
407 stmt.setString(2, getCryptKey());
409 results = stmt.executeQuery();
411 if ((results != null) && results.next()) {
412 strValue = results.getString(1);
413 LOG.debug("Decrypted value is " + strValue);
415 LOG.warn("Cannot decrypt " + tableName + "." + colName);
417 } catch (Exception e) {
418 LOG.error("Caught exception trying to decrypt " + tableName + "." + colName, e);
421 if (results != null) {
425 } catch (Exception exc) {
434 } catch (Exception exc) {
443 } catch (Exception exc) {
452 public static String getCryptKey() {
456 public static String setCryptKey(String key) {
461 public String parameterizedQuery(Map<String, String> parameters, SvcLogicContext ctx) throws SvcLogicException {
462 DbLibService dblibSvc = getDbLibService();
463 String prefix = parameters.get("prefix");
464 String query = parameters.get("query");
466 ArrayList<String> arguments = new ArrayList<String>();
467 for (Entry<String, String> a : parameters.entrySet()) {
468 if (a.getKey().startsWith("param")) {
469 arguments.add(a.getValue());
474 if (dblibSvc == null) {
475 return mapQueryStatus(QueryStatus.FAILURE);
477 if (query.contains("count") || query.contains("COUNT")) {
478 CachedRowSet results = dblibSvc.getData(query, arguments, null);
480 if (!results.next()) {
481 return mapQueryStatus(QueryStatus.FAILURE);
484 int numRows = results.getInt(1);
485 ctx.setAttribute(prefix + ".count", String.valueOf(numRows));
491 } else if (query.startsWith("select") || query.startsWith("SELECT")) {
492 CachedRowSet results = dblibSvc.getData(query, arguments, null);
493 if (!results.next()) {
494 return mapQueryStatus(QueryStatus.NOT_FOUND);
496 saveCachedRowSetToCtx(results, ctx, prefix, dblibSvc);
499 if (!dblibSvc.writeData(query, arguments, null)) {
500 return mapQueryStatus(QueryStatus.FAILURE);
503 return mapQueryStatus(QueryStatus.SUCCESS);
504 } catch (SQLException e) {
505 LOG.error("Caught SQL exception", e);
506 return mapQueryStatus(QueryStatus.FAILURE);
510 protected String mapQueryStatus(QueryStatus status) {
511 String str = status.toString();
512 str = str.toLowerCase();
513 str = str.replaceAll("_", "-");