Skip to content

Add api for sbom occurrences

Zamir Martins requested to merge add_api_for_occurrences into master

What does this MR do and why?

Add api for sbom occurrences related vulnerabilities. This endpoint is going to be used by the frontend in order to populate the vulnerabilities based on an occurrence.

PoC of the solution can be found in the following: Draft:[PoC]: Set the initial structure to load ... (!134695 - closed) • Zamir Martins • 16.8

This endpoint will be added behind the feature flag: sbom_occurrences_vulnerabilities

This is a follow-up from Ignore sbom_occurrences.vulnerabilities and add... (!135746 - merged) • Zamir Martins • 16.7

EE: true

Related issue: #432645 (closed)

Query plan

Link to query plan

SELECT "vulnerabilities"."id", "vulnerabilities"."project_id", "vulnerabilities"."author_id", "vulnerabilities"."created_at", "vulnerabilities"."updated_at", "vulnerabilities"."title", "vulnerabilities"."title_html", "vulnerabilities"."description", "vulnerabilities"."description_html", "vulnerabilities"."state", "vulnerabilities"."severity", "vulnerabilities"."severity_overridden", "vulnerabilities"."confidence", "vulnerabilities"."confidence_overridden", "vulnerabilities"."resolved_by_id", "vulnerabilities"."resolved_at", "vulnerabilities"."report_type", "vulnerabilities"."cached_markdown_version", "vulnerabilities"."confirmed_by_id", "vulnerabilities"."confirmed_at", "vulnerabilities"."dismissed_at", "vulnerabilities"."dismissed_by_id", "vulnerabilities"."resolved_on_default_branch", "vulnerabilities"."present_on_default_branch", "vulnerabilities"."detected_at", "vulnerabilities"."finding_id", "vulnerabilities"."cvss"
FROM "vulnerabilities"
WHERE "vulnerabilities"."id" IN (
  SELECT "sbom_occurrences_vulnerabilities"."vulnerability_id"
  FROM "sbom_occurrences_vulnerabilities"
  WHERE "sbom_occurrences_vulnerabilities"."sbom_occurrence_id" = 2222963392
  ORDER BY "sbom_occurrences_vulnerabilities"."vulnerability_id" ASC
  LIMIT 100
);

WHERE EXIST: alternative didn't perform better for a total of 1000 records.

Note the table sbom_occurrences_vulnerabilities hasn't been populated in production yet. Thus the following records were created:

exec insert into sbom_occurrences_vulnerabilities (vulnerability_id, sbom_occurrence_id, created_at, updated_at)
select vulnerabilities.id as vulnerability_id, (select sbom_occurrences.id from sbom_occurrences where sbom_occurrences.id = 2222963392 limit 1) as sbom_occurrence_id, vulnerabilities.created_at, vulnerabilities.updated_at
from vulnerabilities
limit 1000;

API request/response

Example request:

http://gdk.test:3000/api/v4/occurrences/vulnerabilities?id=57983

Example response:

[{"occurrence_id":"57983","id":1107,"name":"Inefficient Regular Expression Complexity","url":"http://gdk.test:3000/top-group/project-1/-/security/vulnerabilities/1107","severity":"medium"}]

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Zamir Martins

Merge request reports