2 * ============LICENSE_START=======================================================
4 * ================================================================================
5 * Copyright (C) 2017 AT&T 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.onap.ccsdk.sli.adaptors.resource.sql;
25 import java.io.FileInputStream;
26 import java.sql.Connection;
27 import java.sql.PreparedStatement;
28 import java.sql.ResultSet;
29 import java.sql.ResultSetMetaData;
30 import java.sql.SQLException;
31 import java.util.ArrayList;
33 import java.util.Map.Entry;
34 import java.util.Properties;
36 import javax.sql.rowset.CachedRowSet;
38 import org.apache.commons.lang3.StringUtils;
39 import org.onap.ccsdk.sli.core.dblib.DBResourceManager;
40 import org.onap.ccsdk.sli.core.dblib.DbLibService;
41 import org.onap.ccsdk.sli.core.sli.SvcLogicContext;
42 import org.onap.ccsdk.sli.core.sli.SvcLogicException;
43 import org.onap.ccsdk.sli.core.sli.SvcLogicJavaPlugin;
44 import org.onap.ccsdk.sli.core.sli.SvcLogicResource;
45 import org.osgi.framework.Bundle;
46 import org.osgi.framework.BundleContext;
47 import org.osgi.framework.FrameworkUtil;
48 import org.osgi.framework.ServiceReference;
49 import org.slf4j.Logger;
50 import org.slf4j.LoggerFactory;
52 public class SqlResource implements SvcLogicResource, SvcLogicJavaPlugin {
54 private static final Logger LOG = LoggerFactory.getLogger(SqlResource.class);
56 private static final String DBLIB_SERVICE = "org.onap.ccsdk.sli.core.dblib.DbLibService";
58 private static String CRYPT_KEY = "QtfJMKggVk";
60 DbLibService dblibSvc = null;
62 public SqlResource() {
63 this(new SqlResourcePropertiesProviderImpl(), null);
66 public SqlResource(SqlResourcePropertiesProvider propProvider) {
67 this(propProvider, null);
70 public SqlResource(SqlResourcePropertiesProvider propProvider, DbLibService dblibSvc) {
72 this.dblibSvc = dblibSvc;
74 Properties properties = propProvider.getProperties();
76 String cryptKey = properties.getProperty("org.onap.sdnc.resource.sql.cryptkey");
78 if ((cryptKey == null) || (cryptKey.length() == 0)) {
79 cryptKey = properties.getProperty("org.openecomp.sdnc.resource.sql.cryptkey");
82 SqlResource.setCryptKey(cryptKey);
85 // For sql-resource, is-available is the same as exists
87 public QueryStatus isAvailable(String resource, String key, String prefix, SvcLogicContext ctx)
88 throws SvcLogicException {
90 return (exists(resource, key, prefix, ctx));
95 public QueryStatus exists(String resource, String key, String prefix, SvcLogicContext ctx)
96 throws SvcLogicException {
98 DbLibService dblibSvc = getDbLibService();
99 if (dblibSvc == null) {
100 return (QueryStatus.FAILURE);
103 String theStmt = resolveCtxVars(key, ctx, resource);
106 CachedRowSet results = dblibSvc.getData(theStmt, null, null);
108 if (!results.next()) {
109 return (QueryStatus.NOT_FOUND);
112 int numRows = results.getInt(1);
115 return (QueryStatus.SUCCESS);
117 return (QueryStatus.NOT_FOUND);
119 } catch (Exception e) {
120 LOG.error("Caught SQL exception", e);
121 return (QueryStatus.FAILURE);
126 public QueryStatus query(String resource, boolean localOnly, String select, String key, String prefix,
127 String orderBy, SvcLogicContext ctx) throws SvcLogicException {
129 DbLibService dblibSvc = getDbLibService();
131 if (dblibSvc == null) {
132 return (QueryStatus.FAILURE);
135 String sqlQuery = resolveCtxVars(key, ctx, resource);
140 CachedRowSet results = dblibSvc.getData(sqlQuery, null, null);
142 QueryStatus retval = QueryStatus.SUCCESS;
144 if (!results.next()) {
145 retval = QueryStatus.NOT_FOUND;
146 LOG.debug("No data found");
148 saveCachedRowSetToCtx(results, ctx, prefix, dblibSvc);
151 } catch (Exception e) {
152 LOG.error("Caught SQL exception", e);
153 return (QueryStatus.FAILURE);
157 public void saveCachedRowSetToCtx(CachedRowSet results, SvcLogicContext ctx, String prefix, DbLibService dblibSvc)
158 throws SQLException {
160 if ((prefix != null) && prefix.endsWith("[]")) {
162 String pfx = prefix.substring(0, prefix.length() - 2);
165 ResultSetMetaData rsMeta = results.getMetaData();
166 int numCols = rsMeta.getColumnCount();
168 for (int i = 0; i < numCols; i++) {
169 String colValue = null;
170 String tableName = rsMeta.getTableName(i + 1);
171 if (rsMeta.getColumnType(i + 1) == java.sql.Types.VARBINARY) {
172 colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1),
175 colValue = results.getString(i + 1);
177 LOG.debug("Setting " + pfx + "[" + idx + "]."
178 + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue);
179 ctx.setAttribute(pfx + "[" + idx + "]." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"),
183 } while (results.next());
184 LOG.debug("Setting " + pfx + "_length = " + idx);
185 ctx.setAttribute(pfx + "_length", "" + idx);
187 ResultSetMetaData rsMeta = results.getMetaData();
188 int numCols = rsMeta.getColumnCount();
190 for (int i = 0; i < numCols; i++) {
191 String colValue = null;
192 String tableName = rsMeta.getTableName(i + 1);
193 if ("VARBINARY".equalsIgnoreCase(rsMeta.getColumnTypeName(i + 1))) {
194 colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1),
197 colValue = results.getString(i + 1);
199 if (prefix != null) {
200 LOG.debug("Setting " + prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = "
202 ctx.setAttribute(prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue);
204 LOG.debug("Setting " + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue);
205 ctx.setAttribute(rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue);
214 public QueryStatus reserve(String resource, String select, String key, String prefix, SvcLogicContext ctx)
215 throws SvcLogicException {
216 return (QueryStatus.SUCCESS);
221 public QueryStatus release(String resource, String key, SvcLogicContext ctx) throws SvcLogicException {
222 return (QueryStatus.SUCCESS);
225 private QueryStatus executeSqlWrite(String key, SvcLogicContext ctx) throws SvcLogicException {
226 QueryStatus retval = QueryStatus.SUCCESS;
228 DbLibService dblibSvc = getDbLibService();
230 if (dblibSvc == null) {
231 return (QueryStatus.FAILURE);
234 String sqlStmt = resolveCtxVars(key, ctx, "");
236 LOG.debug("key = [" + key + "]; sqlStmt = [" + sqlStmt + "]");
239 if (!dblibSvc.writeData(sqlStmt, null, null)) {
240 retval = QueryStatus.FAILURE;
242 } catch (Exception e) {
243 LOG.error("Caught SQL exception", e);
244 retval = QueryStatus.FAILURE;
251 private String resolveCtxVars(String key, SvcLogicContext ctx, String resource) {
256 if (key.startsWith("'") && key.endsWith("'")) {
257 key = key.substring(1, key.length() - 1);
258 LOG.debug("Stripped outer single quotes - key is now [" + key + "]");
261 //"SQL-TRUE" allows for the key to be used as is.
262 if (!resource.equals("SQL-TRUE")) {
263 String[] keyTerms = key.split("\\s+");
265 StringBuffer sqlBuffer = new StringBuffer();
267 for (int i = 0; i < keyTerms.length; i++) {
268 sqlBuffer.append(resolveTerm(keyTerms[i], ctx));
269 sqlBuffer.append(" ");
271 key = sqlBuffer.toString();
277 private String resolveTerm(String term, SvcLogicContext ctx) {
282 LOG.trace("resolveTerm: term is " + term);
284 if (term.startsWith("$") && (ctx != null)) {
285 // Resolve any index variables.
286 term = resolveCtxVariable(term.substring(1), ctx);
287 // Escape single quote
289 term = term.replaceAll("'", "''");
291 return ("'" + term + "'");
298 private String resolveCtxVariable(String ctxVarName, SvcLogicContext ctx) {
300 if (ctxVarName.indexOf('[') == -1) {
301 // Ctx variable contains no arrays
302 if ("CRYPT_KEY".equals(ctxVarName)) {
303 // Handle crypt key as special case. If it's set as a context
304 // variable, use it. Otherwise, use
305 // configured crypt key.
306 String cryptKey = ctx.getAttribute(ctxVarName);
307 if ((cryptKey != null) && (cryptKey.length() > 0)) {
313 return (ctx.getAttribute(ctxVarName));
316 // Resolve any array references
317 StringBuffer sbuff = new StringBuffer();
318 String[] ctxVarParts = ctxVarName.split("\\[");
319 sbuff.append(ctxVarParts[0]);
320 for (int i = 1; i < ctxVarParts.length; i++) {
321 if (ctxVarParts[i].startsWith("$")) {
322 int endBracketLoc = ctxVarParts[i].indexOf("]");
323 if (endBracketLoc == -1) {
324 // Missing end bracket ... give up parsing
325 LOG.warn("Variable reference " + ctxVarName + " seems to be missing a ']'");
326 return (ctx.getAttribute(ctxVarName));
329 String idxVarName = ctxVarParts[i].substring(1, endBracketLoc);
330 String remainder = ctxVarParts[i].substring(endBracketLoc);
333 sbuff.append(ctx.getAttribute(idxVarName));
334 sbuff.append(remainder);
337 // Index is not a variable reference
339 sbuff.append(ctxVarParts[i]);
343 return (ctx.getAttribute(sbuff.toString()));
347 public QueryStatus save(String resource, boolean force, boolean localOnly, String key, Map<String, String> parms,
348 String prefix, SvcLogicContext ctx) throws SvcLogicException {
349 return (executeSqlWrite(key, ctx));
352 private DbLibService getDbLibService() {
354 if (dblibSvc != null) {
357 // Try to get dblib as an OSGI service
358 BundleContext bctx = null;
359 ServiceReference sref = null;
361 Bundle bundle = FrameworkUtil.getBundle(SqlResource.class);
363 if (bundle != null) {
364 bctx = bundle.getBundleContext();
368 sref = bctx.getServiceReference(DBLIB_SERVICE);
372 LOG.warn("Could not find service reference for DBLIB service (" + DBLIB_SERVICE + ")");
374 dblibSvc = (DbLibService) bctx.getService(sref);
375 if (dblibSvc == null) {
376 LOG.warn("Could not find service reference for DBLIB service (" + DBLIB_SERVICE + ")");
380 if (dblibSvc == null) {
381 // Must not be running in an OSGI container. See if you can load it
385 // If $SDNC_CONFIG_DIR/dblib.properties exists, that should
386 // be the properties passed to DBResourceManager constructor.
387 // If not, as default just use system properties.
388 Properties dblibProps = System.getProperties();
389 String cfgDir = System.getenv("SDNC_CONFIG_DIR");
391 if ((cfgDir == null) || (cfgDir.length() == 0)) {
392 cfgDir = "/opt/sdnc/data/properties";
395 File dblibPropFile = new File(cfgDir + "/dblib.properties");
396 if (dblibPropFile.exists()) {
398 dblibProps = new Properties();
399 dblibProps.load(new FileInputStream(dblibPropFile));
400 } catch (Exception e) {
401 LOG.warn("Could not load properties file " + dblibPropFile.getAbsolutePath(), e);
403 dblibProps = System.getProperties();
408 dblibSvc = new DBResourceManager(dblibProps);
409 } catch (Exception e) {
410 LOG.error("Caught exception trying to create dblib service", e);
413 if (dblibSvc == null) {
414 LOG.warn("Could not create new DBResourceManager");
422 public QueryStatus notify(String resource, String action, String key, SvcLogicContext ctx)
423 throws SvcLogicException {
424 if (LOG.isDebugEnabled()) {
425 LOG.debug("SqlResource.notify called with resource=" + resource + ", action=" + action);
427 return QueryStatus.SUCCESS;
431 public QueryStatus delete(String resource, String key, SvcLogicContext ctx) throws SvcLogicException {
432 return (executeSqlWrite(key, ctx));
435 public QueryStatus update(String resource, String key, Map<String, String> parms, String prefix,
436 SvcLogicContext ctx) throws SvcLogicException {
437 return (executeSqlWrite(key, ctx));
440 private String decryptColumn(String tableName, String colName, byte[] colValue, DbLibService dblibSvc) {
441 String strValue = new String(colValue);
443 if (StringUtils.isAsciiPrintable(strValue)) {
445 // If printable, not encrypted
448 ResultSet results = null;
449 try (Connection conn = dblibSvc.getConnection();
450 PreparedStatement stmt = conn.prepareStatement("SELECT CAST(AES_DECRYPT(?, ?) AS CHAR(50)) FROM DUAL")) {
452 stmt.setBytes(1, colValue);
453 stmt.setString(2, getCryptKey());
454 results = stmt.executeQuery();
456 if ((results != null) && results.next()) {
457 strValue = results.getString(1);
458 LOG.debug("Decrypted value is " + strValue);
460 LOG.warn("Cannot decrypt " + tableName + "." + colName);
462 } catch (Exception e) {
463 LOG.error("Caught exception trying to decrypt " + tableName + "." + colName, e);
465 if (results != null) {
468 } catch (SQLException se) {
469 LOG.error("Caught exception trying to close ResultSet",se);
477 public static String getCryptKey() {
481 public static String setCryptKey(String key) {
486 public String parameterizedQuery(Map<String, String> parameters, SvcLogicContext ctx) throws SvcLogicException {
487 DbLibService dblibSvc = getDbLibService();
488 String prefix = parameters.get("prefix");
489 String query = parameters.get("query");
491 ArrayList<String> arguments = new ArrayList<String>();
492 for (Entry<String, String> a : parameters.entrySet()) {
493 if (a.getKey().startsWith("param")) {
494 arguments.add(a.getValue());
499 if (dblibSvc == null) {
500 return mapQueryStatus(QueryStatus.FAILURE);
502 if (query.contains("count") || query.contains("COUNT")) {
503 CachedRowSet results = dblibSvc.getData(query, arguments, null);
505 if (!results.next()) {
506 return mapQueryStatus(QueryStatus.FAILURE);
509 int numRows = results.getInt(1);
510 ctx.setAttribute(prefix + ".count", String.valueOf(numRows));
516 } else if (query.startsWith("select") || query.startsWith("SELECT")) {
517 CachedRowSet results = dblibSvc.getData(query, arguments, null);
518 if (!results.next()) {
519 return mapQueryStatus(QueryStatus.NOT_FOUND);
521 saveCachedRowSetToCtx(results, ctx, prefix, dblibSvc);
524 if (!dblibSvc.writeData(query, arguments, null)) {
525 return mapQueryStatus(QueryStatus.FAILURE);
528 return mapQueryStatus(QueryStatus.SUCCESS);
529 } catch (SQLException e) {
530 LOG.error("Caught SQL exception", e);
531 return mapQueryStatus(QueryStatus.FAILURE);
535 protected String mapQueryStatus(QueryStatus status) {
536 String str = status.toString();
537 str = str.toLowerCase();
538 str = str.replaceAll("_", "-");