secure-testing/bin/tracker.cgi

#!/usr/bin/python

import cgi
import cgitb
cgitb.enable()                          # FIXME for production use

import sys
sys.path.insert(0,'../lib/python')

import os
import re
import string
import types
import urllib

import security_db
import bugs

def print_header(status):
    print "Content-Type: text/html"
    print "Status:", status
    print ""

def print_title(title, status=200, selectSearch=False):
    print_header(status)
    title = cgi.escape(title)
    print '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">'
    print '''<html><head>
<style type="text/css">
h1 { font-size : 144%; }
h2 { font-size : 120%; }
h3 { font-size : 100%; }

table { padding-left : 1.5em }
td, th { text-align : left; 
         padding-left : 0.25em;
         padding-right : 0.25em; }
td { vertical-align: baseline }
span.red { color: red; }
span.dangerous { color: rgb(191,127,0); }
</style>

<script type="text/javascript" language="JavaScript">
var old_query_value = "";

function onLoad() {
'''
    if selectSearch:
        print ' document.searchForm.query.focus();'

    print '''}

function onSearch(query) {
  if (old_query_value == "") {
    if (query.length > 5) {
      old_query_value = query;
      document.searchForm.submit();
    } else {
      old_query_value = query;
    }
  }
}
</script>
'''
    print '<title>%s</title></head><body onload="onLoad()"><h1>%s</h1>' \
          % (title, title)

def print_footer(withSearch=True):
    print "<hr/>"
    if withSearch:
        print_paragraph(make_search())

    print_paragraph(make_a(url_from_rel(""), "Home"),
                    " - ", make_a("http://secure-testing.debian.net",
                                  "Testing Security Team"),
                    " - ", make_a("http://www.debian.org/security/",
                                  "Debian Security"),
                    " - ", make_a("http://www.enyo.de/fw/impressum.html",
                                  "Imprint"))
    print "</body></html>"

class NoEscape:
    """Prevent escaping of HTML text."""
    def __init__(self, data):
        self.data = data
    def __repr__(self):
        return "NoEscape(%s)" % `self.data`

def escape(data):
    if type(data) == types.StringType:
        return cgi.escape(data)
    assert type(data) == types.InstanceType, type(data)
    assert data.__class__ == NoEscape, data.__class__
    return data.data

def print_error(msg):
    msg = escape(msg)
    print "<p><b>ERROR:</b> %s</p>" % msg
    print '<p>Please contact <a href="mailto:fw@deneb.enyo.de">Florian Weimer</a> and report this problem.</p>'

try:
    path_info = os.environ['PATH_INFO']
except KeyError:
    path_info = ''

try:
    server_name = os.environ['SERVER_NAME']
except KeyError:
    server_name = 'localhost'

try:
    script_name = cgi.escape(os.environ['SCRIPT_NAME'])
except KeyError:
    script_name = ''
while script_name[0:2] == '//':
    script_name = script_name[1:]

def print_no_results(query):
    print_title("No results", status=404)
    print_error(NoEscape('Your query "<code>%s</code>" matched no results.'
                         % cgi.escape(query)))
    print_footer()

def print_invalid_query():
    print_title("Invalid query", status=404)
    print_error("The URL you specified is incorrect for this application.")
    print_footer()

def print_table(gen, caption=(), replacement='', introduction='', style=None):
    w = sys.stdout.write
    if style:
        style = ' class="%s"' % escape(style)
    else:
        style = ''

    first_row = True
    for row in gen:
        if first_row:
            w(escape(introduction))
            if style:
                w('<table%s>' % style)
            else:
                w('<table>')
            if caption:
                w('<tr%s>' % style)
                for c in caption:
                    w('<th%s>' % style)
                    w(escape(c))
                    w('</th>')
                w('</tr>\n')
            first_row = False
        w("<tr>")
        for col in row:
            w("<td%s>" % style)
            w(escape(col))
            w("</td>")
        w("</tr>\n")
    if first_row:
        if replacement:
            w(escape(replacement))
    else:
        w("</table>\n")

def print_escaped(*args):
    for x in args:
        sys.stdout.write(escape(x))
def print_paragraph(*args):
    sys.stdout.write('<p>')
    apply(print_escaped, args)
    sys.stdout.write('</p>\n')

def make_bold(s):
    return NoEscape("<b>%s</b>" % escape(s))
def make_code(s):
    return NoEscape("<code>%s</code>" % escape(s))
