VulnerabilityQueryManager.java

/*
 * This file is part of Dependency-Track.
 *
 * 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.
 *
 * SPDX-License-Identifier: Apache-2.0
 * Copyright (c) OWASP Foundation. All Rights Reserved.
 */
package org.dependencytrack.persistence;

import alpine.common.logging.Logger;
import alpine.event.framework.Event;
import alpine.persistence.PaginatedResult;
import alpine.resources.AlpineRequest;
import org.apache.commons.collections4.ListUtils;
import org.apache.commons.lang3.StringUtils;
import org.dependencytrack.event.IndexEvent;
import org.dependencytrack.model.AffectedVersionAttribution;
import org.dependencytrack.model.Analysis;
import org.dependencytrack.model.Component;
import org.dependencytrack.model.FindingAttribution;
import org.dependencytrack.model.Project;
import org.dependencytrack.model.VulnIdAndSource;
import org.dependencytrack.model.Vulnerability;
import org.dependencytrack.model.VulnerabilityAlias;
import org.dependencytrack.model.VulnerableSoftware;
import org.dependencytrack.resources.v1.vo.AffectedProject;
import org.dependencytrack.tasks.VulnDbSyncTask;
import org.dependencytrack.tasks.scanners.AnalyzerIdentity;
import org.dependencytrack.util.PersistenceUtil;

import javax.jdo.PersistenceManager;
import javax.jdo.Query;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.function.Function;
import java.util.stream.Collectors;

final class VulnerabilityQueryManager extends QueryManager implements IQueryManager {
    private static final Logger LOGGER = Logger.getLogger(VulnDbSyncTask.class);
    /**
     * Constructs a new QueryManager.
     * @param pm a PersistenceManager object
     */
    VulnerabilityQueryManager(final PersistenceManager pm) {
        super(pm);
    }

    /**
     * Constructs a new QueryManager.
     * @param pm a PersistenceManager object
     * @param request an AlpineRequest object
     */
    VulnerabilityQueryManager(final PersistenceManager pm, final AlpineRequest request) {
        super(pm, request);
    }

    /**
     * Creates a new Vulnerability.
     * @param vulnerability the vulnerability to persist
     * @param commitIndex specifies if the search index should be committed (an expensive operation)
     * @return a new vulnerability object
     */
    public Vulnerability createVulnerability(Vulnerability vulnerability, boolean commitIndex) {
        final Vulnerability result = persist(vulnerability);
        Event.dispatch(new IndexEvent(IndexEvent.Action.CREATE, result));
        commitSearchIndex(commitIndex, Vulnerability.class);
        return result;
    }

    private boolean hasChanges(Vulnerability vulnerability, Vulnerability transientVulnerability) {
        return vulnerability.getUpdated() == null || transientVulnerability.getUpdated() == null || !vulnerability.getUpdated().equals(transientVulnerability.getUpdated());
    }

    private Vulnerability getExistingVulnerability(Vulnerability transientVulnerability){
        if (transientVulnerability == null) {
            return null;
        }
        if (transientVulnerability.getId() > 0) {
            return getObjectById(Vulnerability.class, transientVulnerability.getId());
        }
        return getVulnerabilityByVulnId(transientVulnerability.getSource(), transientVulnerability.getVulnId());
    }

    /**
     * Updates a vulnerability.
     * @param transientVulnerability the vulnerability to update
     * @param commitIndex specifies if the search index should be committed (an expensive operation)
     * @return a Vulnerability object
     */
    @Override
    public Vulnerability updateVulnerability(Vulnerability transientVulnerability, boolean commitIndex) {
        return callInTransaction(() -> {
            Vulnerability existingVulnerability = getExistingVulnerability(transientVulnerability);
            if (existingVulnerability != null) {
                final PersistenceUtil.Differ<Vulnerability> differ = new PersistenceUtil.Differ<>(existingVulnerability, transientVulnerability);
                differ.applyIfChanged("created", Vulnerability::getCreated, existingVulnerability::setCreated);
                differ.applyIfChanged("published", Vulnerability::getPublished, existingVulnerability::setPublished);
                differ.applyIfChanged("updated", Vulnerability::getUpdated, existingVulnerability::setUpdated);
                differ.applyIfChanged("vulnId", Vulnerability::getVulnId, existingVulnerability::setVulnId);
                differ.applyIfChanged("source", Vulnerability::getSource, existingVulnerability::setSource);
                differ.applyIfChanged("credits", Vulnerability::getCredits, existingVulnerability::setCredits);
                differ.applyIfChanged("vulnerableVersions", Vulnerability::getVulnerableVersions, existingVulnerability::setVulnerableVersions);
                differ.applyIfChanged("patchedVersions", Vulnerability::getPatchedVersions, existingVulnerability::setPatchedVersions);
                differ.applyIfChanged("description", Vulnerability::getDescription, existingVulnerability::setDescription);
                differ.applyIfChanged("detail", Vulnerability::getDetail, existingVulnerability::setDetail);
                differ.applyIfChanged("title", Vulnerability::getTitle, existingVulnerability::setTitle);
                differ.applyIfChanged("subTitle", Vulnerability::getSubTitle, existingVulnerability::setSubTitle);
                differ.applyIfChanged("references", Vulnerability::getReferences, existingVulnerability::setReferences);
                differ.applyIfChanged("recommendation", Vulnerability::getRecommendation, existingVulnerability::setRecommendation);
                differ.applyIfChanged("severity", Vulnerability::getSeverity, existingVulnerability::setSeverity);
                differ.applyIfChanged("cvssV2Vector", Vulnerability::getCvssV2Vector, existingVulnerability::setCvssV2Vector);
                differ.applyIfChanged("cvssV2BaseScore", Vulnerability::getCvssV2BaseScore, existingVulnerability::setCvssV2BaseScore);
                differ.applyIfChanged("cvssV2ImpactSubScore", Vulnerability::getCvssV2ImpactSubScore, existingVulnerability::setCvssV2ImpactSubScore);
                differ.applyIfChanged("cvssV2ExploitabilitySubScore", Vulnerability::getCvssV2ExploitabilitySubScore, existingVulnerability::setCvssV2ExploitabilitySubScore);
                differ.applyIfChanged("cvssV3Vector", Vulnerability::getCvssV3Vector, existingVulnerability::setCvssV3Vector);
                differ.applyIfChanged("cvssV3BaseScore", Vulnerability::getCvssV3BaseScore, existingVulnerability::setCvssV3BaseScore);
                differ.applyIfChanged("cvssV3ImpactSubScore", Vulnerability::getCvssV3ImpactSubScore, existingVulnerability::setCvssV3ImpactSubScore);
                differ.applyIfChanged("cvssV3ExploitabilitySubScore", Vulnerability::getCvssV3ExploitabilitySubScore, existingVulnerability::setCvssV3ExploitabilitySubScore);
                differ.applyIfChanged("owaspRRLikelihoodScore", Vulnerability::getOwaspRRLikelihoodScore, existingVulnerability::setOwaspRRLikelihoodScore);
                differ.applyIfChanged("owaspRRBusinessImpactScore", Vulnerability::getOwaspRRBusinessImpactScore, existingVulnerability::setOwaspRRBusinessImpactScore);
                differ.applyIfChanged("owaspRRTechnicalImpactScore", Vulnerability::getOwaspRRTechnicalImpactScore, existingVulnerability::setOwaspRRTechnicalImpactScore);
                differ.applyIfChanged("owaspRRVector", Vulnerability::getOwaspRRVector, existingVulnerability::setOwaspRRVector);
                differ.applyIfChanged("cwes", Vulnerability::getCwes, existingVulnerability::setCwes);
                differ.applyIfNonNullAndChanged("vulnerableSoftware", Vulnerability::getVulnerableSoftware, existingVulnerability::setVulnerableSoftware);
            }
            else {
                // Handle cases where no existing vulnerability is found if needed (e.g., log an error)
                LOGGER.warn("No existing vulnerability found for update operation.");
            }
            //return updated Vulnerability
            return existingVulnerability;
        });
    }

