From d2f3228e1ae5ce34c63115b1a3a67019276fc74b Mon Sep 17 00:00:00 2001 From: Sylvain Beucler Date: Fri, 12 Feb 2021 17:09:54 +0100 Subject: tracker_service: display CVE entries using natural sort order [#76] to avoid annoying confusions with the default incorrect sort due to e.g. CVE-2021-3392 considered higher than CVE-2021-20203 Approach: - use 'COLLATE natorder' [1]; however, we'd have to leave the bug unfixed for a few years, until this feature is merged and packaged in stable sqlite3 [1] https://sqlite.org/forum/forumpost/e4dc6f3331 - sort at the Python level; AFAICS this breaks the current code global logic that delegates the sort to the database, so we'd need to revamp the Python code or introduce ad-hoc logic - use a size-bounded sort at the SQL level (current patch) using a reasonable max size (10 digits / 32-bits), until 1) is available. (variable-length is feasible but impacts readability and performance) --- lib/python/security_db.py | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) (limited to 'lib') diff --git a/lib/python/security_db.py b/lib/python/security_db.py index e8167f3187..a7979f61da 100644 --- a/lib/python/security_db.py +++ b/lib/python/security_db.py @@ -137,7 +137,14 @@ BugsForSourcePackage_query = \ JOIN source_packages sp ON (st.package = sp.rowid) WHERE sp.name = ? AND (bugs.name LIKE 'CVE-%' OR bugs.name LIKE 'TEMP-%') - ORDER BY bugs.name DESC, sp.release""" + ORDER BY + -- 'COLLATE natorder' emulation, using 0-padding (MR#76) + -- e.g. CVE-2016-1000393 -> CVE-2016-0001000393 + CASE substr(bugs.name,1,3) + WHEN 'CVE' THEN + substr(bugs.name,1,9) || substr("0000000000"||substr(bugs.name, 10, 10), -10) + ELSE bugs.name + END DESC, sp.release""" # Sort order is important for the groupby operation below. def getBugsForSourcePackage(cursor, pkg): -- cgit v1.2.3