security_db.py 78.2 KB
Newer Older
1 2
# security_db.py -- simple, CVE-driven Debian security bugs database
# Copyright (C) 2005 Florian Weimer <fw@deneb.enyo.de>
3
#
4 5 6 7
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
8
#
9 10 11 12
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
13
#
14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

"""This module implements a small database for tracking security bugs.

Note that the database is always secondary to the text files.  The
database is only an implementation tool, and not used for maintaining
the data.

The data is kept in a SQLite 3 database.

FIXME: Document the database schema once it is finished.
"""

import apsw
30
import base64
31
import bugs
32 33 34
import cPickle
import cStringIO
import glob
35
import os
36
import os.path
37 38 39
import re
import sys
import types
40
import zlib
41

42 43 44
import debian_support
import dist_config

45 46 47 48 49
class InsertError(Exception):
    """Class for capturing insert errors.

    The 'errors' member collects all error messages.
    """
50

51 52 53 54
    def __init__(self, errors):
        assert len(errors) > 0, errors
        assert type(errors) == types.ListType, errors
        self.errors = errors
55

56 57 58
    def __str__(self):
        return self.errors[0] + ' [more...]'

59 60
def mergeLists(a, b):
    """Merges two lists."""
Luciano Bello's avatar
Luciano Bello committed
61
    if type(a) == types.UnicodeType:
62 63 64 65
        if a == "":
            a = []
        else:
            a = a.split(',')
Luciano Bello's avatar
Luciano Bello committed
66
    if type(b) == types.UnicodeType:
67 68 69 70 71 72 73 74 75 76 77 78 79
        if b == "":
            b = []
        else:
            b = b.split(',')
    result = {}
    for x in a:
        result[x] = 1
    for x in b:
        result[x] = 1
    result = result.keys()
    result.sort()
    return result

80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95
class NVDEntry:
    """A class for an entry in the nvd_data table.
    Objects have the same fileds as the table."""
    def __init__(self, row, description):
        for x in range(len(row)):
            setattr(self, description[x][0], row[x])
    def rangeString(self):
        result = []
        if self.range_local:
            result.append("local")
        if self.range_remote:
            result.append("remote")
        if self.range_user_init:
            result.append("user-initiated")
        return ", ".join(result)

96 97 98 99 100
class SchemaMismatch(Exception):
    """Raised to indicate a schema mismatch.

    The caller is expected to remove and regenerate the database."""

101 102 103 104 105 106 107 108 109 110 111
class DB:
    """Access to the security database.

    This is a wrapper around an SQLite database object (which is
    accessible as the "db" member.

    Most operations need a special cursor object, which can be created
    with a cursor object.  The name "cursor" is somewhat of a
    misnomer because these objects are quite versatile.
    """

112
    def __init__(self, name, verbose=False):
113
        self.name = name
114
        self.db = apsw.Connection(name)
115
        self.verbose = verbose
116 117 118 119 120 121 122 123 124
        c = self.cursor()

        # This gives us better performance (it's usually the file
        # system block size).  This must come first to be effective.

        c.execute("PRAGMA page_size = 4096")

        # Enable WAL.  This means that updates will not block readers.
        c.execute("PRAGMA journal_mode = WAL")
125

126
        self.schema_version = 22
127
        self._initFunctions()
128

129 130 131
        for (v,) in c.execute("PRAGMA user_version"):
            if v == 0:
                self.initSchema()
132 133
            elif v == 20:
                self._initSchema20()
134 135 136 137 138 139 140 141 142
            elif v == 21:
                # Remove legacy views.
                for view in ('testing_status', 'stable_status',
                             'oldstable_status'):
                    try:
                        c.execute('DROP VIEW ' + view)
                    except apsw.SQLError:
                        pass
                c.execute("PRAGMA user_version = 22")
143
            elif v <> self.schema_version:
144 145 146
                if self.verbose:
                    print "DB: schema version mismatch: expected %d, got %d" \
                          % (self.schema_version, v)
147
                raise SchemaMismatch, `v`
148
            self._initViews(c)
149 150 151
            return
        assert False

152 153 154
    def __del__(self):
        self.db.close()

155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178
    def cursor(self):
        """Creates a new database cursor.

        Also see the writeTxn method."""
        return self.db.cursor()

    def writeTxn(self):
        """Creates a cursor for an exclusive transaction.

        No other process may modify the database at the same time.
        After finishing the work, you should invoke the commit or
        rollback methods below.
        """
        c = self.cursor()
        c.execute("BEGIN TRANSACTION EXCLUSIVE")
        return c

    def commit(self, cursor):
        """Makes the changes in the transaction permanent."""
        cursor.execute("COMMIT")

    def rollback(self, cursor):
        """Undos the changes in the transaction."""
        cursor.execute("ROLLBACK")
179

180 181 182 183
    def initSchema(self):
        """Creates the database schema."""
        cursor = self.cursor()

184 185 186 187 188 189
        # Set the schema version to an invalid value which is
        # different from zero.  We can use this to detect a partially
        # created schema.

        cursor.execute("PRAGMA user_version = 1")

190 191
        cursor.execute("""CREATE TABLE inodeprints
        (file TEXT NOT NULL PRIMARY KEY,
192 193
         inodeprint TEXT NOT NULL,
         parsed BLOB)""")
194

195 196 197 198
        cursor.execute("""CREATE TABLE version_linear_order
        (id INTEGER NOT NULL PRIMARY KEY,
         version TEXT NOT NULL UNIQUE)""")

199 200 201 202
        cursor.execute(
            """CREATE TABLE source_packages
            (name TEXT NOT NULL,
            release TEXT NOT NULL,
203
            subrelease TEXT NOT NULL,
204 205 206
            archive TEXT NOT NULL,
            version TEXT NOT NULL,
            version_id INTEGER NOT NULL DEFAULT 0,
207
            PRIMARY KEY (name, release, subrelease, archive))""")
208 209 210 211 212

        cursor.execute(
            """CREATE TABLE binary_packages
            (name TEXT NOT NULL,
            release TEXT NOT NULL,
213
            subrelease TEXT NOT NULL,
214 215 216 217 218
            archive TEXT NOT NULL,
            version TEXT NOT NULL,
            source TEXT NOT NULL,
            source_version TEXT NOT NULL,
            archs TEXT NOT NULL,
219
            PRIMARY KEY (name, release, subrelease, archive, version, source,
220 221 222 223
            source_version))""")
        cursor.execute(
            """CREATE INDEX binary_packages_source
            ON binary_packages(source)""")