    /**
     * Synchronizes a vulnerability. Method first checkes to see if the vulnerability already
     * exists and if so, updates the vulnerability. If the vulnerability does not already exist,
     * this method will create a new vulnerability.
     * @param vulnerability the vulnerability to synchronize
     * @param commitIndex specifies if the search index should be committed (an expensive operation)
     * @return a Vulnerability object
     */
    @Override
    public Vulnerability synchronizeVulnerability(Vulnerability vulnerability, boolean commitIndex) {
        return callInTransaction(() -> {
            Vulnerability existingVulnerability  = getExistingVulnerability(vulnerability);
            if (existingVulnerability == null) {
                // Create new vulnerability if it doesnt exist
                return  createVulnerability(vulnerability, commitIndex);
            } else {
                // Update only if changes are detected
                return hasChanges(existingVulnerability, vulnerability)
                ? updateVulnerability(vulnerability, commitIndex)
                : null;
            }
        });
    }

    /**
     * Returns a vulnerability by it's name (i.e. CVE-2017-0001) and source.
     * @param source the source of the vulnerability
     * @param vulnId the name of the vulnerability
     * @return the matching Vulnerability object, or null if not found
     */
    public Vulnerability getVulnerabilityByVulnId(String source, String vulnId, boolean includeVulnerableSoftware) {
        final Query<Vulnerability> query = pm.newQuery(Vulnerability.class, "source == :source && vulnId == :vulnId");
        query.getFetchPlan().addGroup(Vulnerability.FetchGroup.COMPONENTS.name());
        if (includeVulnerableSoftware) {
            query.getFetchPlan().addGroup(Vulnerability.FetchGroup.VULNERABLE_SOFTWARE.name());
        }
        query.setRange(0, 1);
        final Vulnerability vulnerability = singleResult(query.execute(source, vulnId));
        if (vulnerability != null) {
            vulnerability.setAliases(getVulnerabilityAliases(vulnerability));
        }
        return vulnerability;
    }

    /**
     * Returns a vulnerability by it's name (i.e. CVE-2017-0001) and source.
     * @param source the source of the vulnerability
     * @param vulnId the name of the vulnerability
     * @return the matching Vulnerability object, or null if not found
     */
    public Vulnerability getVulnerabilityByVulnId(Vulnerability.Source source, String vulnId, boolean includeVulnerableSoftware) {
        return getVulnerabilityByVulnId(source.name(), vulnId, includeVulnerableSoftware);
    }

    /**
     * Adds a vulnerability to a component.
     * @param vulnerability the vulnerability to add
     * @param component the component affected by the vulnerability
     * @param analyzerIdentity the identify of the analyzer
     */
    public void addVulnerability(Vulnerability vulnerability, Component component, AnalyzerIdentity analyzerIdentity) {
        this.addVulnerability(vulnerability, component, analyzerIdentity, null, null, null);
    }

    /**
     * Adds a vulnerability to a component.
     * @param vulnerability the vulnerability to add
     * @param component the component affected by the vulnerability
     * @param analyzerIdentity the identify of the analyzer
     * @param alternateIdentifier the optional identifier if the analyzer refers to the vulnerability by an alternative identifier
     * @param referenceUrl the optional URL that references the occurrence of the vulnerability if uniquely identified
     */
    public void addVulnerability(Vulnerability vulnerability, Component component, AnalyzerIdentity analyzerIdentity,
                                 String alternateIdentifier, String referenceUrl) {
        this.addVulnerability(vulnerability, component, analyzerIdentity, alternateIdentifier, referenceUrl, null);
    }

    /**
     * Adds a vulnerability to a component.
     * @param vulnerability the vulnerability to add
     * @param component the component affected by the vulnerability
     * @param analyzerIdentity the identify of the analyzer
     * @param alternateIdentifier the optional identifier if the analyzer refers to the vulnerability by an alternative identifier
     * @param referenceUrl the optional URL that references the occurrence of the vulnerability if uniquely identified
     * @param attributedOn the optional attribution date of the vulnerability. Used primarily when cloning projects, leave null when adding a new one.
     */
    public void addVulnerability(Vulnerability vulnerability, Component component, AnalyzerIdentity analyzerIdentity,
                                 String alternateIdentifier, String referenceUrl, Date attributedOn) {
        if (!contains(vulnerability, component)) {
            component.addVulnerability(vulnerability);
            component = persist(component);
            FindingAttribution findingAttribution = new FindingAttribution(component, vulnerability, analyzerIdentity, alternateIdentifier, referenceUrl);
            if (attributedOn != null) {
                findingAttribution.setAttributedOn(attributedOn);
            }
            persist(findingAttribution);
        }
    }