def make_red(s):
    return NoEscape('<span class="red">%s</span>' % escape(s))
def make_dangerous(s):
    return NoEscape('<span class="dangerous">%s</span>' % escape(s))
def url_from_rel(x, full=False):
    if full:
        return "http://%s%s/%s" % (server_name, script_name, x)
    else:
        return "%s/%s" % (script_name, x)
url_known_bug = url_from_rel
    
def url_source_package(p, full=False):
    return url_from_rel("source-package/" + p, full)
def url_binary_package(p, full=False):
    return url_from_rel("binary-package/" + p, full)

def make_xref(x):
    url = escape(url_known_bug(x))
    return NoEscape('<a href="%s">%s</a>' % (url, escape(x)))
def make_cve_xref(cve, name=None):
    cve = escape(cve)
    if name is None:
        name = cve
    else:
        name = escape(name)
    return NoEscape('<a href="http://cve.mitre.org/cgi-bin/cvename.cgi?name=%s">%s</a>' % (cve, name))

def make_dsa_xref(cursor, dsa, name,
                  re_dsa=re.compile(r'^DSA-(\d+)(?:-\d+)?$')):
    match = re_dsa.match(dsa)
    if name is None:
        name = dsa
    else:
        name = escape(name)
    if match:
        # We must determine the year because there is no generic URL.
        (number,) = match.groups()
        for (date,) in cursor.execute(
            "SELECT release_date FROM bugs WHERE name = ?", (dsa,)):
            (y, m, d) = date.split('-')
            return NoEscape('<a href="http://www.debian.org/security/%d/dsa-%d">%s</a>'
                            % (int(y), int(number), name))

    return escape(dsa)
    
    
def url_debian_bug(bug):
    return "http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=%d" % bug
def url_debian_bug_pkg(pkg):
    return ("http://bugs.debian.org/cgi-bin/pkgreport.cgi?pkg="
            + urllib.quote(pkg))

def make_debian_bug(bug, internal=False):
    if internal:
        assert False
    return NoEscape('<a href="%s">%d</a>' % (url_debian_bug(bug), bug))

def url_pts(src):
    return "http://packages.qa.debian.org/common/index.html?src=" \
           + urllib.quote(src)

def url_testing_status(src):
    return "http://bjorn.haxx.se/debian/testing.pl?package=" \
           + urllib.quote(src)

def make_a(url, text):
    return NoEscape('<a href="%s">%s</a>' % (escape(url), escape(text)))
def make_pts_ref(pkg, title=None):
    if title is None:
        title = pkg
    return make_a(url_pts(pkg), title)

def make_source_package_ref(pkg, title=None):
    if title is None:
        title = pkg
    return make_a(url_source_package(pkg), title)
def make_binary_package_ref(pkg, title=None):
    if title is None:
        title = pkg
    return make_a(url_binary_package(pkg), title)
def make_binary_packages_ref(lst):
    assert type(lst) <> types.StringType
    return make_list(map(make_binary_package_ref, lst))

def make_list(lst, separator=NoEscape(", ")):
    assert type(lst) <> types.StringType
    return NoEscape(escape(separator).join(map(escape, lst)))

def make_search():
    return NoEscape(\
        ('<form name="searchForm" method="get" action="%s">'
         % escape(url_from_rel('search/')))
        + 'Search for package or bug name: '
        + '<input type="text" name="query" onkeyup="onSearch(this.value)"'
        + 'onmousemove="onSearch(this.value)"> '
        + '<input type="submit" value="Go"/></form>')
    