224 225 226 227 228 229 230

        cursor.execute("""CREATE TABLE package_notes
        (id INTEGER NOT NULL PRIMARY KEY,
         bug_name TEXT NOT NULL,
         package TEXT NOT NULL,
         fixed_version TEXT
             CHECK (fixed_version IS NULL OR fixed_version <> ''),
231
         fixed_version_id INTEGER NOT NULL DEFAULT 0,
232
         release TEXT NOT NULL,
233
         package_kind TEXT NOT NULL DEFAULT 'unknown',
234 235
         urgency TEXT NOT NULL,
         bug_origin TEXT NOT NULL DEFAULT '')""")
236
        cursor.execute(
237 238
            """CREATE UNIQUE INDEX package_notes_bug
            ON package_notes(bug_name, package, release)""")
239 240 241
        cursor.execute(
            """CREATE INDEX package_notes_package
            ON package_notes(package)""")
242 243 244 245 246 247 248 249 250 251 252 253 254

        cursor.execute("""CREATE TABLE debian_bugs
        (bug INTEGER NOT NULL,
         note INTEGER NOT NULL,
         PRIMARY KEY (bug, note))""")

        cursor.execute("""CREATE TABLE bugs
        (name TEXT NOT NULL PRIMARY KEY,
         cve_status TEXT NOT NULL
             CHECK (cve_status IN
                    ('', 'CANDIDATE', 'ASSIGNED', 'RESERVED', 'REJECTED')),
         not_for_us INTEGER NOT NULL CHECK (not_for_us IN (0, 1)),
         description TEXT NOT NULL,
255
         release_date TEXT NOT NULL,
256 257 258 259 260 261 262 263 264 265 266 267 268
         source_file TEXT NOT NULL,
         source_line INTEGER NOT NULL)""")

        cursor.execute("""CREATE TABLE bugs_notes
        (bug_name TEXT NOT NULL CHECK (typ <> ''),
         typ TEXT NOT NULL CHECK (typ IN ('TODO', 'NOTE')),
         release TEXT NOT NULL DEFAULT '',
         comment TEXT NOT NULL CHECK (comment <> ''))""")

        cursor.execute("""CREATE TABLE bugs_xref
        (source TEXT NOT NULL,
         target TEXT NOT NULL,
         PRIMARY KEY (source, target))""")
269
        cursor.execute("CREATE INDEX bugs_xref_target ON bugs_xref(target)")
270

271
        cursor.execute("""CREATE TABLE bug_status
272 273
        (bug_name TEXT NOT NULL,
         release TEXT NOT NULL,
274
         status TEXT NOT NULL
275
             CHECK (status IN ('vulnerable', 'fixed', 'unknown', 'undetermined',
276
                               'partially-fixed', 'todo')),
277
         reason TEXT NOT NULL,
278
         PRIMARY KEY (bug_name, release))""")
279

280
        cursor.execute("""CREATE TABLE source_package_status
281
        (bug_name TEXT NOT NULL,
282 283
         package INTEGER NOT NULL,
         vulnerable INTEGER NOT NULL,
284 285
         urgency TEXT NOT NULL,
         PRIMARY KEY (bug_name, package))""")
286
        cursor.execute(
287 288
            """CREATE INDEX source_package_status_package
            ON source_package_status(package)""")
289

290 291 292
        cursor.execute(
            "CREATE TABLE removed_packages (name TEXT NOT NULL PRIMARY KEY)")

293 294 295
        cursor.execute(
            """CREATE TABLE nvd_data
            (cve_name TEXT NOT NULL PRIMARY KEY,
296
            cve_desc TEXT NOT NULL,
297 298 299 300 301 302 303 304 305 306 307 308 309
            discovered TEXT NOT NULL,
            published TEXT NOT NULL,
            severity TEXT NOT NULL,
            range_local INTEGER,
            range_remote INTEGER,
            range_user_init INTEGER,
            loss_avail INTEGER NOT NULL,
            loss_conf INTEGER NOT NULL,
            loss_int INTEGER NOT NULL,
            loss_sec_prot_user INTEGER NOT NULL,
            loss_sec_prot_admin INTEGER NOT NULL,
            loss_sec_prot_other INTEGER NOT NULL)""")

310 311 312 313 314
        cursor.execute(
            """CREATE TABLE debsecan_data
            (name TEXT NOT NULL PRIMARY KEY,
            data TEXT NOT NULL)""")

315 316 317 318 319 320
        self._initNoDSA(cursor)

        cursor.execute("PRAGMA user_version = %d" % self.schema_version)

    def _initSchema20(self):
        cursor = self.db.cursor()
321

322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339
        cursor.execute("PRAGMA user_version = 1")
        self._initNoDSA(cursor)
        self._initViews(cursor)
        cursor.execute("DELETE FROM inodeprints WHERE file ='data/CVE/list'")
        cursor.execute("PRAGMA user_version = %d" % self.schema_version)

    def _initNoDSA(self, cursor):
        cursor.execute(
            """CREATE TABLE package_notes_nodsa
            (bug_name TEXT NOT NULL,
            package TEXT NOT NULL,
            release TEXT NOT NULL,
            reason TEXT NOT NULL,
            comment TEXT NOT NULL,
            PRIMARY KEY (bug_name, package, release))
            """)

    def _initViews(self, cursor):
340
        cursor.execute(
341
            """CREATE TEMPORARY VIEW testing_status AS
342 343
            SELECT DISTINCT sp.name AS package, st.bug_name AS bug,
            sp.archive AS section, st.urgency AS urgency,
344
            st.vulnerable AS vulnerable,
345 346 347 348 349 350 351 352 353 354
            (SELECT vulnerable
            FROM source_packages AS sidp, source_package_status AS sidst
            WHERE sidp.name = sp.name
            AND sidp.release = 'sid' AND sidp.subrelease = ''
            AND sidp.archive = sp.archive
            AND sidst.bug_name = st.bug_name
            AND sidst.package = sidp.rowid) AS unstable_vulnerable,
            COALESCE((SELECT NOT vulnerable
            FROM source_packages AS tsecp, source_package_status AS tsecst
            WHERE tsecp.name = sp.name
355
            AND tsecp.release = 'jessie' AND tsecp.subrelease = 'security'
356 357
            AND tsecp.archive = sp.archive
            AND tsecst.bug_name = st.bug_name
358 359
            AND tsecst.package = tsecp.rowid), 0) AS testing_security_fixed,
            (SELECT range_remote FROM nvd_data
360 361 362 363
             WHERE cve_name = st.bug_name) AS remote,
            (EXISTS (SELECT * FROM package_notes_nodsa AS pnd
            WHERE pnd.bug_name = st.bug_name
            AND pnd.package = sp.name
364
            AND pnd.release = 'jessie')) AS no_dsa
365
            FROM source_package_status AS st, source_packages AS sp
366
            WHERE st.vulnerable > 0 AND sp.rowid = st.package
367
            AND sp.release = 'jessie' AND sp.subrelease = ''
368
            ORDER BY sp.name, st.urgency, st.bug_name""")
369

370
        for (name, nickname) in (('stable', 'wheezy'), ('oldstable', 'squeeze'),):