    /**
     * Removes a vulnerability from a component.
     * @param vulnerability the vulnerabillity to remove
     * @param component the component unaffected by the vulnerabiity
     */
    public void removeVulnerability(Vulnerability vulnerability, Component component) {
        runInTransaction(() -> {
            if (contains(vulnerability, component)) {
                component.removeVulnerability(vulnerability);
            }

            final FindingAttribution fa = getFindingAttribution(vulnerability, component);
            if (fa != null) {
                delete(fa);
            }
        });
    }

    /**
     * Returns a FindingAttribution object form a given vulnerability and component.
     * @param vulnerability the vulnerabillity of the finding attribution
     * @param component the component of the finding attribution
     * @return a FindingAttribution object
     */
    public FindingAttribution getFindingAttribution(Vulnerability vulnerability, Component component) {
        final Query<FindingAttribution> query = pm.newQuery(FindingAttribution.class, "vulnerability == :vulnerability && component == :component");
        query.setRange(0, 1);
        return singleResult(query.execute(vulnerability, component));
    }

    /**
     * Deleted all FindingAttributions associated for the specified Component.
     * @param component the Component to delete FindingAttributions for
     */
    void deleteFindingAttributions(Component component) {
        final Query<FindingAttribution> query = pm.newQuery(FindingAttribution.class, "component == :component");
        query.deletePersistentAll(component);
    }

    /**
     * Deleted all FindingAttributions associated for the specified Component.
     * @param project the Component to delete FindingAttributions for
     */
    void deleteFindingAttributions(Project project) {
        final Query<FindingAttribution> query = pm.newQuery(FindingAttribution.class, "project == :project");
        query.deletePersistentAll(project);
    }

    /**
     * Reconciles findings for a component from a specific analyzer by removing
     * any FindingAttributions that are no longer reported by the analyzer.
     *
     * @param component the component to reconcile findings for
     * @param analyzerIdentity the analyzer identity to scope reconciliation
     * @param currentVulnIdAndSources the set of VulnIdAndSource reported by the latest scan
     */
    public void reconcileFindingsForComponentAnalyzer(Component component, AnalyzerIdentity analyzerIdentity, Set<VulnIdAndSource> currentVulnIdAndSources) {
        runInTransaction(() -> {
            final Query<FindingAttribution> query = pm.newQuery(FindingAttribution.class, "component == :component && analyzerIdentity == :analyzerIdentity");
            final List<FindingAttribution> existing = (List<FindingAttribution>) query.execute(component, analyzerIdentity);
            final Component persistentComponent = getObjectById(Component.class, component.getId());
            boolean changed = false;
            for (final FindingAttribution fa : existing) {
                final Vulnerability vuln = getObjectById(Vulnerability.class, fa.getVulnerability().getId());
                final VulnIdAndSource vid = new VulnIdAndSource(vuln.getVulnId(), vuln.getSource());
                if (!currentVulnIdAndSources.contains(vid)) {
                    persistentComponent.removeVulnerability(vuln);
                    delete(fa);
                    changed = true;
                }
            }
            if (changed) {
                persist(persistentComponent);
            }
        });
    }


    /**
     * Determines if a Component is affected by a specific Vulnerability by checking
     * {@link Vulnerability#getSource()} and {@link Vulnerability#getVulnId()}.
     * @param vulnerability The vulnerability to check if associated with component
     * @param component The component to check against
     * @return true if vulnerability is associated with the component, false if not
     */
    public boolean contains(Vulnerability vulnerability, Component component) {
        vulnerability = getObjectById(Vulnerability.class, vulnerability.getId());
        component = getObjectById(Component.class, component.getId());
        for (final Vulnerability vuln: component.getVulnerabilities()) {
            if (vuln.getSource() != null && vuln.getSource().equals(vulnerability.getSource())
                    && vuln.getVulnId() != null && vuln.getVulnId().equals(vulnerability.getVulnId())) {
                return true;
            }
        }
        return false;
    }

    /**
     * Returns a List of all Vulnerabilities.
     * @return a List of Vulnerability objects
     */
    public PaginatedResult getVulnerabilities() {
        PaginatedResult result;
        final Query<Vulnerability> query = pm.newQuery(Vulnerability.class);
        if (orderBy == null) {
            query.setOrdering("id asc");
        }
        if (filter != null) {
            query.setFilter("vulnId.toLowerCase().matches(:vulnId)");
            final String filterString = ".*" + filter.toLowerCase() + ".*";
            result = execute(query, filterString);
        } else {
            result = execute(query);
        }
        for (final Vulnerability vulnerability: result.getList(Vulnerability.class)) {
            List<AffectedProject> affectedProjects = this.getAffectedProjects(vulnerability);
            int affectedProjectsCount = affectedProjects.size();
            int affectedActiveProjectsCount = (int) affectedProjects.stream().filter(AffectedProject::getActive).count();
            int affectedInactiveProjectsCount = affectedProjectsCount - affectedActiveProjectsCount;

            vulnerability.setAffectedProjectCount(affectedProjectsCount);
            vulnerability.setAffectedActiveProjectCount(affectedActiveProjectsCount);
            vulnerability.setAffectedInactiveProjectCount(affectedInactiveProjectsCount);
            vulnerability.setAliases(getVulnerabilityAliases(vulnerability));
        }
        return result;
    }

    /**
     * Returns a List of Vulnerability for the specified Component and excludes suppressed vulnerabilities.
     * @param component the Component to retrieve vulnerabilities of
     * @return a List of Vulnerability objects
     */
    public PaginatedResult getVulnerabilities(Component component) {
        return getVulnerabilities(component, false);
    }

