Use only the valid report findings in PipelineVulnerabilitiesFinder
What does this MR do and why?
With this change, we will reject report findings with missing required attributes and will not use them in MR security report comparison logic as those records are causing problems.
For more information: #397013 (comment 1318493767)
Database review
This change is marked as database one because we are changing an existing finder though we are not changing any existing query. The main purpose of this change is to remove firing an unnecessary query which is problematic;
Problematic query which won't be fired anymore after this change
SELECT
"vulnerabilities"."id" AS t0_r0,
"vulnerabilities"."milestone_id" AS t0_r1,
"vulnerabilities"."epic_id" AS t0_r2,
"vulnerabilities"."project_id" AS t0_r3,
"vulnerabilities"."author_id" AS t0_r4,
"vulnerabilities"."updated_by_id" AS t0_r5,
"vulnerabilities"."last_edited_by_id" AS t0_r6,
"vulnerabilities"."start_date_sourcing_milestone_id" AS t0_r7,
"vulnerabilities"."due_date_sourcing_milestone_id" AS t0_r8,
"vulnerabilities"."last_edited_at" AS t0_r9,
"vulnerabilities"."created_at" AS t0_r10,
"vulnerabilities"."updated_at" AS t0_r11,
"vulnerabilities"."start_date" AS t0_r12,
"vulnerabilities"."due_date" AS t0_r13,
"vulnerabilities"."state" AS t0_r14,
"vulnerabilities"."severity" AS t0_r15,
"vulnerabilities"."confidence" AS t0_r16,
"vulnerabilities"."severity_overridden" AS t0_r17,
"vulnerabilities"."confidence_overridden" AS t0_r18,
"vulnerabilities"."title" AS t0_r19,
"vulnerabilities"."title_html" AS t0_r20,
"vulnerabilities"."description" AS t0_r21,
"vulnerabilities"."description_html" AS t0_r22,
"vulnerabilities"."report_type" AS t0_r23,
"vulnerabilities"."cached_markdown_version" AS t0_r24,
"vulnerabilities"."resolved_by_id" AS t0_r25,
"vulnerabilities"."resolved_at" AS t0_r26,
"vulnerabilities"."confirmed_by_id" AS t0_r27,
"vulnerabilities"."confirmed_at" AS t0_r28,
"vulnerabilities"."dismissed_at" AS t0_r29,
"vulnerabilities"."dismissed_by_id" AS t0_r30,
"vulnerabilities"."resolved_on_default_branch" AS t0_r31,
"vulnerabilities"."present_on_default_branch" AS t0_r32,
"vulnerabilities"."detected_at" AS t0_r33,
"findings"."id" AS t1_r0,
"findings"."created_at" AS t1_r1,
"findings"."updated_at" AS t1_r2,
"findings"."severity" AS t1_r3,
"findings"."confidence" AS t1_r4,
"findings"."report_type" AS t1_r5,
"findings"."project_id" AS t1_r6,
"findings"."scanner_id" AS t1_r7,
"findings"."primary_identifier_id" AS t1_r8,
"findings"."project_fingerprint" AS t1_r9,
"findings"."location_fingerprint" AS t1_r10,
"findings"."uuid" AS t1_r11,
"findings"."name" AS t1_r12,
"findings"."metadata_version" AS t1_r13,
"findings"."raw_metadata" AS t1_r14,
"findings"."vulnerability_id" AS t1_r15,
"findings"."details" AS t1_r16,
"findings"."description" AS t1_r17,
"findings"."message" AS t1_r18,
"findings"."solution" AS t1_r19,
"findings"."cve" AS t1_r20,
"findings"."location" AS t1_r21,
"findings"."detection_method" AS t1_r22
FROM
"vulnerabilities"
LEFT OUTER JOIN "vulnerability_occurrences" "findings" ON "findings"."vulnerability_id" = "vulnerabilities"."id"
WHERE
"findings"."uuid" IS NULL
Note: I can't provide an execution plan as this query is a really long-running one ¯_(ツ)_/¯
Related to #397013 (closed).
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Mehmet Emin INAC