/*
    * This file is part of dependency-check-core.
    *
    * 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.
   *
   * Copyright (c) 2018 Jeremy Long. All Rights Reserved.
   */
  package org.owasp.dependencycheck.data.nvdcve;
  //CSOFF: AvoidStarImport
  
  import com.google.common.io.Resources;
  import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
  import io.github.jeremylong.openvulnerability.client.nvd.Config;
  import io.github.jeremylong.openvulnerability.client.nvd.CpeMatch;
  import org.apache.commons.collections.map.ReferenceMap;
  import org.owasp.dependencycheck.dependency.Vulnerability;
  import org.owasp.dependencycheck.dependency.VulnerableSoftware;
  import org.owasp.dependencycheck.utils.*;
  import org.slf4j.Logger;
  import org.slf4j.LoggerFactory;
  
  import javax.annotation.concurrent.ThreadSafe;
  import java.io.IOException;
  import java.net.URL;
  import java.nio.charset.StandardCharsets;
  import java.sql.CallableStatement;
  import java.sql.Connection;
  import java.sql.JDBCType;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.*;
  import java.util.stream.Collectors;
  import org.anarres.jdiagnostics.DefaultQuery;
  
  import static org.apache.commons.collections.map.AbstractReferenceMap.HARD;
  import static org.apache.commons.collections.map.AbstractReferenceMap.SOFT;
  import org.owasp.dependencycheck.analyzer.exception.LambdaExceptionWrapper;
  import org.owasp.dependencycheck.analyzer.exception.UnexpectedAnalysisException;
  import io.github.jeremylong.openvulnerability.client.nvd.DefCveItem;
  import static org.owasp.dependencycheck.data.nvdcve.CveDB.PreparedStatementCveDb.*;
  import org.owasp.dependencycheck.data.update.cpe.CpeEcosystemCache;
  import org.owasp.dependencycheck.data.update.cpe.CpePlus;
  import io.github.jeremylong.openvulnerability.client.nvd.CvssV2;
  import io.github.jeremylong.openvulnerability.client.nvd.CvssV2Data;
  import io.github.jeremylong.openvulnerability.client.nvd.CvssV3;
  import io.github.jeremylong.openvulnerability.client.nvd.CvssV3Data;
  import io.github.jeremylong.openvulnerability.client.nvd.LangString;
  import io.github.jeremylong.openvulnerability.client.nvd.Node;
  import io.github.jeremylong.openvulnerability.client.nvd.Reference;
  import io.github.jeremylong.openvulnerability.client.nvd.Weakness;
  import org.owasp.dependencycheck.dependency.VulnerableSoftwareBuilder;
  import us.springett.parsers.cpe.Cpe;
  import us.springett.parsers.cpe.CpeBuilder;
  import us.springett.parsers.cpe.CpeParser;
  import us.springett.parsers.cpe.exceptions.CpeParsingException;
  import us.springett.parsers.cpe.exceptions.CpeValidationException;
  
  /**
   * The database holding information about the NVD CVE data. This class is safe
   * to be accessed from multiple threads in parallel, however internally only one
   * connection will be used.
   *
   * @author Jeremy Long
   */
  @ThreadSafe
  public final class CveDB implements AutoCloseable {
  
      /**
       * The logger.
       */
      private static final Logger LOGGER = LoggerFactory.getLogger(CveDB.class);
  
      /**
       * Resource location for SQL file containing updates to the ecosystem cache.
       */
      public static final String DB_ECOSYSTEM_CACHE = "data/dbEcosystemCacheUpdates.sql";
  
      /**
       * The database connection manager.
       */
      private final DatabaseManager databaseManager;
  
      /**
       * The bundle of statements used when accessing the database.
       */
      private ResourceBundle statementBundle;
      /**
      * Database properties object containing the 'properties' from the database
      * table.
      */
     private DatabaseProperties databaseProperties;
     /**
      * The filter for 2.3 CPEs in the CVEs - we don't import unless we get a
      * match.
      */
     private final String cpeStartsWithFilter;
     /**
      * Cache for CVE lookup; used to speed up the vulnerability search process.
      */
     @SuppressWarnings("unchecked")
     private final Map<String, List<Vulnerability>> vulnerabilitiesForCpeCache = Collections.synchronizedMap(new ReferenceMap(HARD, SOFT));
     /**
      * The configured settings
      */
     private final Settings settings;
 
     /**
      * Utility to extract information from
      * {@linkplain org.owasp.dependencycheck.data.nvd.json.DefCveItem}.
      */
     private final CveItemOperator cveItemConverter;
     /**
      * Flag indicating if the database is Oracle.
      */
     private boolean isOracle = false;
     /**
      * Flag indicating if the database is H2.
      */
     private boolean isH2 = false;
 
     /**
      * Updates the EcoSystem Cache.
      *
      * @return The number of records updated by the DB_ECOSYSTEM_CACHE update
      * script.
      */
     public int updateEcosystemCache() {
         LOGGER.debug("Updating the ecosystem cache");
         int updateCount = 0;
         try {
             final URL url = Resources.getResource(DB_ECOSYSTEM_CACHE);
             final List<String> sql = Resources.readLines(url, StandardCharsets.UTF_8);
 
             try (Connection conn = databaseManager.getConnection(); Statement statement = conn.createStatement()) {
                 for (String single : sql) {
                     updateCount += statement.executeUpdate(single);
                 }
             } catch (SQLException ex) {
                 LOGGER.debug("", ex);
                 throw new DatabaseException("Unable to update the ecosystem cache", ex);
             }
         } catch (IOException ex) {
             throw new DatabaseException("Unable to update the ecosystem cache", ex);
         } catch (LinkageError ex) {
             LOGGER.debug(new DefaultQuery(ex).call().toString());
         }
         return updateCount;
     }
 
     /**
      * The enumeration value names must match the keys of the statements in the
      * statement bundles "dbStatements*.properties".
      */
     enum PreparedStatementCveDb {
         /**
          * Key for SQL Statement.
          */
         CLEANUP_ORPHANS,
         /**
          * Key for update ecosystem.
          */
         UPDATE_ECOSYSTEM,
         /**
          * Key for update ecosystem.
          */
         UPDATE_ECOSYSTEM2,
         /**
          * Key for SQL Statement.
          */
         COUNT_CPE,
         /**
          * Key for SQL Statement.
          */
         DELETE_VULNERABILITY,
         /**
          * Key for SQL Statement.
          */
         INSERT_PROPERTY,
         /**
          * Key for SQL Statement.
          */
         INSERT_CWE,
         /**
          * Key for SQL Statement.
          */
         INSERT_REFERENCE,
         /**
          * Key for SQL Statement.
          */
         INSERT_SOFTWARE,
         /**
          * Key for SQL Statement.
          */
         MERGE_PROPERTY,
         /**
          * Key for SQL Statement.
          */
         SELECT_CPE_ENTRIES,
         /**
          * Key for SQL Statement.
          */
         SELECT_CVE_FROM_SOFTWARE,
         /**
          * Key for SQL Statement.
          */
         SELECT_PROPERTIES,
         /**
          * Key for SQL Statement.
          */
         SELECT_VULNERABILITY_CWE,
         /**
          * Key for SQL Statement.
          */
         SELECT_REFERENCES,
         /**
          * Key for SQL Statement.
          */
         SELECT_SOFTWARE,
         /**
          * Key for SQL Statement.
          */
         SELECT_VENDOR_PRODUCT_LIST,
         /**
          * Key for SQL Statement.
          */
         SELECT_VENDOR_PRODUCT_LIST_FOR_NODE,
         /**
          * Key for SQL Statement.
          */
         SELECT_VULNERABILITY,
         /**
          * Key for SQL Statement.
          */
         UPDATE_PROPERTY,
         /**
          * Key for SQL Statement.
          */
         UPDATE_VULNERABILITY,
         /**
          * Key for SQL Statement.
          */
         SELECT_CPE_ECOSYSTEM,
         /**
          * Key for SQL Statement.
          */
         MERGE_CPE_ECOSYSTEM,
         /**
          * Key for SQL Statement.
          */
         DELETE_UNUSED_DICT_CPE,
         /**
          * Key for SQL Statement.
          */
         ADD_DICT_CPE,
         /**
          * Key for SQL Statement.
          */
         SELECT_KNOWN_EXPLOITED_VULNERABILITIES,
         /**
          * Key for SQL Statement.
          */
         MERGE_KNOWN_EXPLOITED
     }
 
     /**
      * Creates a new CveDB object and opens the database connection. Note, the
      * connection must be closed by the caller by calling the close method.
      *
      * @param settings the configured settings
      * @throws DatabaseException thrown if there is an exception opening the
      * database.
      */
     public CveDB(Settings settings) throws DatabaseException {
         this.settings = settings;
         this.cpeStartsWithFilter = settings.getString(Settings.KEYS.CVE_CPE_STARTS_WITH_FILTER, "cpe:2.3:a:");
         this.cveItemConverter = new CveItemOperator(cpeStartsWithFilter);
         databaseManager = new DatabaseManager(settings);
         statementBundle = databaseManager.getSqlStatements();
         isOracle = databaseManager.isOracle();
         isH2 = databaseManager.isH2Connection();
     }
 
     /**
      * Opens the database connection pool.
      */
     public void open() {
         databaseManager.open();
         databaseProperties = new DatabaseProperties(this);
     }
 
     /**
      * Closes the database connection. Close should be called on this object
      * when it is done being used.
      */
     @Override
     public void close() {
         if (isOpen()) {
             LOGGER.debug("Closing database");
             clearCache();
             LOGGER.debug("Cache cleared");
             try {
                 databaseManager.close();
                 LOGGER.debug("Connection closed");
             } catch (Throwable ex) {
                 LOGGER.error("There was an exception attempting to close the CveDB, see the log for more details.");
                 LOGGER.debug("", ex);
             }
             releaseResources();
             LOGGER.debug("Resources released");
             databaseManager.cleanup();
         }
     }
 
     /**
      * Releases the resources used by CveDB.
      */
     private void releaseResources() {
         statementBundle = null;
         databaseProperties = null;
     }
 
     /**
      * Returns whether the database connection is open or closed.
      *
      * @return whether the database connection is open or closed
      */
     public boolean isOpen() {
         return databaseManager.isOpen();
     }
 
     /**
      * Creates a prepared statement from the given key. The SQL is stored in a
      * properties file and the key is used to lookup the specific query.
      *
      * @param connection the database connection
      * @param key the key to select the prepared statement from the properties
      * file
      * @param parameter the first parameter to pass into the statement
      * @return the prepared statement
      * @throws DatabaseException throw if there is an error generating the
      * prepared statement
      */
     private PreparedStatement getPreparedStatement(Connection connection, PreparedStatementCveDb key, String parameter)
             throws DatabaseException, SQLException {
         final PreparedStatement preparedStatement = getPreparedStatement(connection, key);
         preparedStatement.setString(1, parameter);
         return preparedStatement;
     }
 
     /**
      * Creates a prepared statement from the given key. The SQL is stored in a
      * properties file and the key is used to lookup the specific query.
      *
      * @param connection the database connection
      * @param key the key to select the prepared statement from the properties
      * file
      * @param parameter the first parameter to pass into the statement
      * @return the prepared statement
      * @throws DatabaseException throw if there is an error generating the
      * prepared statement
      */
     private PreparedStatement getPreparedStatement(Connection connection, PreparedStatementCveDb key, int parameter)
             throws DatabaseException, SQLException {
         final PreparedStatement preparedStatement = getPreparedStatement(connection, key);
         preparedStatement.setInt(1, parameter);
         return preparedStatement;
     }
 
     /**
      * Creates a prepared statement from the given key. The SQL is stored in a
      * properties file and the key is used to lookup the specific query.
      *
      * @param connection the database connection
      * @param key the key to select the prepared statement from the properties
      * file
      * @return the prepared statement
      * @throws DatabaseException throw if there is an error generating the
      * prepared statement
      */
     private PreparedStatement getPreparedStatement(Connection connection, PreparedStatementCveDb key) throws DatabaseException {
         PreparedStatement preparedStatement = null;
         try {
             final String statementString = statementBundle.getString(key.name());
             if (isOracle && key == UPDATE_VULNERABILITY) {
                 preparedStatement = connection.prepareCall(statementString);
 //            } else if (key == INSERT_CPE) {
 //                final String[] returnedColumns = {"id"};
 //                preparedStatement = connection.prepareStatement(statementString, returnedColumns);
             } else {
                 preparedStatement = connection.prepareStatement(statementString);
             }
             if (isOracle) {
                 // Oracle has a default fetch-size of 10; MariaDB, MySQL, SQLServer and PostgreSQL by default cache the full
                 // resultset at the client https://venkatsadasivam.com/2009/02/01/jdbc-performance-tuning-with-optimal-fetch-size/
                 preparedStatement.setFetchSize(10_000);
             }
         } catch (SQLException ex) {
             throw new DatabaseException(ex);
         } catch (MissingResourceException ex) {
             if (!ex.getMessage().contains("key MERGE_PROPERTY")) {
                 throw new DatabaseException(ex);
             }
         }
         return preparedStatement;
     }
 
     /**
      * Cleans up the object and ensures that "close" has been called.
      *
      * @throws Throwable thrown if there is a problem
      */
     @Override
     @SuppressWarnings("FinalizeDeclaration")
     protected void finalize() throws Throwable {
         LOGGER.debug("Entering finalize");
         close();
         super.finalize();
     }
 
     /**
      * Get the value of databaseProperties.
      *
      * @return the value of databaseProperties
      */
     public DatabaseProperties getDatabaseProperties() {
         return databaseProperties;
     }
 
     /**
      * Used within the unit tests to reload the database properties.
      *
      * @return the database properties
      */
     DatabaseProperties reloadProperties() {
         databaseProperties = new DatabaseProperties(this);
         return databaseProperties;
     }
 
     /**
      * Searches the CPE entries in the database and retrieves all entries for a
      * given vendor and product combination. The returned list will include all
      * versions of the product that are registered in the NVD CVE data.
      *
      * @param vendor the identified vendor name of the dependency being analyzed
      * @param product the identified name of the product of the dependency being
      * analyzed
      * @return a set of vulnerable software
      */
     public Set<CpePlus> getCPEs(String vendor, String product) {
         final Set<CpePlus> cpe = new HashSet<>();
         try (Connection conn = databaseManager.getConnection(); PreparedStatement ps = getPreparedStatement(conn, SELECT_CPE_ENTRIES)) {
             //part, vendor, product, version, update_version, edition,
             //lang, sw_edition, target_sw, target_hw, other, ecosystem
             ps.setString(1, vendor);
             ps.setString(2, product);
             try (ResultSet rs = ps.executeQuery()) {
                 final CpeBuilder builder = new CpeBuilder();
                 while (rs.next()) {
                     final Cpe entry = builder
                             .part(rs.getString(1))
                             .vendor(rs.getString(2))
                             .product(rs.getString(3))
                             .version(rs.getString(4))
                             .update(rs.getString(5))
                             .edition(rs.getString(6))
                             .language(rs.getString(7))
                             .swEdition(rs.getString(8))
                             .targetSw(rs.getString(9))
                             .targetHw(rs.getString(10))
                             .other(rs.getString(11)).build();
                     final CpePlus plus = new CpePlus(entry, rs.getString(12));
                     cpe.add(plus);
                 }
             }
         } catch (SQLException | CpeParsingException | CpeValidationException ex) {
             LOGGER.error("An unexpected SQL Exception occurred; please see the verbose log for more details.");
             LOGGER.debug("", ex);
         }
         return cpe;
     }
 
     /**
      * Returns the entire list of vendor/product combinations.
      *
      * @return the entire list of vendor/product combinations
      * @throws DatabaseException thrown when there is an error retrieving the
      * data from the DB
      */
     public Set<Pair<String, String>> getVendorProductList() throws DatabaseException {
         final Set<Pair<String, String>> data = new HashSet<>();
         try (Connection conn = databaseManager.getConnection();
                 PreparedStatement ps = getPreparedStatement(conn, SELECT_VENDOR_PRODUCT_LIST);
                 ResultSet rs = ps.executeQuery()) {
             while (rs.next()) {
                 data.add(new Pair<>(rs.getString(1), rs.getString(2)));
             }
         } catch (SQLException ex) {
             final String msg = "An unexpected SQL Exception occurred; please see the verbose log for more details.";
             throw new DatabaseException(msg, ex);
         }
         return data;
     }
 
     /**
      * Returns the entire list of vendor/product combinations filtered for just
      * Node JS related products.
      *
      * @return the list of vendor/product combinations that are known to be
      * related to Node JS
      * @throws DatabaseException thrown when there is an error retrieving the
      * data from the DB
      */
     public Set<Pair<String, String>> getVendorProductListForNode() throws DatabaseException {
         final Set<Pair<String, String>> data = new HashSet<>();
         try (Connection conn = databaseManager.getConnection();
                 PreparedStatement ps = getPreparedStatement(conn, SELECT_VENDOR_PRODUCT_LIST_FOR_NODE);
                 ResultSet rs = ps.executeQuery()) {
             while (rs.next()) {
                 data.add(new Pair<>(rs.getString(1), rs.getString(2)));
             }
         } catch (SQLException ex) {
             final String msg = "An unexpected SQL Exception occurred; please see the verbose log for more details.";
             throw new DatabaseException(msg, ex);
         }
         return data;
     }
 
     /**
      * Returns a set of properties.
      *
      * @return the properties from the database
      */
     public Properties getProperties() {
         final Properties prop = new Properties();
         try (Connection conn = databaseManager.getConnection();
                 PreparedStatement ps = getPreparedStatement(conn, SELECT_PROPERTIES);
                 ResultSet rs = ps.executeQuery()) {
             while (rs.next()) {
                 prop.setProperty(rs.getString(1), rs.getString(2));
             }
         } catch (SQLException ex) {
             LOGGER.error("An unexpected SQL Exception occurred; please see the verbose log for more details.");
             LOGGER.debug("", ex);
         }
         return prop;
     }
 
     /**
      * Saves a property to the database.
      *
      * @param key the property key
      * @param value the property value
      */
     public void saveProperty(String key, String value) {
         clearCache();
         try (Connection conn = databaseManager.getConnection(); PreparedStatement mergeProperty = getPreparedStatement(conn, MERGE_PROPERTY)) {
             if (mergeProperty != null) {
                 mergeProperty.setString(1, key);
                 mergeProperty.setString(2, value);
                 mergeProperty.execute();
             } else {
                 // No Merge statement, so doing an Update/Insert...
                 try (PreparedStatement updateProperty = getPreparedStatement(conn, UPDATE_PROPERTY)) {
                     updateProperty.setString(1, value);
                     updateProperty.setString(2, key);
                     if (updateProperty.executeUpdate() == 0) {
                         try (PreparedStatement insertProperty = getPreparedStatement(conn, INSERT_PROPERTY)) {
                             insertProperty.setString(1, key);
                             insertProperty.setString(2, value);
                             insertProperty.executeUpdate();
                         }
                     }
                 }
             }
         } catch (SQLException ex) {
             LOGGER.warn("Unable to save property '{}' with a value of '{}' to the database", key, value);
             LOGGER.debug("", ex);
         }
     }
 
     /**
      * Clears cache. Should be called whenever something is modified. While this
      * is not the optimal cache eviction strategy, this is good enough for
      * typical usage (update DB and then only read) and it is easier to maintain
      * the code.
      * <p>
      * It should be also called when DB is closed.
      * </p>
      */
     private void clearCache() {
         vulnerabilitiesForCpeCache.clear();
     }
 
     /**
      * Retrieves the vulnerabilities associated with the specified CPE.
      *
      * @param cpe the CPE to retrieve vulnerabilities for
      * @return a list of Vulnerabilities
      * @throws DatabaseException thrown if there is an exception retrieving data
      */
     public List<Vulnerability> getVulnerabilities(Cpe cpe) throws DatabaseException {
         final List<Vulnerability> cachedVulnerabilities = vulnerabilitiesForCpeCache.get(cpe.toCpe23FS());
         if (cachedVulnerabilities != null) {
             LOGGER.debug("Cache hit for {}", cpe.toCpe23FS());
             return cachedVulnerabilities;
         } else {
             LOGGER.debug("Cache miss for {}", cpe.toCpe23FS());
         }
 
         final List<Vulnerability> vulnerabilities = new ArrayList<>();
         try (Connection conn = databaseManager.getConnection(); PreparedStatement ps = getPreparedStatement(conn, SELECT_CVE_FROM_SOFTWARE)) {
             ps.setString(1, cpe.getVendor());
             ps.setString(2, cpe.getProduct());
             try (ResultSet rs = ps.executeQuery()) {
                 String currentCVE = "";
                 final Set<VulnerableSoftware> vulnSoftware = new HashSet<>();
                 final VulnerableSoftwareBuilder vulnerableSoftwareBuilder = new VulnerableSoftwareBuilder();
                 while (rs.next()) {
                     final String cveId = rs.getString(1);
                     if (currentCVE.isEmpty()) {
                         //first loop we don't have the cveId
                         currentCVE = cveId;
                     }
                     if (!vulnSoftware.isEmpty() && !currentCVE.equals(cveId)) { //check for match and add
                         final VulnerableSoftware matchedCPE = getMatchingSoftware(cpe, vulnSoftware);
                         if (matchedCPE != null) {
                             final Vulnerability v = getVulnerability(currentCVE, conn);
                             if (v != null) {
                                 v.setMatchedVulnerableSoftware(matchedCPE);
                                 v.setSource(Vulnerability.Source.NVD);
                                 vulnerabilities.add(v);
                             }
                         }
                         vulnSoftware.clear();
                         currentCVE = cveId;
                     }
                     // 1 cve, 2 part, 3 vendor, 4 product, 5 version, 6 update_version, 7 edition,
                     // 8 lang, 9 sw_edition, 10 target_sw, 11 target_hw, 12 other, 13 versionEndExcluding,
                     //14 versionEndIncluding, 15 versionStartExcluding, 16 versionStartIncluding, 17 vulnerable
                     final VulnerableSoftware vs;
                     try {
                         vs = vulnerableSoftwareBuilder.part(rs.getString(2)).vendor(rs.getString(3))
                                 .product(rs.getString(4)).version(rs.getString(5)).update(rs.getString(6))
                                 .edition(rs.getString(7)).language(rs.getString(8)).swEdition(rs.getString(9))
                                 .targetSw(rs.getString(10)).targetHw(rs.getString(11)).other(rs.getString(12))
                                 .versionEndExcluding(rs.getString(13)).versionEndIncluding(rs.getString(14))
                                 .versionStartExcluding(rs.getString(15)).versionStartIncluding(rs.getString(16))
                                 .vulnerable(rs.getBoolean(17)).build();
                     } catch (CpeParsingException | CpeValidationException ex) {
                         throw new DatabaseException("Database contains an invalid Vulnerable Software Entry", ex);
                     }
                     vulnSoftware.add(vs);
                 }
 
                 //remember to process the last set of CVE/CPE entries
                 final VulnerableSoftware matchedCPE = getMatchingSoftware(cpe, vulnSoftware);
                 if (matchedCPE != null) {
                     final Vulnerability v = getVulnerability(currentCVE, conn);
                     if (v != null) {
                         v.setMatchedVulnerableSoftware(matchedCPE);
                         v.setSource(Vulnerability.Source.NVD);
                         vulnerabilities.add(v);
                     }
                 }
             }
         } catch (SQLException ex) {
             throw new DatabaseException("Exception retrieving vulnerability for " + cpe.toCpe23FS(), ex);
         }
         vulnerabilitiesForCpeCache.put(cpe.toCpe23FS(), vulnerabilities);
         return vulnerabilities;
     }
 
     /**
      * Gets a vulnerability for the provided CVE.
      *
      * @param cve the CVE to lookup
      * @return a vulnerability object
      * @throws DatabaseException if an exception occurs
      */
     public Vulnerability getVulnerability(String cve) throws DatabaseException {
         try (Connection conn = databaseManager.getConnection()) {
             return getVulnerability(cve, conn);
         } catch (SQLException ex) {
             throw new DatabaseException("Error retrieving " + cve, ex);
         }
     }
 
     /**
      * Gets a vulnerability for the provided CVE.
      *
      * @param cve the CVE to lookup
      * @param conn already active database connection
      * @return a vulnerability object
      * @throws DatabaseException if an exception occurs
      */
     public Vulnerability getVulnerability(String cve, Connection conn) throws DatabaseException {
         final int cveId;
         final VulnerableSoftwareBuilder vulnerableSoftwareBuilder = new VulnerableSoftwareBuilder();
         Vulnerability vuln = null;
         try {
             try (PreparedStatement psV = getPreparedStatement(conn, SELECT_VULNERABILITY, cve); ResultSet rsV = psV.executeQuery()) {
                 if (rsV.next()) {
                     //1.id, 2.description,
                     cveId = rsV.getInt(1);
                     vuln = new Vulnerability();
                     vuln.setSource(Vulnerability.Source.NVD);
                     vuln.setName(cve);
                     vuln.setDescription(rsV.getString(2));
 
                     //3.v2Severity, 4.v2ExploitabilityScore, 5.v2ImpactScore, 6.v2AcInsufInfo, 7.v2ObtainAllPrivilege,
                     //8.v2ObtainUserPrivilege, 9.v2ObtainOtherPrivilege, 10.v2UserInteractionRequired, 11.v2Score,
                     //12.v2AccessVector, 13.v2AccessComplexity, 14.v2Authentication, 15.v2ConfidentialityImpact,
                     //16.v2IntegrityImpact, 17.v2AvailabilityImpact, 18.v2Version,
                     if (rsV.getObject(11) != null) {
 
                         final CvssV2Data.AccessVectorType accessVector = CvssV2Data.AccessVectorType.fromValue(rsV.getString(12));
                         final CvssV2Data.AccessComplexityType accessComplexity = CvssV2Data.AccessComplexityType.fromValue(rsV.getString(13));
                         final CvssV2Data.AuthenticationType authentication = CvssV2Data.AuthenticationType.fromValue(rsV.getString(14));
                         final CvssV2Data.CiaType confidentialityImpact = CvssV2Data.CiaType.fromValue(rsV.getString(15));
                         final CvssV2Data.CiaType integrityImpact = CvssV2Data.CiaType.fromValue(rsV.getString(16));
                         final CvssV2Data.CiaType availabilityImpact = CvssV2Data.CiaType.fromValue(rsV.getString(17));
                         final String vector = String.format("/AV:%s/AC:%s/Au:%s/C:%s/I:%s/A:%s",
                                 accessVector == null ? "" : accessVector.value().substring(0, 1),
                                 accessComplexity == null ? "" : accessComplexity.value().substring(0, 1),
                                 authentication == null ? "" : authentication.value().substring(0, 1),
                                 confidentialityImpact == null ? "" : confidentialityImpact.value().substring(0, 1),
                                 integrityImpact == null ? "" : integrityImpact.value().substring(0, 1),
                                 availabilityImpact == null ? "" : availabilityImpact.value().substring(0, 1));
 
                         //some older test data may not correctly have the version set.
                         String cveVersion = "2.0";
                         if (rsV.getString(18) != null) {
                             cveVersion = rsV.getString(18);
                         }
                         final CvssV2Data cvssData = new CvssV2Data(cveVersion, vector, accessVector,
                                 accessComplexity, authentication, confidentialityImpact,
                                 integrityImpact, availabilityImpact, rsV.getDouble(11), rsV.getString(3),
                                 null, null, null, null, null, null, null, null, null, null);
                         final CvssV2 cvss = new CvssV2(null, CvssV2.Type.PRIMARY, cvssData, rsV.getString(3),
                                 rsV.getDouble(4), rsV.getDouble(5), rsV.getBoolean(6), rsV.getBoolean(7),
                                 rsV.getBoolean(8), rsV.getBoolean(9), rsV.getBoolean(10));
                         vuln.setCvssV2(cvss);
                     }
                     //19.v3ExploitabilityScore, 20.v3ImpactScore, 21.v3AttackVector, 22.v3AttackComplexity, 23.v3PrivilegesRequired,
                     //24.v3UserInteraction, 25.v3Scope, 26.v3ConfidentialityImpact, 27.v3IntegrityImpact, 28.v3AvailabilityImpact,
                     //29.v3BaseScore, 30.v3BaseSeverity, 31.v3Version
                     if (rsV.getObject(21) != null) {
                         //some older test data may not correctly have the version set.
                         String cveVersion = "3.1";
                         if (rsV.getString(31) != null) {
                             cveVersion = rsV.getString(31);
                         }
                         final CvssV3Data.Version version = CvssV3Data.Version.fromValue(cveVersion);
                         final CvssV3Data.AttackVectorType attackVector = CvssV3Data.AttackVectorType.fromValue(rsV.getString(21));
                         final CvssV3Data.AttackComplexityType attackComplexity = CvssV3Data.AttackComplexityType.fromValue(rsV.getString(22));
                         final CvssV3Data.PrivilegesRequiredType privilegesRequired = CvssV3Data.PrivilegesRequiredType.fromValue(rsV.getString(23));
                         final CvssV3Data.UserInteractionType userInteraction = CvssV3Data.UserInteractionType.fromValue(rsV.getString(24));
                         final CvssV3Data.ScopeType scope = CvssV3Data.ScopeType.fromValue(rsV.getString(25));
                         final CvssV3Data.CiaType confidentialityImpact = CvssV3Data.CiaType.fromValue(rsV.getString(26));
                         final CvssV3Data.CiaType integrityImpact = CvssV3Data.CiaType.fromValue(rsV.getString(27));
                         final CvssV3Data.CiaType availabilityImpact = CvssV3Data.CiaType.fromValue(rsV.getString(28));
                         final CvssV3Data.SeverityType baseSeverity = CvssV3Data.SeverityType.fromValue(rsV.getString(30));
                         final String vector = String.format("CVSS:%s/AV:%s/AC:%s/PR:%s/UI:%s/S:%s/C:%s/I:%s/A:%s",
                                 version == null ? "" : version,
                                 attackVector == null ? "" : attackVector.value().substring(0, 1),
                                 attackComplexity == null ? "" : attackComplexity.value().substring(0, 1),
                                 privilegesRequired == null ? "" : privilegesRequired.value().substring(0, 1),
                                 userInteraction == null ? "" : userInteraction.value().substring(0, 1),
                                 scope == null ? "" : scope.value().substring(0, 1),
                                 confidentialityImpact == null ? "" : confidentialityImpact.value().substring(0, 1),
                                 integrityImpact == null ? "" : integrityImpact.value().substring(0, 1),
                                 availabilityImpact == null ? "" : availabilityImpact.value().substring(0, 1));
 
                         final CvssV3Data cvssData = new CvssV3Data(version, vector, attackVector, attackComplexity, privilegesRequired,
                                 userInteraction, scope, confidentialityImpact, integrityImpact, availabilityImpact,
                                 rsV.getDouble(29), baseSeverity, CvssV3Data.ExploitCodeMaturityType.PROOF_OF_CONCEPT,
                                 CvssV3Data.RemediationLevelType.NOT_DEFINED, CvssV3Data.ConfidenceType.REASONABLE, 0.0,
                                 CvssV3Data.SeverityType.MEDIUM, CvssV3Data.CiaRequirementType.NOT_DEFINED,
                                 CvssV3Data.CiaRequirementType.NOT_DEFINED, CvssV3Data.CiaRequirementType.NOT_DEFINED,
                                 CvssV3Data.ModifiedAttackVectorType.ADJACENT_NETWORK, CvssV3Data.ModifiedAttackComplexityType.NOT_DEFINED,
                                 CvssV3Data.ModifiedPrivilegesRequiredType.NOT_DEFINED, CvssV3Data.ModifiedUserInteractionType.NOT_DEFINED,
                                 CvssV3Data.ModifiedScopeType.NOT_DEFINED, CvssV3Data.ModifiedCiaType.NOT_DEFINED,
                                 CvssV3Data.ModifiedCiaType.NOT_DEFINED, CvssV3Data.ModifiedCiaType.NOT_DEFINED, 1.0,
                                 CvssV3Data.SeverityType.NONE);
                         final CvssV3 cvss = new CvssV3(null, null, cvssData, rsV.getDouble(19), rsV.getDouble(20));
                         vuln.setCvssV3(cvss);
                     }
                 } else {
                     LOGGER.debug(cve + " does not exist in the database");
                     return null;
                 }
             }
             try (PreparedStatement psCWE = getPreparedStatement(conn, SELECT_VULNERABILITY_CWE, cveId); ResultSet rsC = psCWE.executeQuery()) {
                 while (rsC.next()) {
                     vuln.addCwe(rsC.getString(1));
                 }
             }
             try (PreparedStatement psR = getPreparedStatement(conn, SELECT_REFERENCES, cveId); ResultSet rsR = psR.executeQuery()) {
                 while (rsR.next()) {
                     vuln.addReference(rsR.getString(1), rsR.getString(2), rsR.getString(3));
                 }
             }
             try (PreparedStatement psS = getPreparedStatement(conn, SELECT_SOFTWARE, cveId); ResultSet rsS = psS.executeQuery()) {
                 //1 part, 2 vendor, 3 product, 4 version, 5 update_version, 6 edition, 7 lang,
                 //8 sw_edition, 9 target_sw, 10 target_hw, 11 other, 12 versionEndExcluding,
                 //13 versionEndIncluding, 14 versionStartExcluding, 15 versionStartIncluding, 16 vulnerable
                 while (rsS.next()) {
                     vulnerableSoftwareBuilder.part(rsS.getString(1))
                             .vendor(rsS.getString(2))
                             .product(rsS.getString(3))
                             .version(rsS.getString(4))
                             .update(rsS.getString(5))
                             .edition(rsS.getString(6))
                             .language(rsS.getString(7))
                             .swEdition(rsS.getString(8))
                             .targetSw(rsS.getString(9))
                             .targetHw(rsS.getString(10))
                             .other(rsS.getString(11))
                             .versionEndExcluding(rsS.getString(12))
                             .versionEndIncluding(rsS.getString(13))
                             .versionStartExcluding(rsS.getString(14))
                             .versionStartIncluding(rsS.getString(15))
                             .vulnerable(rsS.getBoolean(16));
                     vuln.addVulnerableSoftware(vulnerableSoftwareBuilder.build());
                 }
             }
         } catch (SQLException ex) {
             throw new DatabaseException("Error retrieving " + cve, ex);
         } catch (CpeParsingException | CpeValidationException ex) {
             throw new DatabaseException("The database contains an invalid Vulnerable Software Entry", ex);
         }
         return vuln;
     }
 
     /**
      * Updates the vulnerability within the database. If the vulnerability does
      * not exist it will be added.
      *
      * @param cve the vulnerability from the NVD CVE Data Feed to add to the
      * database
      * @param baseEcosystem the ecosystem the CVE belongs to; this is based off
      * of things like the CVE description
      * @throws DatabaseException is thrown if the database
      */
     public void updateVulnerability(DefCveItem cve, String baseEcosystem) {
         clearCache();
         final String cveId = cve.getCve().getId();
         try {
             if (cve.getCve().getVulnStatus().toUpperCase().startsWith("REJECT")) {
                 deleteVulnerability(cveId);
             } else {
                 if (cveItemConverter.testCveCpeStartWithFilter(cve)) {
                     final String description = cveItemConverter.extractDescription(cve);
                     final int vulnerabilityId = updateOrInsertVulnerability(cve, description);
                     updateVulnerabilityInsertCwe(vulnerabilityId, cve);
                     updateVulnerabilityInsertReferences(vulnerabilityId, cve);
 
                     final List<VulnerableSoftware> software = parseCpes(cve);
                     updateVulnerabilityInsertSoftware(vulnerabilityId, cveId, software, baseEcosystem);
                 }
             }
         } catch (SQLException ex) {
             final String msg = String.format("Error updating '%s'; %s", cveId, ex.getMessage());
             LOGGER.debug(msg, ex);
             throw new DatabaseException(msg);
         } catch (CpeValidationException ex) {
             final String msg = String.format("Error parsing CPE entry from '%s'; %s", cveId, ex.getMessage());
             LOGGER.debug(msg, ex);
             throw new DatabaseException(msg);
         }
     }
 
     private void loadCpeEcosystemCache() {
         final Map<Pair<String, String>, String> map = new HashMap<>();
         try (Connection conn = databaseManager.getConnection();
                 PreparedStatement ps = getPreparedStatement(conn, SELECT_CPE_ECOSYSTEM);
                 ResultSet rs = ps.executeQuery()) {
             while (rs.next()) {
                 final Pair<String, String> key = new Pair<>(rs.getString(1), rs.getString(2));
                 final String value = rs.getString(3);
                 map.put(key, value);
             }
         } catch (SQLException ex) {
             final String msg = String.format("Error loading the Cpe Ecosystem Cache: %s", ex.getMessage());
             LOGGER.debug(msg, ex);
             throw new DatabaseException(msg, ex);
         }
         CpeEcosystemCache.setCache(map);
     }
 
     private void saveCpeEcosystemCache() {
         final Map<Pair<String, String>, String> map = CpeEcosystemCache.getChanged();
         if (map != null && !map.isEmpty()) {
             try (Connection conn = databaseManager.getConnection(); PreparedStatement ps = getPreparedStatement(conn, MERGE_CPE_ECOSYSTEM)) {
                 for (Map.Entry<Pair<String, String>, String> entry : map.entrySet()) {
                     ps.setString(1, entry.getKey().getLeft());
                     ps.setString(2, entry.getKey().getRight());
                     ps.setString(3, entry.getValue());
                     if (isBatchInsertEnabled()) {
                         ps.addBatch();
                     } else {
                         ps.execute();
                     }
                 }
                 if (isBatchInsertEnabled()) {
                     ps.executeBatch();
                 }
             } catch (SQLException ex) {
                 final String msg = String.format("Error saving the Cpe Ecosystem Cache: %s", ex.getMessage());
                 LOGGER.debug(msg, ex);
                 throw new DatabaseException(msg, ex);
             }
         }
     }
 
     /**
      * Used when updating a vulnerability - this method inserts the
      * vulnerability entry itself.
      *
      * @param cve the CVE data
      * @param description the description of the CVE entry
      * @return the vulnerability ID
      */
     private int updateOrInsertVulnerability(DefCveItem cve, String description) {
         if (CpeEcosystemCache.isEmpty()) {
             loadCpeEcosystemCache();
         }
         final int vulnerabilityId;
         try (Connection conn = databaseManager.getConnection(); PreparedStatement callUpdate = getPreparedStatement(conn, UPDATE_VULNERABILITY)) {
 //            String 1.cve, String 2.description, String 3.v2Severity, Float 4.v2ExploitabilityScore,
 //            Float 5.v2ImpactScore, Boolean 6.v2AcInsufInfo, Boolean 7.v2ObtainAllPrivilege,
 //            Boolean 8.v2ObtainUserPrivilege, Boolean 9.v2ObtainOtherPrivilege, Boolean 10.v2UserInteractionRequired,
 //            Float 11.v2Score, String 12.v2AccessVector, String 13.v2AccessComplexity,
 //            String 14.v2Authentication, String 15.v2ConfidentialityImpact, String 16.v2IntegrityImpact,
 //            String 17.v2AvailabilityImpact, String 18.v2Version, Float 19.v3ExploitabilityScore,
 //            Float 20.v3ImpactScore, String 21.v3AttackVector, String 22.v3AttackComplexity,
 //            String 23.v3PrivilegesRequired, String 24.v3UserInteraction, String 25.v3Scope,
 //            String 26.v3ConfidentialityImpact, String 27.v3IntegrityImpact, String 28.v3AvailabilityImpact,
 //            Float 29.v3BaseScore, String 30.v3BaseSeverity, String 31.v3Version
             callUpdate.setString(1, cve.getCve().getId());
             callUpdate.setString(2, description);
             Optional<CvssV2> optCvssv2 = null;
             if (cve.getCve().getMetrics() != null && cve.getCve().getMetrics().getCvssMetricV2() != null) {
                 optCvssv2 = cve.getCve().getMetrics().getCvssMetricV2().stream().sorted(Comparator.comparing(CvssV2::getType)).findFirst();
             }
             if (optCvssv2 != null && optCvssv2.isPresent()) {
                 final CvssV2 cvssv2 = optCvssv2.get();
                 setUpdateColumn(callUpdate, 3, cvssv2.getBaseSeverity());
                 setUpdateColumn(callUpdate, 4, cvssv2.getExploitabilityScore());
                 setUpdateColumn(callUpdate, 5, cvssv2.getImpactScore());
                 setUpdateColumn(callUpdate, 6, cvssv2.getAcInsufInfo());
                 setUpdateColumn(callUpdate, 7, cvssv2.getObtainAllPrivilege());
                 setUpdateColumn(callUpdate, 8, cvssv2.getObtainUserPrivilege());
                 setUpdateColumn(callUpdate, 9, cvssv2.getObtainOtherPrivilege());
                 setUpdateColumn(callUpdate, 10, cvssv2.getUserInteractionRequired());
                 setUpdateColumn(callUpdate, 11, cvssv2.getCvssData().getBaseScore());
                 setUpdateColumn(callUpdate, 12, cvssv2.getCvssData().getAccessVector());
                 setUpdateColumn(callUpdate, 13, cvssv2.getCvssData().getAccessComplexity());
                 setUpdateColumn(callUpdate, 14, cvssv2.getCvssData().getAuthentication());
                 setUpdateColumn(callUpdate, 15, cvssv2.getCvssData().getConfidentialityImpact());
                 setUpdateColumn(callUpdate, 16, cvssv2.getCvssData().getIntegrityImpact());
                 setUpdateColumn(callUpdate, 17, cvssv2.getCvssData().getAvailabilityImpact());
                 setUpdateColumn(callUpdate, 18, cvssv2.getCvssData().getVersion());
             } else {
                 callUpdate.setNull(3, java.sql.Types.VARCHAR);
                 callUpdate.setNull(4, java.sql.Types.DOUBLE);
                 callUpdate.setNull(5, java.sql.Types.DOUBLE);
                 callUpdate.setNull(6, java.sql.Types.VARCHAR);
                 //TODO this is may also be an issue for MS SQL, if an issue is created we'll just need
                 // to create an isMsSQL flag. See todo below in setUpdateColum
                 if (isOracle) {
                     callUpdate.setNull(7, java.sql.Types.BIT);
                     callUpdate.setNull(8, java.sql.Types.BIT);
                     callUpdate.setNull(9, java.sql.Types.BIT);
                     callUpdate.setNull(10, java.sql.Types.BIT);
                 } else {
                     callUpdate.setNull(7, java.sql.Types.BOOLEAN);
                     callUpdate.setNull(8, java.sql.Types.BOOLEAN);
                     callUpdate.setNull(9, java.sql.Types.BOOLEAN);
                     callUpdate.setNull(10, java.sql.Types.BOOLEAN);
                 }
                 callUpdate.setNull(11, java.sql.Types.DOUBLE);
                 callUpdate.setNull(12, java.sql.Types.VARCHAR);
                 callUpdate.setNull(13, java.sql.Types.VARCHAR);
                 callUpdate.setNull(14, java.sql.Types.VARCHAR);
                 callUpdate.setNull(15, java.sql.Types.VARCHAR);
                 callUpdate.setNull(16, java.sql.Types.VARCHAR);
                 callUpdate.setNull(17, java.sql.Types.VARCHAR);
                 callUpdate.setNull(18, java.sql.Types.VARCHAR);
             }
             Optional<CvssV3> optCvssv30 = Optional.empty();
             if (cve.getCve().getMetrics() != null && cve.getCve().getMetrics().getCvssMetricV30() != null) {
                 optCvssv30 = cve.getCve().getMetrics().getCvssMetricV30().stream().sorted(Comparator.comparing(CvssV3::getType)).findFirst();
             }
             Optional<CvssV3> optCvssv31 = Optional.empty();
             if (cve.getCve().getMetrics() != null && cve.getCve().getMetrics().getCvssMetricV31() != null) {
                 optCvssv31 = cve.getCve().getMetrics().getCvssMetricV31().stream().sorted(Comparator.comparing(CvssV3::getType)).findFirst();
             }
 
             CvssV3 cvssv3 = null;
             if (optCvssv31.isPresent()) {
                 cvssv3 = optCvssv31.get();
             } else if (optCvssv30.isPresent()) {
                 cvssv3 = optCvssv30.get();
             }
             if (cvssv3 != null) {
                 setUpdateColumn(callUpdate, 19, cvssv3.getExploitabilityScore());
                 setUpdateColumn(callUpdate, 20, cvssv3.getImpactScore());
                 setUpdateColumn(callUpdate, 21, cvssv3.getCvssData().getAttackVector());
                 setUpdateColumn(callUpdate, 22, cvssv3.getCvssData().getAttackComplexity());
                 setUpdateColumn(callUpdate, 23, cvssv3.getCvssData().getPrivilegesRequired());
                 setUpdateColumn(callUpdate, 24, cvssv3.getCvssData().getUserInteraction());
                 setUpdateColumn(callUpdate, 25, cvssv3.getCvssData().getScope());
                 setUpdateColumn(callUpdate, 26, cvssv3.getCvssData().getConfidentialityImpact());
                 setUpdateColumn(callUpdate, 27, cvssv3.getCvssData().getIntegrityImpact());
                 setUpdateColumn(callUpdate, 28, cvssv3.getCvssData().getAvailabilityImpact());
                 setUpdateColumn(callUpdate, 29, cvssv3.getCvssData().getBaseScore());
                 setUpdateColumn(callUpdate, 30, cvssv3.getCvssData().getBaseSeverity());
                 setUpdateColumn(callUpdate, 31, cvssv3.getCvssData().getVersion());
             } else {
                 callUpdate.setNull(19, java.sql.Types.DOUBLE);
                 callUpdate.setNull(20, java.sql.Types.DOUBLE);
                 callUpdate.setNull(21, java.sql.Types.VARCHAR);
                 callUpdate.setNull(22, java.sql.Types.VARCHAR);
                 callUpdate.setNull(23, java.sql.Types.VARCHAR);
                 callUpdate.setNull(24, java.sql.Types.VARCHAR);
                 callUpdate.setNull(25, java.sql.Types.VARCHAR);
                 callUpdate.setNull(26, java.sql.Types.VARCHAR);
                 callUpdate.setNull(27, java.sql.Types.VARCHAR);
                 callUpdate.setNull(28, java.sql.Types.VARCHAR);
                 callUpdate.setNull(29, java.sql.Types.DOUBLE);
                 callUpdate.setNull(30, java.sql.Types.VARCHAR);
                 callUpdate.setNull(31, java.sql.Types.VARCHAR);
             }
             if (isOracle) {
                 try {
                     final CallableStatement cs = (CallableStatement) callUpdate;
                     cs.registerOutParameter(32, JDBCType.INTEGER);
                     cs.executeUpdate();
                     vulnerabilityId = cs.getInt(32);
                 } catch (SQLException ex) {
                     final String msg = String.format("Unable to retrieve id for new vulnerability for '%s'", cve.getCve().getId());
                     throw new DatabaseException(msg, ex);
                 }
             } else {
                 try (ResultSet rs = callUpdate.executeQuery()) {
                     rs.next();
                     vulnerabilityId = rs.getInt(1);
                 } catch (SQLException ex) {
                     final String msg = String.format("Unable to retrieve id for new vulnerability for '%s'", cve.getCve().getId());
                     throw new DatabaseException(msg, ex);
                 }
             }
         } catch (SQLException ex) {
             throw new UnexpectedAnalysisException(ex);
         }
         return vulnerabilityId;
     }
 
     /**
      * Used when updating a vulnerability - this method inserts the CWE entries.
      *
      * @param vulnerabilityId the vulnerability ID
      * @param cve the CVE entry that contains the CWE entries to insert
      * @throws SQLException thrown if there is an error inserting the data
      */
     private void updateVulnerabilityInsertCwe(int vulnerabilityId, DefCveItem cve) throws SQLException {
         if (cve.getCve() != null && cve.getCve().getWeaknesses() != null) {
             try (Connection conn = databaseManager.getConnection();
                     PreparedStatement insertCWE = getPreparedStatement(conn, INSERT_CWE, vulnerabilityId)) {
                 for (Weakness weakness : cve.getCve().getWeaknesses()) {
                     for (LangString desc : weakness.getDescription()) {
                         if ("en".equals(desc.getLang())) {
                             insertCWE.setString(2, desc.getValue());
                             if (isBatchInsertEnabled()) {
                                 insertCWE.addBatch();
                             } else {
                                 insertCWE.execute();
                             }
                         }
                     }
                 }
                 if (isBatchInsertEnabled()) {
                     insertCWE.executeBatch();
                 }
             }
         }
     }
 
     /**
      * Used when updating a vulnerability - in some cases a CVE needs to be
      * removed.
      *
      * @param cve the vulnerability CVE
      * @throws SQLException thrown if there is an error deleting the
      * vulnerability
      */
     private void deleteVulnerability(String cve) throws SQLException {
         try (Connection conn = databaseManager.getConnection();
                 PreparedStatement deleteVulnerability = getPreparedStatement(conn, DELETE_VULNERABILITY, cve)) {
             deleteVulnerability.executeUpdate();
         }
     }
 
     /**
      * Merges the list of known exploited vulnerabilities into the database.
      *
      * @param vulnerabilities the list of known exploited vulnerabilities
      * @throws DatabaseException thrown if there is an exception... duh..
      * @throws SQLException thrown if there is an exception... duh..
      */
     public void updateKnownExploitedVulnerabilities(
             List<org.owasp.dependencycheck.data.knownexploited.json.Vulnerability> vulnerabilities)
             throws DatabaseException, SQLException {
         try (Connection conn = databaseManager.getConnection();
                 PreparedStatement mergeKnownVulnerability = getPreparedStatement(conn, MERGE_KNOWN_EXPLOITED)) {
             int ctr = 0;
             for (org.owasp.dependencycheck.data.knownexploited.json.Vulnerability v : vulnerabilities) {
                 mergeKnownVulnerability.setString(1, v.getCveID());
                 addNullableStringParameter(mergeKnownVulnerability, 2, v.getVendorProject());
                 addNullableStringParameter(mergeKnownVulnerability, 3, v.getProduct());
                 addNullableStringParameter(mergeKnownVulnerability, 4, v.getVulnerabilityName());
                 addNullableStringParameter(mergeKnownVulnerability, 5, v.getDateAdded());
                 addNullableStringParameter(mergeKnownVulnerability, 6, v.getShortDescription());
                 addNullableStringParameter(mergeKnownVulnerability, 7, v.getRequiredAction());
                 addNullableStringParameter(mergeKnownVulnerability, 8, v.getDueDate());
                 addNullableStringParameter(mergeKnownVulnerability, 9, v.getNotes());
                 if (isBatchInsertEnabled()) {
                     mergeKnownVulnerability.addBatch();
                     ctr++;
                     if (ctr >= getBatchSize()) {
                         mergeKnownVulnerability.executeBatch();
                         ctr = 0;
                     }
                 } else {
                     try {
                         mergeKnownVulnerability.execute();
                     } catch (SQLException ex) {
                         if (ex.getMessage().contains("Duplicate entry")) {
                             final String msg = String.format("Duplicate known exploited vulnerability key identified in '%s'", v.getCveID());
                             LOGGER.info(msg, ex);
                         } else {
                             throw ex;
                         }
                     }
                 }
             }
             if (isBatchInsertEnabled()) {
                 mergeKnownVulnerability.executeBatch();
             }
         }
     }
 
     /**
      * Used when updating a vulnerability - this method inserts the list of
      * vulnerable software.
      *
      * @param vulnerabilityId the vulnerability id
      * @param cveId the CVE ID - used for reporting
      * @param software the list of vulnerable software
      * @param baseEcosystem the ecosystem based off of the vulnerability
      * description
      * @throws DatabaseException thrown if there is an error inserting the data
      * @throws SQLException thrown if there is an error inserting the data
      */
     private void updateVulnerabilityInsertSoftware(int vulnerabilityId, String cveId,
             List<VulnerableSoftware> software, String baseEcosystem)
             throws DatabaseException, SQLException {
         try (Connection conn = databaseManager.getConnection(); PreparedStatement insertSoftware = getPreparedStatement(conn, INSERT_SOFTWARE)) {
             for (VulnerableSoftware parsedCpe : software) {
                 insertSoftware.setInt(1, vulnerabilityId);
                 insertSoftware.setString(2, parsedCpe.getPart().getAbbreviation());
                 insertSoftware.setString(3, parsedCpe.getVendor());
                 insertSoftware.setString(4, parsedCpe.getProduct());
                 insertSoftware.setString(5, parsedCpe.getVersion());
                 insertSoftware.setString(6, parsedCpe.getUpdate());
                 insertSoftware.setString(7, parsedCpe.getEdition());
                 insertSoftware.setString(8, parsedCpe.getLanguage());
                 insertSoftware.setString(9, parsedCpe.getSwEdition());
                 insertSoftware.setString(10, parsedCpe.getTargetSw());
                 insertSoftware.setString(11, parsedCpe.getTargetHw());
                 insertSoftware.setString(12, parsedCpe.getOther());
                 final String ecosystem = CpeEcosystemCache.getEcosystem(parsedCpe.getVendor(), parsedCpe.getProduct(),
                         cveItemConverter.extractEcosystem(baseEcosystem, parsedCpe));
 
                 addNullableStringParameter(insertSoftware, 13, ecosystem);
                 addNullableStringParameter(insertSoftware, 14, parsedCpe.getVersionEndExcluding());
                 addNullableStringParameter(insertSoftware, 15, parsedCpe.getVersionEndIncluding());
                 addNullableStringParameter(insertSoftware, 16, parsedCpe.getVersionStartExcluding());
                 addNullableStringParameter(insertSoftware, 17, parsedCpe.getVersionStartIncluding());
                 insertSoftware.setBoolean(18, parsedCpe.isVulnerable());
 
                 if (isBatchInsertEnabled()) {
                     insertSoftware.addBatch();
                 } else {
                     try {
                         insertSoftware.execute();
                     } catch (SQLException ex) {
                         if (ex.getMessage().contains("Duplicate entry")) {
                             final String msg = String.format("Duplicate software key identified in '%s'", cveId);
                             LOGGER.info(msg, ex);
                         } else {
                             throw ex;
                         }
                     }
                 }
             }
             if (isBatchInsertEnabled()) {
                 executeBatch(cveId, insertSoftware);
             }
         }
     }
 
     /**
      * Used when updating a vulnerability - this method inserts the list of
      * references.
      *
      * @param vulnerabilityId the vulnerability id
      * @param cve the CVE entry that contains the list of references
      * @throws SQLException thrown if there is an error inserting the data
      */
     private void updateVulnerabilityInsertReferences(int vulnerabilityId, DefCveItem cve) throws SQLException {
         try (Connection conn = databaseManager.getConnection(); PreparedStatement insertReference = getPreparedStatement(conn, INSERT_REFERENCE)) {
             if (cve.getCve().getReferences() != null) {
                 for (Reference r : cve.getCve().getReferences()) {
                     insertReference.setInt(1, vulnerabilityId);
                     String name = null;
                     if (r.getTags() != null) {
                         name = r.getTags().stream().sorted().collect(Collectors.joining(",")).toUpperCase().replaceAll("\\s", "_");
                     }
                     if (name != null) {
                         insertReference.setString(2, name);
                     } else {
                         insertReference.setNull(2, java.sql.Types.VARCHAR);
                     }
                     if (r.getUrl() != null && !r.getUrl().isEmpty()) {
                         insertReference.setString(3, r.getUrl());
                     } else {
                         insertReference.setNull(3, java.sql.Types.VARCHAR);
                     }
                     if (r.getSource() != null && !r.getSource().isEmpty()) {
                         insertReference.setString(4, r.getSource());
                     } else {
                         insertReference.setNull(4, java.sql.Types.VARCHAR);
                     }
                     if (isBatchInsertEnabled()) {
                         insertReference.addBatch();
                     } else {
                         insertReference.execute();
                     }
                 }
             }
             if (isBatchInsertEnabled()) {
                 insertReference.executeBatch();
             }
         }
     }
 
     /**
      * Parses the configuration entries from the CVE entry into a list of
      * VulnerableSoftware objects.
      *
      * @param cve the CVE to parse the vulnerable software entries from
      * @return the list of vulnerable software
      * @throws CpeValidationException if an invalid CPE is present
      */
     private List<VulnerableSoftware> parseCpes(DefCveItem cve) throws CpeValidationException {
         final List<VulnerableSoftware> software = new ArrayList<>();
 
         final List<CpeMatch> cpeEntries = cve.getCve().getConfigurations().stream()
                 .map(Config::getNodes)
                 .flatMap(List::stream)
                 .map(Node::getCpeMatch)
                 .flatMap(List::stream)
                 .filter(predicate -> predicate.getCriteria() != null)
                 .filter(predicate -> predicate.getCriteria().startsWith(cpeStartsWithFilter))
                 //this single CPE entry causes nearly 100% FP - so filtering it at the source.
                 .filter(entry -> !("CVE-2009-0754".equals(cve.getCve().getId())
                 && "cpe:2.3:a:apache:apache:*:*:*:*:*:*:*:*".equals(entry.getCriteria())))
                 .collect(Collectors.toList());
         final VulnerableSoftwareBuilder builder = new VulnerableSoftwareBuilder();
 
         try {
             cpeEntries.forEach(entry -> {
                 builder.cpe(parseCpe(entry, cve.getCve().getId()))
                         .versionEndExcluding(entry.getVersionEndExcluding())
                         .versionStartExcluding(entry.getVersionStartExcluding())
                         .versionEndIncluding(entry.getVersionEndIncluding())
                         .versionStartIncluding(entry.getVersionStartIncluding())
                         .vulnerable(entry.getVulnerable());
                 try {
                     software.add(builder.build());
                 } catch (CpeValidationException ex) {
                     throw new LambdaExceptionWrapper(ex);
                 }
             });
         } catch (LambdaExceptionWrapper ex) {
             throw (CpeValidationException) ex.getCause();
         }
         return software;
     }
 
     /**
      * Helper method to convert a CpeMatch (generated code used in parsing the
      * NVD JSON) into a CPE object.
      *
      * @param cpe the CPE Match
      * @param cveId the CVE associated with the CPEMatch - used for error
      * reporting
      * @return the resulting CPE object
      * @throws DatabaseException thrown if there is an error converting the
      * CpeMatch into a CPE object
      */
     private Cpe parseCpe(CpeMatch cpe, String cveId) throws DatabaseException {
         final Cpe parsedCpe;
         try {
             //the replace is a hack as the NVD does not properly escape backslashes in their JSON
             parsedCpe = CpeParser.parse(cpe.getCriteria(), true);
         } catch (CpeParsingException ex) {
             LOGGER.debug("NVD (" + cveId + ") contain an invalid 2.3 CPE: " + cpe.getCriteria());
             throw new DatabaseException("Unable to parse CPE: " + cpe.getCriteria(), ex);
         }
         return parsedCpe;
     }
 
     /**
      * Returns the size of the batch.
      *
      * @return the size of the batch
      */
     private int getBatchSize() {
         int max;
         try {
             max = settings.getInt(Settings.KEYS.MAX_BATCH_SIZE);
         } catch (InvalidSettingException pE) {
             max = 1000;
         }
         return max;
     }
 
     /**
      * Determines whether or not batch insert is enabled.
      *
      * @return <code>true</code> if batch insert is enabled; otherwise
      * <code>false</code>
      */
     private boolean isBatchInsertEnabled() {
         boolean batch;
         try {
             batch = settings.getBoolean(Settings.KEYS.ENABLE_BATCH_UPDATES);
         } catch (InvalidSettingException pE) {
             //If there's no configuration, default is to not perform batch inserts
             batch = false;
         }
         return batch;
     }
 
     /**
      * Executes batch inserts of vulnerabilities when property
      * database.batchinsert.maxsize is reached.
      *
      * @param vulnId the vulnerability ID
      * @param statement the prepared statement to batch execute
      * @throws SQLException thrown when the batch cannot be executed
      */
     private void executeBatch(String vulnId, PreparedStatement statement)
             throws SQLException {
         try {
             statement.executeBatch();
         } catch (SQLException ex) {
             if (ex.getMessage().contains("Duplicate entry")) {
                 final String msg = String.format("Duplicate software key identified in '%s'",
                         vulnId);
                 LOGGER.info(msg, ex);
             } else {
                 throw ex;
             }
         }
     }
 
     /**
      * Checks to see if data exists so that analysis can be performed.
      *
      * @return <code>true</code> if data exists; otherwise <code>false</code>
      */
     public boolean dataExists() {
         try (Connection conn = databaseManager.getConnection();
                 PreparedStatement cs = getPreparedStatement(conn, COUNT_CPE);
                 ResultSet rs = cs.executeQuery()) {
             if (rs.next() && rs.getInt(1) > 0) {
                 return true;
             }
         } catch (Exception ex) {
             String dd;
             try {
                 dd = settings.getDataDirectory().getAbsolutePath();
             } catch (IOException ex1) {
                 dd = settings.getString(Settings.KEYS.DATA_DIRECTORY);
             }
             LOGGER.error("Unable to access the local database.\n\nEnsure that '{}' is a writable directory. "
                     + "If the problem persist try deleting the files in '{}' and running {} again. If the problem continues, please "
                     + "create a log file (see documentation at https://jeremylong.github.io/DependencyCheck/) and open a ticket at "
                     + "https://github.com/jeremylong/DependencyCheck/issues and include the log file.\n\n",
                     dd, dd, settings.getString(Settings.KEYS.APPLICATION_NAME));
             LOGGER.debug("", ex);
         }
         return false;
     }
 
     /**
      * It is possible that orphaned rows may be generated during database
      * updates. This should be called after all updates have been completed to
      * ensure orphan entries are removed.
      */
     public void cleanupDatabase() {
         LOGGER.info("Begin database maintenance");
         final long start = System.currentTimeMillis();
         try (Connection conn = databaseManager.getConnection();
                 PreparedStatement psOrphans = getPreparedStatement(conn, CLEANUP_ORPHANS);
                 PreparedStatement psEcosystem = getPreparedStatement(conn, UPDATE_ECOSYSTEM);
                 PreparedStatement psEcosystem2 = getPreparedStatement(conn, UPDATE_ECOSYSTEM2)) {
             if (psEcosystem != null) {
                 final int count = psEcosystem.executeUpdate();
                 if (count > 0) {
                     LOGGER.info("Updated the CPE ecosystem on {} NVD records", count);
                 }
             }
             if (psEcosystem2 != null) {
                 final int count = psEcosystem2.executeUpdate();
                 if (count > 0) {
                     LOGGER.info("Removed the CPE ecosystem on {} NVD records", count);
                 }
             }
             if (psOrphans != null) {
                 final int count = psOrphans.executeUpdate();
                 if (count > 0) {
                     LOGGER.info("Cleaned up {} orphaned NVD records", count);
                 }
             }
             final long millis = System.currentTimeMillis() - start;
             //final long seconds = TimeUnit.MILLISECONDS.toSeconds(millis);
             LOGGER.info("End database maintenance ({} ms)", millis);
         } catch (SQLException ex) {
             LOGGER.error("An unexpected SQL Exception occurred; please see the verbose log for more details.");
             LOGGER.debug("", ex);
             throw new DatabaseException("Unexpected SQL Exception", ex);
         }
     }
 
     /**
      * Persist the EcosystemCache into the database.
      */
     public void persistEcosystemCache() {
         saveCpeEcosystemCache();
         clearCache();
     }
 
     /**
      * If the database is using an H2 file based database calling
      * <code>defrag()</code> will de-fragment the database.
      */
     public void defrag() {
         if (isH2) {
             final long start = System.currentTimeMillis();
             try (Connection conn = databaseManager.getConnection(); CallableStatement psCompaxt = conn.prepareCall("SHUTDOWN DEFRAG")) {
                 LOGGER.info("Begin database defrag");
                 psCompaxt.execute();
                 final long millis = System.currentTimeMillis() - start;
                 //final long seconds = TimeUnit.MILLISECONDS.toSeconds(millis);
                 LOGGER.info("End database defrag ({} ms)", millis);
             } catch (SQLException ex) {
                 LOGGER.error("An unexpected SQL Exception occurred compacting the database; please see the verbose log for more details.");
                 LOGGER.debug("", ex);
             }
         }
     }
 
     /**
      * Determines if the given identifiedVersion is affected by the given cpeId
      * and previous version flag. A non-null, non-empty string passed to the
      * previous version argument indicates that all previous versions are
      * affected.
      *
      * @param cpe the CPE for the given dependency
      * @param vulnerableSoftware a set of the vulnerable software
      * @return true if the identified version is affected, otherwise false
      */
     VulnerableSoftware getMatchingSoftware(Cpe cpe, Set<VulnerableSoftware> vulnerableSoftware) {
         VulnerableSoftware matched = null;
         for (VulnerableSoftware vs : vulnerableSoftware) {
             if (vs.matches(cpe)) {
                 if (matched == null) {
                     matched = vs;
                 } else {
                     if ("*".equals(vs.getWellFormedUpdate()) && !"*".equals(matched.getWellFormedUpdate())) {
                         matched = vs;
                     }
                 }
             }
         }
         return matched;
     }
 
     /**
      * This method is only referenced in unused code.
      * <p>
      * Deletes unused dictionary entries from the database.
      * </p>
      */
     public void deleteUnusedCpe() {
         clearCache();
         try (Connection conn = databaseManager.getConnection(); PreparedStatement ps = getPreparedStatement(conn, DELETE_UNUSED_DICT_CPE)) {
             ps.executeUpdate();
         } catch (SQLException ex) {
             LOGGER.error("Unable to delete CPE dictionary entries", ex);
         }
     }
 
     /**
      * This method is only referenced in unused code and will likely break on
      * MySQL if ever used due to the MERGE statement.
      * <p>
      * Merges CPE entries into the database.
      * </p>
      *
      * @param cpe the CPE identifier
      * @param vendor the CPE vendor
      * @param product the CPE product
      */
     public void addCpe(String cpe, String vendor, String product) {
         clearCache();
         try (Connection conn = databaseManager.getConnection(); PreparedStatement ps = getPreparedStatement(conn, ADD_DICT_CPE)) {
             ps.setString(1, cpe);
             ps.setString(2, vendor);
             ps.setString(3, product);
             ps.executeUpdate();
         } catch (SQLException ex) {
             LOGGER.error("Unable to add CPE dictionary entry", ex);
         }
     }
 
     /**
      * Returns a map of known exploited vulnerabilities.
      *
      * @return a map of known exploited vulnerabilities
      */
     public Map<String, org.owasp.dependencycheck.data.knownexploited.json.Vulnerability> getknownExploitedVulnerabilities() {
         final Map<String, org.owasp.dependencycheck.data.knownexploited.json.Vulnerability> known = new HashMap<>();
 
         try (Connection conn = databaseManager.getConnection();
                 PreparedStatement ps = getPreparedStatement(conn, SELECT_KNOWN_EXPLOITED_VULNERABILITIES);
                 ResultSet rs = ps.executeQuery()) {
 
             while (rs.next()) {
                 final org.owasp.dependencycheck.data.knownexploited.json.Vulnerability kev =
                         new org.owasp.dependencycheck.data.knownexploited.json.Vulnerability();
                 kev.setCveID(rs.getString(1));
                 kev.setVendorProject(rs.getString(2));
                 kev.setProduct(rs.getString(3));
                 kev.setVulnerabilityName(rs.getString(4));
                 kev.setDateAdded(rs.getString(5));
                 kev.setShortDescription(rs.getString(6));
                 kev.setRequiredAction(rs.getString(7));
                 kev.setDueDate(rs.getString(8));
                 kev.setNotes(rs.getString(9));
                 known.put(kev.getCveID(), kev);
             }
 
         } catch (SQLException ex) {
             throw new DatabaseException(ex);
         }
         return known;
     }
 
     /**
      * Helper method to add a nullable string parameter.
      *
      * @param ps the prepared statement
      * @param pos the position of the parameter
      * @param value the value of the parameter
      * @throws SQLException thrown if there is an error setting the parameter.
      */
     private void addNullableStringParameter(PreparedStatement ps, int pos, String value) throws SQLException {
         if (value == null || value.isEmpty()) {
             ps.setNull(pos, java.sql.Types.VARCHAR);
         } else {
             ps.setString(pos, value);
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, Double value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.DOUBLE);
         } else {
             ps.setDouble(i, value);
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV2Data.AuthenticationType value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV2Data.CiaType value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV2Data.Version value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV2Data.AccessComplexityType value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV2Data.AccessVectorType value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, String value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value);
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, Boolean value) throws SQLException {
         if (value == null) {
             //TODO this is may also be an issue for MS SQL, if an issue is created we'll just need
             // to create an isMsSQL flag. See todo above in updateOrInsertVulnerability.
             if (isOracle) {
                 ps.setNull(i, java.sql.Types.BIT);
             } else {
                 ps.setNull(i, java.sql.Types.BOOLEAN);
             }
         } else {
             ps.setBoolean(i, value);
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV3Data.AttackVectorType value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV3Data.AttackComplexityType value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV3Data.PrivilegesRequiredType value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV3Data.UserInteractionType value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV3Data.ScopeType value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV3Data.SeverityType value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV3Data.CiaType value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     private void setUpdateColumn(PreparedStatement ps, int i, CvssV3Data.Version value) throws SQLException {
         if (value == null) {
             ps.setNull(i, java.sql.Types.VARCHAR);
         } else {
             ps.setString(i, value.value());
         }
     }
 
     /**
      * Sets the float parameter on a prepared statement from a properties map.
      *
      * @param ps a prepared statement
      * @param i the index of the property
      * @param props the property collection
      * @param key the property key
      * @throws SQLException thrown if there is an error adding the property
      */
     private void setFloatValue(PreparedStatement ps, int i, Map<String, Object> props, String key) throws SQLException {
         if (props != null && props.containsKey(key)) {
             try {
                 ps.setFloat(i, Float.parseFloat(props.get(key).toString()));
             } catch (NumberFormatException nfe) {
                 ps.setNull(i, java.sql.Types.FLOAT);
             }
         } else {
             ps.setNull(i, java.sql.Types.FLOAT);
         }
     }
 
     /**
      * Sets the string parameter on a prepared statement from a properties map.
      *
      * @param ps a prepared statement
      * @param i the index of the property
      * @param props the property collection
      * @param key the property key
      * @throws SQLException thrown if there is an error adding the property
      */
     private void setStringValue(PreparedStatement ps, int i, Map<String, Object> props, String key) throws SQLException {
         if (props != null && props.containsKey(key)) {
             ps.setString(i, props.get(key).toString());
         } else {
             ps.setNull(i, java.sql.Types.VARCHAR);
         }
     }
 
     /**
      * Sets the boolean parameter on a prepared statement from a properties map.
      *
      * @param ps a prepared statement
      * @param i the index of the property
      * @param props the property collection
      * @param key the property key
      * @throws SQLException thrown if there is an error adding the property
      */
     private void setBooleanValue(PreparedStatement ps, int i, Map<String, Object> props, String key) throws SQLException {
         if (props != null && props.containsKey(key)) {
             ps.setBoolean(i, Boolean.parseBoolean(props.get(key).toString()));
         } else {
             ps.setNull(i, java.sql.Types.BOOLEAN);
         }
     }
 
     /**
      * Returns the Boolean value for the given index; if the value is null then
      * null is returned.
      *
      * @param rs the record set
      * @param index the parameter index
      * @return the Boolean value; or null
      * @throws SQLException thrown if there is an error obtaining the value
      */
     @SuppressFBWarnings("NP_BOOLEAN_RETURN_NULL")
     private Boolean getBooleanValue(ResultSet rs, int index) throws SQLException {
         if (rs.getObject(index) == null) {
             return null;
         }
         return rs.getBoolean(index);
     }
 
     /**
      * Returns the Float value for the given index; if the value is null then
      * null is returned.
      *
      * @param rs the record set
      * @param index the parameter index
      * @return the Float value; or null
      * @throws SQLException thrown if there is an error obtaining the value
      */
     private Float getFloatValue(ResultSet rs, int index) throws SQLException {
         if (rs.getObject(index) == null) {
             return null;
         }
         return rs.getFloat(index);
     }
 }