    /**
     * Returns a List of Vulnerability for the specified Component.
     * @param component the Component to retrieve vulnerabilities of
     * @return a List of Vulnerability objects
     */
    public PaginatedResult getVulnerabilities(Component component, boolean includeSuppressed) {
        PaginatedResult result;
        final String componentFilter = (includeSuppressed) ? "components.contains(:component)" : "components.contains(:component)" + generateExcludeSuppressed(component.getProject(), component);
        final Query<Vulnerability> query = pm.newQuery(Vulnerability.class);
        if (orderBy == null) {
            query.setOrdering("id asc");
        }
        if (filter != null) {
            query.setFilter(componentFilter + " && vulnId.toLowerCase().matches(:vulnId)");
            final String filterString = ".*" + filter.toLowerCase() + ".*";
            result = execute(query, component, filterString);
        } else {
            query.setFilter(componentFilter);
            result = execute(query, component);
        }
        for (final Vulnerability vulnerability: result.getList(Vulnerability.class)) {
            //vulnerability.setAffectedProjectCount(this.getProjects(vulnerability).size());
            vulnerability.setAliases(getVulnerabilityAliases(vulnerability));
        }
        return result;
    }

    /**
     * Returns a List of Vulnerability for the specified Component and excludes suppressed vulnerabilities.
     * This method if designed NOT to provide paginated results.
     * @param component the Component to retrieve vulnerabilities of
     * @return a List of Vulnerability objects
     */
    public List<Vulnerability> getAllVulnerabilities(Component component) {
        return getAllVulnerabilities(component, false);
    }

    /**
     * Returns a List of Vulnerability for the specified Component.
     * This method if designed NOT to provide paginated results.
     * @param component the Component to retrieve vulnerabilities of
     * @return a List of Vulnerability objects
     */
    @SuppressWarnings("unchecked")
    public List<Vulnerability> getAllVulnerabilities(Component component, boolean includeSuppressed) {
        final String filter = includeSuppressed ? "components.contains(:component)" : "components.contains(:component)" + generateExcludeSuppressed(component.getProject(), component);
        final Query<Vulnerability> query = pm.newQuery(Vulnerability.class, filter);
        final List<Vulnerability> vulnerabilities = (List<Vulnerability>)query.execute(component);
        for (final Vulnerability vulnerability: vulnerabilities) {
            //vulnerability.setAffectedProjectCount(this.getProjects(vulnerability).size());
            vulnerability.setAliases(getVulnerabilityAliases(vulnerability));
        }
        return vulnerabilities;
    }

    /**
     * Returns a List of Components affected by a specific vulnerability.
     * This method if designed NOT to provide paginated results.
     * @param project the Project to limit retrieval from
     * @param vulnerability the vulnerability to query on
     * @return a List of Component objects
     */
    public List<Component> getAllVulnerableComponents(Project project, Vulnerability vulnerability, boolean includeSuppressed) {
        final List<Component> components = new ArrayList<>();
        for (final Component component: getAllComponents(project)) {
            final Collection<Vulnerability> componentVulns = pm.detachCopyAll(
                    getAllVulnerabilities(component, includeSuppressed)
            );
            for (final Vulnerability componentVuln: componentVulns) {
                if (componentVuln.getUuid() == vulnerability.getUuid()) {
                    components.add(component);
                }
            }
        }
        return components;
    }

    /**
     * Returns the number of Vulnerability objects for the specified Project.
     * @param project the Project to retrieve vulnerabilities of
     * @return the total number of vulnerabilities for the project
     */
    public long getVulnerabilityCount(Project project, boolean includeSuppressed) {
        long total = 0;
        long suppressed = 0;
        final List<Component> components = getAllComponents(project);
        for (final Component component: components) {
            total += getCount(pm.newQuery(Vulnerability.class, "components.contains(:component)"), component);
            if (! includeSuppressed) {
                suppressed += getSuppressedCount(component); // account for globally suppressed components
                suppressed += getSuppressedCount(project, component); // account for per-project/component
            }
        }
        return total - suppressed;
    }

    /**
     * Returns a List of Vulnerability for the specified Project.
     * This method is unique and used by third-party integrations
     * such as ThreadFix for the retrieval of vulnerabilities from
     * a specific project along with the affected component(s).
     * @param project the Project to retrieve vulnerabilities of
     * @return a List of Vulnerability objects
     */
    public List<Vulnerability> getVulnerabilities(Project project, boolean includeSuppressed) {
        final List<Vulnerability> vulnerabilities = new ArrayList<>();
        final List<Component> components = getAllComponents(project);
        for (final Component component: components) {
            final Collection<Vulnerability> componentVulns = pm.detachCopyAll(
                    getAllVulnerabilities(component, includeSuppressed)
            );
            for (final Vulnerability componentVuln: componentVulns) {
                componentVuln.setComponents(Collections.singletonList(pm.detachCopy(component)));
                componentVuln.setAliases(new ArrayList<>(pm.detachCopyAll(getVulnerabilityAliases(componentVuln))));
            }
            vulnerabilities.addAll(componentVulns);
        }
        return vulnerabilities;
    }

    /**
     * Generates partial JDOQL statement excluding suppressed vulnerabilities for this project/component
     * and for globally suppressed vulnerabilities against the specified component.
     * @param component the component to query on
     * @param project the project to query on
     * @return a partial where clause
     */
    @SuppressWarnings("unchecked")
    private String generateExcludeSuppressed(Project project, Component component) {
        // Retrieve a list of all suppressed vulnerabilities
        final Query<Analysis> analysisQuery = pm.newQuery(Analysis.class, "project == :project && component == :component && suppressed == true");
        final List<Analysis> analysisList = (List<Analysis>)analysisQuery.execute(project, component);
        // Construct exclude clause based on above results
        String excludeClause = analysisList.stream().map(analysis -> "id != " + analysis.getVulnerability().getId() + " && ").collect(Collectors.joining());
        if (StringUtils.trimToNull(excludeClause) != null) {
            excludeClause = " && (" + excludeClause.substring(0, excludeClause.lastIndexOf(" && ")) + ")";
        }
        return excludeClause;
    }

