VulnerabilityDao.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.jdbi;

import jakarta.annotation.Nullable;
import org.dependencytrack.model.Component;
import org.dependencytrack.model.Vulnerability;
import org.dependencytrack.model.VulnerableSoftware;
import org.dependencytrack.persistence.jdbi.mapping.ExternalReferenceMapper;
import org.dependencytrack.persistence.jdbi.mapping.OrganizationalContactMapper;
import org.dependencytrack.persistence.jdbi.mapping.VulnerabilityRowMapper;
import org.dependencytrack.persistence.jdbi.mapping.VulnerableSoftwareRowMapper;
import org.jdbi.v3.sqlobject.config.RegisterColumnMapper;
import org.jdbi.v3.sqlobject.config.RegisterConstructorMapper;
import org.jdbi.v3.sqlobject.config.RegisterFieldMapper;
import org.jdbi.v3.sqlobject.config.RegisterRowMapper;
import org.jdbi.v3.sqlobject.customizer.Bind;
import org.jdbi.v3.sqlobject.customizer.Define;
import org.jdbi.v3.sqlobject.customizer.DefineNamedBindings;
import org.jdbi.v3.sqlobject.statement.SqlQuery;
import org.jdbi.v3.sqlobject.statement.SqlUpdate;

import java.time.Instant;
import java.util.List;
import java.util.UUID;

/**
 * @since 5.6.0
 */
public interface VulnerabilityDao {

    @SqlQuery(/* language=InjectedFreeMarker */ """
            <#-- @ftlvariable name="uuid" type="Boolean" -->
            SELECT "V"."ID"
                 , "V"."VULNID"
                 , "V"."SOURCE"
                 , "V"."FRIENDLYVULNID"
                 , "V"."TITLE"
                 , "V"."SUBTITLE"
                 , "V"."DESCRIPTION"
                 , "V"."DETAIL"
                 , "V"."RECOMMENDATION"
                 , "V"."REFERENCES"
                 , "V"."CREDITS"
                 , "V"."CREATED"
                 , "V"."PUBLISHED"
                 , "V"."UPDATED"
                 , CAST(STRING_TO_ARRAY("V"."CWES", ',') AS INT[]) AS "CWES"
                 , "V"."CVSSV2BASESCORE"
                 , "V"."CVSSV2IMPACTSCORE"
                 , "V"."CVSSV2EXPLOITSCORE"
                 , "V"."CVSSV2VECTOR"
                 , "V"."CVSSV3BASESCORE"
                 , "V"."CVSSV3IMPACTSCORE"
                 , "V"."CVSSV3EXPLOITSCORE"
                 , "V"."CVSSV3VECTOR"
                 , "V"."CVSSV4SCORE"
                 , "V"."CVSSV4VECTOR"
                 , "V"."OWASPRRLIKELIHOODSCORE"
                 , "V"."OWASPRRTECHNICALIMPACTSCORE"
                 , "V"."OWASPRRBUSINESSIMPACTSCORE"
                 , "V"."OWASPRRVECTOR"
                 , "V"."SEVERITY"
                 , "V"."VULNERABLEVERSIONS"
                 , "V"."PATCHEDVERSIONS"
                 , "V"."UUID"
                 , JSONB_VULN_ALIASES("V"."SOURCE", "V"."VULNID") AS "vulnAliasesJson"
                 , "EPSS"."SCORE"
                 , "EPSS"."PERCENTILE"
                 ,  (SELECT JSONB_AGG(JSONB_BUILD_OBJECT('id', "ID", 'name', "NAME"))
                        FROM "TAG"
                        INNER JOIN "VULNERABILITIES_TAGS"
                            ON "VULNERABILITIES_TAGS"."VULNERABILITY_ID" = "V"."ID"
                        WHERE "TAG"."ID" = "VULNERABILITIES_TAGS"."TAG_ID"
                    ) AS "vulnTagsJson"
              FROM "VULNERABILITY" AS "V"
              LEFT JOIN "EPSS"
                ON "V"."VULNID" = "EPSS"."CVE"
             WHERE 1 = 1
            <#if uuid>
               AND "V"."UUID" = :uuid
            <#else>
               AND "V"."VULNID" = :vulnId
               AND "V"."SOURCE" = :source
            </#if>
            """)
    @DefineNamedBindings
    @RegisterRowMapper(VulnerabilityRowMapper.class)
    Vulnerability getByUuidOrVulnIdAndSource(@Bind UUID uuid, @Bind String vulnId, @Bind String source);

