Importing OSVDB into a Postgres Database

I was looking at the Open Source Vulnerbility Database (OSVDB) recently.  If you haven’t come across it before, it’s a source vulnerability information, similar to bugtraq or secunia.

OSVDB has a good web frontend which is easy to search.  I was investigating if the database could be downloaded and searched offline during onsite pentests when no Internet connection is available.

In this post I talk about some of the problems I encountered and how I worked around them.  OSVDB is a fantastic resource and I hope this post helps you get some use out of it.

Downloading OSVDB

Once you agree to the license it’s possible to download XML dupms of the OSVDB database.  At the time of writing these were about 100 MB when uncompressed and contained information on around 40,000 vulnerabilities.  This should be a valuable asset when you’re cut off from the Internet.

Searching your Offline OSVDB

There are several ways you could go about searching this information.  The ones that occurred to me initially were:

• Search for an existing solution
• “grep” the XML file
• Write a command-line / web script to search the XML file
• Import XML data into a database, then search the database.

Surprisingly, I couldn’t find an existing interface to search OSVDB offline.  I’m sure they must exist.  Let me know if you find one. (Update: see the end of this post).

Grepping the XML file is fast, but making humans read XML is kinda nasty.

I had a brief attempt at writing a command-line tool, but found that PERL’s XML::Simple module took several minutes to read in the 100 MB XML file – even before you attempted to do any searching.  I wasn’t willing to wait several minutes for each query, so skipped this option.

Importing OSVDB XML file to a SQL database

The OSVDB website provides database schemas for MySQL and PostgreSQL.  There’s also a brilliant diagram of the schema along with a PERL-based import script to read the XML and squirt it into your database.

I ran into some problems importing the XML data into my Postgres database.  I’m sure I’ve had it working in the past, but this time round I found that the script took up 500MB of memory, ran for 16 hours or so, then produced an error like:

Insert Vuln.DBD::Pg::st execute failed: ERROR:  invalid input syntax for type timestamp: ""
Error: ERROR:  invalid input syntax for type timestamp: ""

or:

Insert Ext Ref Value..........................DBD::Pg::st execute failed: ERROR:  duplicate key violates unique constraint "ext_ref_value_pkey"

Error: ERROR:  duplicate key violates unique constraint "ext_ref_value_pkey"

I also found that incorrect relationships where being created in the “credit” table.

I spent a week or so trying to fix the import script and eventually undertook to write a replacement.

Alternative Import Script for Postgres

I’m pretty happy that my replcement script, osvdb-xml-to-postgres.pl parses the XML properly and creates the correct relationships in the database.  However, it’s no faster and uses 2.4GB of RAM.  Oops!  It’s fairly elegant, but apparently not that efficient.

Here’s an example of how I ran it:

First I made some minor changes to the schema.  These are explained in the following section.  Download the modified: OSVDB-procedures.sql , OSVDB-tables.sql and OSVDB-views.sql.  Also make sure you’ve downloaded the XML file (register first).

Next, create a database then run the import script:

$ dropdb -U postgres osvdb; createdb -U postgres osvdb; cat OSVDB-tables.sql OSVDB-views.sql OSVDB-procedures.sql | psql -U postgres osvdb; perl osvdb-xml-to-postgres.pl xmlDumpByID-2008-1-18.xml

If you interrupt the process for some reason, you can resume the import by doing:

$ osvdb-xml-to-postgres.pl xmlDumpByID-2008-1-18.xml

Existing entries won’t be overwritten.

Database connection parameters are hard coded at present.  You might need to edit these in osvdb-xml-to-postgres.pl:

my $dbname = "osvdb";
my $port = 5432;
my $host = "localhost";
my $username = "postgres";
my $password = "";

I use PERL’s DBI, so it should be trivial to modify this script to work with MySQL instead of postgres.  I haven’t tried this, though.

Modifications to the Database Schema

The table “ext_txt” no longer has an author_id field.  I couldn’t see how to parse this information from the XML dump.  “vuln”s still have authors, but “external texts” don’t.  Views were updated to reflect this change.

I also removed this primary key from the “author” table:

PRIMARY KEY (author_name, author_email)

The XML dump seems to violate this uniquness contstraint.

Postgres SQL Dump

If you don’t want to run the import don’t mind using a potentially out-of-data version of OSVDB here’s a SQL dump (6MB compressed): osvdb-postgres-2008-1-18.sql.bz2 .

To restore it:

$ createdb -U postgres osvdb

$ psql -U postgres osvdb -c 'create user osvdb'

$ bzcat osvdb-postgres-2008-1-18.sql.bz2 | psql -U postgres osvdb

It’s a 41MB SQL file, so the import should take a while.

Searching the Schema

I haven’t got round to writing a tool yet!  I’ll make another post when I get round to this.   (See update at and of this post for SQLite / Ruby on rails tool).

Legal Stuff

In order to comply with section 5 of the OSVDB Free License I’m required to inform you of the following with regard to the SQL schema and SQL dump above:

“This product includes data from the Open Source Vulnerability Database developed by OSVDB (www.osvdb.org) and its contributors.”

It’s also interesting to note that you may need to comply with the license depending on what you want to do with the database.  Section 8 of the license says it best:

“NON TRANSFERABILITY: This Free License is non-transferable. This means that it applies to you, not to the people you distribute the product to. These people are subject to the same Copyright and MAY OR MAY NOT qualify for their own, free, unregistered license.”

UPDATE 2007-02-08

Not long after I made this post, OSVDB release their database in CSV, MySQL dump and SQLite format (you need to create an account first).  This is great news for pentesters and other professions who need access to OSVDB offline.  There’s even an offline tools for browsing the SQLite database called OSVDB Personal Edition .

OSVDB Personal Edition is a ruby on rails web server which uses a SQLite backend.  It’s VERY easy to install, has a simple search feature and has none of the performance problems associated with slurping a big XML file into memory.  It’s meant as a PoC, but it’s still useful.  Pentesters beware, though: the web server binds to 0.0.0.0 instead of 127.0.0.1which is probably not what you want. 🙂

Great work OSVDB!  Keep it up.

 

 

 

 

 

 

 

osvdb, postgresql

Blog