    /**
     * Generates partial JDOQL statement excluding suppressed vulnerabilities for this project.
     * @param project the project to query on
     * @return a partial where clause
     */
    private String generateExcludeSuppressed(Project project) {
        return generateExcludeSuppressed(project, null);
    }

    /**
     * Returns a List of Projects affected by a specific vulnerability.
     * @param vulnerability the vulnerability to query on
     * @return a List of AffectedProjects
     */
    public List<AffectedProject> getAffectedProjects(Vulnerability vulnerability) {
        final Map<UUID, AffectedProject> affectedProjectMap = new HashMap<>();

        final List<Project> projects = new ArrayList<>();
        for (final Component component: vulnerability.getComponents()) {
            if (! super.hasAccess(super.principal, component.getProject())) {
                continue;
            }
            boolean affected = true;
            final Analysis projectAnalysis = getAnalysis(component, vulnerability);
            if (projectAnalysis != null && projectAnalysis.isSuppressed()) {
                affected = false;
            }
            if (affected) {
                Project project = component.getProject();
                AffectedProject affectedProject = affectedProjectMap.get(project.getUuid());
                if(affectedProject == null) {
                    affectedProject = new AffectedProject(
                            project.getUuid(),
                            project.getDirectDependencies() != null,
                            project.getName(),
                            project.getVersion(),
                            project.isActive(),
                            null);
                    affectedProjectMap.put(project.getUuid(), affectedProject);
                }
                affectedProject.getAffectedComponentUuids().add(component.getUuid());
            }
        }
        return affectedProjectMap.values().stream().toList();
    }

    public synchronized VulnerabilityAlias synchronizeVulnerabilityAlias(final VulnerabilityAlias alias) {
        return callInTransaction(() -> {
            // Query existing aliases that match AT LEAST ONE identifier of the given alias.
            //
            // For each data source, we want to know the existing aliases where the respective identifier either:
            //   1. matches, or
            //   2. is not set (is null)
            //
            // Given the existing alias:
            //   {cveId: "CVE-123", ghsaId: "GHSA-123"}
            // The logic allows us to merge it with this incoming alias:
            //   {cveId: "CVE-123", sonatypeId: "OSSINDEX-123"}
            // Forming the final result of:
            //   {cveId: "CVE-123", ghsaId: "GHSA-123", sonatypeId: "OSSINDEX-123"}
            // Because CVE-123 aliases GHSA-123, and CVE-123 aliases OSSINDEX-123, we can infer that GHSA-123 aliases OSSINDEX-123.
            //
            // If the given alias has both a CVE and a GHSA ID, the final query will look like this:
            //   (cveId == :cveId || cveId == null) && (ghsaId == :ghsaId || ghsaId == null)
            //      && (cveId != null || ghsaId != null)
            //
            // Note that this logic only works for "true" aliases, not for "related" vulnerabilities.
            // Some data sources will provide advisories, which combine multiple vulnerabilities into one,
            // but still advertise them as aliases. See https://github.com/google/osv.dev/issues/888 for example.
            var filter = "";
            var mustMatchAnyFilter = "";
            final var params = new HashMap<String, Object>();
            if (alias.getCveId() != null) {
                filter += "(cveId == :cveId || cveId == null)";
                mustMatchAnyFilter += "cveId != null";
                params.put("cveId", alias.getCveId());
            }
            if (alias.getSonatypeId() != null) {
                if (filter.length() > 0) {
                    filter += " && ";
                    mustMatchAnyFilter += " || ";
                }
                filter += "(sonatypeId == :sonatypeId || sonatypeId == null)";
                mustMatchAnyFilter += "sonatypeId != null";
                params.put("sonatypeId", alias.getSonatypeId());
            }
            if (alias.getGhsaId() != null) {
                if (filter.length() > 0) {
                    filter += " && ";
                    mustMatchAnyFilter += " || ";
                }
                filter += "(ghsaId == :ghsaId || ghsaId == null)";
                mustMatchAnyFilter += "ghsaId != null";
                params.put("ghsaId", alias.getGhsaId());
            }
            if (alias.getOsvId() != null) {
                if (filter.length() > 0) {
                    filter += " && ";
                    mustMatchAnyFilter += " || ";
                }
                filter += "(osvId == :osvId || osvId == null)";
                mustMatchAnyFilter += "osvId != null";
                params.put("osvId", alias.getOsvId());
            }
            if (alias.getSnykId() != null) {
                if (filter.length() > 0) {
                    filter += " && ";
                    mustMatchAnyFilter += " || ";
                }
                filter += "(snykId == :snykId || snykId == null)";
                mustMatchAnyFilter += "snykId != null";
                params.put("snykId", alias.getSnykId());
            }
            if (alias.getGsdId() != null) {
                if (filter.length() > 0) {
                    filter += " && ";
                    mustMatchAnyFilter += " || ";
                }
                filter += "(gsdId == :gsdId || gsdId == null)";
                mustMatchAnyFilter += "gsdId != null";
                params.put("gsdId", alias.getGsdId());
            }
            if (alias.getVulnDbId() != null) {
                if (filter.length() > 0) {
                    filter += " && ";
                    mustMatchAnyFilter += " || ";
                }
                filter += "(vulnDbId == :vulnDbId || vulnDbId == null)";
                mustMatchAnyFilter += "vulnDbId != null";
                params.put("vulnDbId", alias.getVulnDbId());
            }
            if (alias.getInternalId() != null) {
                if (filter.length() > 0) {
                    filter += " && ";
                    mustMatchAnyFilter += " || ";
                }
                filter += "(internalId == :internalId || internalId == null)";
                mustMatchAnyFilter += "internalId != null";
                params.put("internalId", alias.getInternalId());
            }
            filter += " && (" + mustMatchAnyFilter + ")";
            final Query<VulnerabilityAlias> query = pm.newQuery(VulnerabilityAlias.class);
            query.setFilter(filter);
            query.setNamedParameters(params);
            final List<VulnerabilityAlias> candidates = query.executeList();
            if (candidates.isEmpty()) {
                // No matches at all; Create new alias.
                return pm.makePersistent(alias);
            }

            final VulnerabilityAlias bestMatch;
            if (candidates.size() > 1) {
                // In case there are multiple candidates, find candidate with most matching identifiers.
                bestMatch = candidates.stream()
                        .max(Comparator.comparingInt(alias::computeMatches))
                        .get(); // Safe because we checked candidates for emptiness before.
            } else {
                bestMatch = candidates.get(0);
            }

            bestMatch.copyFieldsFrom(alias);
            return bestMatch;
        });
    }


