Update PossiblyAffectedOccurrencesFinder to work with both Dependency and Container Scanning components

Proposal

Once Ingest source package name from Trivy SBOM comp... (#427095 - closed) has been completed, we'll have a new sbom_source_packages.name column, which is only relevant to Container Scanning advisories. As such, we need to update the Sbom::PossiblyAffectedOccurrencesFinder#component_id method so it uses this new field to return matching occurrences for Container Scanning advisories.

Implementation Plan

  1. Add a new by_purl_type_and_name scope to the Sbom::SourcePackage class.

  2. Add a new filter_by_source_packages scope to the Sbom::Occurrence class, similar to Sbom::Occurrence.filter_by_components, however, we need to instead look up by the source_package:

    scope :filter_by_source_packages, ->(source_packages) do
  where(source_package: source_packages)
end

    This query should be efficient, since a new index should have already been added to the sbom_source_packages table as part of Ingest source package name from SBOM component properties

  3. Update Sbom::PossiblyAffectedOccurrencesFinder#execute_in_batches method:

    Note about alternative approach

    Instead of providing a conditional to check whether the purl_type is for dependency_scanning or container_scanning and then deciding whether to use the sbom_components.source_package_name or sbom_components.name fields, we could set the sbom_components.source_package_name value to sbom_components.name if the sbom_components.source_package_name value doesn't exist during SBOM component properties ingestion.

    In other words, if the properties[].aquasecurity:trivy:SrcName field does not exist (for example in the case of Dependency Scanning reports), automatically set sbom_components.source_package_name equal to sbom_components.name.

    This would allow us to only query the sbom_components.source_package_name column for both dependency_scanning and container_scanning advisories. The downside to this approach is that it requires extra storage space, since we might end up duplicating the sbom_components.name data. With regards to storage space, this extra data would consume about 4MB with the current production data, with the maximum possible required storage space is around 400MB.

    In any case, we discussed this approach and decided against it for now, but I wanted to include the details just in case it becomes necessary.

  4. Add unit tests and provide SQL query information for database review.

Auto-Summary 🤖

Discoto Usage

Points

Discussion points are declared by headings, list items, and single lines that start with the text (case-insensitive) point:. For example, the following are all valid points:

  • #### POINT: This is a point
  • * point: This is a point
  • + Point: This is a point
  • - pOINT: This is a point
  • point: This is a **point**

Note that any markdown used in the point text will also be propagated into the topic summaries.

Topics

Topics can be stand-alone and contained within an issuable (epic, issue, MR), or can be inline.

Inline topics are defined by creating a new thread (discussion) where the first line of the first comment is a heading that starts with (case-insensitive) topic:. For example, the following are all valid topics:

  • # Topic: Inline discussion topic 1
  • ## TOPIC: **{+A Green, bolded topic+}**
  • ### tOpIc: Another topic

Quick Actions

Action Description
/discuss sub-topic TITLE Create an issue for a sub-topic. Does not work in epics
/discuss link ISSUABLE-LINK Link an issuable as a child of this discussion

Last updated by this job

Discoto Settings 
---
summary:
  max_items: -1
  sort_by: created
  sort_direction: ascending

See the settings schema for details.

Edited by Lucas Charles