371
            cursor.execute(
372
                """CREATE TEMPORARY VIEW %s_status AS
373 374
                SELECT DISTINCT sp.name AS package, st.bug_name AS bug,
                sp.archive AS section, st.urgency AS urgency,
375
                st.vulnerable AS vulnerable,
376
                (SELECT range_remote FROM nvd_data
377 378 379 380 381
                 WHERE cve_name = st.bug_name) AS remote,
                (EXISTS (SELECT * FROM package_notes_nodsa AS pnd
                 WHERE pnd.bug_name = st.bug_name
                 AND pnd.package = sp.name
                 AND pnd.release = '%s')) AS no_dsa
382
                FROM source_package_status AS st, source_packages AS sp
383 384
                WHERE st.vulnerable > 0 AND sp.rowid = st.package
                AND sp.release = '%s' AND sp.subrelease = ''
385 386 387
                AND NOT COALESCE((SELECT NOT vulnerable
                FROM source_packages AS secp, source_package_status AS secst
                WHERE secp.name = sp.name
388
                AND secp.release = '%s' AND ( secp.subrelease = 'security' OR secp.subrelease = 'lts' )
389 390 391 392
                AND secp.archive = sp.archive
                AND secst.bug_name = st.bug_name
                AND secst.package = secp.rowid), 0)
                ORDER BY sp.name, urgency_to_number(urgency), st.bug_name"""
393
                % (name, nickname, nickname, nickname))
394

395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426
    def _initFunctions(self):
        """Registers user-defined SQLite functions."""

        def string_list_add(lst, *args):
            for arg in args:
                lst.append(arg)
        def string_list_to_string(lst):
            return ', '.join(lst)
        def string_list_factory():
            return ([], string_list_add, string_list_to_string)
        self.db.createaggregatefunction("string_list", string_list_factory)

        def string_set_add(lst, *args):
            for arg in args:
                for arch in arg.split(','):
                    lst[arch] = True
        def string_set_to_archs(lst):
            l = lst.keys()
            l.sort()
            return ','.join(l)
        def string_set_factory():
            return ({}, string_set_add, string_set_to_archs)
        self.db.createaggregatefunction("string_set", string_set_factory)

        urgencies = ['high', 'medium', 'low', 'unimportant']
        def urgency_to_number(u):
            try:
                return urgencies.index(u)
            except ValueError:
                return 999
        self.db.createscalarfunction("urgency_to_number", urgency_to_number, 1)

427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446
        def releasepart_to_number(r):
            # expects a string in the form "wheezy (security)"
            try:
                u=r.split()[0]
                return release_to_number(u)
            except ValueError:
                return -1
        self.db.createscalarfunction("releasepart_to_number", releasepart_to_number, 1)

        def subreleasepart_to_number(r):
            # expects a string in the form "wheezy (security)"
            try:
                if not "(" in r:
                    return 0
                u=r.split('(', 1)[1].split(')')[0]
                return subrelease_to_number(u)
            except ValueError:
                return -1
        self.db.createscalarfunction("subreleasepart_to_number", subreleasepart_to_number, 1)

Holger Levsen's avatar
Holger Levsen committed
447
        releases = ['potato', 'woody', 'sarge', 'etch', 'lenny', 'squeeze', 'wheezy', 'jessie', 'sid']
448 449 450 451 452 453 454
        def release_to_number(u):
            try:
                return releases.index(u)
            except ValueError:
                return -1
        self.db.createscalarfunction("release_to_number", release_to_number, 1)

455
        subreleases = ['', 'security', 'lts']
456 457 458 459 460 461 462
        def subrelease_to_number(u):
            try:
                return subreleases.index(u)
            except ValueError:
                return -1
        self.db.createscalarfunction("subrelease_to_number", subrelease_to_number, 1)

463 464 465 466 467 468 469 470
        archives = ['main', 'contrib', 'non-free']
        def archive_to_number(u):
            try:
                return archives.index(u)
            except ValueError:
                return -1
        self.db.createscalarfunction("archive_to_number", archive_to_number, 1)

471 472 473 474 475 476 477 478 479
        def release_name(release, subrelease, archive):
            if archive <> 'main':
                release = release + '/' + archive
            if subrelease:
                return "%s (%s)" % (release, subrelease)
            else:
                return release
        self.db.createscalarfunction("release_name", release_name, 3)

480
        self.db.createcollation("version", debian_support.version_compare)
481

482 483 484 485
        def source_arch():
            return "source"
        self.db.createscalarfunction("source_arch", source_arch, 0)

486 487 488 489 490 491 492 493
    def filePrint(self, filename):
        """Returns a fingerprint string for filename."""

        st = os.stat(filename)
        # The "1" is a version number which can be used to trigger a
        # re-read if the code has changed in an incompatible way.
        return `(st.st_size, st.st_ino, st.st_mtime, 1)`

494
    def _parseFile(self, cursor, filename):
495 496
        current_print = self.filePrint(filename)

497
        def do_parse(packages):
498
            if self.verbose:
499
                print "    reading " + `filename`
500 501

            re_source = re.compile\
502
                (r'^([a-zA-Z0-9.+-]+)(?:\s+\(([a-zA-Z0-9.+:~-]+)\))?$')
503

504
            data = {}
505 506 507
            for pkg in packages:
                pkg_name = None
                pkg_version = None
508
                pkg_arch = None
509 510 511 512 513 514 515 516 517 518 519 520 521 522
                pkg_source = None
                pkg_source_version = None
                for (name, contents) in pkg:
                    if name == "Package":
                        pkg_name = contents
                    elif name == "Version":
                        pkg_version = contents
                    elif name == "Source":
                        match = re_source.match(contents)
                        if match is None:
                            raise SyntaxError(('package %s references '
                                               + 'invalid source package %s') %
                                              (pkg_name, `contents`))
                        (pkg_source, pkg_source_version) = match.groups()
523 524
                    elif name == "Architecture":
                        pkg_arch = contents
525 526 527 528 529 530 531
                if pkg_name is None:
                    raise SyntaxError\
                          ("package record does not contain package name")
                if pkg_version is None:
                    raise SyntaxError\
                          ("package record for %s does not contain version"
                           % pkg_name)
532 533 534 535
                if pkg_arch is None:
                    raise SyntaxError\
                          ("package record for %s lacks Architecture: field"
                           % pkg_name)
536 537 538 539 540 541 542 543
                if pkg_name in data:
                    oversion = debian_support.Version(data[pkg_name][0])
                    if oversion < debian_support.Version(pkg_version):
                        data[pkg_name] = (pkg_version, pkg_arch,
                                         pkg_source, pkg_source_version)
                else:
                    data[pkg_name] = (pkg_version, pkg_arch,
                                     pkg_source, pkg_source_version)
