[Backend] Implement filtering of vulnerabilities by identifier on the vulnerabilities GraphQL query
Per the design requirements, we need to facilitate the searching of vulnerabilities by their identifier in the new search implementation.
Vulnerability Identifiers are already exposed by the graphql API, so this should be as simple as modifying the Vulnerability Reads modifier to accept an array of identifiers to search by.
Agreed MVC
- Filtering by identifier will be limited to a single page of records. i.e. show the first page alone with 100 vulnerabilities, without pagination.
- The ID filter will be additive, and can be used in conjunction with other filters, i.e. severity, status, or tool.
Implementation Plan
- DB related schema and migration effort is tracked and detailed in #460080
-
backend database Implement searching by Vulnerability Indentifier on the Vulnerability::Reads finder, along with an index as for project level and group level report page.
- Index to support the project level query:
SELECT
"vulnerability_reads".*
FROM
"vulnerability_reads",
unnest(vulnerability_reads.identifier_external_ids) as "idt_external_ids"
WHERE
"vulnerability_reads"."project_id" = 278964
AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99)
AND "vulnerability_reads"."state" IN (1, 4)
AND "vulnerability_reads"."resolved_on_default_branch" = FALSE,
AND idt_external_ids like 'CWE%'
ORDER BY
"vulnerability_reads"."severity" DESC,
"vulnerability_reads"."vulnerability_id" DESC
LIMIT
101
- Index to support the group level query:
SELECT
vr.*
FROM
"vulnerability_reads" vr
WHERE
vr."vulnerability_id" IN (
SELECT
vr_inner."vulnerability_id"
FROM
"vulnerability_reads" vr_inner,
unnest('{6,2,5,3,1,0,4,99}'::smallint[]) AS "report_types"("report_type"),
unnest('{1,2,4,5,6,7}'::smallint[]) AS "severities"("severity"),
unnest('{1,4}'::smallint[]) AS "states"("state"),
unnest(vr_inner."identifier_external_ids") AS "idt_external_ids"
WHERE
vr_inner."traversal_ids" >= '{9970}'
AND vr_inner."traversal_ids" < '{9971}'
AND vr_inner."archived" = FALSE
AND vr_inner."resolved_on_default_branch" = FALSE
AND vr_inner."report_type" = "report_types"."report_type"
AND vr_inner."severity" = "severities"."severity"
AND vr_inner."state" = "states"."state"
AND "idt_external_ids" LIKE 'A1:2%'
ORDER BY
vr_inner."severity" DESC,
vr_inner."traversal_ids" DESC,
vr_inner."vulnerability_id" DESC
LIMIT
101
)
ORDER BY
vr."severity" DESC,
vr."traversal_ids" DESC,
vr."vulnerability_id" DESC
LIMIT
101;
-
backend Results including filtering by Identifier will be restricted to a single page, and therefore should not include paging information (nextPage, etc) in the API response of
vulnerabilities
.vulnerabilitySeverityCounts
will not be applicable as we are limiting to a single page of the fetched results.
Edited by Bala Kumar