def print_bug(bug, db):
    print_title(bug.name)

    cursor = db.cursor()

    def gen_header():
        yield make_bold("Name"), bug.name

        source = bug.name.split('-')[0]
        if source in ('CAN', 'CVE'):
            source_xref = make_cve_xref(bug.name, 'CVE')
        elif source == 'DSA':
            source_xref = make_dsa_xref(cursor, bug.name, 'Debian')
        elif source == 'DTSA':
            source_xref = 'Debian Testing Security Team'
        elif source == 'FAKE':
            source_xref = 'Automatically generated temporary name.  Not for external reference.'
        else:
            source_xref = None

        if source_xref:
            yield make_bold("Source"), source_xref
        
        if bug.description:
            yield make_bold("Description"), bug.description

        xref = list(db.getBugXrefs(cursor, bug.name))
        if xref:
            yield make_bold("References"), make_list(map(make_xref, xref))
            
        debian_bugs = bug.getDebianBugs(cursor)
        if debian_bugs:
            yield (make_bold("Debian Bugs"),
                   make_list(map(make_debian_bug, debian_bugs)))

        if bug.not_for_us:
            yield make_bold("Status"), "Debian is not affected"
        else:
            for (release, status, reason) in bug.getStatus(cursor):
                if status <> 'fixed':
                    reason = make_red(reason)
                yield make_bold('Status of %s' % release), reason

    print_table(gen_header())

    if bug.notes:
        print """<h2>Vulnerable and fixed packages</h2>
<p>The table below lists information on <em>source packages</em>.</p>
"""

        def gen_source():
            yield (make_bold("Source Package"),
                   make_bold("Release"),
                   make_bold("Version"),
                   make_bold("Status"))

            old_pkg = ''
            for (package, release, version, vulnerable) \
                    in db.getSourcePackages(cursor, bug.name):
                if package == old_pkg:
                    package = ''
                else:
                    old_pkg = package
                    package = NoEscape("%s (%s)"
                        % (escape(make_source_package_ref(package)),
                           escape(make_pts_ref(package, 'PTS'))))
                if vulnerable:
                    vuln = make_red('vulnerable')
                    version = make_red(version)
                else:
                    vuln = 'fixed'
                    
                yield package, ', '.join(release), version, vuln
                
        print_table(gen_source())

        print "<p>The next table lists affected <em>binary packages</em>.<p>"

        def gen_binary():
            yield (make_bold("Binary Package"),
                   make_bold("Release"),
                   make_bold("Version"),
                   make_bold("Status"),
                   make_bold("Arch"))

            old_pkg = ''
            for (packages, releases, version, archs, vulnerable) \
                in db.getBinaryPackages(cursor, bug.name):
                pkg = ', '.join(packages)
                if pkg == old_pkg:
                    packages = ''
                else:
                    old_pkg = pkg
                    packages = make_binary_packages_ref(packages)
                    
                if vulnerable:
                    vuln = make_red('vulnerable')
                    version = make_red(version)
                else:
                    vuln = 'fixed'
                yield (packages,
                       ', '.join(releases),
                       version, vuln,
                       ', '.join(archs))

        print_table(gen_binary())
            

        print """<p>The information above is based on the following
data on fixed versions.</p>"""

        def gen_data():
            yield ()

            notes_sorted = bug.notes[:]
            notes_sorted.sort(lambda a, b: cmp(a.package, b.package))
            for n in notes_sorted:
                if n.release:
                    rel = str(n.release)
                else:
                    rel = '(unstable)'
                urgency = str(n.urgency)
                if n.fixed_version:
                    ver = str(n.fixed_version)
                    if ver == '0':
                        ver = '(not affected)'
                        urgency = ''
                else:
                    ver = make_red('(unfixed)')

                pkg = n.package
                pkg_kind = n.package_kind
                if pkg_kind == 'source':
                    pkg = make_source_package_ref(pkg)
                elif pkg_kind == 'binary':
                    pkg = make_binary_package_ref(pkg)
                elif pkg_kind == 'itp':
                    pkg_kind = 'ITP'
                    rel = ''
                    ver = ''
                    urgency = ''
                
                bugs = n.bugs
                bugs.sort()
                bugs = make_list(map(make_debian_bug, bugs))
                if n.bug_origin:
                    origin = make_xref(n.bug_origin)
                else:
                    origin = ''
                yield (pkg, pkg_kind, rel, ver, urgency, origin, bugs)

        print_table(gen_data(),
                    caption=("Package", "Type", "Release", "Fixed Version",
                             "Urgency", "Origin", "Debian Bugs"))

    if bug.comments:
        print "<h2>Notes</h2>"
        print "<pre>"
        for (t, c) in bug.comments:
            print escape(c)
        print "</pre>"

    print_footer()

def print_debian_bug(db, bug, buglist):
    print_title("Information related to Debian Bug #%d" % bug)

    print_paragraph("The following issues reference to Debian bug ",
                    make_debian_bug(bug), ":")

    def gen():
        yield make_bold("Name"), make_bold("Urgency"), make_bold("Description")

        for (name, urgency, description) in buglist:
            yield make_xref(name), urgency, description

    print_table(gen())
    print_footer()