544 545 546 547 548 549

            return data

        def toString(data):
            result = cStringIO.StringIO()
            cPickle.dump(data, result)
550
            return buffer(result.getvalue())
551

552 553 554 555
        for (old_print, contents) in cursor.execute(
            "SELECT inodeprint, parsed FROM inodeprints WHERE file = ?",
            (filename,)):
            if old_print == current_print:
556
                return (True, cPickle.load(cStringIO.StringIO(contents)))
557 558 559
            result = do_parse(debian_support.PackageFile(filename))
            cursor.execute("""UPDATE inodeprints SET inodeprint = ?, parsed = ?
            WHERE file = ?""", (current_print, toString(result), filename))
560
            return (False, result)
561 562

        # No inodeprints entry, load file and add one.
563 564 565
        result = do_parse(debian_support.PackageFile(filename))
        cursor.execute("""INSERT INTO inodeprints (file, inodeprint, parsed)
        VALUES (?, ?, ?)""", (filename, current_print, toString(result)))
566
        return (False, result)
567

568 569 570 571 572 573 574 575 576 577 578
    def readPackages(self, cursor, directory):
        """Reads a directory of package files."""

        if self.verbose:
            print "readPackages:"

        self._readSourcePackages(cursor, directory)
        self._readBinaryPackages(cursor, directory)

        if self.verbose:
            print "  finished"
579

580 581 582
    def _readSourcePackages(self, cursor, directory):
        """Reads from directory with source package files."""

583
        re_sources = re.compile(r'.*/([a-z-]+)_([a-z-]*)_([a-z-]+)_Sources$')
584 585 586 587 588 589 590 591 592

        if self.verbose:
            print "  reading source packages"

        for filename in glob.glob(directory + '/*_Sources'):
            match = re_sources.match(filename)
            if match is None:
                raise ValueError, "invalid file name: " + `filename`

593 594 595 596 597
            (release, subrelease, archive) = match.groups()
            (unchanged, parsed) = self._parseFile(cursor, filename)
            if unchanged:
                continue

598 599 600
            if release == 'squeeze-lts':
                release = 'squeeze'
                subrelease = 'lts'
601 602 603 604 605
            cursor.execute(
                """DELETE FROM source_packages
                WHERE release = ? AND subrelease = ? AND archive = ?""",
                (release, subrelease, archive))
            self._clearVersions(cursor)
606 607

            def gen():
608 609
                for name in parsed.keys():
                    (version, archs, source, source_version) = parsed[name]
610 611
                    assert source is None
                    assert source_version is None
612
                    yield name, release, subrelease, archive, version
613 614
            cursor.executemany(
                """INSERT INTO source_packages
615 616
               (name, release, subrelease, archive, version)
               VALUES (?, ?, ?, ?, ?)""",
617
                gen())
618

619 620 621 622
    def _readBinaryPackages(self, cursor, directory):
        """Reads from a directory with binary package files."""

        re_packages \
623
            = re.compile(
624
            r'.*/([a-z-]+)_([a-z-]*)_([a-z-]+)_([a-z0-9-]+)_Packages$')
625

626 627 628
        if self.verbose:
            print "  reading binary packages"

629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652
        # First check for any changes.

        filenames = glob.glob(directory + '/*_Packages')
        filenames.sort()
        changed = False
        for filename in filenames:
            changed = True
            for (old_print,) in cursor.execute(
                "SELECT inodeprint FROM inodeprints WHERE file = ?",
                (filename,)):
                if self.filePrint(filename) == old_print:
                    changed = False
            if changed:
                break
        if not changed:
            if self.verbose:
                print "    finished (no changes)"
            return

        # Real import.  We have to re-read all Packages files even if
        # only some of them have changed because the database only
        # stores aggregated data, and there is no efficient way to
        # handle updates of the records related to a single file.

653
        packages = {}
654
        unchanged = True
655
        for filename in filenames:
656 657 658 659
            match = re_packages.match(filename)
            if match is None:
                raise ValueError, "invalid file name: " + `filename`

660
            (release, subrelease, archive, architecture) = match.groups()
661 662 663
            if release == 'squeeze-lts':
                release = 'squeeze'
                subrelease = 'lts'
664 665
            (unch, parsed) = self._parseFile(cursor, filename)
            unchanged = unchanged and unch
666 667
            for name in parsed.keys():
                (version, arch, source, source_version) = parsed[name]
668 669 670 671
                if source is None:
                    source = name
                if source_version is None:
                    source_version = version
672 673 674
                if arch <> 'all' and arch <> architecture:
                    raise ValueError, ("invalid architecture %s for package %s"
                                       % (arch, name))
675 676
                key = (name, release, subrelease, archive, version,
                       source, source_version)
677
                if packages.has_key(key):
678
                    packages[key][arch] = 1
679
                else:
680
                    packages[key] = {arch : 1}
681

682 683 684 685 686 687 688 689 690 691
        if unchanged:
            if self.verbose:
                print "    finished (no changes)"
            return

        if self.verbose:
            print "    deleting old data"
        cursor.execute("DELETE FROM binary_packages")
        self._clearVersions(cursor)

692
        l = packages.keys()
693

694 695
        if len(l) == 0:
            raise ValueError, "no binary packages found"
696

697 698 699 700 701 702 703
        l.sort()
        def gen():
            for key in l:
                archs = packages[key].keys()
                archs.sort()
                archs = ','.join(archs)
                yield key + (archs,)
704

705
        if self.verbose:
706
            print "    storing binary package data"