    default Vulnerability getByUuid(final UUID uuid) {
        return getByUuidOrVulnIdAndSource(uuid, null, null);
    }

    default Vulnerability getByVulnIdAndSource(final String vulnId, final String source) {
        return getByUuidOrVulnIdAndSource(null, vulnId, source);
    }

    @SqlQuery("""
            SELECT "VS"."ID"
                 , "VS"."PURL"
                 , "VS"."PURL_TYPE"
                 , "VS"."PURL_NAMESPACE"
                 , "VS"."PURL_NAME"
                 , "VS"."PURL_VERSION"
                 , "VS"."PURL_QUALIFIERS"
                 , "VS"."PURL_SUBPATH"
                 , "VS"."CPE22"
                 , "VS"."CPE23"
                 , "VS"."PART"
                 , "VS"."VENDOR"
                 , "VS"."PRODUCT"
                 , "VS"."VERSION"
                 , "VS"."UPDATE"
                 , "VS"."EDITION"
                 , "VS"."LANGUAGE"
                 , "VS"."SWEDITION"
                 , "VS"."TARGETSW"
                 , "VS"."TARGETHW"
                 , "VS"."OTHER"
                 , "VS"."VERSIONENDEXCLUDING"
                 , "VS"."VERSIONENDINCLUDING"
                 , "VS"."VERSIONSTARTEXCLUDING"
                 , "VS"."VERSIONSTARTINCLUDING"
                 , "VS"."VULNERABLE"
                 , "VS"."UUID"
                 , (SELECT JSONB_AGG(JSONB_BUILD_OBJECT(
                             'id', "ID"
                           , 'firstSeen', CAST(EXTRACT(EPOCH FROM "FIRST_SEEN") * 1000 AS BIGINT)
                           , 'lastSeen', CAST(EXTRACT(EPOCH FROM "LAST_SEEN") * 1000 AS BIGINT)
                           , 'source', "SOURCE"
                           , 'uuid', "UUID"))
                      FROM "AFFECTEDVERSIONATTRIBUTION"
                     WHERE "VULNERABILITY" = "VSV"."VULNERABILITY_ID"
                       AND "VULNERABLE_SOFTWARE" = "VSV"."VULNERABLESOFTWARE_ID") AS "attributionsJson"
              FROM "VULNERABLESOFTWARE_VULNERABILITIES" AS "VSV"
             INNER JOIN "VULNERABLESOFTWARE" AS "VS"
                ON "VS"."ID" = "VSV"."VULNERABLESOFTWARE_ID"
             WHERE "VSV"."VULNERABILITY_ID" = :id
            """)
    @RegisterRowMapper(VulnerableSoftwareRowMapper.class)
    List<VulnerableSoftware> getVulnerableSoftwareByVulnId(@Bind long id);

