Date: Fri, 27 Aug 2004 07:07:07 -0400 (EDT) From: Dan Langille <dan@langille.org> To: freebsd-vuxml@freebsd.org Subject: FreshPorts - VuXML tables - DDL + data Message-ID: <20040827051637.T78365@xeon.unixathome.org>
next in thread | raw e-mail | index | archive | help
Here is the DDL I created this morning. Note that some slight changes have been made relative to the diagram I posted last night. The diagram is now out of date. The rest of this message contains a sample vuxml entry followed by the DDL to create the tables and populate it with a single VuXML entry. It also shows how I mark which packages are affected by a given vid (i.e. how I populate the commit_log_ports_vuxml table). I have created the code to display VuXML information, but at present, the only data loaded is for http://beta.freshports.org/ftp/tnftpd/ NOTE: I'm assuming there can be multiple VuXML entries for a given version of a package/port. However, there will be only one VuXML icon shown per row of Commit History. Look at the last commit on the page for a commit which is affected by two VuXML entries (this is achieved with a fake VuXML entry). freshports.org=# select id, vid, topic from vuxml; id | vid | topic ----+--------------------------------------+---------------------------------------------- 1 | c4b025bb-f05d-11d8-9837-000c41e2cdad | tnftpd -- remotely exploitable vulnerability (1 row) freshports.org=# select * from vuxml_affected; id | vuxml_id | type ----+----------+--------- 1 | 1 | package 2 | 1 | package 3 | 1 | system This vid affects two packages and relates to one system. freshports.org=# select * from vuxml_names; id | vport_versionport_versionuxml_affected_id | name ----+-------------------+----------- 1 | 1 | tnftpd 2 | 2 | lukemftpd 3 | 3 | FreeBSD (3 rows) The packages and system are listed above. freshports.org=# select * from vuxml_ranges; id | vuxml_name_id | range_operator_start | range_operator_end | range_version_start | range_version_end ----+---------------+----------------------+--------------------+---------------------+------------------- 7 | 1 | | | < | 20040810 8 | 2 | >= | 0 | | 9 | 3 | >= | 4.7 | | (3 rows) These are the ranges related to each vuxml_affected row. freshports.org=# select * from vuxml_references; vuxml_id | reference | type ----------+-------------------------------------------------------------------------------+------- 1 | http://cvsweb.netbsd.org/bsdweb.cgi/src/libexec/ftpd/ftpd.c#rev1.158 | url 1 | ftp://ftp.netbsd.org/pub/NetBSD/security/advisories/NetBSD-SA2004-009.txt.asc | url 1 | http://lists.netsys.com/pipermail/full-disclosure/2004-August/025418.html | mlist (3 rows) freshports.org=# Given the above information, here is one way to populate the commit_log_ports_vuxml table. freshports.org=# insert into commit_log_ports_vuxml (commit_log_id, port_id, vuxml_id) select commit_log_id, port_id, 1 as vuxml_id from commit_log_ports where port_id = (select id from ports_active where name = 'tnftpd') and port_version < '20040810'; INSERT 0 9 DDL follows ### create table vuxml ( id serial not null, vid text not null, topic text not null, description text not null, date_discovery text , date_entry text , date_modified text , status char(1) not null, primary key (id) ); create table commit_log_ports_vuxml ( id serial not null, commit_log_id integer not null, port_id integer not null, vuxml_id integer not null, primary key (id) ); create table vuxml_ranges ( id serial not null, vuxml_name_id integer not null, range_operator_start text , range_operator_end text , range_version_start text , range_version_end text , primary key (id) ); create table vuxml_references ( vuxml_id integer not null, reference text not null, type text not null, primary key (vuxml_id, reference) ); create table vuxml_affected ( id serial not null, vuxml_id integer not null, type text not null, primary key (id) ); create table vuxml_names ( id serial not null, vuxml_affected_id integer not null, name text not null, primary key (id) ); alter table commit_log_ports_vuxml add foreign key (vuxml_id) references vuxml (id) on update cascade on delete cascade; alter table commit_log_ports_vuxml add foreign key (port_id) references ports (id) on update cascade on delete cascade; alter table commit_log_ports_vuxml add foreign key (commit_log_id) references commit_log (id) on update cascade on delete cascade; alter table vuxml_ranges add foreign key (vuxml_name_id) references vuxml_names (id) on update cascade on delete cascade; alter table vuxml_references add foreign key (vuxml_id) references vuxml (id) on update cascade on delete cascade; alter table vuxml_affected add foreign key (vuxml_id) references vuxml (id) on update cascade on delete cascade; alter table vuxml_names add foreign key (vuxml_affected_id) references vuxml_affected (id) on update cascade on delete cascade; ### Data follows: delete from vuxml where id = 1; insert into vuxml (id, vid, topic, description, date_discovery, date_entry, date_modified, status) values (1, 'c4b025bb-f05d-11d8-9837-000c41e2cdad', 'tnftpd -- remotely exploitable vulnerability', ' <p>lukemftpd(8) is an enhanced BSD FTP server produced within the NetBSD project. The sources for lukemftpd are shipped with some versions of FreeBSD, however it is not built or installed by default. The build system option WANT_LUKEMFTPD must be set to build and install lukemftpd. [<strong>NOTE</strong>: An exception is FreeBSD 4.7-RELEASE, wherein lukemftpd was installed, but not enabled, by default.]</p> <p>Przemyslaw Frasunek discovered several vulnerabilities in lukemftpd arising from races in the out-of-band signal handling code used to implement the ABOR command. As a result of these races, the internal state of the FTP server may be manipulated in unexpected ways.</p> <p>A remote attacker may be able to cause FTP commands to be executed with the privileges of the running lukemftpd process. This may be a low-privilege ^Ftp\' user if the -r\' command line option is specified, or it may be superuser privileges if -r\' is *not* specified.</p> ', '2004-08-17', '2004-08-17', NULL, 'A'); insert into vuxml_references (vuxml_id, reference, type) values (1, 'http://cvsweb.netbsd.org/bsdweb.cgi/src/libexec/ftpd/ftpd.c#rev1.158', 'url'); insert into vuxml_references (vuxml_id, reference, type) values (1, 'ftp://ftp.netbsd.org/pub/NetBSD/security/advisories/NetBSD-SA2004-009.txt.asc', 'url'); insert into vuxml_references (vuxml_id, reference, type) values (1, 'http://lists.netsys.com/pipermail/full-disclosure/2004-August/025418.html', 'mlist'); insert into vuxml_affected (id, vuxml_id, type) values (1, 1, 'package'); insert into vuxml_affected (id, vuxml_id, type) values (2, 1, 'package'); insert into vuxml_affected (id, vuxml_id, type) values (3, 1, 'system'); insert into vuxml_names (id, vuxml_affected_id, name) values (1, 1, 'tnftpd'); insert into vuxml_names (id, vuxml_affected_id, name) values (2, 2, 'lukemftpd'); insert into vuxml_names (id, vuxml_affected_id, name) values (3, 3, 'FreeBSD'); insert into vuxml_ranges (vuxml_name_id, range_operator_start, range_operator_end, range_version_start, range_version_end) values (1, NULL, NULL, '<', '20040810'); insert into vuxml_ranges (vuxml_name_id, range_operator_start, range_operator_end, range_version_start, range_version_end) values (2, '>=', '0', NULL, NULL); insert into vuxml_ranges (vuxml_name_id, range_operator_start, range_operator_end, range_version_start, range_version_end) values (3, '>=', '4.7', NULL, NULL); -- Dan Langille - http://www.langille.org/
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20040827051637.T78365>