def handle_simple_search(query):
    db = security_db.DB('../data/security.db')
    c = db.cursor()
    if 'A' <= query[0] <= 'Z':
        try:
            bug = bugs.BugFromDB(c, query)
        except ValueError:
            print_no_results(query)
            return

        if bug.name <> query:
            # Bug name was normalized, perform redirect so that the
            # browser sees the normalized URL.
            print "Location:", url_from_rel(bug.name, full=True)
            print
            return
        
        print_bug(bug, db)
        return
    
    elif db.isSourcePackage(c, query):
        print "Location:", url_source_package(query, full=True)
        print
        return

    elif db.isBinaryPackage(c, query):
        print "Location:", url_binary_package(query, full=True)
        print
        return
        
    elif '0' <= query[0] <= '9':
        # Debian bug number.
        if query[-6:] == '_REDIR':
            query = query[:-6]
            redirect = True
        else:
            redirect = False

        bugnumber = 0
        try:
            bugnumber = int(query)
        except ValueError:
            pass
        if bugnumber:
            buglist = list(db.getBugsFromDebianBug(c, bugnumber))
            if buglist:
                if len(buglist) == 1:
                    # Single issue, redirect.
                    print "Location:", url_known_bug(buglist[0][0], full=True)
                    print
                    return
                else:
                    print_debian_bug(c, bugnumber, buglist)
                    return
            elif redirect:
                print "Location:", url_debian_bug(bugnumber)
                print
                return
    print_no_results(query)

def print_source_package(pkg):
    db = security_db.DB('../data/security.db')
    c = db.cursor()

    print_title("Information on source package " + pkg)

    print_menu([(url_pts(pkg),
                 pkg + ' in the Package Tracking System'),
                (url_debian_bug_pkg(pkg),
                 pkg + ' in the Bug Tracking System'),
                (url_testing_status(pkg),
                 pkg + ' in the testing migration checker')],
               relative=False)

    print "<h2>Available versions</h2>"

    def gen_versions():
        yield make_bold("Release"), make_bold("Version")
        for (releases, version) in db.getSourcePackageVersions(c, pkg):
            yield ', '.join(releases), version
    print_table(gen_versions())

    print "<h2>Available binary packages</h2>"
    
    def gen_binary():
        for (packages, releases, archs, version) \
                in db.getBinaryPackagesForSource(c, pkg):
            yield (make_binary_packages_ref(packages),
                   ', '.join(releases), version, ', '.join(archs))
    print_table(gen_binary(),
        caption=('Package', 'Release', 'Version', 'Architectures'),
        replacement=('No binary packages are recorded in this database. '
                     + 'This probably means that the package is '
                     + 'architecture-specific, and the architecture '
                     + 'is currently not tracked.'))

    print "<h2>Open issues</h2>"

    def gen_bug_list(lst):
        for (bug, description) in lst:
            yield make_xref(bug), description
    print_table(gen_bug_list(db.getBugsForSourcePackage(c, pkg, True)),
                caption=('Bug', 'Description'),
                replacement='No known open issues.')
            
    print "<h2>Resolved issues</h2>"

    print_table(gen_bug_list(db.getBugsForSourcePackage(c, pkg, False)),
                caption=('Bug', 'Description'),
                replacement='No known resolved issues.')

    print_footer()

def print_binary_package(pkg):
    db = security_db.DB('../data/security.db')
    c = db.cursor()

    print_title("Information on binary package " + pkg)

    print_menu([(url_debian_bug_pkg(pkg),
                 pkg + ' in the Bug Tracking System')],
               relative=False)
    print "<h2>Available versions</h2>"

    def gen_versions():
        # FIXME: We should include the source package name in this list.
        yield ()
        for (releases, source, version, archs) \
                in db.getBinaryPackageVersions(c, pkg):
            yield (', '.join(releases), make_source_package_ref(source),
                   version, ', '.join(archs))
    print_table(gen_versions(),
                caption=("Release", "Source", "Version", "Architectures"))

    print "<h2>Open issues</h2>"

    def gen_bug_list(lst):
        for (bug, description) in lst:
            yield make_xref(bug), description
    print_table(gen_bug_list(db.getBugsForBinaryPackage(c, pkg, True)),
                caption=('Bug', 'Description'),
                replacement='No known open issues.')
            
    print "<h2>Resolved issues</h2>"

    print_table(gen_bug_list(db.getBugsForBinaryPackage(c, pkg, False)),
                caption=('Bug', 'Description'),
                replacement='No known resolved issues.')

    print "<h2>Non-issues</h2>"

    print_table(gen_bug_list(db.getNonBugsForBinaryPackage(c, pkg)),
                 caption=('Bug', 'Description'),
                 replacement=('No known issues which do not affect '
                              + 'this package.'))
    
    print_footer()