707 708 709

        cursor.executemany(
            """INSERT INTO binary_packages
710
            (name, release, subrelease, archive, version,
711
            source, source_version, archs)
712
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
713
            gen())
714

715 716 717
    def readBugs(self, cursor, path):
        if self.verbose:
            print "readBugs:"
718

719 720 721 722 723 724
        def clear_db(cleared=[False]):
            # Avoid clearing the database multiple times.
            if cleared[0]:
                return
            else:
                cleared[0] = True
725

726 727 728 729 730
            cursor.execute("DELETE FROM debian_bugs")
            cursor.execute("DELETE FROM bugs")
            cursor.execute("DELETE FROM package_notes")
            cursor.execute("DELETE FROM bugs_notes")
            cursor.execute("DELETE FROM bugs_xref")
731
            cursor.execute("DELETE FROM package_notes_nodsa")
732
            cursor.execute("DELETE FROM removed_packages")
733 734 735

            # The *_status tables are regenerated anyway, no need to
            # delete them here.
736

737
            self._clearVersions(cursor)
738 739

        def do_parse(source, cleared=[False]):
740
            errors = []
741 742

            clear_db()
743

744 745
            if self.verbose:
                print "  reading " + `source.name`
746

747 748 749 750 751 752 753 754 755
            for bug in source:
                try:
                    bug.writeDB(cursor)
                except ValueError, e:
                    errors.append("%s: %d: error: %s"
                                  % (bug.source_file, bug.source_line, e))
            if errors:
                raise InsertError(errors)

756
            cursor.executemany(
757
                "INSERT OR IGNORE INTO removed_packages (name) VALUES (?)",
758 759
                map(lambda x: (x,), source.removed_packages.keys()))

760
        def has_changed(filename):
761 762 763 764 765 766
            current_print = self.filePrint(filename)
            for (old_print,) in cursor.execute(
                "SELECT inodeprint FROM inodeprints WHERE file = ?",
                (filename,)):
                if old_print == current_print:
                    return False
767 768
                else:
                    return True
769
            return True
770

771
        source_removed_packages = '/packages/removed-packages'
772
        sources = ((bugs.CVEFile, '/CVE/list'),
773
                   (bugs.DSAFile, '/DSA/list'),
774
                   (bugs.DTSAFile, '/DTSA/list'),
775
                   (bugs.DLAFile, '/DLA/list'),
776
                   (None, source_removed_packages))
777

778 779 780 781 782
        unchanged = True
        for (_, name) in sources:
            if has_changed(path + name):
                unchanged = False
                break
783 784 785 786
        if unchanged:
            if self.verbose:
                print "  finished (no changes)"
            return
787

788 789 790 791 792 793 794 795 796 797 798 799
        clear_db()

        def read_one(source):
            filename = source.name
            current_print = self.filePrint(filename)

            do_parse(source)
            cursor.execute(
                """INSERT OR REPLACE INTO inodeprints (inodeprint, file)
                VALUES (?, ?)""", (current_print, filename))

        for (cls, name) in sources:
800 801
            if cls is None:
                continue
802 803
            read_one(cls(path + name))

804 805 806 807
        if self.verbose:
            print "  update removed packages"
        self.readRemovedPackages(cursor, path + source_removed_packages)

808
        errors = []
809

810
        if self.verbose:
811
            print "  check cross-references"
812

813 814 815 816 817
        for (bug,) in cursor.execute(
            """SELECT DISTINCT target FROM bugs_xref
        EXCEPT SELECT name FROM bugs"""):
            if bug[0:3] == "VU#":
                continue
818
            errors.append("reference to unknown bug " + bug)
819

820
        if self.verbose:
821 822
            print "  copy notes"

823
        # Copy notes from DSA/DTSA/DLA to CVE.
824

825
        old_source = ''
826
        for source, target in list(cursor.execute(
827
            """SELECT source, target FROM bugs_xref
828
            WHERE (source LIKE 'DTSA-%' OR source LIKE 'DSA-%' OR source LIKE 'DLA-%')
829
            AND target LIKE 'CVE-%'""")):
830
            if source <> old_source:
831
                source_bug = bugs.BugFromDB(cursor, source)
832 833 834 835
                old_source = source
            for n in source_bug.notes:
                # We do not copy recursively.
                assert not n.bug_origin
836

837 838 839 840 841
                if n.release:
                    rel = str(n.release)
                else:
                    rel = ''
                present = False
842

843 844 845 846 847
                for (version, note_id) in list(cursor.execute(
                    """SELECT fixed_version, id
                    FROM package_notes
                    WHERE bug_name = ? AND package = ? AND release = ?""",
                    (target, n.package, rel))):
848 849 850 851 852
                    if version is None:
                        # The target is marked as unfixed.  Our
                        # version cannot win.
                        present = True
                        continue
853

854 855
                    if (n.fixed_version is None
                        or n.fixed_version > debian_support.Version(version)):
856 857 858 859 860 861 862 863 864 865
                        # If our version is larger, it is the definitive one.
                        # Remove the existing entry in this case.
                        cursor.execute(
                            "DELETE FROM debian_bugs WHERE note = ?",
                            (note_id,))
                        cursor.execute(
                            """DELETE FROM package_notes
                            WHERE bug_name = ? AND package = ?
                            AND release = ?""",
                            (target, n.package, rel))
866 867
                    else:
                        present = True
868 869
                if not present:
                    n.writeDB(cursor, target, bug_origin=source)
870

871
        if errors:
872
            raise InsertError(errors)
873 874 875

        if self.verbose:
            print "  finished"
876

877
    def availableReleases(self, cursor=None):
878 879
        """Returns a list of tuples (RELEASE, ARCHIVE,
        SOURCES-PRESENT, ARCHITECTURE-LIST)."""
880 881 882 883
        if cursor is None:
            cursor = self.cursor()

        result = []
884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903
        result.append(('', '', '', False, []))
        for (rel, subrel, archive, archs) in cursor.execute(
            """SELECT * FROM
            (SELECT DISTINCT release, subrelease, archive, archs
            FROM binary_packages
            UNION SELECT DISTINCT release, subrelease, archive, source_arch() as archs
            FROM source_packages)
            ORDER BY release_to_number(release), subrelease_to_number(subrelease), archive_to_number(archive)"""):
	    if "source" in archs:
	        sources=True
            else:
                sources=False
            (p_rel, p_subrel, p_archive, p_sources, p_archs) = result.pop()
            if rel == p_rel and subrel == p_subrel and archive == p_archive:
                sources = sources or p_sources
                result.append((rel, subrel, archive, sources, mergeLists(p_archs, archs)))
            else:
                result.append((p_rel, p_subrel, p_archive, p_sources, mergeLists([], p_archs)))
                result.append((rel, subrel, archive, sources, mergeLists([], archs)))
        result.pop(0)
904 905 906

        return result

907 908 909 910 911 912 913 914 915 916 917 918
    def getFunnyPackageVersions(self):
        """Returns a list of (PACKAGE, RELEASE, ARCHIVE, VERSION,
        SOURCE-VERSION) tuples such that PACKAGE is both a source and
        binary package, but the associated version numbers are
        different."""

        return list(self.db.cursor().execute(
            """SELECT DISTINCT name, release, archive, version, source_version
            FROM binary_packages
            WHERE name = source AND version <> source_version
            ORDER BY name, release, archive"""))

919 920
    def _clearVersions(self, cursor):
        cursor.execute("DELETE FROM version_linear_order")
921

922 923 924 925 926
    def _updateVersions(self, cursor):
        """Updates the linear version table."""

        if self.verbose:
            print "updateVersions:"
927 928 929 930 931 932 933

        for x in cursor.execute("SELECT * FROM version_linear_order LIMIT 1"):
            if self.verbose:
                print "  finished (no changes)"
            return

        if self.verbose:
934 935 936 937 938 939 940
            print "  reading"

        versions = []
        for (v,) in cursor.execute(
            """SELECT DISTINCT *
            FROM (SELECT fixed_version FROM package_notes
                WHERE fixed_version IS NOT NULL
941
            UNION ALL SELECT version FROM source_packages)"""):
942 943 944
            versions.append(debian_support.Version(v))

        if self.verbose:
945
            print "  calculating linear order"
946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971
        versions.sort()

        if self.verbose:
            print "  storing linear order"
        for v in versions:
            cursor.execute(
                "INSERT INTO version_linear_order (version) VALUES (?)",
                (str(v),))

        if self.verbose:
            print "  updating package notes"
        cursor.execute(
            """UPDATE package_notes
            SET fixed_version_id = (SELECT id FROM version_linear_order
            WHERE version = package_notes.fixed_version)
            WHERE fixed_version IS NOT NULL""")

        if self.verbose:
            print "  updating source packages"
        cursor.execute(
            """UPDATE source_packages
            SET version_id = (SELECT id FROM version_linear_order
            WHERE version = source_packages.version)""")

        if self.verbose:
            print "  finished"
972

973 974 975 976
    def calculateVulnerabilities(self, cursor):
        """Calculate vulnerable packages.

        To each package note, a release-specific vulnerability status
977
        is attached.  Currently, only jessie/testing is processed.
978 979

        Returns a list strings describing inconsistencies.
980 981
        """

982 983
        result = []

984 985 986 987
        self._updateVersions(cursor)

        if self.verbose:
            print "calculateVulnerabilities:"
988
            print "  checking version consistency in package notes"
989

990 991 992
        # The following does not work because stable->security ->
        # testing -> unstable propagation is no longer available.
        if False:
993
            # Ignore jessie/testing because stable issues may be
994
            # fast-tracked into testing, bypassing unstable.
995 996 997 998 999 1000
            for (bug_name, pkg_name, rel, unstable_ver, rel_ver) \
                    in list(cursor.execute(
            """SELECT a.bug_name, a.package, b.release,
            a.fixed_version, b.fixed_version
            FROM package_notes a, package_notes b
            WHERE a.bug_name = b.bug_name AND a.package = b.package
1001
            AND a.release = '' AND b.release NOT IN ('', 'jessie')
1002 1003 1004 1005 1006 1007 1008 1009 1010
            AND a.fixed_version IS NOT NULL
            AND a.fixed_version_id < b.fixed_version_id""")):
                b = bugs.BugFromDB(cursor, bug_name)
                result.append("%s:%d: inconsistent versions for package %s"
                              % (b.source_file, b.source_line, pkg_name))
                result.append("%s:%d: unstable: %s"
                              % (b.source_file, b.source_line, unstable_ver))
                result.append("%s:%d: release %s: %s"
                              % (b.source_file, b.source_line, `rel`, rel_ver))
1011

1012
        if self.verbose:
1013
            print "  checking source packages"
1014 1015 1016
        cursor.execute(
            """UPDATE package_notes SET package_kind = 'unknown'
            WHERE package_kind IN ('source', 'binary')""")
1017 1018
        cursor.execute(
            """UPDATE package_notes SET package_kind = 'source'
1019 1020 1021
            WHERE package_kind = 'unknown'
            AND EXISTS (SELECT * FROM source_packages AS p
                        WHERE p.name = package_notes.package)""")
1022 1023 1024 1025 1026
        cursor.execute(
            """UPDATE package_notes SET package_kind = 'source'
            WHERE package_kind = 'unknown'
            AND EXISTS (SELECT * FROM removed_packages AS p
                        WHERE p.name = package_notes.package)""")
1027

1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039
        for (bug_name, package) in list(cursor.execute(
            """SELECT n.bug_name, n.package
            FROM package_notes AS n
            WHERE n.package_kind = 'itp'
            AND ((EXISTS (SELECT * FROM source_packages
                         WHERE name = n.package))
                 OR (EXISTS (SELECT * FROM binary_packages
                             WHERE name = n.package)))""")):
            b = bugs.BugFromDB(cursor, bug_name)
            result.append("%s:%d: ITPed package %s is in the archive"
                          % (b.source_file, b.source_line, package))

1040 1041 1042
        if result:
            return result

1043 1044
        if self.verbose:
            print "  remove old status"
1045
        cursor.execute("DELETE FROM source_package_status")
1046 1047
        cursor.execute("DELETE FROM bug_status")

1048 1049 1050 1051 1052
        if self.verbose:
            print "  calculate package status"
            print "    source packages (unqualified)"

        cursor.execute(
1053
            """INSERT INTO source_package_status
1054
            SELECT n.bug_name, p.rowid,
1055 1056 1057 1058
            CASE WHEN n.fixed_version == 'undetermined' THEN 2
            ELSE CASE WHEN n.fixed_version IS NULL THEN 1
            ELSE CASE WHEN p.version_id < n.fixed_version_id THEN 1
            ELSE 0 END END END,
1059
            n.urgency
1060
            FROM package_notes AS n, source_packages AS p
1061 1062 1063 1064 1065
            WHERE n.release = '' AND p.name = n.package""")

        # Release annotations always override previous results,
        # therefore we use INSERT OR REPLACE.

1066 1067 1068
        if self.verbose:
            print "    source packages (qualified)"
        cursor.execute(
1069
            """INSERT OR REPLACE INTO source_package_status
1070
            SELECT n.bug_name, p.rowid,
1071 1072 1073 1074
            CASE WHEN n.fixed_version == 'undetermined' THEN 2
            ELSE CASE WHEN n.fixed_version IS NULL THEN 1
            ELSE CASE WHEN p.version_id < n.fixed_version_id THEN 1
            ELSE 0 END END END,
1075
            n.urgency
1076
            FROM package_notes AS n, source_packages AS p
1077
            WHERE p.name = n.package
1078
            AND p.release = n.release""")
1079

1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092
        # assign nvd urgencies to those that have not yet been assigned
        if self.verbose:
            print "    insert nvd urgencies"
        cursor.execute(
            """REPLACE INTO source_package_status
            SELECT s.bug_name, s.package, s.vulnerable,
            CASE WHEN n.severity == 'Medium' THEN 'medium**'
            ELSE CASE WHEN n.severity == 'High' THEN 'high**'
            ELSE CASE WHEN n.severity == 'Low' THEN 'low**'
            ELSE 'not yet assigned' END END END
            FROM nvd_data AS n, source_package_status AS s
            WHERE s.bug_name == n.cve_name
            AND s.urgency == 'not yet assigned'""")
1093 1094 1095
        cursor.execute(
            """REPLACE INTO package_notes
            SELECT p.id, p.bug_name, p.package, p.fixed_version,
1096
            p.fixed_version_id, p.release, p.package_kind,
1097 1098 1099 1100 1101 1102 1103 1104
            CASE WHEN n.severity == 'Medium' THEN 'medium'
            ELSE CASE WHEN n.severity == 'High' THEN 'high'
            ELSE CASE WHEN n.severity == 'Low' THEN 'low'
            ELSE 'not yet assigned' END END END,
            p.bug_origin
            FROM nvd_data AS n, package_notes AS p
            WHERE p.bug_name == n.cve_name
            AND p.urgency == 'not yet assigned'""")
1105

1106
        # Calculate the release-specific bug status.
1107 1108

        if self.verbose:
1109
            print "  calculate release status"
1110

1111
        c = self.cursor()
1112

1113 1114 1115
        for (bug_name,) in cursor.execute(
            "SELECT name FROM bugs WHERE NOT not_for_us"):

1116
            self._calcUnstable(c, bug_name)
1117 1118 1119
            self._calcTesting(c, bug_name, 'testing', 'jessie')
            self._calcTesting(c, bug_name, 'stable', 'wheezy')
            self._calcTesting(c, bug_name, 'oldstable', 'squeeze')
1120

1121
        return result
1122

1123 1124
    def _calcUnstable(self, cursor, bug_name):
        """Update bug_status with bug_name for unstable."""
1125

1126
        vulnerable_packages = []
1127
        undetermined_packages = []
1128
        unimportant_packages = []
1129
        have_something = False
1130 1131
        for (package, vulnerable, urgency) in cursor.execute(
            """SELECT DISTINCT sp.name, st.vulnerable, n.urgency
1132 1133 1134 1135 1136
            FROM source_package_status AS st,
            source_packages AS sp, package_notes AS n
            WHERE st.bug_name = ? AND sp.rowid = st.package
            AND sp.release = 'sid'
            AND n.bug_name = st.bug_name AND n.package = sp.name
1137 1138
            ORDER BY sp.name""",
            (bug_name,)):
1139
            have_something = True
1140
            if vulnerable == 1:
1141 1142 1143 1144
                if urgency == 'unimportant':
                    unimportant_packages.append( package )
                else:
                    vulnerable_packages.append(package)
1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163
            elif vulnerable == 2:
                undetermined_packages.append(package)

        if vulnerable_packages or undetermined_packages:
            pkgs = ""
            status = 'undetermined'
            if vulnerable_packages:
                status = 'vulnerable'
                if len(vulnerable_packages) == 1:
                    pkgs += "package %s is vulnerable. " % vulnerable_packages[0]
                else:
                    pkgs += ("packages %s are vulnerable. "
                            % ', '.join(vulnerable_packages))
            if undetermined_packages:
                if len(undetermined_packages) == 1:
                    pkgs += "package %s may be vulnerable but needs to be checked." % undetermined_packages[0]
                else:
                    pkgs += ("packages %s may be vulnerable but need to be checked."
                             % ', '.join(undetermined_packages))
1164
            cursor.execute("""INSERT INTO bug_status
1165 1166
                (bug_name, release, status, reason)
                VALUES (?, 'unstable', ?, ?)""", (bug_name, status, pkgs))
1167 1168 1169 1170 1171 1172
        elif unimportant_packages:
            if len(unimportant_packages) == 1:
                pkgs = "package %s is vulnerable; however, the security impact is unimportant." % unimportant_packages[0]
            else:
                pkgs = "packages %s are vulnerable; however, the security impact is unimportant." % (', '.join(unimportant_packages))
            cursor.execute("""INSERT INTO bug_status
1173 1174
                (bug_name, release, status, reason)
                VALUES (?, 'unstable', 'fixed', ?)""", (bug_name, pkgs))
1175
        else:
1176
            if have_something:
1177
                status = "not vulnerable."
1178
            else:
1179
                status = "not known to be vulnerable."
1180
            cursor.execute("""INSERT INTO bug_status
1181 1182
                (bug_name, release, status, reason)
                VALUES (?, 'unstable', 'fixed', ?)""",
1183
                      (bug_name, status))
1184

1185 1186
    def _calcTesting(self, cursor, bug_name, suite, nickname):
        """Update bug_status with bug_name for testing/stable."""
1187 1188 1189

        # Note that there is at most one source package per
        # note/release/subrelease triple, but we should check that
1190
        # here.
1191

1192
        status = {'' : {}, 'security' : {}, 'lts' :	{}}
1193
        for (package, note, subrelease, vulnerable, urgency) in cursor.execute(
1194
            """SELECT DISTINCT sp.name, n.id, sp.subrelease,
1195
            st.vulnerable, n.urgency
1196 1197 1198
            FROM source_package_status AS st,
            source_packages AS sp, package_notes AS n
            WHERE st.bug_name = ? AND sp.rowid = st.package
1199
            AND sp.release = ? AND sp.subrelease IN ('', 'security', 'lts')
1200
            AND n.bug_name = st.bug_name AND n.package = sp.name
1201
            ORDER BY sp.name""",
1202
            (bug_name, nickname)):
1203
            status[subrelease][(package, note)] = (vulnerable,urgency)
1204 1205 1206 1207 1208 1209 1210 1211

        # Check if any packages in plain testing are vulnerable, and
        # if all of those have been fixed in the security archive.
        fixed_in_security = True
        unfixed_pkgs = {}
        undet_pkgs = {}
        unimp_pkgs = {}
        for ((package, note), (vulnerable, urgency)) in status[''].items():
1212
            if vulnerable == 1:
1213 1214 1215 1216
                if urgency == 'unimportant':
                    unimp_pkgs[package] = True
                else:
                    unfixed_pkgs[package] = True
1217
                if status['security'].get((package, note), True):
1218
                    fixed_in_security = False
1219
                elif status['lts'].get((package, note), True):
1220
                    fixed_in_security = False
1221 1222 1223
            elif vulnerable == 2:
                undet_pkgs[package] = True

1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246
        unfixed_pkgs = unfixed_pkgs.keys()
        unfixed_pkgs.sort()
        undet_pkgs = undet_pkgs.keys()
        undet_pkgs.sort()
        unimp_pkgs = unimp_pkgs.keys()
        unimp_pkgs.sort()

        pkgs = ""
        result = "undetermined"
        if len(unfixed_pkgs) == 0 and len(undet_pkgs) == 0:
            if len(status[''].keys()) == 0:
                pkgs += "not known to be vulnerable."
            else:
                pkgs += "not vulnerable."
            result = "fixed"
        if len(unfixed_pkgs) > 0:
            if len(unfixed_pkgs) == 1:
                pkgs += "package " + unfixed_pkgs[0] + " is "
            else:
                pkgs += "packages " + ", ".join(unfixed_pkgs) + " are "
            if fixed_in_security:
                pkgs = "%sfixed in %s-security. " % (pkgs, suite)
                if suite == "stable":
1247
                    result = "fixed"
1248
                else:
1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263
                    result = "partially-fixed"
            else:
                pkgs += "vulnerable. "
                result = "vulnerable"
        if len(undet_pkgs) > 0:
            if len(undet_pkgs) == 1:
                pkgs += "package " + undet_pkgs[0] + " may be vulnerable but needs to be checked."
            else:
                pkgs += "packages " + ", ".join(undet_pkgs) + " may be vulnerable but need to be checked."
        if len(unimp_pkgs) > 0 and len(undet_pkgs) == 0 and len(unfixed_pkgs) == 0:
            result = "fixed"
            if len(unimp_pkgs) == 1:
                pkgs = "package %s is vulnerable; however, the security impact is unimportant." % unimp_pkgs[0]
            else:
                pkgs = "packages %s are vulnerable; however, the security impact is unimportant." % (', '.join(unimp_pkgs))
1264

1265 1266 1267 1268
        cursor.execute("""INSERT INTO bug_status
        (bug_name, release, status, reason)
        VALUES (?, ?, ?, ?)""",
              (bug_name, suite, result, pkgs))
1269

1270 1271
    def calculateDebsecan0(self, release):
        """Create data for the debsecan tool (VERSION 0 format)."""
1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291

        c = self.cursor()

        c.execute("""CREATE TEMPORARY TABLE vulnlist (
        name TEXT NOT NULL,
        package TEXT NOT NULL,
        note INTEGER NOT NULL,
        PRIMARY KEY (name, package)
        )""")

        # Populate the table with the unstable vulnerabilities;
        # override them with the release-specific status.

        c.execute("""INSERT INTO vulnlist
        SELECT bug_name, package, id FROM package_notes WHERE release = ''""")

        if release:
            c.execute("""INSERT OR REPLACE INTO vulnlist
            SELECT bug_name, package, id FROM package_notes
            WHERE release = ?""", (release,))
1292 1293
        else:
            release = 'sid'
1294 1295

        urgency_to_flag = {'low' : 'L', 'medium' : 'M', 'high' : 'H',
1296
                           'not yet assigned' : ' '}
1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312

        result = ["VERSION 0\n"]
        for (name, package, fixed_version, kind, urgency, remote, description,
             note_id) in list(c.execute("""SELECT
                vulnlist.name, vulnlist.package,
                COALESCE(n.fixed_version, ''),
                n.package_kind, n.urgency,
                (SELECT range_remote FROM nvd_data
                 WHERE cve_name = vulnlist.name) AS remote,
                bugs.description,
                n.id
                FROM vulnlist, bugs, package_notes AS n
                WHERE bugs.name = vulnlist.name
                AND n.id = vulnlist.note
                ORDER BY vulnlist.package""")):
            if fixed_version == '0' or urgency == 'unimportant' \
1313 1314
                    or urgency == 'end-of-life' \
                    or kind not in ('source', 'binary', 'unknown'):
1315 1316 1317 1318 1319 1320
                continue

            # Normalize FAKE-* names a bit.  The line number (which
            # makes the name unique) is completely useless for the
            # client.

1321
            if name[0:5] == 'TEMP-':
1322 1323 1324 1325 1326 1327
                name = '-'.join(name.split('-')[0:2])

            # Determine if a fix is available for the specific
            # release.

            fix_available = ' '
1328 1329 1330 1331 1332
            if kind == 'source':
                fix_available_sql = """SELECT st.vulnerable
                    FROM source_packages AS p, source_package_status AS st
                    WHERE p.name = ?
                    AND p.release = ?
1333
                    AND p.subrelease IN ('', 'security', 'lts')
1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346
                    AND st.bug_name = ?
                    AND st.package = p.rowid
                    ORDER BY p.version COLLATE version DESC"""
            else:
                fix_available_sql = ''

            if fix_available_sql:
                for (v,) in c.execute(fix_available_sql,
                                      (package, release, name)):
                    assert v is not None
                    if not v:
                        fix_available = 'F'
                    break
1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374

            if kind == 'source':
                kind = 'S'
            elif kind == 'binary':
                kind = 'B'
            else:
                kind = ' '

            if remote is None:
                remote = '?'
            elif remote:
                remote = 'R'
            else:
                remote = ' '

            result.append("%s,%c%c%c%c,%s,%s,%s\n"
                          % (name,
                             kind, urgency_to_flag[urgency], remote,
                             fix_available,
                             package, fixed_version, description))
        result = base64.encodestring(zlib.compress(''.join(result), 9))

        c.execute(
            "INSERT OR REPLACE INTO debsecan_data (name, data) VALUES (?, ?)",
            ('release/' + release, result))

        c.execute("DROP TABLE vulnlist")

1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390
    def calculateDebsecan1(self):
        """Calculates debsecan data (release-independent, VERSION 1)."""

        c = self.cursor()

        result_start = ['VERSION 1']
        bug_to_index = {}
        bug_to_remote_flag = {}

        def fill_bug_to_index():
            index = 0
            for (bug, desc, remote) in c.execute(
                """SELECT DISTINCT p.bug_name, b.description,
                (SELECT range_remote FROM nvd_data
                 WHERE cve_name = p.bug_name)
                FROM package_notes AS p, bugs AS b
1391
                WHERE (p.bug_name LIKE 'CVE-%' OR p.bug_name LIKE 'TEMP-%')
1392
                AND p.urgency <> 'unimportant'
1393
                AND COALESCE(p.fixed_version, '') <> '0'
1394
                AND p.package_kind IN ('source', 'binary', 'unknown')
1395
                AND b.name = p.bug_name
1396 1397 1398 1399 1400 1401 1402 1403
                ORDER BY p.bug_name"""):
                if remote is None:
                    remote = '?'
                elif remote:
                    remote = 'R'
                else:
                    remote = ' '

1404
                result_start.append("%s,,%s" % (bug, desc))
1405 1406 1407 1408 1409 1410 1411
                bug_to_index[bug] = index
                bug_to_remote_flag[bug] = remote
                index += 1
            result_start.append('')
        fill_bug_to_index()

        urgency_to_flag = {'low' : 'L', 'medium' : 'M', 'high' : 'H',
1412
                           'not yet assigned' : ' '}
1413 1414 1415 1416 1417 1418 1419

        vuln_list = []
        source_packages = {}
        def fill_vuln_list(source_packages=source_packages):
            for (bug, package) in list(c.execute(
                """SELECT DISTINCT bug_name, package
                FROM package_notes
1420
                WHERE (bug_name LIKE 'CVE-%' OR bug_name LIKE 'TEMP-%')
1421 1422 1423 1424
                AND package_kind IN ('source', 'binary', 'unknown')
                GROUP BY package, bug_name
                ORDER BY package, bug_name""")):

1425 1426 1427 1428 1429 1430 1431 1432 1433
                # By default, unstable is unfixed even if there are
                # only release-specific annotations available.  This
                # is slightly at odds with the web front end (see
                # data/latently-vulnerable) which does not normally
                # report unstable versions as vulnerable in this case.
                # However, in our tracking model, the main branch
                # (sid) cannot be non-vulnerable, while the
                # release-specific branches are.
                unstable_fixed = ''
1434

1435
                total_urgency = ''
1436 1437 1438 1439 1440 1441 1442