    @SqlQuery(/* language=InjectedFreeMarker */ """
            <#-- @ftlvariable name="activeFilter" type="Boolean" -->
            <#-- @ftlvariable name="searchText" type="Boolean" -->
            <#-- @ftlvariable name="apiOrderByClause" type="String" -->
            <#-- @ftlvariable name="apiOffsetLimitClause" type="String" -->
            <#-- @ftlvariable name="apiProjectAclCondition" type="String" -->
            WITH "CTE_AFFECTED_COMPONENTS" AS (
              SELECT "COMPONENT"."UUID" AS "UUID"
                   , "PROJECT"."ID" AS "PROJECT_ID"
                FROM "COMPONENT"
               INNER JOIN "PROJECT"
                  ON "PROJECT"."ID" = "COMPONENT"."PROJECT_ID"
               INNER JOIN "COMPONENTS_VULNERABILITIES"
                  ON "COMPONENTS_VULNERABILITIES"."COMPONENT_ID" = "COMPONENT"."ID"
               INNER JOIN "VULNERABILITY"
                  ON "VULNERABILITY"."ID" = "COMPONENTS_VULNERABILITIES"."VULNERABILITY_ID"
               WHERE ${apiProjectAclCondition}
                 AND "VULNERABILITY"."SOURCE" = :source
                 AND "VULNERABILITY"."VULNID" = :vulnId
            <#if activeFilter>
                 AND "PROJECT"."INACTIVE_SINCE" IS NULL
            </#if>
                 AND EXISTS (
                   SELECT 1
                     FROM "FINDINGATTRIBUTION" AS fa
                    WHERE fa."COMPONENT_ID" = "COMPONENT"."ID"
                      AND fa."VULNERABILITY_ID" = "VULNERABILITY"."ID"
                      AND fa."DELETED_AT" IS NULL
                 )
            )
            SELECT "UUID" AS "uuid"
                 , "NAME" AS "name"
                 , "VERSION" AS "version"
                 , "INACTIVE_SINCE" AS "inactiveSince"
                 , ("DIRECT_DEPENDENCIES" IS NOT NULL) AS "dependencyGraphAvailable"
                 , (SELECT ARRAY_AGG("UUID")
                      FROM "CTE_AFFECTED_COMPONENTS"
                     WHERE "PROJECT_ID" = "PROJECT"."ID") AS "affectedComponentUuids"
                 , COUNT(*) OVER() AS "totalCount"
              FROM "PROJECT"
             WHERE EXISTS(
                 SELECT 1
                   FROM "CTE_AFFECTED_COMPONENTS"
                  WHERE "PROJECT_ID" = "PROJECT"."ID")
            <#if searchText>
              AND (
                LOWER("PROJECT"."NAME") LIKE ('%' || LOWER(:searchText) || '%')
              )
            </#if>
            <#if apiOrderByClause??>
              ${apiOrderByClause}
            <#else>
             ORDER BY "NAME" ASC, "VERSION" DESC
            </#if>
            ${apiOffsetLimitClause!}
            """)
    @DefineNamedBindings
    @AllowApiOrdering(by = {
            @AllowApiOrdering.Column(name = "name"),
            @AllowApiOrdering.Column(name = "version"),
            @AllowApiOrdering.Column(name = "inactiveSince")
    })
    @RegisterConstructorMapper(AffectedProjectListRow.class)
    List<AffectedProjectListRow> getAffectedProjects(
            @Bind String source,
            @Bind String vulnId,
            @Bind Boolean activeFilter,
            @Bind @Nullable String searchText);

    record AffectedProjectListRow(
            UUID uuid,
            String name,
            String version,
            @Nullable Instant inactiveSince,
            boolean dependencyGraphAvailable,
            List<UUID> affectedComponentUuids,
            long totalCount) {
    }

    @SqlQuery(/* language=InjectedFreeMarker */ """
            <#-- @ftlvariable name="apiFilterParameter" type="String" -->
            <#-- @ftlvariable name="includeSuppressed" type="boolean" -->
            SELECT "V"."ID" AS "ID"
                 , "V"."VULNID"
                 , "V"."SOURCE"
                 , "V"."FRIENDLYVULNID"
                 , "V"."TITLE"
                 , "V"."SUBTITLE"
                 , "V"."DESCRIPTION"
                 , "V"."DETAIL"
                 , "V"."RECOMMENDATION"
                 , "V"."REFERENCES"
                 , "V"."CREDITS"
                 , "V"."CREATED"
                 , "V"."PUBLISHED"
                 , "V"."UPDATED"
                 , CAST(STRING_TO_ARRAY("V"."CWES", ',') AS INT[]) AS "CWES"
                 , "V"."CVSSV2BASESCORE"
                 , "V"."CVSSV2IMPACTSCORE"
                 , "V"."CVSSV2EXPLOITSCORE"
                 , "V"."CVSSV2VECTOR"
                 , "V"."CVSSV3BASESCORE"
                 , "V"."CVSSV3IMPACTSCORE"
                 , "V"."CVSSV3EXPLOITSCORE"
                 , "V"."CVSSV3VECTOR"
                 , "V"."CVSSV4SCORE"
                 , "V"."CVSSV4VECTOR"
                 , "V"."OWASPRRLIKELIHOODSCORE"
                 , "V"."OWASPRRTECHNICALIMPACTSCORE"
                 , "V"."OWASPRRBUSINESSIMPACTSCORE"
                 , "V"."OWASPRRVECTOR"
                 , "V"."SEVERITY"
                 , "V"."VULNERABLEVERSIONS"
                 , "V"."PATCHEDVERSIONS"
                 , "V"."UUID"
                 , JSONB_VULN_ALIASES("V"."SOURCE", "V"."VULNID") AS "vulnAliasesJson"
                 , "EPSS"."SCORE"
                 , "EPSS"."PERCENTILE"
                 ,  (SELECT JSONB_AGG(JSONB_BUILD_OBJECT('id', "ID", 'name', "NAME"))
                        FROM "TAG"
                        INNER JOIN "VULNERABILITIES_TAGS"
                            ON "VULNERABILITIES_TAGS"."VULNERABILITY_ID" = "V"."ID"
                        WHERE "TAG"."ID" = "VULNERABILITIES_TAGS"."TAG_ID"
                    ) AS "vulnTagsJson"
              FROM "VULNERABILITY" AS "V"
             INNER JOIN "COMPONENTS_VULNERABILITIES"
                ON "V"."ID" = "COMPONENTS_VULNERABILITIES"."VULNERABILITY_ID"
               AND "COMPONENTS_VULNERABILITIES"."COMPONENT_ID" = :componentId
             INNER JOIN "COMPONENT"
                ON "COMPONENTS_VULNERABILITIES"."COMPONENT_ID" = "COMPONENT"."ID"
              LEFT JOIN "ANALYSIS"
            	ON "V"."ID" = "ANALYSIS"."VULNERABILITY_ID"
               AND "COMPONENT"."PROJECT_ID" = "ANALYSIS"."PROJECT_ID"
              LEFT JOIN "EPSS"
            	ON "V"."VULNID" = "EPSS"."CVE"
             WHERE TRUE
            <#if !includeSuppressed>
               AND "ANALYSIS"."SUPPRESSED" IS DISTINCT FROM TRUE
            </#if>
            <#if apiFilterParameter??>
               AND (LOWER("V"."VULNID") LIKE ('%' || LOWER(${apiFilterParameter}) || '%'))
            </#if>
               AND EXISTS (
                 SELECT 1
                   FROM "FINDINGATTRIBUTION" AS fa
                  WHERE fa."COMPONENT_ID" = "COMPONENT"."ID"
                    AND fa."VULNERABILITY_ID" = "V"."ID"
                    AND fa."DELETED_AT" IS NULL
               )
            ORDER BY "V"."ID"
            """)
    @RegisterRowMapper(VulnerabilityRowMapper.class)
    List<Vulnerability> getVulnerabilitiesByComponent(@Bind Long componentId, @Define boolean includeSuppressed);

