Duplicate Vulnerabilities in the Audit Tab Causes Fortify SSC SQL Duplicate Key Error

[eyups]

Issue Description

Under project page there is Audit tab for auditing vulnerabilities. On some project I realized that there is duplicate vulnerabilities listed. This also affects Fortify Integration. Processing findings.json fails because it produces same issueInstanceId resulting SQL duplicate key error in the Fortify SSC scan_issue table of the database.

Environment

Dependency Track Version: 3.7.1 DB: PostgreSQL

Jenkins plugin is used to upload BOM results produced by cyclonedx

Results

Expected Results

Only one vulnerability should be listed for a unique component.

Additional Info

Only one dependency is shown in Dependencies tab of the project

Only one vulnerability listed under All Vulnerabilities Section when you search it with CVE

[stevespringett]

This obviously should not be occurring.

Note: Verify that the upgrade required in #593 takes this scenario into account.

[stevespringett]

As a workaroiund, you may be able to clean this up manually by looking at the COMPONENT_VULNERABILTY table and removing any duplicate records.

[eyups]

Hi Steve,

Thanks for the quick response. I will try the workaround until that version will come then.

By the way I tried to remove project and recreate it. It still produces duplicate vulnerabilities. It seems to be happening specific vulnerabilities as it resulted the same ones duplicated again. Also noticed 2020 ones are duplicating, I couldn't see any CVE-2019 ones.

[eyups]

Hello all,

Here is the SQL command that I used to clear duplicate vulnerabilities on my PostgreSQL server. Wanted to share Steve's workaround suggestion in case of someone is having the same problem.

DELETE FROM COMPONENTS_VULNERABILITIES a USING ( SELECT MIN(ctid) as ctid, COMPONENT_ID, VULNERABILITY_ID FROM COMPONENTS_VULNERABILITIES GROUP BY COMPONENT_ID, VULNERABILITY_ID HAVING COUNT(*) > 1 ) b WHERE a.COMPONENT_ID = b.COMPONENT_ID AND a.VULNERABILITY_ID = b.VULNERABILITY_ID AND a.ctid <> b.ctid;

Also created fiddle for it https://www.db-fiddle.com/f/wwhfgGMETVnVN6HhMqZtkG/1

[stevespringett]

@eyups have you experienced this issue since running the SQL statement? Or has new duplicate vulns appeared since running it?

[eyups]

@stevespringett sorry for delayed answer. I didn't get any duplicate since then. There are about 600 projects was hard to check them always. But I confirmed from Fortify logs there was no Duplicate key error in the logs since then.

[stevespringett]

Thanks for the response. I'm going to close this ticket since it appears it's no longer an issue. It was likely an issue with a previous release that was fixed and that script simply cleaned up the prior mess. Please reopen if you see this issue again.

[github-actions[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.