    @SuppressWarnings("unchecked")
    public List<VulnerabilityAlias> getVulnerabilityAliases(Vulnerability vulnerability) {
        final Query<VulnerabilityAlias> query;
        if (Vulnerability.Source.NVD.name().equals(vulnerability.getSource())) {
            query = pm.newQuery(VulnerabilityAlias.class, "cveId == :cveId");
        } else if (Vulnerability.Source.OSSINDEX.name().equals(vulnerability.getSource())) {
            query = pm.newQuery(VulnerabilityAlias.class, "sonatypeId == :sonatypeId");
        } else if (Vulnerability.Source.GITHUB.name().equals(vulnerability.getSource())) {
            query = pm.newQuery(VulnerabilityAlias.class, "ghsaId == :ghsaId");
        } else if (Vulnerability.Source.OSV.name().equals(vulnerability.getSource())) {
            query = pm.newQuery(VulnerabilityAlias.class, "osvId == :osvId");
        } else if (Vulnerability.Source.SNYK.name().equals(vulnerability.getSource())) {
            query = pm.newQuery(VulnerabilityAlias.class, "snykId == :snykId");
        } else if (Vulnerability.Source.VULNDB.name().equals(vulnerability.getSource())) {
            query = pm.newQuery(VulnerabilityAlias.class, "vulnDbId == :vulnDb");
        } else {
            query = pm.newQuery(VulnerabilityAlias.class, "internalId == :internalId");
        }
            return (List<VulnerabilityAlias>)query.execute(vulnerability.getVulnId());
    }



    /**
     * Bulk-load {@link VulnerabilityAlias}es for one or more {@link VulnIdAndSource}s.
     *
     * @param vulnIdAndSources The Vulnerability ID - Source pairs to load {@link VulnerabilityAlias}es for
     * @return {@link VulnerabilityAlias}es, grouped by {@link VulnIdAndSource}
     * @since 4.12.0
     */
    public Map<VulnIdAndSource, List<VulnerabilityAlias>> getVulnerabilityAliases(
            final Collection<VulnIdAndSource> vulnIdAndSources) {
        if (vulnIdAndSources == null || vulnIdAndSources.isEmpty()) {
            return Collections.emptyMap();
        }

        // Prevent database queries from becoming too large and parameters
        // becoming too many for the database to handle.
        // https://github.com/DependencyTrack/dependency-track/issues/5096
        final List<List<VulnIdAndSource>> partitions =
                ListUtils.partition(List.copyOf(vulnIdAndSources), 250);

        final var results = new HashMap<VulnIdAndSource, List<VulnerabilityAlias>>(vulnIdAndSources.size());

        for (final List<VulnIdAndSource> partition : partitions) {
            results.putAll(getVulnerabilityAliasesInternal(partition));
        }

        return results;
    }

    private Map<VulnIdAndSource, List<VulnerabilityAlias>> getVulnerabilityAliasesInternal(
            final Collection<VulnIdAndSource> vulnIdAndSources) {
        var subQueryIndex = 0;
        final var subQueries = new ArrayList<String>(vulnIdAndSources.size());
        final var params = new HashMap<String, Object>(vulnIdAndSources.size());
        for (final VulnIdAndSource vulnIdAndSource : vulnIdAndSources) {
            subQueryIndex++;
            final String vulnIdParamName = "vulnId" + subQueryIndex;

            final String filter = switch (vulnIdAndSource.source()) {
                case GITHUB -> "\"GHSA_ID\" = :" + vulnIdParamName;
                case INTERNAL -> "\"INTERNAL_ID\" = :" + vulnIdParamName;
                case NVD -> "\"CVE_ID\" = :" + vulnIdParamName;
                case OSSINDEX -> "\"SONATYPE_ID\" = :" + vulnIdParamName;
                case OSV -> "\"OSV_ID\" = :" + vulnIdParamName;
                case SNYK -> "\"SNYK_ID\" = :" + vulnIdParamName;
                case VULNDB -> "\"VULNDB_ID\" = :" + vulnIdParamName;
                default -> null;
            };
            if (filter == null) {
                continue;
            }

            // We'll need to correlate query results with VulnIdAndSource objects
            // later, so prepend an identifier for them to the result set.
            // NB: DataNucleus doesn't support usage of query parameters
            // in the SELECT statement. Use hashCode of vulnIdAndSource instead
            // of string literals (which could be susceptible to SQL injection).
            subQueries.add("""
                    SELECT %d
                         , "GHSA_ID"
                         , "INTERNAL_ID"
                         , "CVE_ID"
                         , "SONATYPE_ID"
                         , "OSV_ID"
                         , "SNYK_ID"
                         , "VULNDB_ID"
                      FROM "VULNERABILITYALIAS"
                     WHERE %s
                    """.formatted(vulnIdAndSource.hashCode(), filter));
            params.put(vulnIdParamName, vulnIdAndSource.vulnId());
        }

        if (subQueries.isEmpty()) {
            return Collections.emptyMap();
        }

        final Query<Object[]> query = pm.newQuery(Query.SQL, String.join(" UNION ALL ", subQueries));
        query.setNamedParameters(params);
        final List<Object[]> queryResultRows = executeAndCloseList(query);
        if (queryResultRows.isEmpty()) {
            return Collections.emptyMap();
        }

        final Map<Integer, VulnIdAndSource> vulnIdAndSourceByHashCode = vulnIdAndSources.stream()
                .collect(Collectors.toMap(Record::hashCode, Function.identity()));

        return queryResultRows.stream()
                .map(row -> {
                    final var vulnIdAndSource = vulnIdAndSourceByHashCode.get((Integer) row[0]);
                    final var alias = new VulnerabilityAlias();
                    alias.setGhsaId((String) row[1]);
                    alias.setInternalId((String) row[2]);
                    alias.setCveId((String) row[3]);
                    alias.setSonatypeId((String) row[4]);
                    alias.setOsvId((String) row[5]);
                    alias.setSnykId((String) row[6]);
                    alias.setVulnDbId((String) row[7]);
                    return Map.entry(vulnIdAndSource, alias);
                })
                .collect(Collectors.groupingBy(
                        Map.Entry::getKey,
                        Collectors.mapping(Map.Entry::getValue, Collectors.toList())
                ));
    }