def print_todo():
    db = security_db.DB('../data/security.db')
    print_title("Bugs with TODO items")

    def gen():
        yield make_bold("Bug"), make_bold("Description")
        for (bug, description) in db.getTODOs():
            yield make_xref(bug), description
    print_table(gen())

    print_footer()

def print_menu(entries,relative=True):
    w = sys.stdout.write
    w("<ul>")
    for e in entries:
        w("<li>")
        if type(e) == types.TupleType:
            (relurl, label) = e
            if relative:
                relurl = url_from_rel(relurl)
            sys.stdout.write(escape(make_a(relurl, label)))
        else:
            w(escape(e))
        w("</li>\n")
    w("</ul>\n")

def print_overview():
    print_title("Security issue tracker", selectSearch=True)

    print """<p>This is the experimental issue tracker for Debian's testing
security team.  Keep in mind that this is merely a prototype.
Please report any problems to <a href="mailto:fw@deneb.enyo.de">Florian
Weimer</a>.  Note that some of the data presented here is known
to be wrong (see below), but the data for the testing suite
should be fine.
</p>

<h2>Starting points</h2>
"""

    print_menu([('status/release/testing',
                 'Vulnerable packages in the testing suite'),
                ('status/release/unstable',
                 'Vulnerable packages in the unstable suite'),
                ('status/dtsa-candidates', "Candidates for DTSAs"),
                ('status/todo', 'TODO items'),
                ('status/itp', 'ITPs with potential security issues'),
                ('data/unknown-packages',
                 'Packages names not found in the archive'),
                ('data/missing-epochs',
                 'Package versions which might lack an epoch'),
                ('data/funny-versions',
                 'Packages with strange version numbers'),
                ('data/releases',
                 'Covered Debian releases and architectures (slow)'),
                make_search()])

    print """<h2>A few notes on data sources</h2>

<p>Data in this tracker comes solely from the bug database
which is maintained by Debian's testing security team in their
Subversion repository.  All external data (this includes
Debian bug reports and official Debian security advisories)
must be added to this database before it appears here, and there
can be some delay before this happens.
</p>

<p>At the moment, the database only contains information which is
relevant for tracking the security status of the testing suite.
This means that data for stable or oldstable is likely wrong.
The unstable suite should be covered pretty well, though,
because it is relevant to the status of testing.
</p>
"""
    print_footer(withSearch = False)

def handle_cmd(cmd, arg):
    if cmd == 'source-package':
        print_source_package(arg)
        sys.exit(0)
    elif cmd == 'binary-package':
        print_binary_package(arg)
        sys.exit(0)

if path_info in ('', '/'):
    print_overview()
    sys.exit(0)