    @SqlQuery(/* language=InjectedFreeMarker */ """
            <#-- @ftlvariable name="includeSuppressed" type="boolean" -->
            <#-- @ftlvariable name="apiProjectAclCondition" type="String" -->
            SELECT "VULNERABILITY"."ID" AS "id"
                 , COUNT("PROJECT"."ID") AS "totalProjectCount"
                 , COUNT(*) FILTER (WHERE "PROJECT"."INACTIVE_SINCE" IS NULL) AS "activeProjectCount"
              FROM "VULNERABILITY"
             INNER JOIN "COMPONENTS_VULNERABILITIES"
             	ON "VULNERABILITY"."ID" = "COMPONENTS_VULNERABILITIES"."VULNERABILITY_ID"
             INNER JOIN "COMPONENT"
              	ON "COMPONENTS_VULNERABILITIES"."COMPONENT_ID" = "COMPONENT"."ID"
             INNER JOIN "PROJECT"
            	ON "COMPONENT"."PROJECT_ID" = "PROJECT"."ID"
            <#if !includeSuppressed>
              LEFT JOIN "ANALYSIS"
                ON "ANALYSIS"."VULNERABILITY_ID" = "VULNERABILITY"."ID"
               AND "ANALYSIS"."COMPONENT_ID" = "COMPONENT"."ID"
            </#if>
             WHERE "VULNERABILITY"."ID" = ANY(:vulnerabilityIds)
            <#if !includeSuppressed>
               AND "ANALYSIS"."SUPPRESSED" IS DISTINCT FROM TRUE
            </#if>
               AND ${apiProjectAclCondition}
               AND EXISTS (
                 SELECT 1
                   FROM "FINDINGATTRIBUTION" AS fa
                  WHERE fa."COMPONENT_ID" = "COMPONENT"."ID"
                    AND fa."VULNERABILITY_ID" = "VULNERABILITY"."ID"
                    AND fa."DELETED_AT" IS NULL
               )
             GROUP BY "VULNERABILITY"."ID"
            """)
    @RegisterConstructorMapper(AffectedProjectCountRow.class)
    List<AffectedProjectCountRow> getAffectedProjectCount(
            @Bind List<Long> vulnerabilityIds,
            @Define boolean includeSuppressed);

    record AffectedProjectCountRow(
            long id,
            int totalProjectCount,
            int activeProjectCount
    ) {
    }

