CVE fix queries¶
These queries answer the questions about CVE fixes:
which commits fix which CVEs;
where each fix landed and when;
how fixes reached maintenance branches;
which branches never got a fix;
who makes the fixes;
what code the fixes touch;
whether a fix changed the project's own code or only a dependency version.
Read the analysis guide first: it explains the parameters, how to run a query, and the caveats every query here inherits.
All queries read only aveloxis_cve, except where Reads Aveloxis is noted. Each is tested against a seeded schema by TestAnalysisQueriesRun (internal/db).
How a fix is identified¶
The views do the matching (see cve_fix_commits):
|
Meaning |
|---|---|
|
The commit's own message names the CVE ( |
|
The commit carries a resolved |
|
The commit has the same |
A commit naming the CVE that undoes its fix (an odd revert chain, commit_cve_undoes) is never a fix. The views do not track whether a fix is still in effect: a branch where the fix was later reverted still shows the fix. CVE-12 answers that.
CVE-01 CVE inventory of a repository¶
Question: Which CVEs has this repository fixed, how was each fix found, and how far did it spread?
WITH default_branch AS (
SELECT branch_id
FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC
LIMIT 1
), fixes AS (
SELECT cve_id,
count(*) AS fix_commits,
count(*) FILTER (WHERE matched_by = 'message') AS by_message,
count(*) FILTER (WHERE matched_by = 'cherry_pick') AS by_cherry_pick,
count(*) FILTER (WHERE matched_by = 'patch_id') AS by_patch_id,
min(committed_at) AS first_committed_at
FROM aveloxis_cve.cve_fix_commits
WHERE repo_id = :repo_id
GROUP BY cve_id
), spread AS (
SELECT cve_id,
count(*) FILTER (WHERE archive_reason IS NULL) AS live_branches,
count(*) FILTER (WHERE archive_reason IS NOT NULL) AS archived_refs,
min(first_landed_at) FILTER (WHERE branch_id = (SELECT branch_id FROM default_branch)) AS landed_on_default,
max(lag_behind_first) AS slowest_lag
FROM aveloxis_cve.cve_fix_propagation
WHERE repo_id = :repo_id
GROUP BY cve_id
)
SELECT f.cve_id, f.fix_commits, f.by_message, f.by_cherry_pick, f.by_patch_id,
f.first_committed_at, s.landed_on_default,
COALESCE(s.live_branches, 0) AS live_branches,
COALESCE(s.archived_refs, 0) AS archived_refs,
s.slowest_lag
FROM fixes f
LEFT JOIN spread s USING (cve_id)
ORDER BY f.first_committed_at, f.cve_id;
Reading the result:
landed_on_defaultis NULL when no fix commit is on the default branch: the CVE was fixed only on other branches.slowest_lagis the longest delay between the first landing anywhere and a landing on another branch. It includes branches that inherited the fix by sharing history. CVE-04 separates those from real backports.
CVE-02 The fix commits of one CVE¶
Question: Which commits fix this CVE, who wrote them, and which refs contain them?
Reads Aveloxis: aveloxis_data.contributors for the login of a resolved author.
SELECT f.commit_hash, f.matched_by, f.subject, f.committed_at,
c.author_name, c.author_email, c.cntrb_id, ac.cntrb_login, f.patch_id,
array_agg(b.ref_name ORDER BY bc.landed_at, b.ref_name) FILTER (WHERE b.branch_id IS NOT NULL) AS on_refs
FROM aveloxis_cve.cve_fix_commits f
JOIN aveloxis_cve.commits c ON c.commit_id = f.commit_id
LEFT JOIN aveloxis_data.contributors ac ON ac.cntrb_id = c.cntrb_id
LEFT JOIN aveloxis_cve.branch_commits bc ON bc.commit_id = f.commit_id
LEFT JOIN aveloxis_cve.branch_repos b ON b.branch_id = bc.branch_id
WHERE f.repo_id = :repo_id AND f.cve_id = :'cve_id'
GROUP BY f.commit_hash, f.matched_by, f.subject, f.committed_at, c.author_name, c.author_email,
c.cntrb_id, ac.cntrb_login, f.patch_id
ORDER BY f.committed_at, f.commit_hash;
Reading the result: committed_at is the raw committer time. For when the change reached a branch, use landed_at (CVE-03). on_refs is empty for a commit reachable from no stored ref, which should not happen after a successful sync.
CVE-03 Propagation of one CVE across branches¶
Question: When did the fix for this CVE first land on each branch, and how long after the first branch?
SELECT p.branch_name, p.ref_name,
CASE WHEN p.archive_reason IS NULL THEN 'live' ELSE 'archived (' || p.archive_reason || ')' END AS state,
p.is_default, p.first_landed_at,
round((extract(epoch FROM p.lag_behind_first) / 86400)::numeric, 1) AS lag_days,
b.head_committed_at AS branch_last_commit_at,
b.behind_default, p.fix_commits
FROM aveloxis_cve.cve_fix_propagation p
JOIN aveloxis_cve.branch_repos b ON b.branch_id = p.branch_id
WHERE p.repo_id = :repo_id AND p.cve_id = :'cve_id'
ORDER BY p.first_landed_at, p.branch_name, p.ref_name;
Reading the result:
lag_days0 marks the branch (or branches) where the fix landed first.Archived refs keep the original branch name, so
ref_nametells two rows of one name apart.
CVE-04 How the fix reached each branch¶
Question: For each branch holding a fix, did it get the fix by sharing history with the default branch, or through a separate commit (a backport)? How long after the default branch?
|
Meaning |
|---|---|
|
The default branch itself. |
|
Every fix commit on the branch came to it with the default branch's history: the branch landed the fix through the same commit that brought it the commit which landed the fix on the default branch. That is the case when the branch was cut from, or merged with, the default branch after the fix landed there. |
|
At least one fix commit on the branch did not come to it with the default branch's history: the branch lacks the commit that landed the fix on the default branch, or had the fix earlier through its own history (it got that commit later, through a merge): a cherry-pick, a manual patch, or a follow-up fix made for this branch. A backport later merged up into the default branch stays a backport. A fix made on this branch first and merged into the default branch later is one too, with a negative |
|
The default branch holds no fix commit for the CVE. The branch was fixed on its own, for example an end-of-life branch, or one fixed before the default branch. |
|
An archived tip of the default branch itself, from a force-push or rename. Not a separate branch. |
WITH default_branch AS (
SELECT branch_id, branch_name
FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC
LIMIT 1
), default_landing AS (
SELECT cve_id, first_landed_at AS default_landed_at
FROM aveloxis_cve.cve_fix_propagation
WHERE repo_id = :repo_id AND branch_id = (SELECT branch_id FROM default_branch)
), on_branch AS (
SELECT p.cve_id, p.branch_id, p.branch_name, p.ref_name, p.archive_reason,
p.first_landed_at, p.lag_behind_first, bc.landed_at,
EXISTS (SELECT 1 FROM aveloxis_cve.branch_commits dc
WHERE dc.branch_id = (SELECT branch_id FROM default_branch)
AND dc.commit_id = f.commit_id
AND EXISTS (SELECT 1 FROM aveloxis_cve.branch_commits x
WHERE x.branch_id = bc.branch_id
AND x.commit_id = dc.landed_via
AND x.landed_via = bc.landed_via)) AS on_default
FROM aveloxis_cve.cve_fix_propagation p
JOIN aveloxis_cve.cve_fix_commits f ON f.repo_id = p.repo_id AND f.cve_id = p.cve_id
JOIN aveloxis_cve.branch_commits bc ON bc.commit_id = f.commit_id AND bc.branch_id = p.branch_id
WHERE p.repo_id = :repo_id
), reach AS (
SELECT o.cve_id, o.branch_id, o.branch_name, o.ref_name, o.archive_reason,
o.first_landed_at, o.lag_behind_first, d.default_landed_at,
count(*) AS fix_commits_on_branch,
count(*) FILTER (WHERE NOT o.on_default) AS branch_only_fix_commits,
min(o.landed_at) FILTER (WHERE NOT o.on_default) AS first_branch_only_landing,
CASE
WHEN o.branch_id = (SELECT branch_id FROM default_branch) THEN 'default branch'
WHEN o.branch_name = (SELECT branch_name FROM default_branch) THEN 'archived default-branch tip'
WHEN d.default_landed_at IS NULL THEN 'fixed only off the default branch'
WHEN bool_and(o.on_default) THEN 'shares history'
ELSE 'backport'
END AS reached_by
FROM on_branch o
LEFT JOIN default_landing d ON d.cve_id = o.cve_id
GROUP BY o.cve_id, o.branch_id, o.branch_name, o.ref_name, o.archive_reason,
o.first_landed_at, o.lag_behind_first, d.default_landed_at
), reached AS (
SELECT r.*,
extract(epoch FROM CASE WHEN r.reached_by = 'backport' THEN r.first_branch_only_landing
ELSE r.first_landed_at END
- r.default_landed_at) / 86400 AS days_after_default
FROM reach r
)
SELECT cve_id, branch_name, ref_name, archive_reason, first_landed_at, reached_by,
round(days_after_default::numeric, 1) AS days_after_default,
round((extract(epoch FROM lag_behind_first) / 86400)::numeric, 1) AS days_after_first,
fix_commits_on_branch, branch_only_fix_commits
FROM reached
ORDER BY cve_id, first_landed_at, ref_name;
Reading the result:
For
backportrows,days_after_defaultis the backport delay. It is measured from the default branch's first fix landing to the branch's first own fix commit (one not brought in through the default branch's history), so a branch that inherited part of a multi-commit fix and completed it later counts from the completion. It is negative when the branch was fixed before the default branch, for example a fix released on stable branches first.For other rows,
days_after_defaultcompares the branch's first fix landing with the default branch's. Forshares historyrows that only reflects when the branch was cut or merged. It is not a backport delay.The classes compare commits, not times, so a committer clock that ran fast on either branch cannot move a branch between
shares historyandbackport.Known limit: a
git pullmerge pushed to the default branch demotes the commits that were its tip to a second parent, so the pull merge becomes their landing commit. A branch cut at that earlier tip lacks the pull merge and reads asbackport. Its graph is the same as a backport merged up, so no rule on the graph alone can tell them apart.Archived refs keep the original branch name, so one branch can appear several times: live, and as each archived tip. CVE-06 counts each branch name once.
When the repository has no live default branch row, every branch is
fixed only off the default branch.
CVE-05 Live branches without a fix¶
Question: Which live branches contain no fix for a CVE the repository fixed elsewhere?
WITH cves AS (
SELECT cve_id, min(first_landed_at) AS first_fixed_at
FROM aveloxis_cve.cve_fix_propagation
WHERE repo_id = :repo_id
GROUP BY cve_id
)
SELECT c.cve_id, c.first_fixed_at, b.branch_name, b.head_committed_at AS branch_last_commit_at,
b.head_committed_at > c.first_fixed_at AS active_after_fix,
b.ahead_of_default, b.behind_default
FROM cves c
CROSS JOIN aveloxis_cve.branch_repos b
WHERE b.repo_id = :repo_id
AND b.archive_reason IS NULL
AND NOT EXISTS (SELECT 1 FROM aveloxis_cve.cve_fix_propagation p
WHERE p.repo_id = :repo_id AND p.cve_id = c.cve_id AND p.branch_id = b.branch_id)
ORDER BY c.cve_id, active_after_fix DESC, b.head_committed_at DESC NULLS LAST, b.ref_name;
Reading the result: aveloxis-cve knows where a fix is, not where a vulnerability is. A branch cut before the vulnerable code was written needs no fix. Treat active_after_fix (the branch got commits after the first fix landed) as a sign the branch was maintained while it lacked the fix, then confirm by reading the code.
CVE-06 Backport delay per CVE¶
Question: For each CVE fixed on the default branch, how many branches got a backport, and how long after the default branch?
The reached step is the one in CVE-04. A branch is counted once by name, whether it is live, archived, or both, and its delay is its earliest backport.
WITH default_branch AS (
SELECT branch_id, branch_name
FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC
LIMIT 1
), default_landing AS (
SELECT cve_id, first_landed_at AS default_landed_at
FROM aveloxis_cve.cve_fix_propagation
WHERE repo_id = :repo_id AND branch_id = (SELECT branch_id FROM default_branch)
), on_branch AS (
SELECT p.cve_id, p.branch_id, p.branch_name, p.ref_name, p.archive_reason,
p.first_landed_at, p.lag_behind_first, bc.landed_at,
EXISTS (SELECT 1 FROM aveloxis_cve.branch_commits dc
WHERE dc.branch_id = (SELECT branch_id FROM default_branch)
AND dc.commit_id = f.commit_id
AND EXISTS (SELECT 1 FROM aveloxis_cve.branch_commits x
WHERE x.branch_id = bc.branch_id
AND x.commit_id = dc.landed_via
AND x.landed_via = bc.landed_via)) AS on_default
FROM aveloxis_cve.cve_fix_propagation p
JOIN aveloxis_cve.cve_fix_commits f ON f.repo_id = p.repo_id AND f.cve_id = p.cve_id
JOIN aveloxis_cve.branch_commits bc ON bc.commit_id = f.commit_id AND bc.branch_id = p.branch_id
WHERE p.repo_id = :repo_id
), reach AS (
SELECT o.cve_id, o.branch_id, o.branch_name, o.ref_name, o.archive_reason,
o.first_landed_at, o.lag_behind_first, d.default_landed_at,
count(*) AS fix_commits_on_branch,
count(*) FILTER (WHERE NOT o.on_default) AS branch_only_fix_commits,
min(o.landed_at) FILTER (WHERE NOT o.on_default) AS first_branch_only_landing,
CASE
WHEN o.branch_id = (SELECT branch_id FROM default_branch) THEN 'default branch'
WHEN o.branch_name = (SELECT branch_name FROM default_branch) THEN 'archived default-branch tip'
WHEN d.default_landed_at IS NULL THEN 'fixed only off the default branch'
WHEN bool_and(o.on_default) THEN 'shares history'
ELSE 'backport'
END AS reached_by
FROM on_branch o
LEFT JOIN default_landing d ON d.cve_id = o.cve_id
GROUP BY o.cve_id, o.branch_id, o.branch_name, o.ref_name, o.archive_reason,
o.first_landed_at, o.lag_behind_first, d.default_landed_at
), reached AS (
SELECT r.*,
extract(epoch FROM CASE WHEN r.reached_by = 'backport' THEN r.first_branch_only_landing
ELSE r.first_landed_at END
- r.default_landed_at) / 86400 AS days_after_default
FROM reach r
), per_branch AS (
SELECT cve_id, branch_name,
bool_or(reached_by = 'backport') AS backported,
min(days_after_default) FILTER (WHERE reached_by = 'backport') AS days
FROM reached
WHERE default_landed_at IS NOT NULL
AND reached_by NOT IN ('default branch', 'archived default-branch tip')
GROUP BY cve_id, branch_name
)
SELECT d.cve_id,
count(pb.branch_name) AS other_branches_with_fix,
count(*) FILTER (WHERE pb.backported) AS backports,
round(min(pb.days)::numeric, 1) AS min_days,
round((percentile_cont(0.5) WITHIN GROUP (ORDER BY pb.days))::numeric, 1) AS median_days,
round(max(pb.days)::numeric, 1) AS max_days
FROM default_landing d
LEFT JOIN per_branch pb ON pb.cve_id = d.cve_id
GROUP BY d.cve_id
ORDER BY backports DESC, d.cve_id;
Reading the result: CVEs fixed only off the default branch are left out. CVE-04 lists them. The percentiles ignore branches without a backport, whose days is NULL.
CVE-07 Repository backport summary¶
Question: Across all CVEs of a repository, how many were backported, and how fast?
The reached step is the one in CVE-04. Each (CVE, branch name) pair with a backport counts once, at its earliest backport.
WITH default_branch AS (
SELECT branch_id, branch_name
FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC
LIMIT 1
), default_landing AS (
SELECT cve_id, first_landed_at AS default_landed_at
FROM aveloxis_cve.cve_fix_propagation
WHERE repo_id = :repo_id AND branch_id = (SELECT branch_id FROM default_branch)
), on_branch AS (
SELECT p.cve_id, p.branch_id, p.branch_name, p.ref_name, p.archive_reason,
p.first_landed_at, p.lag_behind_first, bc.landed_at,
EXISTS (SELECT 1 FROM aveloxis_cve.branch_commits dc
WHERE dc.branch_id = (SELECT branch_id FROM default_branch)
AND dc.commit_id = f.commit_id
AND EXISTS (SELECT 1 FROM aveloxis_cve.branch_commits x
WHERE x.branch_id = bc.branch_id
AND x.commit_id = dc.landed_via
AND x.landed_via = bc.landed_via)) AS on_default
FROM aveloxis_cve.cve_fix_propagation p
JOIN aveloxis_cve.cve_fix_commits f ON f.repo_id = p.repo_id AND f.cve_id = p.cve_id
JOIN aveloxis_cve.branch_commits bc ON bc.commit_id = f.commit_id AND bc.branch_id = p.branch_id
WHERE p.repo_id = :repo_id
), reach AS (
SELECT o.cve_id, o.branch_id, o.branch_name, o.ref_name, o.archive_reason,
o.first_landed_at, o.lag_behind_first, d.default_landed_at,
count(*) AS fix_commits_on_branch,
count(*) FILTER (WHERE NOT o.on_default) AS branch_only_fix_commits,
min(o.landed_at) FILTER (WHERE NOT o.on_default) AS first_branch_only_landing,
CASE
WHEN o.branch_id = (SELECT branch_id FROM default_branch) THEN 'default branch'
WHEN o.branch_name = (SELECT branch_name FROM default_branch) THEN 'archived default-branch tip'
WHEN d.default_landed_at IS NULL THEN 'fixed only off the default branch'
WHEN bool_and(o.on_default) THEN 'shares history'
ELSE 'backport'
END AS reached_by
FROM on_branch o
LEFT JOIN default_landing d ON d.cve_id = o.cve_id
GROUP BY o.cve_id, o.branch_id, o.branch_name, o.ref_name, o.archive_reason,
o.first_landed_at, o.lag_behind_first, d.default_landed_at
), reached AS (
SELECT r.*,
extract(epoch FROM CASE WHEN r.reached_by = 'backport' THEN r.first_branch_only_landing
ELSE r.first_landed_at END
- r.default_landed_at) / 86400 AS days_after_default
FROM reach r
), backports AS (
SELECT cve_id, branch_name, min(days_after_default) AS days
FROM reached
WHERE reached_by = 'backport'
GROUP BY cve_id, branch_name
)
SELECT (SELECT count(DISTINCT cve_id) FROM aveloxis_cve.cve_fix_commits WHERE repo_id = :repo_id) AS cves_fixed,
(SELECT count(*) FROM default_landing) AS cves_on_default,
count(DISTINCT cve_id) AS cves_backported,
count(*) AS backports,
round(avg(days)::numeric, 1) AS mean_days,
round((percentile_cont(0.5) WITHIN GROUP (ORDER BY days))::numeric, 1) AS median_days,
round((percentile_cont(0.9) WITHIN GROUP (ORDER BY days))::numeric, 1) AS p90_days,
round(max(days)::numeric, 1) AS max_days
FROM backports;
CVE-08 Who fixes CVEs¶
Question: Which contributors author CVE fixes, for how many CVEs, and over what period?
Reads Aveloxis: aveloxis_data.contributors for the login.
SELECT COALESCE(c.cntrb_id::text, lower(c.author_email)) AS author_key,
ac.cntrb_login,
min(c.author_name) AS author_name,
count(DISTINCT f.cve_id) AS cves,
count(DISTINCT f.commit_id) AS fix_commits,
count(DISTINCT f.commit_id) FILTER (WHERE f.matched_by <> 'message') AS fixes_not_naming_cve,
min(c.author_at) AS first_fix_authored,
max(c.author_at) AS last_fix_authored
FROM aveloxis_cve.cve_fix_commits f
JOIN aveloxis_cve.commits c ON c.commit_id = f.commit_id
LEFT JOIN aveloxis_data.contributors ac ON ac.cntrb_id = c.cntrb_id
WHERE f.repo_id = :repo_id
GROUP BY 1, ac.cntrb_login
ORDER BY cves DESC, fix_commits DESC, author_key;
Reading the result:
fixes_not_naming_cvecounts fix commits matched by a cherry-pick trailer or a patch-id rather than by their own message. Acherry-pick -xcopy usually names the CVE, so it is counted as amessagefix, not here.The author is the git author, not the committer. A cherry-pick keeps the original author, so the person who backported shows as the committer (
commits.committer_name), not here.An unresolved author (
cntrb_idNULL) is keyed by the lowercased email, so one person with resolved and unresolved commits appears twice. See DQ-03.
CVE-09 Size of each CVE's fix¶
Question: How large is each CVE's fix: how many distinct changes, files and lines?
The same fix is copied to other branches, so the query keeps one commit per distinct change, the earliest committed. Fix commits of one CVE are the same change when they are linked, directly or through others, by a shared patch-id or a resolved cherry-pick trailer. A chain of cherry-picks whose conflicts changed the diff is one change. This avoids counting one fix once per branch.
WITH RECURSIVE fx AS MATERIALIZED (
-- The repository's fix commits, read once. Copies sharing a patch-id
-- are one node; a fix without a patch-id is a node of its own.
SELECT cve_id, commit_id, commit_hash, committed_at,
CASE WHEN patch_id IS NULL THEN 'c:' || commit_hash ELSE 'p:' || patch_id END AS node
FROM aveloxis_cve.cve_fix_commits
WHERE repo_id = :repo_id
), links AS (
-- Resolved cherry-pick trailers between nodes of one CVE, both ways,
-- and every node to itself.
SELECT a.cve_id, a.node AS x, b.node AS y
FROM fx a
JOIN aveloxis_cve.commit_cherry_picks cp ON cp.commit_id = a.commit_id
JOIN fx b ON b.commit_id = cp.picked_from_id AND b.cve_id = a.cve_id
UNION
SELECT b.cve_id, b.node, a.node
FROM fx a
JOIN aveloxis_cve.commit_cherry_picks cp ON cp.commit_id = a.commit_id
JOIN fx b ON b.commit_id = cp.picked_from_id AND b.cve_id = a.cve_id
UNION
SELECT cve_id, node, node FROM fx
), reach (cve_id, x, y) AS (
SELECT cve_id, x, y FROM links
UNION
SELECT r.cve_id, r.x, l.y FROM reach r JOIN links l ON l.cve_id = r.cve_id AND l.x = r.y
), changes AS (
-- One commit per change: the earliest committed of each connected group.
SELECT DISTINCT rep.cve_id, rep.commit_id
FROM (
SELECT DISTINCT ON (r.cve_id, r.x) r.cve_id, m.commit_id
FROM reach r
JOIN fx m ON m.cve_id = r.cve_id AND m.node = r.y
ORDER BY r.cve_id, r.x, m.committed_at, m.commit_hash
) rep
)
SELECT ch.cve_id,
count(DISTINCT ch.commit_id) AS distinct_changes,
count(DISTINCT cf.file_path) AS files,
COALESCE(sum(cf.lines_added), 0) AS lines_added,
COALESCE(sum(cf.lines_removed), 0) AS lines_removed,
array_agg(DISTINCT cf.file_path) FILTER (WHERE cf.file_path IS NOT NULL) AS paths
FROM changes ch
LEFT JOIN aveloxis_cve.commit_files cf ON cf.commit_id = ch.commit_id
GROUP BY ch.cve_id
ORDER BY files DESC, ch.cve_id;
Reading the result:
Line counts are raw
--numstat(binary files count 0, a rename counts as a delete plus an add). A merge commit that names the CVE has no files.A manual backport adjusted to an old branch has its own patch-id and no trailer, so it counts as another distinct change.
CVE-10 Files most often touched by CVE fixes¶
Question: Which files do CVE fixes touch most often?
WITH RECURSIVE fx AS MATERIALIZED (
-- The repository's fix commits, read once. Copies sharing a patch-id
-- are one node; a fix without a patch-id is a node of its own.
SELECT cve_id, commit_id, commit_hash, committed_at,
CASE WHEN patch_id IS NULL THEN 'c:' || commit_hash ELSE 'p:' || patch_id END AS node
FROM aveloxis_cve.cve_fix_commits
WHERE repo_id = :repo_id
), links AS (
-- Resolved cherry-pick trailers between nodes of one CVE, both ways,
-- and every node to itself.
SELECT a.cve_id, a.node AS x, b.node AS y
FROM fx a
JOIN aveloxis_cve.commit_cherry_picks cp ON cp.commit_id = a.commit_id
JOIN fx b ON b.commit_id = cp.picked_from_id AND b.cve_id = a.cve_id
UNION
SELECT b.cve_id, b.node, a.node
FROM fx a
JOIN aveloxis_cve.commit_cherry_picks cp ON cp.commit_id = a.commit_id
JOIN fx b ON b.commit_id = cp.picked_from_id AND b.cve_id = a.cve_id
UNION
SELECT cve_id, node, node FROM fx
), reach (cve_id, x, y) AS (
SELECT cve_id, x, y FROM links
UNION
SELECT r.cve_id, r.x, l.y FROM reach r JOIN links l ON l.cve_id = r.cve_id AND l.x = r.y
), changes AS (
-- One commit per change: the earliest committed of each connected group.
SELECT DISTINCT rep.cve_id, rep.commit_id
FROM (
SELECT DISTINCT ON (r.cve_id, r.x) r.cve_id, m.commit_id
FROM reach r
JOIN fx m ON m.cve_id = r.cve_id AND m.node = r.y
ORDER BY r.cve_id, r.x, m.committed_at, m.commit_hash
) rep
)
SELECT cf.file_path,
count(DISTINCT ch.cve_id) AS cves,
array_agg(DISTINCT ch.cve_id) AS cve_ids,
(SELECT sum(x.lines_added + x.lines_removed)
FROM aveloxis_cve.commit_files x
WHERE x.file_path = cf.file_path
AND x.commit_id IN (SELECT commit_id FROM changes)) AS lines_changed
FROM changes ch
JOIN aveloxis_cve.commit_files cf ON cf.commit_id = ch.commit_id
GROUP BY cf.file_path
ORDER BY cves DESC, lines_changed DESC NULLS LAST, cf.file_path
LIMIT 50;
Reading the result: lines_changed counts each fix commit once, even when it fixes several CVEs. Paths are as of each fix commit. A file renamed later appears under both names.
CVE-11 Every copy of one CVE's fix¶
Question: Which commits carry the same change as a fix for this CVE, on any branch, whether or not they name the CVE?
WITH fix_patches AS (
SELECT DISTINCT patch_id
FROM aveloxis_cve.cve_fix_commits
WHERE repo_id = :repo_id AND cve_id = :'cve_id' AND patch_id IS NOT NULL
)
SELECT c.patch_id, e.commits AS copies_in_repo, c.commit_hash, c.subject, c.committed_at,
:'cve_id' = ANY (c.cve_ids) AS names_cve,
cardinality(c.cherry_picked_from) AS cherry_pick_trailers,
array_agg(b.ref_name ORDER BY bc.landed_at, b.ref_name) FILTER (WHERE b.branch_id IS NOT NULL) AS on_refs
FROM aveloxis_cve.commits c
JOIN aveloxis_cve.commit_equivalence e ON e.repo_id = c.repo_id AND e.patch_id = c.patch_id
LEFT JOIN aveloxis_cve.branch_commits bc ON bc.commit_id = c.commit_id
LEFT JOIN aveloxis_cve.branch_repos b ON b.branch_id = bc.branch_id
WHERE c.repo_id = :repo_id AND c.patch_id IN (SELECT patch_id FROM fix_patches)
GROUP BY c.commit_id, e.commits
ORDER BY c.patch_id, c.committed_at, c.commit_hash;
Reading the result: a patch-id equality has no direction. A group can mix the fix, its backports, and a reapply after a revert. names_cve false with cherry_pick_trailers 0 is a manual backport.
CVE-12 Fixes that were reverted¶
Question: Which CVE fixes were reverted, on which branches, and was the fix applied again afterwards?
commit_cve_undoes holds each commit that names a CVE and undoes its fix (a revert, or a revert of a reapply). For each branch holding such an undo, the query compares the latest fix landing with the latest undo landing on that branch. When they landed at the same time, it orders them by first-parent order and ancestry.
WITH RECURSIVE undo_landings AS (
SELECT u.cve_id, bc.branch_id, max(bc.landed_at) AS last_undo_at,
array_agg(DISTINCT c.commit_hash) AS undo_commits
FROM aveloxis_cve.commit_cve_undoes u
JOIN aveloxis_cve.commits c ON c.commit_id = u.commit_id
JOIN aveloxis_cve.branch_commits bc ON bc.commit_id = u.commit_id
WHERE c.repo_id = :repo_id
GROUP BY u.cve_id, bc.branch_id
), fix_landings AS (
SELECT f.cve_id, bc.branch_id, max(bc.landed_at) AS last_fix_at
FROM aveloxis_cve.cve_fix_commits f
JOIN aveloxis_cve.branch_commits bc ON bc.commit_id = f.commit_id
WHERE f.repo_id = :repo_id
GROUP BY f.cve_id, bc.branch_id
), tie_ends AS (
-- Where the latest fix and the latest undo landed at the same time:
-- the fix and undo commits that landed then, the first-parent commit
-- that landed each, whether each is a merge, and a merge's first parent.
SELECT u.cve_id, u.branch_id, u.last_undo_at AS landed_at, 'undo' AS kind, x.commit_id, bc.landed_via,
cardinality(c.parent_hashes) > 1 AS is_merge, fp.commit_id AS first_parent_id
FROM undo_landings u
JOIN fix_landings fl ON fl.cve_id = u.cve_id AND fl.branch_id = u.branch_id AND fl.last_fix_at = u.last_undo_at
JOIN aveloxis_cve.commit_cve_undoes x ON x.cve_id = u.cve_id
JOIN aveloxis_cve.commits c ON c.commit_id = x.commit_id
JOIN aveloxis_cve.branch_commits bc ON bc.branch_id = u.branch_id AND bc.commit_id = x.commit_id
AND bc.landed_at = u.last_undo_at
LEFT JOIN aveloxis_cve.commits fp ON cardinality(c.parent_hashes) > 1
AND fp.repo_id = c.repo_id AND fp.commit_hash = c.parent_hashes[1]
UNION
SELECT u.cve_id, u.branch_id, u.last_undo_at, 'fix', f.commit_id, bc.landed_via,
cardinality(c.parent_hashes) > 1, fp.commit_id
FROM undo_landings u
JOIN fix_landings fl ON fl.cve_id = u.cve_id AND fl.branch_id = u.branch_id AND fl.last_fix_at = u.last_undo_at
JOIN aveloxis_cve.cve_fix_commits f ON f.repo_id = :repo_id AND f.cve_id = u.cve_id
JOIN aveloxis_cve.commits c ON c.commit_id = f.commit_id
JOIN aveloxis_cve.branch_commits bc ON bc.branch_id = u.branch_id AND bc.commit_id = f.commit_id
AND bc.landed_at = u.last_undo_at
LEFT JOIN aveloxis_cve.commits fp ON cardinality(c.parent_hashes) > 1
AND fp.repo_id = c.repo_id AND fp.commit_hash = c.parent_hashes[1]
), below (cve_id, branch_id, landed_at, start_id, commit_id) AS (
-- Ancestors (and the commit itself) of each such commit, of each landing
-- commit and of each merge's first parent. A commit between two of them
-- descends from the lower one, so it cannot have landed earlier: the
-- walk stays among the commits that landed at the same time.
SELECT DISTINCT e.cve_id, e.branch_id, e.landed_at, s.id, s.id
FROM tie_ends e CROSS JOIN LATERAL (VALUES (e.commit_id), (e.landed_via), (e.first_parent_id)) s(id)
WHERE s.id IS NOT NULL
UNION
SELECT w.cve_id, w.branch_id, w.landed_at, w.start_id, p.commit_id
FROM below w
JOIN aveloxis_cve.commits c ON c.commit_id = w.commit_id
JOIN aveloxis_cve.commits p ON p.repo_id = c.repo_id AND p.commit_hash = ANY (c.parent_hashes)
JOIN aveloxis_cve.branch_commits bc ON bc.branch_id = w.branch_id AND bc.commit_id = p.commit_id
AND bc.landed_at = w.landed_at
), later_than (cve_id, branch_id, later_id, earlier_id) AS (
-- Pairs of them in landing order. Across landings: the first-parent
-- order of the commits that landed them. Within one landing, a merge
-- names the CVE but carries no change of its own, so it never outranks
-- a change: a commit it merged in (an ancestor not on its first-parent
-- side) is later than it, and so is a non-merge descendant. Nothing
-- else is ordered there, so the latest change is always among the
-- latest of them.
SELECT l.cve_id, l.branch_id, l.commit_id, e.commit_id
FROM tie_ends l
JOIN tie_ends e ON e.cve_id = l.cve_id AND e.branch_id = l.branch_id AND e.commit_id <> l.commit_id
WHERE (l.landed_via <> e.landed_via
AND EXISTS (SELECT 1 FROM below w WHERE w.cve_id = l.cve_id AND w.branch_id = l.branch_id
AND w.start_id = l.landed_via AND w.commit_id = e.landed_via))
OR (l.landed_via = e.landed_via AND e.is_merge
AND EXISTS (SELECT 1 FROM below w WHERE w.cve_id = l.cve_id AND w.branch_id = l.branch_id
AND w.start_id = e.commit_id AND w.commit_id = l.commit_id)
AND NOT EXISTS (SELECT 1 FROM below w WHERE w.cve_id = l.cve_id AND w.branch_id = l.branch_id
AND w.start_id = e.first_parent_id AND w.commit_id = l.commit_id))
OR (NOT l.is_merge
AND EXISTS (SELECT 1 FROM below w WHERE w.cve_id = l.cve_id AND w.branch_id = l.branch_id
AND w.start_id = l.commit_id AND w.commit_id = e.commit_id))
), tie_order AS (
-- The latest of them are those nothing is later than. Within one
-- landing the latest change is always among them; across landings a
-- later landing's merge naming the CVE counts as a change. When they
-- are all undos or all fixes, that is the answer; when they are both,
-- the order is unknown.
SELECT e.cve_id, e.branch_id,
bool_and(e.kind = 'undo') AS undo_last, bool_and(e.kind = 'fix') AS fix_last
FROM tie_ends e
WHERE NOT EXISTS (SELECT 1 FROM later_than t
WHERE t.cve_id = e.cve_id AND t.branch_id = e.branch_id AND t.earlier_id = e.commit_id)
GROUP BY e.cve_id, e.branch_id
)
SELECT u.cve_id, b.branch_name, b.ref_name, b.archive_reason,
fl.last_fix_at, u.last_undo_at, u.undo_commits,
CASE
WHEN fl.last_fix_at IS NULL THEN 'undo without a fix on this branch'
WHEN fl.last_fix_at > u.last_undo_at THEN 'fixed again after the revert'
WHEN fl.last_fix_at < u.last_undo_at THEN 'reverted; not fixed again'
WHEN o.fix_last THEN 'fixed again after the revert'
WHEN o.undo_last THEN 'reverted; not fixed again'
ELSE 'order unknown: landed together'
END AS status
FROM undo_landings u
JOIN aveloxis_cve.branch_repos b ON b.branch_id = u.branch_id
LEFT JOIN fix_landings fl ON fl.cve_id = u.cve_id AND fl.branch_id = u.branch_id
LEFT JOIN tie_order o ON o.cve_id = u.cve_id AND o.branch_id = u.branch_id
ORDER BY u.cve_id, b.branch_name, b.ref_name;
Reading the result:
reverted; not fixed againmeans the branch's latest fix landing precedes its latest undo, or, when they landed at the same time, comes before it in first-parent and ancestry order. CVE-03 still lists the branch as fixed, because the fix commit is still in its history.Landing order is a heuristic. A reapply that changed the code without naming the CVE is not seen.
The latest fix and the latest undo can land at the same time: one merge brings both, two first-parent commits land in the same second, or a first-parent commit whose clock is behind its parent's, or implausibly far ahead, takes its parent's landing time. The query then orders them:
across landings, every commit, merges included, by the first-parent commits that landed them;
within one landing, a commit after its ancestors, except that a merge never outranks a change: a merge names the CVE but carries no change of its own, so the commits it merged in (not on its first-parent side) and the non-merge commits after it are later than it, and nothing on its first-parent side is ordered against it.
Merges naming the CVE are common: a pull-request merge carries the PR title, so the merge of a fix PR names the CVE, and so does the merge of a revert PR ("Revert ..."), which has no "This reverts commit" line and so counts as a fix. When the mainline is merged into a release branch, these land together with the commits they merged; they then never outrank those commits, so a merged revert reads "reverted". Within one landing, the latest change is always among the latest commits, so an answer naming one kind is right there. Across landings, a merge naming the CVE counts as a change: a later landing merge naming the fix reads as a reapply, as it would a second later.
order unknown: landed togethermeans the latest include both kinds: a revert and an independent fix merged together, or a merge naming the CVE with only commits on its first-parent side (a branch updated from the mainline, say) of the other kind. The same history fast-forwarded, its merges on the first-parent chain, can then read "fixed again" or "reverted".Any later commit naming the CVE counts as a reapply, including one that only mentions it (a changelog entry, say).
The undo rules follow named commits and cherry-pick trailers only (the "safe subset", see
commit_cve_undoes).
CVE-13 CVE fix activity over time¶
Question: How many CVEs were first fixed each quarter, and how many backports landed?
WITH default_branch AS (
SELECT branch_id
FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC
LIMIT 1
), firsts AS (
SELECT cve_id, min(first_landed_at) AS first_fixed_at
FROM aveloxis_cve.cve_fix_propagation
WHERE repo_id = :repo_id
GROUP BY cve_id
), other_landings AS (
SELECT cve_id, first_landed_at
FROM aveloxis_cve.cve_fix_propagation
WHERE repo_id = :repo_id AND lag_behind_first > interval '0'
AND branch_id IS DISTINCT FROM (SELECT branch_id FROM default_branch)
)
SELECT q.quarter,
(SELECT count(*) FROM firsts f WHERE date_trunc('quarter', f.first_fixed_at) = q.quarter) AS cves_first_fixed,
(SELECT count(*) FROM other_landings o WHERE date_trunc('quarter', o.first_landed_at) = q.quarter) AS later_branch_landings
FROM (SELECT DISTINCT date_trunc('quarter', t) AS quarter
FROM (SELECT first_fixed_at AS t FROM firsts UNION ALL SELECT first_landed_at FROM other_landings) x) q
ORDER BY q.quarter;
Reading the result: later_branch_landings counts every landing after the first on a branch row other than the default branch: each live branch and each archived ref, including branches that only inherited the fix. Use CVE-04 to keep only backports.
CVE-14 Advisories named only by GHSA id¶
Question: Which commits name a GitHub advisory (GHSA) but no CVE?
The views match CVE ids only, so these commits are not in cve_fix_commits.
SELECT c.commit_hash, c.ghsa_ids, c.subject, c.committed_at,
array_agg(b.ref_name ORDER BY bc.landed_at, b.ref_name) FILTER (WHERE b.branch_id IS NOT NULL) AS on_refs
FROM aveloxis_cve.commits c
LEFT JOIN aveloxis_cve.branch_commits bc ON bc.commit_id = c.commit_id
LEFT JOIN aveloxis_cve.branch_repos b ON b.branch_id = bc.branch_id
WHERE c.repo_id = :repo_id
AND cardinality(c.ghsa_ids) > 0
AND cardinality(c.cve_ids) = 0
GROUP BY c.commit_id, c.commit_hash, c.ghsa_ids, c.subject, c.committed_at
ORDER BY c.committed_at, c.commit_hash;
Reading the result: a GHSA usually has a CVE alias. Mapping one to the other needs an advisory database, which aveloxis-cve does not hold.
CVE-15 CVEs fixed only on archived refs¶
Question: Which CVEs have fixes only on deleted or force-pushed branches, and on no live branch?
SELECT p.cve_id,
array_agg(DISTINCT p.ref_name) AS archived_refs,
min(p.first_landed_at) AS first_landed_at
FROM aveloxis_cve.cve_fix_propagation p
WHERE p.repo_id = :repo_id
GROUP BY p.cve_id
HAVING bool_and(p.archive_reason IS NOT NULL)
ORDER BY p.cve_id;
Reading the result: a fix only on a force-pushed ref may have been rewritten into a commit that no longer names the CVE, or dropped. Check CVE-11 for a copy with the same patch-id on a live branch.
CVE-16 Code fixes versus dependency, build and docs changes¶
Question: For each CVE, did its fix change the project's own code, or only dependency versions, build files, tests or documentation?
A commit message naming a CVE is often a dependency upgrade that fixes a vulnerability in a library, not in the project. This query classifies each distinct change (as in CVE-09) by the paths its commit touched, and each CVE by its changes:
|
Meaning |
|---|---|
|
At least one change touched source code that is not a test: program source, scripts, templates and UI files ( |
|
Changes touched build or dependency files ( |
|
Changes touched test code, and perhaps docs or configuration, but no code or build file. A test is a source file under a test directory ( |
|
Changes touched only documentation (anything under |
|
Every change is a commit with no file list (a merge). |
WITH RECURSIVE fx AS MATERIALIZED (
-- The repository's fix commits, read once. Copies sharing a patch-id
-- are one node; a fix without a patch-id is a node of its own.
SELECT cve_id, commit_id, commit_hash, committed_at,
CASE WHEN patch_id IS NULL THEN 'c:' || commit_hash ELSE 'p:' || patch_id END AS node
FROM aveloxis_cve.cve_fix_commits
WHERE repo_id = :repo_id
), links AS (
-- Resolved cherry-pick trailers between nodes of one CVE, both ways,
-- and every node to itself.
SELECT a.cve_id, a.node AS x, b.node AS y
FROM fx a
JOIN aveloxis_cve.commit_cherry_picks cp ON cp.commit_id = a.commit_id
JOIN fx b ON b.commit_id = cp.picked_from_id AND b.cve_id = a.cve_id
UNION
SELECT b.cve_id, b.node, a.node
FROM fx a
JOIN aveloxis_cve.commit_cherry_picks cp ON cp.commit_id = a.commit_id
JOIN fx b ON b.commit_id = cp.picked_from_id AND b.cve_id = a.cve_id
UNION
SELECT cve_id, node, node FROM fx
), reach (cve_id, x, y) AS (
SELECT cve_id, x, y FROM links
UNION
SELECT r.cve_id, r.x, l.y FROM reach r JOIN links l ON l.cve_id = r.cve_id AND l.x = r.y
), changes AS (
-- One commit per change: the earliest committed of each connected group.
SELECT DISTINCT rep.cve_id, rep.commit_id
FROM (
SELECT DISTINCT ON (r.cve_id, r.x) r.cve_id, m.commit_id
FROM reach r
JOIN fx m ON m.cve_id = r.cve_id AND m.node = r.y
ORDER BY r.cve_id, r.x, m.committed_at, m.commit_hash
) rep
), files AS (
-- Each file a fix change touched, by kind of path. The patterns are
-- heuristics: adjust them to the project. The test-name rule (?c) is
-- case-sensitive, so Latest.java or Submit.java stay code. Dockerfiles
-- are matched after code, so dockerfile.go stays code. The test-name
-- rule also takes a few production classes named like tests (Groovy's
-- ASTTest annotation, Spark's ANOVATest): rare enough to accept.
SELECT ch.cve_id, ch.commit_id, cf.file_path,
CASE
WHEN cf.file_path ~* '(^|/)(pom\.xml|[^/]*\.gradle(\.kts)?|gradle\.properties|gradle-wrapper\.properties|libs\.versions\.toml|build\.xml|ivy\.xml|[^/]*\.sbt|package\.json|package-lock\.json|npm-shrinkwrap\.json|yarn\.lock|pnpm-lock\.yaml|go\.mod|go\.sum|go\.work|cargo\.(toml|lock)|gemfile(\.lock)?|[^/]*\.gemspec|requirements[^/]*\.txt|pyproject\.toml|poetry\.lock|pipfile(\.lock)?|setup\.(py|cfg)|composer\.(json|lock)|[^/]*\.csproj|packages\.config|cmakelists\.txt|makefile|gnumakefile|meson\.build|configure\.(ac|in))$'
OR cf.file_path ~* '(^|/)(requirements|buildsrc|\.build|vendor|third[_-]?party|node_modules|bower_components)/'
OR cf.file_path ~* '\.min\.(js|css)$'
OR cf.file_path ~* '(^|/)project/[^/]*\.scala$'
THEN 'dependency/build'
WHEN cf.file_path ~* '(^|/)docs?/'
AND (cf.file_path !~* '(^|/)src/(.*/)?docs?/' OR cf.file_path ~* '/resources/docs?/')
THEN 'docs'
WHEN cf.file_path ~* '\.(java|kt|kts|scala|groovy|c|h|cc|cpp|cxx|hpp|hh|go|py|js|mjs|cjs|ts|tsx|jsx|vue|svelte|rb|erb|php|twig|cs|cshtml|razor|rs|swift|m|mm|sh|bat|cmd|ps1|pl|lua|erl|ex|exs|clj|sql|jsp|jspx|tag|tagx|gsp|jelly|xsl|xslt|ftl|vm|hbs|mustache|ejs|pug|jinja|j2|html?|xhtml)$'
THEN CASE WHEN cf.file_path ~* '(^|/)(src/it|test|tests|__tests__|testing|itests|test-infra|test-scripts|integration-tests?|e2e|e2e-tests?|end-to-end-tests|[^/]*-end-to-end-tests|[^/]*-e2e-tests?)/'
OR (cf.file_path ~* '(^|/)spec/' AND cf.file_path !~* '(^|/)src/')
OR cf.file_path ~* '(_test|_spec|\.test|\.spec|[._-]e2e-spec)\.[a-z]+$'
OR cf.file_path ~ '(?c)([A-Za-z0-9](Test|Tests|TestCase)|[a-z0-9]IT)\.[A-Za-z]+$'
THEN 'test' ELSE 'code' END
WHEN cf.file_path ~* '(^|/)((docker|container)file([.-][^/]*)?|[^/]*\.dockerfile)$'
THEN 'dependency/build'
WHEN cf.file_path ~* '\.(adoc|asciidoc|md|markdown|rst|txt)$'
THEN 'docs'
ELSE 'config/other'
END AS kind
FROM changes ch
JOIN aveloxis_cve.commit_files cf ON cf.commit_id = ch.commit_id
), per_change AS (
SELECT ch.cve_id, ch.commit_id,
count(f.file_path) FILTER (WHERE f.kind = 'code') AS code_files,
count(f.file_path) FILTER (WHERE f.kind = 'test') AS test_files,
count(f.file_path) FILTER (WHERE f.kind = 'dependency/build') AS build_files,
count(f.file_path) FILTER (WHERE f.kind = 'docs') AS docs_files,
count(f.file_path) FILTER (WHERE f.kind = 'config/other') AS other_files,
CASE
WHEN count(f.file_path) = 0 THEN 'no files'
WHEN bool_or(f.kind = 'code') THEN 'code'
WHEN bool_or(f.kind = 'dependency/build') THEN 'dependency or build'
WHEN bool_or(f.kind = 'test') THEN 'test only'
ELSE 'docs or config only'
END AS change_kind
FROM changes ch
LEFT JOIN files f ON f.cve_id = ch.cve_id AND f.commit_id = ch.commit_id
GROUP BY ch.cve_id, ch.commit_id
)
SELECT cve_id,
CASE
WHEN bool_or(change_kind = 'code') THEN 'code'
WHEN bool_or(change_kind = 'dependency or build') THEN 'dependency or build'
WHEN bool_or(change_kind = 'test only') THEN 'test only'
WHEN bool_or(change_kind = 'docs or config only') THEN 'docs or config only'
ELSE 'no files'
END AS cve_kind,
count(*) AS distinct_changes,
sum(code_files) AS code_files,
sum(test_files) AS test_files,
sum(build_files) AS build_files,
sum(docs_files) AS docs_files,
sum(other_files) AS other_files
FROM per_change
GROUP BY cve_id
ORDER BY cve_kind, cve_id;
Reading the result:
The classes come from file paths only. A generated source file counts as code, and a source file under
docs/as documentation, unless thedocs/is a package inside asrc/tree (and not aresources/docs/directory).Adjust the patterns in the
filesstep to the project (for example, add a project's own version catalog).To restrict another CVE query to code fixes, keep the CVEs this query classes
code.
CVE-17 How many CVE fixes change code¶
Question: Across a repository, how many CVEs were fixed by changing its own code, and how many only by dependency, build, test or documentation changes?
The steps are those of CVE-16.
WITH RECURSIVE fx AS MATERIALIZED (
-- The repository's fix commits, read once. Copies sharing a patch-id
-- are one node; a fix without a patch-id is a node of its own.
SELECT cve_id, commit_id, commit_hash, committed_at,
CASE WHEN patch_id IS NULL THEN 'c:' || commit_hash ELSE 'p:' || patch_id END AS node
FROM aveloxis_cve.cve_fix_commits
WHERE repo_id = :repo_id
), links AS (
-- Resolved cherry-pick trailers between nodes of one CVE, both ways,
-- and every node to itself.
SELECT a.cve_id, a.node AS x, b.node AS y
FROM fx a
JOIN aveloxis_cve.commit_cherry_picks cp ON cp.commit_id = a.commit_id
JOIN fx b ON b.commit_id = cp.picked_from_id AND b.cve_id = a.cve_id
UNION
SELECT b.cve_id, b.node, a.node
FROM fx a
JOIN aveloxis_cve.commit_cherry_picks cp ON cp.commit_id = a.commit_id
JOIN fx b ON b.commit_id = cp.picked_from_id AND b.cve_id = a.cve_id
UNION
SELECT cve_id, node, node FROM fx
), reach (cve_id, x, y) AS (
SELECT cve_id, x, y FROM links
UNION
SELECT r.cve_id, r.x, l.y FROM reach r JOIN links l ON l.cve_id = r.cve_id AND l.x = r.y
), changes AS (
-- One commit per change: the earliest committed of each connected group.
SELECT DISTINCT rep.cve_id, rep.commit_id
FROM (
SELECT DISTINCT ON (r.cve_id, r.x) r.cve_id, m.commit_id
FROM reach r
JOIN fx m ON m.cve_id = r.cve_id AND m.node = r.y
ORDER BY r.cve_id, r.x, m.committed_at, m.commit_hash
) rep
), files AS (
-- Each file a fix change touched, by kind of path. The patterns are
-- heuristics: adjust them to the project. The test-name rule (?c) is
-- case-sensitive, so Latest.java or Submit.java stay code. Dockerfiles
-- are matched after code, so dockerfile.go stays code. The test-name
-- rule also takes a few production classes named like tests (Groovy's
-- ASTTest annotation, Spark's ANOVATest): rare enough to accept.
SELECT ch.cve_id, ch.commit_id, cf.file_path,
CASE
WHEN cf.file_path ~* '(^|/)(pom\.xml|[^/]*\.gradle(\.kts)?|gradle\.properties|gradle-wrapper\.properties|libs\.versions\.toml|build\.xml|ivy\.xml|[^/]*\.sbt|package\.json|package-lock\.json|npm-shrinkwrap\.json|yarn\.lock|pnpm-lock\.yaml|go\.mod|go\.sum|go\.work|cargo\.(toml|lock)|gemfile(\.lock)?|[^/]*\.gemspec|requirements[^/]*\.txt|pyproject\.toml|poetry\.lock|pipfile(\.lock)?|setup\.(py|cfg)|composer\.(json|lock)|[^/]*\.csproj|packages\.config|cmakelists\.txt|makefile|gnumakefile|meson\.build|configure\.(ac|in))$'
OR cf.file_path ~* '(^|/)(requirements|buildsrc|\.build|vendor|third[_-]?party|node_modules|bower_components)/'
OR cf.file_path ~* '\.min\.(js|css)$'
OR cf.file_path ~* '(^|/)project/[^/]*\.scala$'
THEN 'dependency/build'
WHEN cf.file_path ~* '(^|/)docs?/'
AND (cf.file_path !~* '(^|/)src/(.*/)?docs?/' OR cf.file_path ~* '/resources/docs?/')
THEN 'docs'
WHEN cf.file_path ~* '\.(java|kt|kts|scala|groovy|c|h|cc|cpp|cxx|hpp|hh|go|py|js|mjs|cjs|ts|tsx|jsx|vue|svelte|rb|erb|php|twig|cs|cshtml|razor|rs|swift|m|mm|sh|bat|cmd|ps1|pl|lua|erl|ex|exs|clj|sql|jsp|jspx|tag|tagx|gsp|jelly|xsl|xslt|ftl|vm|hbs|mustache|ejs|pug|jinja|j2|html?|xhtml)$'
THEN CASE WHEN cf.file_path ~* '(^|/)(src/it|test|tests|__tests__|testing|itests|test-infra|test-scripts|integration-tests?|e2e|e2e-tests?|end-to-end-tests|[^/]*-end-to-end-tests|[^/]*-e2e-tests?)/'
OR (cf.file_path ~* '(^|/)spec/' AND cf.file_path !~* '(^|/)src/')
OR cf.file_path ~* '(_test|_spec|\.test|\.spec|[._-]e2e-spec)\.[a-z]+$'
OR cf.file_path ~ '(?c)([A-Za-z0-9](Test|Tests|TestCase)|[a-z0-9]IT)\.[A-Za-z]+$'
THEN 'test' ELSE 'code' END
WHEN cf.file_path ~* '(^|/)((docker|container)file([.-][^/]*)?|[^/]*\.dockerfile)$'
THEN 'dependency/build'
WHEN cf.file_path ~* '\.(adoc|asciidoc|md|markdown|rst|txt)$'
THEN 'docs'
ELSE 'config/other'
END AS kind
FROM changes ch
JOIN aveloxis_cve.commit_files cf ON cf.commit_id = ch.commit_id
), per_change AS (
SELECT ch.cve_id, ch.commit_id,
count(f.file_path) FILTER (WHERE f.kind = 'code') AS code_files,
count(f.file_path) FILTER (WHERE f.kind = 'test') AS test_files,
count(f.file_path) FILTER (WHERE f.kind = 'dependency/build') AS build_files,
count(f.file_path) FILTER (WHERE f.kind = 'docs') AS docs_files,
count(f.file_path) FILTER (WHERE f.kind = 'config/other') AS other_files,
CASE
WHEN count(f.file_path) = 0 THEN 'no files'
WHEN bool_or(f.kind = 'code') THEN 'code'
WHEN bool_or(f.kind = 'dependency/build') THEN 'dependency or build'
WHEN bool_or(f.kind = 'test') THEN 'test only'
ELSE 'docs or config only'
END AS change_kind
FROM changes ch
LEFT JOIN files f ON f.cve_id = ch.cve_id AND f.commit_id = ch.commit_id
GROUP BY ch.cve_id, ch.commit_id
), per_cve AS (
SELECT cve_id,
CASE
WHEN bool_or(change_kind = 'code') THEN 'code'
WHEN bool_or(change_kind = 'dependency or build') THEN 'dependency or build'
WHEN bool_or(change_kind = 'test only') THEN 'test only'
WHEN bool_or(change_kind = 'docs or config only') THEN 'docs or config only'
ELSE 'no files'
END AS cve_kind
FROM per_change
GROUP BY cve_id
)
SELECT cve_kind,
count(*) AS cves,
round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct_of_cves
FROM per_cve
GROUP BY cve_kind
ORDER BY cves DESC, cve_kind;