BUG: Duplicate packages with same PURL break SBOM import and DejaCode component catalog

[rogu-beta]

Describe the bug
Importing an SBOM results in errors for several packages:

{'__all__': ['Package with this Dataspace, Type, Namespace, Name, Version, Qualifiers, Subpath, Download URL and Filename already exists.']}

Comparing the content of the SBOM with the inventory as well as existing package revealed, that the issue is caused by duplicate packages in the component catalog. Apparently there are packages with the same PURL, hash, type, name, and version. One is properly populated with scan data, while the other is not. (Edit: It seems that the above error is caused when there is an existing package without a download url, see #295 (comment) for the two distinct cases). I suspect the duplicate/broken one was formerly associated with a project that has since been deleted.

The even bigger issue is that while we can see those packages in the regular UI, they do not get shown in the admin's dashboard when searching for its name. Thus, deleting over 400 affected ones is a bit of a challenge, given that the error message does not indicate which specific packages cause the issue.

It seems that there is some uniqueness constraint not properly checked when importing SBOMs, as all packages have been imported through SBOMs.

To Reproduce
Unclear

Expected behavior
DejaCode should not allow to create duplicate packages through imported SBOMs.

Screenshots

Context (OS, Browser, Device, etc.):
n.a.

[rogu-beta]

Manually going through them sorted by date in the admin dashboard I think I managed to delete the affected ones manually.

[rogu-beta]

I also noticed that reimporting SBOMs on a product that already had an SBOM import can result in dependency count increasing, as it is seemingly not checking for duplicates. Perhaps this issue is related to that?

[rogu-beta]

The behavior is quite strange. For some packages the SBOM import will create new package entries, despite there already being one with the same PURL, albeit different / non-empty download URL. However, this does not happen for every package, so I'm still missing some factor that plays into this decision.

[rogu-beta]

Running "Improve Package from PurlDB" fails with duplicate key value violates unique constraint"component_catalog_packag_dataspace_id_type_namesp_c6620419_uniq"DETAIL:Key(dataspace_id,type,namespace,name,version,qualifiers,subpath,download_url,filename)=(3,npm,,parse-json,4.0.0,,,https://registry.npmjs.org/parse-json/-/parse-json-4.0.0.tgz,parse-json-4.0.0.tgz)alreadyexists. since assigning the download_url would make it a fully duplicate package.

[rogu-beta]

It seems there are two issues here:

• It seems that there are packages that already exist that would result in a uniqueness constraint violation, if the SBOM would create them again, resulting in the error: {'__all__': ['Package with this Dataspace, Type, Namespace, Name, Version, Qualifiers, Subpath, Download URL and Filename already exists.']}
  • It is unclear to me why it is not simply referencing the original package in a product package relationship as it would usually do
• DejaCode creates a new package and adds a product package relationship when the package import from the SBOM deviates only in the download URL. It seems that this happens when we already have a package in the catalog that was enhanced from PurlDB and even scanned and the one coming from the SBOM has no download url at all. However, this is not sufficient as criteria, there is something else at play or otherwise there would be far more duplicate packages
  • Attempting to enhance the package in the product with data from PurlDB fails, because assigning the download URL would violate the uniqueness constraint that covers dataspace_id, type, namespace, name, version, qualifiers, subpath, download_url, and filename

[rogu-beta]

I'll investigate the issue further today and see if I can narrow down condition under which this happens.

[tdruez]

Some clarification about a Package uniqueness from the source code:

# If one value of the filename, download_url, or any purl fields, changed,
# the Package is not a duplicate and can be created.
# Note that an empty string '' counts as a unique value.
#
# A `package_url` can be identical for multiple files.
# For example, ".zip" and ".whl" release of a Package may share the same `package_url`.
# Therefore, we only apply this unique constraint on `package_url` in the context of a
# `download_url` and `filename`.
# Also, a duplicated `download_url`+`filename` combination is allowed if any of the
# `package_url` fields is different.

Note that the filename + download_url combo predates the existence of PURL to define packages.

For example:

• https://files.pythonhosted.org/packages/4c/1b/c6da718c65228eb3a7ff7ba6a32d8e80fa840ca9057490504e099e4dd1ef/Django-5.2.tar.gz
• https://files.pythonhosted.org/packages/63/e0/6a5b5ea350c5bd63fe94b05e4c146c18facb51229d9dee42aa39f9fc2214/Django-5.2-py3-none-any.whl

In DejaCode, those 2 packages may share the same pkg:pypi/django@5.2 PURL but will exist are 2 separate packages.

[tdruez]

that the issue is caused by duplicate packages in the component catalog. Apparently there are packages with the same PURL, hash, type, name, and version.

I'm able to reproduce the error locally. This is a bug in the importer logic that occurs when multiple packages with the same PURL, but different download_url or filename, are present in the Dataspace.
The logic to "get" the proper Package instance is missing the "empty" fields, raising the MultipleObjectsReturned exception.
Since no package instance is found, the import process tries to create a new package entry raising the "unique" constraint exception.

I'll provide a fix to ensure the proper existing package (from the multiple records) is fetched and assigned.

For some packages the SBOM import will create new package entries, despite there already being one with the same PURL, albeit different / non-empty download URL. However, this does not happen for every package, so I'm still missing some factor that plays into this decision.

As explained above, the issue occurs when there's more than 1 package with the same PURL but different filename or download_url.

---

The even bigger issue is that while we can see those packages in the regular UI, they do not get shown in the admin's dashboard when searching for its name.

That would be a search bug. Have you tried the advanced syntax such as name=value?

I also noticed that reimporting SBOMs on a product that already had an SBOM import can result in dependency count increasing, as it is seemingly not checking for duplicates. Perhaps this issue is related to that?

That's a separate issue that should also be fixed.