Verify & Expand

The last week I have been working on verifying and expanding our inquiry into "missed vulnerabilities". Since we are defining this concept from scratch, we are now looking at both 6-month and 1 year intervals. As in "I participated on a file within 6 months of it being fixed for a vulnerability" or "I participated on a file within 1 year of it being fixed for a vulnerability". By defining "missed vulnerabilities" a few different ways and comparing how the metric performs, we will be able to find the best way to implement it.

It is challenging because the queries we are writing for this metric are very complex, and mistakes are easily made. Dr. Meneely and I are also verifying that each query we write is giving us the information we intend. This week we ran through our large query and confirmed they are correct, logically. Dr.Meneely is working with Nathan on writing verifies for them.

We have also added a direct count for "fixed vulnerabilities" where we are counting how many times a developer has owned a code review that fixed a vulnerability. We will also count how many times a developer participated on a code review that fixed a vulnerability. Since we are now going for metrics of 'blaming' developers in a sense, it is logical to give them credit for the fixes they are involved in as well.

SELECT cvenum_id, issue, owner_id, created
    FROM code_reviews 
    INNER JOIN code_reviews_cvenums ON code_reviews.issue = code_reviews_cvenums.code_review_id 
    WHERE created >= '2008-01-01 00:00:00' AND created < '2010-01-01 00:00:00' 
    ORDER BY cvenum_id, issue desc;

We also are counting the participations on fixes

SELECT cvenum_id, code_reviews.issue, code_reviews.owner_id, dev_id, created, review_date 
    FROM code_reviews 
    INNER JOIN code_reviews_cvenums ON code_reviews.issue = code_reviews_cvenums.code_review_id 
    INNER JOIN participants ON code_reviews.issue = participants.issue
    WHERE created >= '2008-01-01 00:00:00' AND created < '2010-01-01 00:00:00' 
    ORDER BY cvenum_id, issue desc;