View Javadoc

    1   /*
    2    * This file is part of dependency-check-core.
    3    *
    4    * Dependency-check-core is free software: you can redistribute it and/or modify it
    5    * under the terms of the GNU General Public License as published by the Free
    6    * Software Foundation, either version 3 of the License, or (at your option) any
    7    * later version.
    8    *
    9    * Dependency-check-core is distributed in the hope that it will be useful, but
    10   * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
    11   * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
    12   * details.
    13   *
    14   * You should have received a copy of the GNU General Public License along with
    15   * dependency-check-core. If not, see http://www.gnu.org/licenses/.
    16   *
    17   * Copyright (c) 2012 Jeremy Long. All Rights Reserved.
    18   */
    19  package org.owasp.dependencycheck.data.nvdcve;
    20
    21  import java.io.BufferedReader;
    22  import java.io.File;
    23  import java.io.IOException;
    24  import java.io.InputStream;
    25  import java.io.InputStreamReader;
    26  import java.io.UnsupportedEncodingException;
    27  import java.sql.Connection;
    28  import java.sql.DriverManager;
    29  import java.sql.PreparedStatement;
    30  import java.sql.ResultSet;
    31  import java.sql.SQLException;
    32  import java.sql.Statement;
    33  import java.util.ArrayList;
    34  import java.util.HashSet;
    35  import java.util.List;
    36  import java.util.Set;
    37  import java.util.logging.Level;
    38  import java.util.logging.Logger;
    39  import org.owasp.dependencycheck.data.cwe.CweDB;
    40  import org.owasp.dependencycheck.dependency.Reference;
    41  import org.owasp.dependencycheck.dependency.Vulnerability;
    42  import org.owasp.dependencycheck.dependency.VulnerableSoftware;
    43  import org.owasp.dependencycheck.utils.DependencyVersion;
    44  import org.owasp.dependencycheck.utils.DependencyVersionUtil;
    45  import org.owasp.dependencycheck.utils.Settings;
    46
    47  /**
    48   * The database holding information about the NVD CVE data.
    49   *
    50   * @author Jeremy Long (jeremy.long@owasp.org)
    51   */
    52  public class CveDB {
    53
    54      /**
    55       * Resource location for SQL file used to create the database schema.
    56       */
    57      public static final String DB_STRUCTURE_RESOURCE = "data/initialize.sql";
    58      /**
    59       * The version of the current DB Schema.
    60       */
    61      public static final String DB_SCHEMA_VERSION = "2.6";
    62      /**
    63       * Database connection
    64       */
    65      private Connection conn;
    66      //<editor-fold defaultstate="collapsed" desc="Constants to create, maintain, and retrieve data from the CVE Database">
    67      /**
    68       * SQL Statement to delete references by vulnerability ID.
    69       */
    70      public static final String DELETE_REFERENCE = "DELETE FROM reference WHERE cveid = ?";
    71      /**
    72       * SQL Statement to delete software by vulnerability ID.
    73       */
    74      public static final String DELETE_SOFTWARE = "DELETE FROM software WHERE cveid = ?";
    75      /**
    76       * SQL Statement to delete a vulnerability by CVE.
    77       */
    78      public static final String DELETE_VULNERABILITY = "DELETE FROM vulnerability WHERE cve = ?";
    79      /**
    80       * SQL Statement to cleanup orphan entries. Yes, the db schema could be a
    81       * little tighter, but what we have works well to keep the data file size
    82       * down a bit.
    83       */
    84      public static final String CLEANUP_ORPHANS = "DELETE FROM CpeEntry WHERE id not in (SELECT CPEEntryId FROM Software); ";
    85      /**
    86       * SQL Statement to insert a new reference.
    87       */
    88      public static final String INSERT_REFERENCE = "INSERT INTO reference (cveid, name, url, source) VALUES (?, ?, ?, ?)";
    89      /**
    90       * SQL Statement to insert a new software.
    91       */
    92      public static final String INSERT_SOFTWARE = "INSERT INTO software (cveid, cpeEntryId, previousVersion) VALUES (?, ?, ?)";
    93      /**
    94       * SQL Statement to insert a new cpe.
    95       */
    96      public static final String INSERT_CPE = "INSERT INTO cpeEntry (cpe, vendor, product) VALUES (?, ?, ?)";
    97      /**
    98       * SQL Statement to get a CPEProductID.
    99       */
    100     public static final String SELECT_CPE_ID = "SELECT id FROM cpeEntry WHERE cpe = ?";
    101     /**
    102      * SQL Statement to insert a new vulnerability.
    103      */
    104     public static final String INSERT_VULNERABILITY = "INSERT INTO vulnerability (cve, description, cwe, cvssScore, cvssAccessVector, "
    105             + "cvssAccessComplexity, cvssAuthentication, cvssConfidentialityImpact, cvssIntegrityImpact, cvssAvailabilityImpact) "
    106             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    107     /**
    108      * SQL Statement to update a vulnerability.
    109      */
    110     public static final String UPDATE_VULNERABILITY = "UPDATE vulnerability SET description=?, cwe=?, cvssScore=?, cvssAccessVector=?, "
    111             + "cvssAccessComplexity=?, cvssAuthentication=?, cvssConfidentialityImpact=?, cvssIntegrityImpact=?, cvssAvailabilityImpact=? "
    112             + "WHERE id=?";
    113     /**
    114      * SQL Statement to find CVE entries based on CPE data.
    115      */
    116     public static final String SELECT_CVE_FROM_SOFTWARE = "SELECT cve, cpe, previousVersion "
    117             + "FROM software INNER JOIN vulnerability ON vulnerability.id = software.cveId "
    118             + "INNER JOIN cpeEntry ON cpeEntry.id = software.cpeEntryId "
    119             + "WHERE vendor = ? AND product = ?";
    120     //unfortunately, the version info is too complicated to do in a select. Need to filter this afterwards
    121     //        + " AND (version = '-' OR previousVersion IS NOT NULL OR version=?)";
    122     //
    123     /**
    124      * SQL Statement to find the CPE entry based on the vendor and product.
    125      */
    126     public static final String SELECT_CPE_ENTRIES = "SELECT cpe FROM cpeEntry WHERE vendor = ? AND product = ?";
    127     /**
    128      * SQL Statement to select references by CVEID.
    129      */
    130     public static final String SELECT_REFERENCE = "SELECT source, name, url FROM reference WHERE cveid = ?";
    131     /**
    132      * SQL Statement to select software by CVEID.
    133      */
    134     public static final String SELECT_SOFTWARE = "SELECT cpe, previousVersion "
    135             + "FROM software INNER JOIN cpeEntry ON software.cpeEntryId = cpeEntry.id WHERE cveid = ?";
    136 //    public static final String SELECT_SOFTWARE = "SELECT part, vendor, product, version, revision, previousVersion "
    137 //            + "FROM software INNER JOIN cpeProduct ON cpeProduct.id = software.cpeProductId LEFT JOIN cpeVersion ON "
    138 //            + "software.cpeVersionId = cpeVersion.id LEFT JOIN Version ON cpeVersion.versionId = version.id WHERE cveid = ?";
    139     /**
    140      * SQL Statement to select a vulnerability by CVEID.
    141      */
    142     public static final String SELECT_VULNERABILITY = "SELECT id, description, cwe, cvssScore, cvssAccessVector, cvssAccessComplexity, "
    143             + "cvssAuthentication, cvssConfidentialityImpact, cvssIntegrityImpact, cvssAvailabilityImpact FROM vulnerability WHERE cve = ?";
    144     /**
    145      * SQL Statement to select a vulnerability's primary key.
    146      */
    147     public static final String SELECT_VULNERABILITY_ID = "SELECT id FROM vulnerability WHERE cve = ?";
    148     //</editor-fold>
    149
    150     /**
    151      * Opens the database connection. If the database does not exist, it will
    152      * create a new one.
    153      *
    154      * @throws IOException thrown if there is an IO Exception
    155      * @throws SQLException thrown if there is a SQL Exception
    156      * @throws DatabaseException thrown if there is an error initializing a new
    157      * database
    158      * @throws ClassNotFoundException thrown if the h2 database driver cannot be
    159      * loaded
    160      */
    161     @edu.umd.cs.findbugs.annotations.SuppressWarnings(
    162             value = "DMI_EMPTY_DB_PASSWORD",
    163             justification = "Yes, I know... Blank password.")
    164     public void open() throws IOException, SQLException, DatabaseException, ClassNotFoundException {
    165         final String fileName = CveDB.getDataDirectory().getCanonicalPath();
    166         final File f = new File(fileName, "cve." + DB_SCHEMA_VERSION);
    167         final File check = new File(f.getAbsolutePath() + ".h2.db");
    168         final boolean createTables = !check.exists();
    169         final String connStr = String.format("jdbc:h2:file:%s;AUTO_SERVER=TRUE", f.getAbsolutePath());
    170         Class.forName("org.h2.Driver");
    171         conn = DriverManager.getConnection(connStr, "sa", "");
    172         if (createTables) {
    173             createTables();
    174         }
    175     }
    176
    177     /**
    178      * Commits all completed transactions.
    179      *
    180      * @throws SQLException thrown if a SQL Exception occurs
    181      */
    182     public void commit() throws SQLException {
    183         if (conn != null) {
    184             conn.commit();
    185         }
    186     }
    187
    188     /**
    189      * Cleans up the object and ensures that "close" has been called.
    190      *
    191      * @throws Throwable thrown if there is a problem
    192      */
    193     @Override
    194     protected void finalize() throws Throwable {
    195         close();
    196         super.finalize(); //not necessary if extending Object.
    197     }
    198
    199     /**
    200      * Closes the DB4O database. Close should be called on this object when it
    201      * is done being used.
    202      */
    203     public void close() {
    204         if (conn != null) {
    205             try {
    206                 conn.close();
    207             } catch (SQLException ex) {
    208                 final String msg = "There was an error attempting to close the CveDB, see the log for more details.";
    209                 Logger.getLogger(CveDB.class.getName()).log(Level.SEVERE, msg, ex);
    210                 Logger.getLogger(CveDB.class.getName()).log(Level.FINE, null, ex);
    211             }
    212             conn = null;
    213         }
    214     }
    215
    216     /**
    217      * Searches the CPE entries in the database and retrieves all entries for a
    218      * given vendor and product combination. The returned list will include all
    219      * versions of the product that are registered in the NVD CVE data.
    220      *
    221      * @param vendor the identified vendor name of the dependency being analyzed
    222      * @param product the identified name of the product of the dependency being
    223      * analyzed
    224      * @return a set of vulnerable software
    225      */
    226     public Set<VulnerableSoftware> getCPEs(String vendor, String product) {
    227         final Set<VulnerableSoftware> cpe = new HashSet<VulnerableSoftware>();
    228         ResultSet rs = null;
    229         PreparedStatement ps = null;
    230         try {
    231             ps = conn.prepareStatement(SELECT_CPE_ENTRIES);
    232             ps.setString(1, vendor);
    233             ps.setString(2, product);
    234             rs = ps.executeQuery();
    235
    236             while (rs.next()) {
    237                 final VulnerableSoftware vs = new VulnerableSoftware();
    238                 vs.setCpe(rs.getString(1));
    239                 cpe.add(vs);
    240             }
    241         } catch (SQLException ex) {
    242             Logger.getLogger(CveDB.class.getName()).log(Level.SEVERE, null, ex);
    243         } finally {
    244             closeResultSet(rs);
    245             closeStatement(ps);
    246         }
    247         return cpe;
    248     }
    249
    250     /**
    251      * Retrieves the vulnerabilities associated with the specified CPE.
    252      *
    253      * @param cpeStr the CPE name
    254      * @return a list of Vulnerabilities
    255      * @throws DatabaseException thrown if there is an exception retrieving data
    256      */
    257     public List<Vulnerability> getVulnerabilities(String cpeStr) throws DatabaseException {
    258         ResultSet rs = null;
    259         final VulnerableSoftware cpe = new VulnerableSoftware();
    260         try {
    261             cpe.parseName(cpeStr);
    262         } catch (UnsupportedEncodingException ex) {
    263             Logger.getLogger(CveDB.class.getName()).log(Level.FINEST, null, ex);
    264         }
    265         final DependencyVersion detectedVersion = parseDependencyVersion(cpe);
    266         final List<Vulnerability> vulnerabilities = new ArrayList<Vulnerability>();
    267
    268         PreparedStatement ps;
    269         final HashSet<String> cveEntries = new HashSet<String>();
    270         try {
    271             ps = conn.prepareStatement(SELECT_CVE_FROM_SOFTWARE);
    272             ps.setString(1, cpe.getVendor());
    273             ps.setString(2, cpe.getProduct());
    274             rs = ps.executeQuery();
    275             while (rs.next()) {
    276                 final String cveId = rs.getString(1);
    277                 final String cpeId = rs.getString(2);
    278                 final String previous = rs.getString(3);
    279                 if (!cveEntries.contains(cveId) && isAffected(cpe.getVendor(), cpe.getProduct(), detectedVersion, cpeId, previous)) {
    280                     cveEntries.add(cveId);
    281                 }
    282             }
    283             closeResultSet(rs);
    284             closeStatement(ps);
    285             for (String cve : cveEntries) {
    286                 final Vulnerability v = getVulnerability(cve);
    287                 vulnerabilities.add(v);
    288             }
    289
    290         } catch (SQLException ex) {
    291             throw new DatabaseException("Exception retrieving vulnerability for " + cpeStr, ex);
    292         } finally {
    293             closeResultSet(rs);
    294         }
    295         return vulnerabilities;
    296     }
    297
    298     /**
    299      * Gets a vulnerability for the provided CVE.
    300      *
    301      * @param cve the CVE to lookup
    302      * @return a vulnerability object
    303      * @throws DatabaseException if an exception occurs
    304      */
    305     private Vulnerability getVulnerability(String cve) throws DatabaseException {
    306         PreparedStatement psV = null;
    307         PreparedStatement psR = null;
    308         PreparedStatement psS = null;
    309         ResultSet rsV = null;
    310         ResultSet rsR = null;
    311         ResultSet rsS = null;
    312         Vulnerability vuln = null;
    313         try {
    314             psV = conn.prepareStatement(SELECT_VULNERABILITY);
    315             psV.setString(1, cve);
    316             rsV = psV.executeQuery();
    317             if (rsV.next()) {
    318                 vuln = new Vulnerability();
    319                 vuln.setName(cve);
    320                 vuln.setDescription(rsV.getString(2));
    321                 String cwe = rsV.getString(3);
    322                 if (cwe != null) {
    323                     final String name = CweDB.getCweName(cwe);
    324                     if (name != null) {
    325                         cwe += " " + name;
    326                     }
    327                 }
    328                 final int cveId = rsV.getInt(1);
    329                 vuln.setCwe(cwe);
    330                 vuln.setCvssScore(rsV.getFloat(4));
    331                 vuln.setCvssAccessVector(rsV.getString(5));
    332                 vuln.setCvssAccessComplexity(rsV.getString(6));
    333                 vuln.setCvssAuthentication(rsV.getString(7));
    334                 vuln.setCvssConfidentialityImpact(rsV.getString(8));
    335                 vuln.setCvssIntegrityImpact(rsV.getString(9));
    336                 vuln.setCvssAvailabilityImpact(rsV.getString(10));
    337
    338                 psR = conn.prepareStatement(SELECT_REFERENCE);
    339                 psR.setInt(1, cveId);
    340                 rsR = psR.executeQuery();
    341                 while (rsR.next()) {
    342                     vuln.addReference(rsR.getString(1), rsR.getString(2), rsR.getString(3));
    343                 }
    344                 psS = conn.prepareStatement(SELECT_SOFTWARE);
    345                 psS.setInt(1, cveId);
    346                 rsS = psS.executeQuery();
    347                 while (rsS.next()) {
    348                     final String cpe = rsS.getString(1);
    349                     final String prevVersion = rsS.getString(2);
    350                     if (prevVersion == null) {
    351                         vuln.addVulnerableSoftware(cpe);
    352                     } else {
    353                         vuln.addVulnerableSoftware(cpe, prevVersion);
    354                     }
    355                 }
    356             }
    357         } catch (SQLException ex) {
    358             throw new DatabaseException("Error retrieving " + cve, ex);
    359         } finally {
    360             closeResultSet(rsV);
    361             closeResultSet(rsR);
    362             closeResultSet(rsS);
    363             closeStatement(psV);
    364             closeStatement(psR);
    365             closeStatement(psS);
    366         }
    367         return vuln;
    368     }
    369
    370     /**
    371      * Updates the vulnerability within the database. If the vulnerability does
    372      * not exist it will be added.
    373      *
    374      * @param vuln the vulnerability to add to the database
    375      * @throws DatabaseException is thrown if the database
    376      */
    377     public void updateVulnerability(Vulnerability vuln) throws DatabaseException {
    378         PreparedStatement selectVulnerabilityId = null;
    379         PreparedStatement deleteReferences = null;
    380         PreparedStatement deleteSoftware = null;
    381         PreparedStatement updateVulnerability = null;
    382         PreparedStatement insertVulnerability = null;
    383         PreparedStatement insertReference = null;
    384         PreparedStatement selectCpeId = null;
    385         PreparedStatement insertCpe = null;
    386         PreparedStatement insertSoftware = null;
    387
    388         try {
    389             selectVulnerabilityId = conn.prepareStatement(SELECT_VULNERABILITY_ID);
    390             deleteReferences = conn.prepareStatement(DELETE_REFERENCE);
    391             deleteSoftware = conn.prepareStatement(DELETE_SOFTWARE);
    392             updateVulnerability = conn.prepareStatement(UPDATE_VULNERABILITY);
    393             insertVulnerability = conn.prepareStatement(INSERT_VULNERABILITY, Statement.RETURN_GENERATED_KEYS);
    394             insertReference = conn.prepareStatement(INSERT_REFERENCE);
    395             selectCpeId = conn.prepareStatement(SELECT_CPE_ID);
    396             insertCpe = conn.prepareStatement(INSERT_CPE, Statement.RETURN_GENERATED_KEYS);
    397             insertSoftware = conn.prepareStatement(INSERT_SOFTWARE);
    398             int vulnerabilityId = 0;
    399             selectVulnerabilityId.setString(1, vuln.getName());
    400             ResultSet rs = selectVulnerabilityId.executeQuery();
    401             if (rs.next()) {
    402                 vulnerabilityId = rs.getInt(1);
    403                 // first delete any existing vulnerability info. We don't know what was updated. yes, slower but atm easier.
    404                 deleteReferences.setInt(1, vulnerabilityId);
    405                 deleteReferences.execute();
    406                 deleteSoftware.setInt(1, vulnerabilityId);
    407                 deleteSoftware.execute();
    408             }
    409             closeResultSet(rs);
    410             rs = null;
    411             if (vulnerabilityId != 0) {
    412                 updateVulnerability.setString(1, vuln.getDescription());
    413                 updateVulnerability.setString(2, vuln.getCwe());
    414                 updateVulnerability.setFloat(3, vuln.getCvssScore());
    415                 updateVulnerability.setString(4, vuln.getCvssAccessVector());
    416                 updateVulnerability.setString(5, vuln.getCvssAccessComplexity());
    417                 updateVulnerability.setString(6, vuln.getCvssAuthentication());
    418                 updateVulnerability.setString(7, vuln.getCvssConfidentialityImpact());
    419                 updateVulnerability.setString(8, vuln.getCvssIntegrityImpact());
    420                 updateVulnerability.setString(9, vuln.getCvssAvailabilityImpact());
    421                 updateVulnerability.setInt(10, vulnerabilityId);
    422                 updateVulnerability.executeUpdate();
    423             } else {
    424                 insertVulnerability.setString(1, vuln.getName());
    425                 insertVulnerability.setString(2, vuln.getDescription());
    426                 insertVulnerability.setString(3, vuln.getCwe());
    427                 insertVulnerability.setFloat(4, vuln.getCvssScore());
    428                 insertVulnerability.setString(5, vuln.getCvssAccessVector());
    429                 insertVulnerability.setString(6, vuln.getCvssAccessComplexity());
    430                 insertVulnerability.setString(7, vuln.getCvssAuthentication());
    431                 insertVulnerability.setString(8, vuln.getCvssConfidentialityImpact());
    432                 insertVulnerability.setString(9, vuln.getCvssIntegrityImpact());
    433                 insertVulnerability.setString(10, vuln.getCvssAvailabilityImpact());
    434                 insertVulnerability.execute();
    435                 try {
    436                     rs = insertVulnerability.getGeneratedKeys();
    437                     rs.next();
    438                     vulnerabilityId = rs.getInt(1);
    439                 } catch (SQLException ex) {
    440                     final String msg = String.format("Unable to retrieve id for new vulnerability for '%s'", vuln.getName());
    441                     throw new DatabaseException(msg, ex);
    442                 } finally {
    443                     closeResultSet(rs);
    444                     rs = null;
    445                 }
    446             }
    447             insertReference.setInt(1, vulnerabilityId);
    448             for (Reference r : vuln.getReferences()) {
    449                 insertReference.setString(2, r.getName());
    450                 insertReference.setString(3, r.getUrl());
    451                 insertReference.setString(4, r.getSource());
    452                 insertReference.execute();
    453             }
    454             for (VulnerableSoftware s : vuln.getVulnerableSoftware()) {
    455                 int cpeProductId = 0;
    456                 selectCpeId.setString(1, s.getName());
    457                 try {
    458                     rs = selectCpeId.executeQuery();
    459                     if (rs.next()) {
    460                         cpeProductId = rs.getInt(1);
    461                     }
    462                 } catch (SQLException ex) {
    463                     throw new DatabaseException("Unable to get primary key for new cpe: " + s.getName(), ex);
    464                 } finally {
    465                     closeResultSet(rs);
    466                     rs = null;
    467                 }
    468
    469                 if (cpeProductId == 0) {
    470                     insertCpe.setString(1, s.getName());
    471                     insertCpe.setString(2, s.getVendor());
    472                     insertCpe.setString(3, s.getProduct());
    473                     insertCpe.executeUpdate();
    474                     cpeProductId = getGeneratedKey(insertCpe);
    475                 }
    476                 if (cpeProductId == 0) {
    477                     throw new DatabaseException("Unable to retrieve cpeProductId - no data returned");
    478                 }
    479
    480                 insertSoftware.setInt(1, vulnerabilityId);
    481                 insertSoftware.setInt(2, cpeProductId);
    482                 if (s.getPreviousVersion() == null) {
    483                     insertSoftware.setNull(3, java.sql.Types.VARCHAR);
    484                 } else {
    485                     insertSoftware.setString(3, s.getPreviousVersion());
    486                 }
    487                 insertSoftware.execute();
    488             }
    489
    490         } catch (SQLException ex) {
    491             final String msg = String.format("Error updating '%s'", vuln.getName());
    492             Logger.getLogger(CveDB.class.getName()).log(Level.FINE, null, ex);
    493             throw new DatabaseException(msg, ex);
    494         } finally {
    495             closeStatement(selectVulnerabilityId);
    496             closeStatement(deleteReferences);
    497             closeStatement(deleteSoftware);
    498             closeStatement(updateVulnerability);
    499             closeStatement(insertVulnerability);
    500             closeStatement(insertReference);
    501             closeStatement(selectCpeId);
    502             closeStatement(insertCpe);
    503             closeStatement(insertSoftware);
    504         }
    505     }
    506
    507     /**
    508      * Retrieves the directory that the JAR file exists in so that we can ensure
    509      * we always use a common data directory.
    510      *
    511      * @return the data directory for this index.
    512      * @throws IOException is thrown if an IOException occurs of course...
    513      */
    514     public static File getDataDirectory() throws IOException {
    515         final File path = Settings.getFile(Settings.KEYS.CVE_DATA_DIRECTORY);
    516         if (!path.exists()) {
    517             if (!path.mkdirs()) {
    518                 throw new IOException("Unable to create NVD CVE Data directory");
    519             }
    520         }
    521         return path;
    522     }
    523
    524     /**
    525      * It is possible that orphaned rows may be generated during database
    526      * updates. This should be called after all updates have been completed to
    527      * ensure orphan entries are removed.
    528      */
    529     public void cleanupDatabase() {
    530         PreparedStatement ps = null;
    531         try {
    532             ps = conn.prepareStatement(CLEANUP_ORPHANS);
    533             if (ps != null) {
    534                 ps.executeUpdate();
    535             }
    536         } catch (SQLException ex) {
    537             Logger.getLogger(CveDB.class.getName()).log(Level.SEVERE, null, ex);
    538         } finally {
    539             closeStatement(ps);
    540         }
    541     }
    542
    543     /**
    544      * Creates the database structure (tables and indexes) to store the CVE data
    545      *
    546      * @throws SQLException thrown if there is a sql exception
    547      * @throws DatabaseException thrown if there is a database exception
    548      */
    549     protected void createTables() throws SQLException, DatabaseException {
    550         InputStream is;
    551         InputStreamReader reader;
    552         BufferedReader in = null;
    553         try {
    554             is = this.getClass().getClassLoader().getResourceAsStream(DB_STRUCTURE_RESOURCE);
    555             reader = new InputStreamReader(is, "UTF-8");
    556             in = new BufferedReader(reader);
    557             final StringBuilder sb = new StringBuilder(2110);
    558             String tmp;
    559             while ((tmp = in.readLine()) != null) {
    560                 sb.append(tmp);
    561             }
    562             Statement statement = null;
    563             try {
    564                 statement = conn.createStatement();
    565                 statement.execute(sb.toString());
    566             } finally {
    567                 closeStatement(statement);
    568             }
    569         } catch (IOException ex) {
    570             throw new DatabaseException("Unable to create database schema", ex);
    571         } finally {
    572             if (in != null) {
    573                 try {
    574                     in.close();
    575                 } catch (IOException ex) {
    576                     Logger.getLogger(CveDB.class
    577                             .getName()).log(Level.FINEST, null, ex);
    578                 }
    579             }
    580         }
    581     }
    582
    583     /**
    584      * Closes the given statement object ignoring any exceptions that occur.
    585      *
    586      * @param statement a Statement object
    587      */
    588     private void closeStatement(Statement statement) {
    589         if (statement != null) {
    590             try {
    591                 statement.close();
    592             } catch (SQLException ex) {
    593                 Logger.getLogger(CveDB.class
    594                         .getName()).log(Level.FINEST, statement.toString(), ex);
    595             }
    596         }
    597     }
    598
    599     /**
    600      * Closes the result set capturing and ignoring any SQLExceptions that
    601      * occur.
    602      *
    603      * @param rs a ResultSet to close
    604      */
    605     private void closeResultSet(ResultSet rs) {
    606         if (rs != null) {
    607             try {
    608                 rs.close();
    609             } catch (SQLException ex) {
    610                 Logger.getLogger(CveDB.class
    611                         .getName()).log(Level.FINEST, rs.toString(), ex);
    612             }
    613         }
    614     }
    615
    616     /**
    617      * Returns the generated integer primary key for a newly inserted row.
    618      *
    619      * @param statement a prepared statement that just executed an insert
    620      * @return a primary key
    621      * @throws DatabaseException thrown if there is an exception obtaining the
    622      * key
    623      */
    624     private int getGeneratedKey(PreparedStatement statement) throws DatabaseException {
    625         ResultSet rs = null;
    626         int id = 0;
    627         try {
    628             rs = statement.getGeneratedKeys();
    629             rs.next();
    630             id = rs.getInt(1);
    631         } catch (SQLException ex) {
    632             throw new DatabaseException("Unable to get primary key for inserted row");
    633         } finally {
    634             closeResultSet(rs);
    635         }
    636         return id;
    637     }
    638
    639     /**
    640      * Determines if the given identifiedVersion is affected by the given cpeId
    641      * and previous version flag. A non-null, non-empty string passed to the
    642      * previous version argument indicates that all previous versions are
    643      * affected.
    644      *
    645      * @param vendor the vendor of the dependency being analyzed
    646      * @param product the product name of the dependency being analyzed
    647      * @param identifiedVersion the identified version of the dependency being
    648      * analyzed
    649      * @param cpeId the cpe identifier of software that has a known
    650      * vulnerability
    651      * @param previous a flag indicating if previous versions of the product are
    652      * vulnerable
    653      * @return true if the identified version is affected, otherwise false
    654      */
    655     private boolean isAffected(String vendor, String product, DependencyVersion identifiedVersion, String cpeId, String previous) {
    656         boolean affected = false;
    657         final boolean isStruts = "apache".equals(vendor) && "struts".equals(product);
    658         final DependencyVersion v = parseDependencyVersion(cpeId);
    659         final boolean prevAffected = previous == null ? false : !previous.isEmpty();
    660         if (identifiedVersion == null || "-".equals(identifiedVersion.toString())) {
    661             if (v == null || "-".equals(v.toString())) {
    662                 affected = true;
    663             }
    664         } else if (identifiedVersion.equals(v) || (prevAffected && identifiedVersion.compareTo(v) < 0)) {
    665             if (isStruts) { //struts 2 vulns don't affect struts 1
    666                 if (identifiedVersion.getVersionParts().get(0).equals(v.getVersionParts().get(0))) {
    667                     affected = true;
    668                 }
    669             } else {
    670                 affected = true;
    671             }
    672         }
    673         /*
    674          * TODO consider utilizing the matchThreeVersion method to get additional results. However, this
    675          *      might also introduce false positives.
    676          */
    677         return affected;
    678     }
    679
    680     /**
    681      * Parses the version (including revision) from a CPE identifier. If no
    682      * version is identified then a '-' is returned.
    683      *
    684      * @param cpeStr a cpe identifier
    685      * @return a dependency version
    686      */
    687     private DependencyVersion parseDependencyVersion(String cpeStr) {
    688         final VulnerableSoftware cpe = new VulnerableSoftware();
    689         try {
    690             cpe.parseName(cpeStr);
    691         } catch (UnsupportedEncodingException ex) {
    692             //never going to happen.
    693             Logger.getLogger(CveDB.class.getName()).log(Level.FINEST, null, ex);
    694         }
    695         return parseDependencyVersion(cpe);
    696     }
    697
    698     /**
    699      * Takes a CPE and parses out the version number. If no version is
    700      * identified then a '-' is returned.
    701      *
    702      * @param cpe a cpe object
    703      * @return a dependency version
    704      */
    705     private DependencyVersion parseDependencyVersion(VulnerableSoftware cpe) {
    706         DependencyVersion cpeVersion;
    707         if (cpe.getVersion() != null && cpe.getVersion().length() > 0) {
    708             String versionText;
    709             if (cpe.getRevision() != null && cpe.getRevision().length() > 0) {
    710                 versionText = String.format("%s.%s", cpe.getVersion(), cpe.getRevision());
    711             } else {
    712                 versionText = cpe.getVersion();
    713             }
    714             cpeVersion = DependencyVersionUtil.parseVersion(versionText);
    715         } else {
    716             cpeVersion = new DependencyVersion("-");
    717         }
    718         return cpeVersion;
    719     }
    720 }