sql-resource/provider/src/main/java/org/openecomp/sdnc/sli/resource/sql/SqlResource.java

   1 /*-
   2  * ============LICENSE_START=======================================================
   3  * openECOMP : SDN-C
   4  * ================================================================================
   5  * Copyright (C) 2017 AT&T Intellectual Property. All rights
   6  *                      reserved.
   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
  11  *
  12  *      http://www.apache.org/licenses/LICENSE-2.0
  13  *
  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=========================================================
  20  */
  21
  22 package org.openecomp.sdnc.sli.resource.sql;
  23
  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;
  30 import java.util.Map;
  31 import java.util.Map.Entry;
  32
  33 import javax.sql.rowset.CachedRowSet;
  34
  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;
  49
  50 public class SqlResource implements SvcLogicResource, SvcLogicJavaPlugin {
  51
  52         private static final Logger LOG = LoggerFactory.getLogger(SqlResource.class);
  53
  54         private static final String DBLIB_SERVICE = "org.openecomp.sdnc.sli.resource.dblib.DBResourceManager";
  55
  56         private static String CRYPT_KEY = "";
  57
  58         public SqlResource() {
  59         }
  60
  61         // For sql-resource, is-available is the same as exists
  62         @Override
  63         public QueryStatus isAvailable(String resource, String key, String prefix, SvcLogicContext ctx)
  64                         throws SvcLogicException {
  65
  66                 return (exists(resource, key, prefix, ctx));
  67
  68         }
  69
  70         @Override
  71         public QueryStatus exists(String resource, String key, String prefix, SvcLogicContext ctx)
  72                         throws SvcLogicException {
  73
  74                 DbLibService dblibSvc = getDbLibService();
  75                 if (dblibSvc == null) {
  76                         return (QueryStatus.FAILURE);
  77                 }
  78
  79                 String theStmt = resolveCtxVars(key, ctx);
  80
  81                 try {
  82                         CachedRowSet results = dblibSvc.getData(theStmt, null, null);
  83
  84                         if (!results.next()) {
  85                                 return (QueryStatus.NOT_FOUND);
  86                         }
  87
  88                         int numRows = results.getInt(1);
  89
  90                         if (numRows > 0) {
  91                                 return (QueryStatus.SUCCESS);
  92                         } else {
  93                                 return (QueryStatus.NOT_FOUND);
  94                         }
  95                 } catch (Exception e) {
  96                         LOG.error("Caught SQL exception", e);
  97                         return (QueryStatus.FAILURE);
  98                 }
  99         }
 100
 101         // @Override
 102         public QueryStatus query(String resource, boolean localOnly, String select, String key, String prefix,
 103                         String orderBy, SvcLogicContext ctx) throws SvcLogicException {
 104
 105                 DbLibService dblibSvc = getDbLibService();
 106
 107                 if (dblibSvc == null) {
 108                         return (QueryStatus.FAILURE);
 109                 }
 110
 111                 String sqlQuery = resolveCtxVars(key, ctx);
 112
 113                 try {
 114
 115                         CachedRowSet results = dblibSvc.getData(sqlQuery, null, null);
 116
 117                         QueryStatus retval = QueryStatus.SUCCESS;
 118
 119                         if (!results.next()) {
 120                                 retval = QueryStatus.NOT_FOUND;
 121                                 LOG.debug("No data found");
 122                         } else {
 123                                 saveCachedRowSetToCtx(results, ctx, prefix, dblibSvc);
 124                         }
 125                         return (retval);
 126                 } catch (Exception e) {
 127                         LOG.error("Caught SQL exception", e);
 128                         return (QueryStatus.FAILURE);
 129                 }
 130         }
 131
 132         public void saveCachedRowSetToCtx(CachedRowSet results, SvcLogicContext ctx, String prefix, DbLibService dblibSvc)
 133                         throws SQLException {
 134                 if (ctx != null) {
 135                         if ((prefix != null) && prefix.endsWith("[]")) {
 136                                 // Return an array.
 137                                 String pfx = prefix.substring(0, prefix.length() - 2);
 138                                 int idx = 0;
 139                                 do {
 140                                         ResultSetMetaData rsMeta = results.getMetaData();
 141                                         int numCols = rsMeta.getColumnCount();
 142
 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),
 148                                                                         dblibSvc);
 149                                                 } else {
 150                                                         colValue = results.getString(i + 1);
 151                                                 }
 152                                                 LOG.debug("Setting " + pfx + "[" + idx + "]."
 153                                                                 + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue);
 154                                                 ctx.setAttribute(pfx + "[" + idx + "]." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"),
 155                                                                 colValue);
 156                                         }
 157                                         idx++;
 158                                 } while (results.next());
 159                                 LOG.debug("Setting " + pfx + "_length = " + idx);
 160                                 ctx.setAttribute(pfx + "_length", "" + idx);
 161                         } else {
 162                                 ResultSetMetaData rsMeta = results.getMetaData();
 163                                 int numCols = rsMeta.getColumnCount();
 164
 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),
 170                                                                 dblibSvc);
 171                                         } else {
 172                                                 colValue = results.getString(i + 1);
 173                                         }
 174                                         if (prefix != null) {
 175                                                 LOG.debug("Setting " + prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = "
 176                                                                 + colValue);
 177                                                 ctx.setAttribute(prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue);
 178                                         } else {
 179                                                 LOG.debug("Setting " + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue);
 180                                                 ctx.setAttribute(rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue);
 181                                         }
 182                                 }
 183                         }
 184                 }
 185         }
 186
 187         // reserve is no-op
 188         @Override
 189         public QueryStatus reserve(String resource, String select, String key, String prefix, SvcLogicContext ctx)
 190                         throws SvcLogicException {
 191                 return (QueryStatus.SUCCESS);
 192         }
 193
 194         // release is no-op
 195         @Override
 196         public QueryStatus release(String resource, String key, SvcLogicContext ctx) throws SvcLogicException {
 197                 return (QueryStatus.SUCCESS);
 198         }
 199
 200         private QueryStatus executeSqlWrite(String key, SvcLogicContext ctx) throws SvcLogicException {
 201                 QueryStatus retval = QueryStatus.SUCCESS;
 202
 203                 DbLibService dblibSvc = getDbLibService();
 204
 205                 if (dblibSvc == null) {
 206                         return (QueryStatus.FAILURE);
 207                 }
 208
 209                 String sqlStmt = resolveCtxVars(key, ctx);
 210
 211                 LOG.debug("key = [" + key + "]; sqlStmt = [" + sqlStmt + "]");
 212                 try {
 213
 214                         if (!dblibSvc.writeData(sqlStmt, null, null)) {
 215                                 retval = QueryStatus.FAILURE;
 216                         }
 217                 } catch (Exception e) {
 218                         LOG.error("Caught SQL exception", e);
 219                         retval = QueryStatus.FAILURE;
 220                 }
 221
 222                 return (retval);
 223
 224         }
 225
 226         private String resolveCtxVars(String key, SvcLogicContext ctx) {
 227                 if (key == null) {
 228                         return (null);
 229                 }
 230
 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 + "]");
 234                 }
 235
 236                 String[] keyTerms = key.split("\\s+");
 237
 238                 StringBuffer sqlBuffer = new StringBuffer();
 239
 240                 for (int i = 0; i < keyTerms.length; i++) {
 241                         sqlBuffer.append(resolveTerm(keyTerms[i], ctx));
 242                         sqlBuffer.append(" ");
 243                 }
 244
 245                 return (sqlBuffer.toString());
 246         }
 247
 248         private String resolveTerm(String term, SvcLogicContext ctx) {
 249                 if (term == null) {
 250                         return (null);
 251                 }
 252
 253                 LOG.trace("resolveTerm: term is " + term);
 254
 255                 if (term.startsWith("$") && (ctx != null)) {
 256                         // Resolve any index variables.
 257                         term = resolveCtxVariable(term.substring(1), ctx);
 258                         // Escape single quote
 259                         if (term != null) {
 260                                 term = term.replaceAll("'", "''");
 261                         }
 262                         return ("'" + term + "'");
 263                 } else {
 264                         return (term);
 265                 }
 266
 267         }
 268
 269         private String resolveCtxVariable(String ctxVarName, SvcLogicContext ctx) {
 270
 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)) {
 279                                         return (cryptKey);
 280                                 } else {
 281                                         return (CRYPT_KEY);
 282                                 }
 283                         }
 284                         return (ctx.getAttribute(ctxVarName));
 285                 }
 286
 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));
 298                                 }
 299
 300                                 String idxVarName = ctxVarParts[i].substring(1, endBracketLoc);
 301                                 String remainder = ctxVarParts[i].substring(endBracketLoc);
 302
 303                                 sbuff.append("[");
 304                                 sbuff.append(ctx.getAttribute(idxVarName));
 305                                 sbuff.append(remainder);
 306
 307                         } else {
 308                                 // Index is not a variable reference
 309                                 sbuff.append("[");
 310                                 sbuff.append(ctxVarParts[i]);
 311                         }
 312                 }
 313
 314                 return (ctx.getAttribute(sbuff.toString()));
 315         }
 316
 317         @Override
 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));
 321         }
 322
 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;
 328
 329                 Bundle bundle = FrameworkUtil.getBundle(SqlResource.class);
 330
 331                 if (bundle != null) {
 332                         bctx = bundle.getBundleContext();
 333                 }
 334
 335                 if (bctx != null) {
 336                         sref = bctx.getServiceReference(DBLIB_SERVICE);
 337                 }
 338
 339                 if (sref == null) {
 340                         LOG.warn("Could not find service reference for DBLIB service (" + DBLIB_SERVICE + ")");
 341                 } else {
 342                         dblibSvc = (DbLibService) bctx.getService(sref);
 343                         if (dblibSvc == null) {
 344                                 LOG.warn("Could not find service reference for DBLIB service (" + DBLIB_SERVICE + ")");
 345                         }
 346                 }
 347
 348                 if (dblibSvc == null) {
 349                         // Must not be running in an OSGI container. See if you can load it
 350                         // as a
 351                         // a POJO then.
 352                         try {
 353                                 dblibSvc = DBResourceManager.create(System.getProperties());
 354                         } catch (Exception e) {
 355                                 LOG.error("Caught exception trying to create dblib service", e);
 356                         }
 357
 358                         if (dblibSvc == null) {
 359                                 LOG.warn("Could not create new DBResourceManager");
 360                         }
 361                 }
 362
 363                 return (dblibSvc);
 364         }
 365
 366         @Override
 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);
 371                 }
 372                 return QueryStatus.SUCCESS;
 373         }
 374
 375         @Override
 376         public QueryStatus delete(String resource, String key, SvcLogicContext ctx) throws SvcLogicException {
 377                 return (executeSqlWrite(key, ctx));
 378         }
 379
 380         public QueryStatus update(String resource, String key, Map<String, String> parms, String prefix,
 381                         SvcLogicContext ctx) throws SvcLogicException {
 382                 return (executeSqlWrite(key, ctx));
 383         }
 384
 385         private String decryptColumn(String tableName, String colName, byte[] colValue, DbLibService dblibSvc) {
 386                 String strValue = new String(colValue);
 387
 388                 if (StringUtils.isAsciiPrintable(strValue)) {
 389
 390                         // If printable, not encrypted
 391                         return (strValue);
 392                 } else {
 393                         PreparedStatement stmt = null;
 394                         Connection conn = null;
 395                         ResultSet results = null;
 396                         try {
 397                                 // CachedRowSet results =
 398                                 // dblibSvc.getData("SELECT
 399                                 // CAST(AES_DECRYPT('"+strValue+"','"+CRYPT_KEY+"') AS CHAR(50))
 400                                 // FROM DUAL",
 401                                 // null, null);
 402                                 conn = ((DBResourceManager) dblibSvc).getConnection();
 403
 404                                 stmt = conn.prepareStatement("SELECT CAST(AES_DECRYPT(?, ?) AS CHAR(50)) FROM DUAL");
 405
 406                                 stmt.setBytes(1, colValue);
 407                                 stmt.setString(2, getCryptKey());
 408
 409                                 results = stmt.executeQuery();
 410
 411                                 if ((results != null) && results.next()) {
 412                                         strValue = results.getString(1);
 413                                         LOG.debug("Decrypted value is " + strValue);
 414                                 } else {
 415                                         LOG.warn("Cannot decrypt " + tableName + "." + colName);
 416                                 }
 417                         } catch (Exception e) {
 418                                 LOG.error("Caught exception trying to decrypt " + tableName + "." + colName, e);
 419                         } finally {
 420                                 try {
 421                                         if (results != null) {
 422                                                 results.close();
 423                                                 results = null;
 424                                         }
 425                                 } catch (Exception exc) {
 426
 427                                 }
 428
 429                                 try {
 430                                         if (stmt != null) {
 431                                                 stmt.close();
 432                                                 stmt = null;
 433                                         }
 434                                 } catch (Exception exc) {
 435
 436                                 }
 437
 438                                 try {
 439                                         if (conn != null) {
 440                                                 conn.close();
 441                                                 conn = null;
 442                                         }
 443                                 } catch (Exception exc) {
 444
 445                                 }
 446
 447                         }
 448                 }
 449                 return (strValue);
 450         }
 451
 452         public static String getCryptKey() {
 453                 return (CRYPT_KEY);
 454         }
 455
 456         public static String setCryptKey(String key) {
 457                 CRYPT_KEY = key;
 458                 return (CRYPT_KEY);
 459         }
 460
 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");
 465
 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());
 470                         }
 471                 }
 472
 473                 try {
 474                         if (dblibSvc == null) {
 475                                 return mapQueryStatus(QueryStatus.FAILURE);
 476                         }
 477                         if (query.contains("count") || query.contains("COUNT")) {
 478                                 CachedRowSet results = dblibSvc.getData(query, arguments, null);
 479
 480                                 if (!results.next()) {
 481                                         return mapQueryStatus(QueryStatus.FAILURE);
 482                                 }
 483
 484                                 int numRows = results.getInt(1);
 485                                 ctx.setAttribute(prefix + ".count", String.valueOf(numRows));
 486                                 if (numRows > 0) {
 487                                         return "true";
 488                                 } else {
 489                                         return "false";
 490                                 }
 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);
 495                                 } else {
 496                                         saveCachedRowSetToCtx(results, ctx, prefix, dblibSvc);
 497                                 }
 498                         } else {
 499                                 if (!dblibSvc.writeData(query, arguments, null)) {
 500                                         return mapQueryStatus(QueryStatus.FAILURE);
 501                                 }
 502                         }
 503                         return mapQueryStatus(QueryStatus.SUCCESS);
 504                 } catch (SQLException e) {
 505                         LOG.error("Caught SQL exception", e);
 506                         return mapQueryStatus(QueryStatus.FAILURE);
 507                 }
 508         }
 509
 510         protected String mapQueryStatus(QueryStatus status) {
 511                 String str = status.toString();
 512                 str = str.toLowerCase();
 513                 str = str.replaceAll("_", "-");
 514                 return str;
 515         }
 516 }