[checklists] Ensured CVE ordering is numeric and not lexicographic.

Fixes #2577.

3 files changed, 67 insertions, 6 deletions

diff --git a/checklists/admin.py b/checklists/admin.py
index 35ca1c5b..48d61e7b 100644
--- a/checklists/admin.py
+++ b/checklists/admin.py
@@ -14,6 +14,7 @@ from .models import (
     SecurityIssue,
     SecurityIssueReleasesThrough,
     SecurityRelease,
+    cve_sort_key,
 )
 
 
@@ -101,7 +102,6 @@ class SecurityIssueAdmin(admin.ModelAdmin):
     ]
     list_filter = ["severity", "release"]
     search_fields = ["cve_year_number", "summary", "description", "commit_hash_main"]
-    ordering = ["-updated_at", "-created_at", "-cve_year_number"]
     readonly_fields = [
         "cvss_base_severity",
         "cvss_vector",
@@ -197,6 +197,13 @@ class SecurityIssueAdmin(admin.ModelAdmin):
         ),
     )
 
+    def get_ordering(self, request):
+        return [
+            "-updated_at",
+            "-created_at",
+            *cve_sort_key(desc=True),
+        ]
+
     @admin.display(description="CVE Record")
     def cve_json_record_link(self, obj):
         url = obj.get_absolute_url()
@@ -212,4 +219,9 @@ class SecurityIssueReleasesThroughAdmin(admin.ModelAdmin):
         "release__version",
         "commit_hash",
     ]
-    ordering = ["-securityissue__cve_year_number", "release__version"]
+
+    def get_ordering(self, request):
+        return [
+            *cve_sort_key("securityissue__cve_year_number", desc=True),
+            "release__version",
+        ]
diff --git a/checklists/models.py b/checklists/models.py
index 60bad0a8..27d7ec47 100644
--- a/checklists/models.py
+++ b/checklists/models.py
@@ -4,6 +4,7 @@ import json
 from django.contrib.auth.models import User
 from django.core.validators import MaxValueValidator, MinValueValidator
 from django.db import models
+from django.db.models.functions import Cast, Substr
 from django.shortcuts import reverse
 from django.template.defaultfilters import urlize
 from django.template.loader import render_to_string
@@ -16,6 +17,18 @@ from .templatetags.checklist_extras import enumerate_items, format_releases_for_
 
 CNA_DSF_UUID = "6a34fbeb-21d4-45e7-8e0a-62b95bc12c92"
 
+
+# CVE IDs have the form CVE-YYYY-NNNNN. The year (4 digits) is always at
+# positions 5-8 and the number starts at position 10 (both 1-based). This
+# helper extracts each part as an integer for correct numeric DB-level sorting.
+def cve_sort_key(field="cve_year_number", *, desc=False):
+    year = Cast(Substr(field, 5, 4), output_field=models.IntegerField())
+    number = Cast(Substr(field, 10), output_field=models.IntegerField())
+    if desc:
+        return year.desc(), number.desc()
+    return year, number
+
+
 # CVSS metrics choices.
 
 CVSS_ATTACK_VECTOR_CHOICES = [  # AV
@@ -385,13 +398,13 @@ class SecurityRelease(ReleaseChecklist):
 
     @cached_property
     def cves(self):
-        return [cve for cve in self.securityissue_set.all().order_by("cve_year_number")]
+        return list(self.securityissue_set.all().order_by(*cve_sort_key()))
 
     @cached_property
     def cnas(self):
         return (
             self.securityissue_set.all()
-            .order_by("cve_year_number")
+            .order_by(*cve_sort_key())
             .values_list("cna", flat=True)
         )
 
@@ -443,14 +456,17 @@ class SecurityRelease(ReleaseChecklist):
                 "securityissue", "release"
             )
             .filter(securityissue__release_id=self.id)
-            .order_by("securityissue__id", "-release__version")
+            .order_by(
+                *cve_sort_key("securityissue__cve_year_number"),
+                "-release__version",
+            )
         ] + [
             {
                 "branch": "main",
                 "cve": issue.cve_year_number,
                 "hash": issue.commit_hash_main,
             }
-            for issue in self.securityissue_set.all()
+            for issue in self.cves
         ]
 
     def get_absolute_url(self):
diff --git a/checklists/tests/test_models.py b/checklists/tests/test_models.py
index 9fecd5c0..e1259f63 100644
--- a/checklists/tests/test_models.py
+++ b/checklists/tests/test_models.py
@@ -319,6 +319,39 @@ class SecurityReleaseChecklistTestCase(BaseChecklistTestCaseMixin, TestCase):
         self.assertEqual(list(checklist.cnas), [])
         self.assertEqual(checklist.hashes_by_versions, [])
 
+    def test_cves_numeric_sort_order_on_cve_year_number(self):
+        release = self.factory.make_release(version="5.2")
+        checklist = self.make_checklist(releases=[])
+        # Lexicographic sort would put 10000 before 9999 ("1" < "9").
+        issue_9999 = self.factory.make_security_issue(
+            checklist, [release], cve_year_number="CVE-2025-9999"
+        )
+        issue_10000 = self.factory.make_security_issue(
+            checklist, [release], cve_year_number="CVE-2025-10000"
+        )
+        self.assertEqual(checklist.cves, [issue_9999, issue_10000])
+
+    def test_hashes_by_versions_numeric_sort_order_on_cve_year_number(self):
+        release = self.factory.make_release(version="5.2")
+        checklist = self.make_checklist(releases=[])
+        # Lexicographic sort would put 10000 before 9999 ("1" < "9").
+        self.factory.make_security_issue(
+            checklist,
+            [release],
+            cve_year_number="CVE-2025-9999",
+            commit_hash_main="main9999",
+        )
+        self.factory.make_security_issue(
+            checklist,
+            [release],
+            cve_year_number="CVE-2025-10000",
+            commit_hash_main="main10000",
+        )
+        self.assertEqual(
+            [h["cve"] for h in checklist.hashes_by_versions],
+            ["CVE-2025-9999", "CVE-2025-10000", "CVE-2025-9999", "CVE-2025-10000"],
+        )
+
     def test_render_checklist_simple(self):
         checklist = self.make_checklist()
         checklist_content = self.do_render_checklist(checklist)