    @SqlQuery(/* language=InjectedFreeMarker */ """
            <#-- @ftlvariable name="apiFilterParameter" type="String" -->
            <#-- @ftlvariable name="includeSuppressed" type="boolean" -->
            SELECT DISTINCT ON ("V"."ID")
                   "V"."ID"
                 , "V"."VULNID"
                 , "V"."SOURCE"
                 , "V"."FRIENDLYVULNID"
                 , "V"."TITLE"
                 , "V"."SUBTITLE"
                 , "V"."DESCRIPTION"
                 , "V"."DETAIL"
                 , "V"."RECOMMENDATION"
                 , "V"."REFERENCES"
                 , "V"."CREDITS"
                 , "V"."CREATED"
                 , "V"."PUBLISHED"
                 , "V"."UPDATED"
                 , CAST(STRING_TO_ARRAY("V"."CWES", ',') AS INT[]) AS "CWES"
                 , "V"."CVSSV2BASESCORE"
                 , "V"."CVSSV2IMPACTSCORE"
                 , "V"."CVSSV2EXPLOITSCORE"
                 , "V"."CVSSV2VECTOR"
                 , "V"."CVSSV3BASESCORE"
                 , "V"."CVSSV3IMPACTSCORE"
                 , "V"."CVSSV3EXPLOITSCORE"
                 , "V"."CVSSV3VECTOR"
                 , "V"."CVSSV4SCORE"
                 , "V"."CVSSV4VECTOR"
                 , "V"."OWASPRRLIKELIHOODSCORE"
                 , "V"."OWASPRRTECHNICALIMPACTSCORE"
                 , "V"."OWASPRRBUSINESSIMPACTSCORE"
                 , "V"."OWASPRRVECTOR"
                 , "V"."SEVERITY"
                 , "V"."VULNERABLEVERSIONS"
                 , "V"."PATCHEDVERSIONS"
                 , "V"."UUID"
                 , JSONB_VULN_ALIASES("V"."SOURCE", "V"."VULNID") AS "vulnAliasesJson"
                 , ARRAY_AGG(DISTINCT("COMPONENT"."ID")) as "componentIdsArray"
                 , "EPSS"."SCORE"
                 , "EPSS"."PERCENTILE"
                 ,  (SELECT JSONB_AGG(JSONB_BUILD_OBJECT('id', "ID", 'name', "NAME"))
                        FROM "TAG"
                        INNER JOIN "VULNERABILITIES_TAGS"
                            ON "VULNERABILITIES_TAGS"."VULNERABILITY_ID" = "V"."ID"
                        WHERE "TAG"."ID" = "VULNERABILITIES_TAGS"."TAG_ID"
                    ) AS "vulnTagsJson"
              FROM "VULNERABILITY" AS "V"
             INNER JOIN "COMPONENTS_VULNERABILITIES"
                ON "V"."ID" = "COMPONENTS_VULNERABILITIES"."VULNERABILITY_ID"
             INNER JOIN "COMPONENT"
                ON "COMPONENTS_VULNERABILITIES"."COMPONENT_ID" = "COMPONENT"."ID"
              LEFT JOIN "EPSS"
                ON "V"."VULNID" = "EPSS"."CVE"
              LEFT JOIN "ANALYSIS"
                ON "V"."ID" = "ANALYSIS"."VULNERABILITY_ID"
               AND "COMPONENT"."ID" = "ANALYSIS"."COMPONENT_ID"
               AND "COMPONENT"."PROJECT_ID" = "ANALYSIS"."PROJECT_ID"
             WHERE "COMPONENT"."PROJECT_ID" = :projectId
            <#if !includeSuppressed>
               AND "ANALYSIS"."SUPPRESSED" IS DISTINCT FROM TRUE
            </#if>
            <#if apiFilterParameter??>
               AND (LOWER("V"."VULNID") LIKE ('%' || LOWER(${apiFilterParameter}) || '%'))
            </#if>
               AND EXISTS (
                 SELECT 1
                   FROM "FINDINGATTRIBUTION" AS fa
                  WHERE fa."COMPONENT_ID" = "COMPONENT"."ID"
                    AND fa."VULNERABILITY_ID" = "V"."ID"
                    AND fa."DELETED_AT" IS NULL
               )
             GROUP BY "V"."ID", "EPSS"."SCORE", "EPSS"."PERCENTILE"
             ORDER BY "V"."ID"
            """)
    @RegisterRowMapper(VulnerabilityRowMapper.class)
    List<Vulnerability> getVulnerabilitiesByProject(@Bind long projectId, @Define boolean includeSuppressed);