    /**
     * Reconcile {@link VulnerableSoftware} for a given {@link Vulnerability}.
     * <p>
     * {@link AffectedVersionAttribution}s are utilized to ensure that{@link VulnerableSoftware}
     * records are dropped that were previously reported by {@code source}, but aren't anymore.
     * <p>
     * {@link AffectedVersionAttribution}s are removed for a {@link VulnerableSoftware} record
     * if it is part of {@code vsListOld}, but not {@code vsList}.
     *
     * @param vulnerability The vulnerability this is about
     * @param vsListOld     Affected versions as previously reported
     * @param vsList        Affected versions as currently reported
     * @param source        The source who reported {@code vsList}
     * @return The reconciled {@link List} of {@link VulnerableSoftware}s
     * @since 4.7.0
     */
    public List<VulnerableSoftware> reconcileVulnerableSoftware(final Vulnerability vulnerability,
                                                                final List<VulnerableSoftware> vsListOld,
                                                                final List<VulnerableSoftware> vsList,
                                                                final Vulnerability.Source source) {
        if (vsListOld == null || vsListOld.isEmpty()) {
            return vsList;
        }

        for (final VulnerableSoftware vs : vsListOld) {
            final var vsPersistent = getObjectByUuid(VulnerableSoftware.class, vs.getUuid());
            if (vsPersistent == null) {
                continue; // Doesn't exist anymore
            } else if (vsList.contains(vsPersistent)) {
                continue; // We already have this one covered
            }

            final List<AffectedVersionAttribution> attributions = getAffectedVersionAttributions(vulnerability, vsPersistent);
            if (attributions.isEmpty()) {
                // DT versions prior to 4.7.0 did not record attributions.
                // Drop the VulnerableSoftware for now. If it was previously
                // reported by another source, it will be recorded and attributed
                // whenever that source is mirrored again.
                continue;
            }

            final boolean previouslyReportedBySource = attributions.stream()
                    .anyMatch(attr -> attr.getSource() == source);
            final boolean previouslyReportedByOthers = attributions.stream()
                    .anyMatch(attr -> attr.getSource() != source);

            if (previouslyReportedByOthers) {
                // Reported by another source, keep it.
                vsList.add(vsPersistent);
            }
            if (previouslyReportedBySource) {
                // Not reported anymore, remove attribution.
                deleteAffectedVersionAttribution(vulnerability, vsPersistent, source);
            }
        }

        return vsList;
    }

    /**
     * Fetch a {@link AffectedVersionAttribution} associated with a given
     * {@link Vulnerability}-{@link VulnerableSoftware} relationship.
     *
     * @param vulnerableSoftware the vulnerable software of the affected version attribution
     * @return a AffectedVersionAttribution object
     * @since 4.7.0
     */
    @Override
    public AffectedVersionAttribution getAffectedVersionAttribution(final Vulnerability vulnerability,
                                                                    final VulnerableSoftware vulnerableSoftware,
                                                                    final Vulnerability.Source source) {
        final Query<AffectedVersionAttribution> query = pm.newQuery(AffectedVersionAttribution.class, """
                vulnerability == :vulnerability && vulnerableSoftware == :vulnerableSoftware && source == :source
                """);
        query.setParameters(vulnerability, vulnerableSoftware, source);
        try {
            return query.executeUnique();
        } finally {
            query.closeAll();
        }
    }

    /**
     * Fetch all {@link AffectedVersionAttribution}s associated with a given
     * {@link Vulnerability}-{@link VulnerableSoftware} relationship.
     *
     * @param vulnerability      The {@link Vulnerability} to fetch attributions for
     * @param vulnerableSoftware The {@link VulnerableSoftware} to fetch attributions for
     * @return A {@link List} of {@link AffectedVersionAttribution}s
     * @since 4.7.0
     */
    @Override
    public List<AffectedVersionAttribution> getAffectedVersionAttributions(final Vulnerability vulnerability,
                                                                           final VulnerableSoftware vulnerableSoftware) {
        final Query<AffectedVersionAttribution> query = pm.newQuery(AffectedVersionAttribution.class, """
                vulnerability == :vulnerability && vulnerableSoftware == :vulnerableSoftware
                """);
        query.setParameters(vulnerability, vulnerableSoftware);
        try {
            return List.copyOf(query.executeList());
        } finally {
            query.closeAll();
        }
    }

    /**
     * Fetch all {@link AffectedVersionAttribution}s associated with a given {@link Vulnerability},
     * and any of the given {@link VulnerableSoftware}s.
     *
     * @param vulnerability       The {@link Vulnerability} to fetch attributions for
     * @param vulnerableSoftwares he {@link VulnerableSoftware}s to fetch attributions for
     * @return A {@link List} of {@link AffectedVersionAttribution}s
     * @since 4.10.0
     */
    @Override
    public List<AffectedVersionAttribution> getAffectedVersionAttributions(final Vulnerability vulnerability,
                                                                           final List<VulnerableSoftware> vulnerableSoftwares) {
        final Query<AffectedVersionAttribution> query = pm.newQuery(AffectedVersionAttribution.class);
        query.setFilter("vulnerability.id == :vulnId && :vsIdList.contains(vulnerableSoftware.id)");
        query.setNamedParameters(Map.of(
                "vulnId", vulnerability.getId(),
                "vsIdList", vulnerableSoftwares.stream().map(VulnerableSoftware::getId).toList()
        ));
        try {
            return List.copyOf(query.executeList());
        } finally {
            query.closeAll();
        }
    }