re_query = re.compile(r'^/([a-zA-Z0-9_.-]+)$')
match = re_query.match(path_info)
if match is None:
    cmd_list = path_info.split('/')
    if len(cmd_list) == 3:
        handle_cmd(cmd_list[1], cmd_list[2])
        # fall-through if not handled
    
    def print_releases():
        db = security_db.DB('../data/security.db')
        
        print_title("Available releases")

        print """<p>The security issue database is checked against
the Debian releases listed in the table below.  Currently, space
and processing resources are limited, so the list of architectures
is incomplete.
</p>"""

        def gen():
            yield (make_bold("Release"),
                   make_bold("Subrelease"),
                   make_bold("Archive"),
                   make_bold("Sources"),
                   make_bold("Architectures"))
            for (rel, subrel, archive, sources, archs) \
                    in db.availableReleases():
                if sources:
                    sources = 'yes'
                else:
                    sources = 'no'
                yield rel, subrel, archive, sources, make_list(archs)

        print_table(gen())
        print_footer()

    def print_funny_versions():
        db = security_db.DB('../data/security.db')
        print_title("Version conflicts between source/binary packages")
        
        print """<p>The table below lists source packages
which have a binary package of the same name, but with a different
version.  This means that extra care is necessary to determine
the version of a package which has been fixed.  (Note that
the bug tracker prefers source versions to binary versions
in this case.)
</p>"""
        def gen():
            yield (make_bold("Package"),
                   make_bold("Release"), 
                   make_bold("Archive"),
                   make_bold("Source Version"),
                   make_bold("Binary Version"))

            for name, release, archive, version, source_version \
                in db.getFunnyPackageVersions():
                yield name, release, archive, source_version, version

        print_table(gen())

        print """<p>Technically speaking, these version numbering is fine,
but it makes version-based bug tracking quite difficult for these packages.
</p>

<p>There are many binary packages which are built from source packages
with different version numbering schemes.  However, as long as none of
the binary packages carries the same name as the source package, most
confusion is avoided or can be easily explained.</p>"""        
        
        print_footer()

    def print_missing_epochs():
        db = security_db.DB('../data/security.db')
        print_title("Missing epochs in package versions")

        def gen():
            old_bug = ''
            old_pkg = ''
            for bug, pkg, ver1, ver2 in db.cursor().execute(
                """SELECT DISTINCT bug_name, n.package,
                n.fixed_version, sp.version
                FROM package_notes AS n, source_packages AS sp
                WHERE n.package_kind = 'source'
                AND n.fixed_version NOT LIKE '%:%'
                AND n.fixed_version <> '0'
                AND n.bug_origin = ''
                AND sp.name = n.package
                AND sp.version LIKE '%:%'
                ORDER BY bug_name, package"""):
                if bug == old_bug:
                    bug = ''
                else:
                    old_bug = bug
                    old_pkg = ''
                    bug = make_xref(bug)
                if pkg == old_pkg:
                    pkg = ''
                else:
                    old_pkg = pkg
                    pkg = make_source_package_ref(pkg)
                yield bug, pkg, ver1, ver2

        print_table(gen(),
            caption=("Bug", "Package", "Version 1", "Version 2"),
            replacement="No source package version with missing epochs.")

        print_footer()

    def print_unknown_packages():
        db = security_db.DB('../data/security.db')
        print_title("Unknown packages")

        print_paragraph("Sometimes, a package referenced in a bug report ",
                        "cannot be found in the database.  This can be ",
                        "the result of a spelling error, or a historic ",
                        "entry refers to a package which is no longer in ",
                        "the archive.")
                        
        def gen():
            for name, bugs in db.getUnknownPackages(db.cursor()):
                yield name, make_list(map(make_xref, bugs))

        print_table(gen(), caption=("Package", "Bugs"),
            replacement="No unknown packages are referenced in the database.")

        print_footer()

    def print_itp():
        db = security_db.DB('../data/security.db')
        print_title("ITPs with potential security issues")

        def gen():
            old_pkg = ''
            for pkg, bugs, debian_bugs in db.getITPs(db.cursor()):
                if pkg == old_pkg:
                    pkg = ''
                else:
                    old_pkg = pkg
                yield (pkg,
                       make_list(map(make_xref, bugs)),
                       make_list(map(make_debian_bug, debian_bugs)))

        print_table(gen(), caption=("Package", "Issue", "Debian Bugs"),
            replacement="No ITPs are currently known.")

        print_footer()

    def print_testing_status():
        db = security_db.DB('../data/security.db')

        print_title("Vulnerable source packages in testing")

        print_menu([("status/dtsa-candidates", "Candidates for DTSAs")])

        def gen():
            yield (make_bold("Package"),
                   make_bold("Bug"))

            c = db.cursor()

            old_pkg_name = ''
            for (pkg_name, bug_name, archive, urgency,
                 sid_vulnerable, ts_fixed) in db.cursor().execute(
                """SELECT package, bug, section, urgency, unstable_vulnerable,
                testing_security_fixed
                FROM testing_status"""):
                if pkg_name == old_pkg_name:
                    pkg_name = ''
                else:
                    old_pkg_name = pkg_name
                    if archive <> 'main':
                        pkg_name = "%s (%s)" % (pkg_name, archive)

                if ts_fixed:
                    status = 'fixed in testing-security'
                else:
                    if sid_vulnerable:
                        status = make_red('unstable is vulnerable')
                    else:
                        status = make_dangerous('fixed in unstable')

                if urgency == 'unknown':
                    urgency = ''

                yield pkg_name, make_xref(bug_name), urgency, status

        print_table(gen())
        
        print_footer()
    
    def print_dtsa_candidates():
        db = security_db.DB('../data/security.db')

        print_title("Candidates for DTSAs")

        print_paragraph("The table below lists packages which are fixed ",
                        "in unstable, but unfixed in testing. ",
                        "Use the testing migration tracker to find out ",
                        "why they have not entered testing yet.")

        print_menu([("status/release/testing",
                     "List of vulnerable packages in testing")])

        def gen():
            old_pkg_name = ''
            for (pkg_name, bug_name, archive, urgency, stable_later) \
                    in db.cursor().execute(
                """SELECT package, bug, section, urgency,
                (SELECT testing.version_id < stable.version_id
                 FROM source_packages AS testing, source_packages AS stable
                 WHERE testing.name = testing_status.package
                 AND testing.release = 'etch'
                 AND testing.subrelease = ''
                 AND testing.archive = testing_status.section
                 AND stable.name = testing_status.package
                 AND stable.release = 'sarge'
                 AND stable.subrelease = 'security'
                 AND stable.archive = testing_status.section)
                FROM testing_status
                WHERE (NOT unstable_vulnerable)
                AND (NOT testing_security_fixed)"""):
                if pkg_name == old_pkg_name:
                    pkg_name = ''
                    migration = ''
                else:
                    old_pkg_name = pkg_name
                    migration = make_a(url_testing_status(pkg_name),
                                       "check")
                    if archive <> 'main':
                        pkg_name = "%s (%s)" % (pkg_name, archive)
                    else:
                        pkg_name = make_source_package_ref(pkg_name)

                if urgency == 'unknown':
                    urgency = ''
                elif urgency == 'high':
                    urgency = make_red(urgency)

                if stable_later:
                    notes = "(fixed in stable?)"
                else:
                    notes = ''

                yield pkg_name, migration, make_xref(bug_name), urgency, notes

        print_table(gen(),
                    caption=("Package", "Migration", "Bug", "Urgency"))
        
        print_footer()

    def print_unstable_status():
        db = security_db.DB('../data/security.db')

        print_title("Vulnerable source packages in unstable")

        print_paragraph(
            "Note that the list below is based on source packages. ",
            "This means that packages are not listed here once a new, ",
            "fixed source version has been uploaded to the archive, even ",
            "if there are still some vulnerably binary packages present ",
            "in the archive.")

        def gen():
            c = db.cursor()

            old_pkg_name = ''
            for (pkg_name, bug_name, section, urgency) in db.cursor().execute(
                """SELECT DISTINCT sp.name, st.bug_name,
                sp.archive, st.urgency
                FROM source_package_status AS st, source_packages AS sp
                WHERE st.vulnerable AND st.urgency <> 'unimportant'
                AND sp.rowid = st.package AND sp.release = 'sid'
                AND sp.subrelease = ''
                ORDER BY sp.name, st.bug_name"""):
                if pkg_name == old_pkg_name:
                    pkg_name = ''
                else:
                    old_pkg_name = pkg_name
                    if section <> 'main':
                        pkg_name = "%s (%s)" % (pkg_name, section)
                    else:
                        pkg_name = make_xref(pkg_name)

                if urgency == 'unknown':
                    urgency = ''
                elif urgency == 'high':
                    urgency = make_red(urgency)

                yield pkg_name, make_xref(bug_name), urgency

        print_table(gen(), caption=('Package', 'Bug', 'Urgency'))
        
        print_footer()
    
    def do_search():
        form = cgi.FieldStorage()
        query = form.getfirst("query", None)
        if query is None:
            # redirect to start page
            print "Location:", url_from_rel("", full=True)
            print
        else:
            re_simple_query = re.compile(r'^[A-Za-z0-9_.-]+$')
            if re_simple_query.match(query):
                print "Location:", url_from_rel(query, full=True)
                print
            else:
                print_invalid_query()

    commands = {'/data/releases' : print_releases,
                '/data/funny-versions' : print_funny_versions,
                '/data/missing-epochs' : print_missing_epochs,
                '/data/unknown-packages' : print_unknown_packages,
                '/status/release/testing' : print_testing_status,
                '/status/release/unstable' : print_unstable_status,
                '/status/todo' : print_todo,
                '/status/dtsa-candidates' : print_dtsa_candidates,
                '/status/itp' : print_itp,
                '/search/' : do_search}
    try:
        cmd = commands[path_info]
    except KeyError:
        print_invalid_query()
        cmd = None
    if cmd:
        cmd()
else:
    handle_simple_search(match.group(1))