    @SqlQuery("""
            SELECT DISTINCT "C"."ID"
                 , "C"."NAME"
                 , "C"."AUTHORS"
                 , "C"."BLAKE2B_256"
                 , "C"."BLAKE2B_384"
                 , "C"."BLAKE2B_512"
                 , "C"."BLAKE3"
                 , "C"."CLASSIFIER"
                 , "C"."COPYRIGHT"
                 , "C"."CPE"
                 , "C"."PUBLISHER"
                 , "C"."PURL"
                 , "C"."PURLCOORDINATES"
                 , "C"."DESCRIPTION"
                 , "C"."DIRECT_DEPENDENCIES"
                 , "C"."EXTENSION"
                 , "C"."EXTERNAL_REFERENCES"
                 , "C"."FILENAME"
                 , "C"."GROUP"
                 , "C"."INTERNAL"
                 , "C"."LAST_RISKSCORE"
                 , "C"."LICENSE"
                 , "C"."LICENSE_EXPRESSION"
                 , "C"."LICENSE_URL"
                 , "C"."TEXT"
                 , "C"."MD5"
                 , "C"."SHA1"
                 , "C"."SHA_256"
                 , "C"."SHA_384"
                 , "C"."SHA_512"
                 , "C"."SHA3_256"
                 , "C"."SHA3_384"
                 , "C"."SHA3_512"
                 , "C"."SWIDTAGID"
                 , "C"."UUID"
                 , "C"."VERSION"
              FROM "COMPONENT" AS "C"
             INNER JOIN "COMPONENTS_VULNERABILITIES"
                ON "C"."ID" = "COMPONENTS_VULNERABILITIES"."COMPONENT_ID"
             INNER JOIN "VULNERABILITY"
                ON "COMPONENTS_VULNERABILITIES"."VULNERABILITY_ID" = "VULNERABILITY"."ID"
             WHERE "VULNERABILITY"."ID" = ANY(:vulnerabilityIds)
               AND "C"."PROJECT_ID" = :projectId
               AND EXISTS (
                   SELECT 1
                     FROM "FINDINGATTRIBUTION" AS "FA"
                    WHERE "FA"."COMPONENT_ID" = "C"."ID"
                      AND "FA"."VULNERABILITY_ID" = "VULNERABILITY"."ID"
                      AND "FA"."DELETED_AT" IS NULL
                 )
            """)
    @RegisterFieldMapper(Component.class)
    @RegisterColumnMapper(OrganizationalContactMapper.class)
    @RegisterColumnMapper(ExternalReferenceMapper.class)
    List<Component> getVulnerableComponents(@Bind long projectId, @Bind List<Long> vulnerabilityIds);

    @SqlUpdate("""
            DELETE
              FROM "VULNERABLESOFTWARE"
             WHERE NOT EXISTS(
               SELECT 1
                 FROM "VULNERABLESOFTWARE_VULNERABILITIES"
                WHERE "VULNERABLESOFTWARE_ID" = "VULNERABLESOFTWARE"."ID")
            """)
    int deleteOrphanVulnerableSoftware();

    @SqlUpdate("""
            DELETE
              FROM "VULNERABILITY"
             WHERE "UUID" = :vulnUuid
            """)
    int deleteVulnerability(@Bind final UUID vulnUuid);

    @SqlQuery("""
            SELECT EXISTS(
                SELECT 1
                  FROM "COMPONENT" AS c
                 INNER JOIN "COMPONENTS_VULNERABILITIES" AS cv
                    ON cv."COMPONENT_ID" = c."ID"
                 WHERE c."PROJECT_ID" = :projectId
                   AND EXISTS (
                     SELECT 1
                       FROM "FINDINGATTRIBUTION" AS fa
                      WHERE fa."COMPONENT_ID" = cv."COMPONENT_ID"
                        AND fa."VULNERABILITY_ID" = cv."VULNERABILITY_ID"
                        AND fa."DELETED_AT" IS NULL
                   )
            )
            """)
    boolean hasVulnerabilities(@Bind final long projectId);

    @SqlQuery("""
            SELECT "ID" FROM "VULNERABILITY" WHERE "UUID" = :vulnUuid
            """)
    Long getVulnerabilityId(@Bind UUID vulnUuid);
}