DeletedFinding.id doesn't match DeletedScan.deletedFindingId

[marcelo....@gmail.com]

Situation:

I will operate ThreadFix using REST. I'm creating applications and uploading scan reports just fine. Let's assume I want to remove all information after a report was sent.

It seems to me that there is no way to remove information using REST (https://github.com/denimgroup/threadfix/wiki/Threadfix-REST-Interface). I decided to use MySQL so I can query and exclude information on my own. I upgraded DB to MySQL. It is working just fine.

Scenario:

I uploaded and deleted a few scan reports manually (web interface). Querying DeletedFinding, DeletedScan, DeletedVulnerability and DeletedSurfaceLocation shows that the deleted information is there.

Attempted solution:

I'm using SELECT instead of DELETE so I can see the results first. I'm still working on the query.

SELECT *
FROM threadfix.DeletedFinding
JOIN threadfix.DeletedScan ON threadfix.DeletedScan.id = threadfix.DeletedFinding.deletedScanId
JOIN threadfix.DeletedSurfaceLocation ON threadfix.DeletedSurfaceLocation.deletedFindingId = threadfix.DeletedFinding.id
WHERE threadfix.DeletedScan.applicationId = 2
ORDER BY threadfix.DeletedFinding.id ASC;

ApplicationId is equal 2 in this exercise.

Achieved Result:

1,true,"2014-10-28 02:31:20","2014-10-28 02:31:20",3,false,false,NULL,false,78a4c9cb7b31906c06ee7f81962ca03c,1,NULL,83,9729,NULL,NULL,3,2,2,"2014-10-27 22:23:39",0,4,0,NULL,0,0,0,NULL,1,true,"2014-10-28 02:31:20","2014-10-28 02:31:20",1,NULL,NULL,/,0,NULL,NULL

2,true,"2014-10-28 02:31:20","2014-10-28 02:31:20",3,false,false,NULL,false,dc3d07d77d001c5bcb7547c81eb22329,1,NULL,86,9815,NULL,NULL,3,2,2,"2014-10-27 22:23:39",0,4,0,NULL,0,0,0,NULL,2,true,"2014-10-28 02:31:20","2014-10-28 02:31:20",2,NULL,NULL,"Web Server",0,NULL,NULL

3,true,"2014-10-28 02:31:20","2014-10-28 02:31:20",3,false,false,NULL,false,625fff93187841f515c2ed255d5fa930,1,NULL,83,10088,NULL,NULL,3,2,2,"2014-10-27 22:23:39",0,4,0,NULL,0,0,0,NULL,3,true,"2014-10-28 02:31:20","2014-10-28 02:31:20",3,NULL,NULL,"Web Server",0,NULL,NULL

Question / problem:

1. This query should have returned 18 rows (in this specific scenario), the number of rows I have in DeletedFinding (all rows belong to applicationId = 2)

I think the problem is that DeletedFinding.id doesn't match DeletedSurfaceLocation.deletedFindingId. DeletedFinding.id goes from 1 to 18, accounting for 18 rows. DeletedSurfaceLocation.deletedFindingId goes from 1 to 40, with a few gaps in between, accounting for 18 rows.

2. I could not JOIN DeletedVulnerability. All I had there was applicationId. I tried to JOIN using DeletedFinding but all I have there is deletedScanId, that I am already using. So I had to build another query:

SELECT *
FROM threadfix.DeletedVulnerability
WHERE threadfix.DeletedVulnerability.applicationId = 2;

Regards,
Marcelo Martins

[Dan Cornell]

The way we do deletes is a little goofy and might be causing the problems you are seeing. I think we explicitly set the ID of deleted objects to match the ID of the original objects, but Hibernate might be overriding that. What we probably ought to do is add an oridingalId field to the Deleted* objects so that could be used to correlate to the originals.

That said, what methods would you like to see added to the REST API to facilitate what you are trying to do? If you have a use case we haven't considered we can probably extend the API. It should be easy to add methods like deleteScan(scanId) and perhaps even a purgeDeletedData() (we'd need to think about that one a bit)

Thanks,

Dan