    /**
     * Attributes multiple {@link Vulnerability}-{@link VulnerableSoftware} relationships to a given {@link Vulnerability.Source}.
     *
     * @param vulnerability The {@link Vulnerability} to update the attribution for
     * @param vsList        The {@link VulnerableSoftware}s to update the attribution for
     * @param source        The {@link Vulnerability.Source} to attribute
     * @see #updateAffectedVersionAttribution(Vulnerability, VulnerableSoftware, Vulnerability.Source)
     * @since 4.7.0
     */
    @Override
    public void updateAffectedVersionAttributions(final Vulnerability vulnerability,
                                                  final List<VulnerableSoftware> vsList,
                                                  final Vulnerability.Source source) {
        runInTransaction(() -> vsList.forEach(vs -> {
            AffectedVersionAttribution attribution = getAffectedVersionAttribution(vulnerability, vs, source);
            if (attribution == null) {
                attribution = new AffectedVersionAttribution(source, vulnerability, vs);
                pm.makePersistent(attribution);
            } else {
                attribution.setLastSeen(new Date());
            }
        }));
    }

    /**
     * Attributes a {@link Vulnerability}-{@link VulnerableSoftware} relationship to a given {@link Vulnerability.Source}.
     * <p>
     * If the attribution does not exist already, it is created.
     * If it does exist, the {@code lastSeen} timestamp is updated.
     *
     * @param vulnerability      The {@link Vulnerability} to update the attribution for
     * @param vulnerableSoftware The {@link VulnerableSoftware} to update the attribution for
     * @param source             The {@link Vulnerability.Source} to attribute
     * @since 4.7.0
     */
    @Override
    public void updateAffectedVersionAttribution(final Vulnerability vulnerability,
                                                 final VulnerableSoftware vulnerableSoftware,
                                                 final Vulnerability.Source source) {
        final AffectedVersionAttribution attribution = getAffectedVersionAttribution(vulnerability, vulnerableSoftware, source);
        if (attribution == null) {
            runInTransaction(() -> {
                final var newAttribution = new AffectedVersionAttribution(source, vulnerability, vulnerableSoftware);
                pm.makePersistent(newAttribution);
            });
        } else {
            runInTransaction(() -> attribution.setLastSeen(new Date()));
        }
    }

    /**
     * Delete all {@link AffectedVersionAttribution}s for a given {@link Vulnerability.Source},
     * that are associated with a given {@link Vulnerability}, and <em>any</em> of the given {@link VulnerableSoftware}s.
     *
     * @param vulnerability       The {@link Vulnerability} to delete attributions for
     * @param vulnerableSoftwares The {@link VulnerableSoftware}s to delete attributions for
     * @param source              The {@link Vulnerability.Source} to delete attributions for
     * @since 4.10.0
     */
    @Override
    public void deleteAffectedVersionAttributions(final Vulnerability vulnerability,
                                                  final List<VulnerableSoftware> vulnerableSoftwares,
                                                  final Vulnerability.Source source) {
        final Query<AffectedVersionAttribution> deleteAttributionQuery = pm.newQuery(AffectedVersionAttribution.class);
        deleteAttributionQuery.setFilter(":vsListToRemove.contains(vulnerableSoftware) && vulnerability == :vuln && source == :source");
        deleteAttributionQuery.setNamedParameters(Map.of(
                "vsListToRemove", vulnerableSoftwares,
                "vuln", vulnerability,
                "source", source
        ));
        try {
            deleteAttributionQuery.deletePersistentAll();
        } finally {
            deleteAttributionQuery.closeAll();
        }
    }

    /**
     * Delete a {@link AffectedVersionAttribution}.
     *
     * @param vulnerability      The {@link Vulnerability} to delete the attribution for
     * @param vulnerableSoftware The {@link VulnerableSoftware} to delete the attribution for
     * @param source             The {@link Vulnerability.Source} to delete the attribution for
     * @since 4.7.0
     */
    @Override
    public void deleteAffectedVersionAttribution(final Vulnerability vulnerability,
                                                 final VulnerableSoftware vulnerableSoftware,
                                                 final Vulnerability.Source source) {
        final Query<AffectedVersionAttribution> query = pm.newQuery(AffectedVersionAttribution.class);
        query.setFilter("""
                vulnerability == :vulnerability
                && vulnerableSoftware == :vulnerableSoftware
                && source == :source
                """);
        query.setParameters(vulnerability, vulnerableSoftware, source);
        query.deletePersistentAll();
    }

    /**
     * Delete all {@link AffectedVersionAttribution}s associated with a given {@link Vulnerability}.
     *
     * @param vulnerability The {@link Vulnerability} to delete {@link AffectedVersionAttribution}s for
     * @since 4.7.0
     */
    @Override
    public void deleteAffectedVersionAttributions(final Vulnerability vulnerability) {
        final Query<AffectedVersionAttribution> query = pm.newQuery(AffectedVersionAttribution.class);
        query.setFilter("vulnerability == :vulnerability");
        query.setParameters(vulnerability);
        query.deletePersistentAll();
    }

    /**
     * Check if any {@link AffectedVersionAttribution} exists for a given {@link Vulnerability}-{@link VulnerableSoftware} relationship.
     *
     * @param vulnerability      The {@link Vulnerability} to check for
     * @param vulnerableSoftware The {@link VulnerableSoftware} to check for
     * @param source             The {@link Vulnerability.Source} to check for
     * @return {@code true} when an attribution exists, otherwise {@code false}
     * @since 4.10.0
     */
    @Override
    public boolean hasAffectedVersionAttribution(final Vulnerability vulnerability,
                                                 final VulnerableSoftware vulnerableSoftware,
                                                 final Vulnerability.Source source) {
        final Query<AffectedVersionAttribution> query = pm.newQuery(AffectedVersionAttribution.class);
        query.setFilter("source == :source && vulnerability == :vuln && vulnerableSoftware == :vs");
        query.setNamedParameters(Map.of(
                "source", source,
                "vuln", vulnerability,
                "vs", vulnerableSoftware
        ));
        query.setResult("count(this)");
        try {
            return query.executeResultUnique(Long.class) > 0;
        } finally {
            query.closeAll();
        }
    }

}