{"id":39,"date":"2008-02-03T15:56:28","date_gmt":"2008-02-03T15:56:28","guid":{"rendered":"http:\/\/pentestmonkey.net\/?p=39"},"modified":"2011-08-20T15:51:13","modified_gmt":"2011-08-20T15:51:13","slug":"osvdb-import","status":"publish","type":"post","link":"https:\/\/pentestmonkey.net\/blog\/osvdb-import","title":{"rendered":"Importing OSVDB into a Postgres Database"},"content":{"rendered":"<p>I was looking at the <a href=\"http:\/\/osvdb.org\/\">Open Source Vulnerbility Database<\/a> (OSVDB) recently.\u00a0 If you haven&#8217;t come across it before, it&#8217;s a source vulnerability information, similar to <a href=\"http:\/\/www.securityfocus.com\/\">bugtraq<\/a> or <a href=\"http:\/\/secunia.com\">secunia<\/a>.<\/p>\n<p>OSVDB has a good web frontend which is easy to search.\u00a0 I was investigating if the database could be downloaded and searched offline during onsite pentests when no Internet connection is available.<\/p>\n<p>In this post I talk about some of the problems I encountered and how I worked around them.\u00a0 OSVDB is a fantastic resource and I hope this post helps you get some use out of it.<\/p>\n<p><!--more--><\/p>\n<h3>Downloading OSVDB<\/h3>\n<p>Once you agree to the <a href=\"http:\/\/osvdb.org\/license\">license<\/a> it&#8217;s possible to download XML dupms of the OSVDB database.\u00a0 At the time of writing these were about 100 MB when uncompressed and contained information on around 40,000 vulnerabilities.\u00a0 This should be a valuable asset when you&#8217;re cut off from the Internet.<\/p>\n<h3>Searching your Offline OSVDB<\/h3>\n<p>There are several ways you could go about searching this information.\u00a0 The ones that occurred to me initially were:<\/p>\n<ul>\n<li>Search for an existing solution<\/li>\n<li>&#8220;grep&#8221; the XML file<\/li>\n<li>Write a command-line \/ web script to search the XML file<\/li>\n<li>Import XML data into a database, then search the database.<\/li>\n<\/ul>\n<p>Surprisingly, I couldn&#8217;t find an existing interface to search OSVDB offline.\u00a0 I&#8217;m sure they must exist.\u00a0 <a href=\"mailto:pentestmonkeyAATTpentestmonkey.net\">Let me know<\/a> if you find one. (Update: see the end of this post).<\/p>\n<p>Grepping the XML file is fast, but making humans read XML is kinda nasty.<\/p>\n<p>I had a brief attempt at writing a command-line tool, but found that PERL&#8217;s XML::Simple module took several minutes to read in the 100 MB XML file &#8211; even before you attempted to do any searching.\u00a0 I wasn&#8217;t willing to wait several minutes for each query, so skipped this option.<\/p>\n<h3>Importing OSVDB XML file to a SQL database<\/h3>\n<p>The OSVDB website provides <a href=\"http:\/\/osvdb.org\/database_info\">database schemas<\/a> for MySQL and PostgreSQL.\u00a0 There&#8217;s also a brilliant <a href=\"http:\/\/osvdb.org\/images\/osvdb-schema-large.jpg\">diagram<\/a> of the schema along with a PERL-based <a href=\"http:\/\/osvdb.org\/tools#importtools\">import script<\/a> to read the XML and squirt it into your database.<\/p>\n<p>I ran into some problems importing the XML data into my Postgres database.\u00a0 I&#8217;m sure I&#8217;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:<\/p>\n<pre>Insert Vuln.DBD::Pg::st execute failed: ERROR:\u00a0 invalid input syntax for type timestamp: \"\"\r\nError: ERROR:\u00a0 invalid input syntax for type timestamp: \"\"<\/pre>\n<p>or:<\/p>\n<pre>Insert Ext Ref Value..........................DBD::Pg::st execute failed: ERROR:\u00a0 duplicate key violates unique constraint \"ext_ref_value_pkey\"<\/pre>\n<pre>Error: ERROR:\u00a0 duplicate key violates unique constraint \"ext_ref_value_pkey\"<\/pre>\n<p>I also found that incorrect relationships where being created in the &#8220;credit&#8221; table.<\/p>\n<p>I spent a week or so trying to fix the import script and eventually undertook to write a replacement.<\/p>\n<h3>Alternative Import Script for Postgres<\/h3>\n<p>I&#8217;m pretty happy that my replcement script, <a href=\"http:\/\/pentestmonkey.net\/tools\/osvdb\/osvdb-xml-to-postgres.pl\">osvdb-xml-to-postgres.pl<\/a> parses the XML properly and creates the correct relationships in the database.\u00a0 However, it&#8217;s no faster and uses 2.4GB of RAM.\u00a0 Oops!\u00a0 It&#8217;s fairly elegant, but apparently not that efficient.<\/p>\n<p>Here&#8217;s an example of how I ran it:<\/p>\n<p>First I made some minor changes to the schema.\u00a0 These are explained in the following section.\u00a0 Download the modified: <a href=\"http:\/\/pentestmonkey.net\/tools\/osvdb\/OSVDB-procedures.sql\">OSVDB-procedures.sql<\/a> , <a href=\"http:\/\/pentestmonkey.net\/tools\/osvdb\/OSVDB-tables.sql\">OSVDB-tables.sql<\/a> and <a href=\"http:\/\/pentestmonkey.net\/tools\/osvdb\/OSVDB-views.sql\">OSVDB-views.sql<\/a>.\u00a0 Also make sure you&#8217;ve downloaded the <a href=\"http:\/\/osvdb.org\/exports\">XML file<\/a> (register first).<\/p>\n<p>Next, create a database then run the import script:<\/p>\n<pre>$ 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<\/pre>\n<p>If you interrupt the process for some reason, you can resume the import by doing:<\/p>\n<pre>$ osvdb-xml-to-postgres.pl xmlDumpByID-2008-1-18.xml<\/pre>\n<p>Existing entries won&#8217;t be overwritten.<\/p>\n<p>Database connection parameters are hard coded at present.\u00a0 You might need to edit these in <a href=\"http:\/\/pentestmonkey.net\/tools\/osvdb\/osvdb-xml-to-postgres.pl\">osvdb-xml-to-postgres.pl<\/a>:<\/p>\n<pre>my $dbname = \"osvdb\";\r\nmy $port = 5432;\r\nmy $host = \"localhost\";\r\nmy $username = \"postgres\";\r\nmy $password = \"\";<\/pre>\n<p>I use PERL&#8217;s DBI, so it should be trivial to modify this script to work with MySQL instead of postgres.\u00a0 I haven&#8217;t tried this, though.<\/p>\n<h3>Modifications to the Database Schema<\/h3>\n<p>The table &#8220;ext_txt&#8221; no longer has an author_id field.\u00a0 I couldn&#8217;t see how to parse this information from the XML dump.\u00a0 &#8220;vuln&#8221;s still have authors, but &#8220;external texts&#8221; don&#8217;t.\u00a0 Views were updated to reflect this change.<\/p>\n<p>I also removed this primary key from the &#8220;author&#8221; table:<\/p>\n<pre>PRIMARY KEY (author_name, author_email)<\/pre>\n<p>The XML dump seems to violate this uniquness contstraint.<\/p>\n<h3>Postgres SQL Dump<\/h3>\n<p>If you don&#8217;t want to run the import don&#8217;t mind using a potentially out-of-data version of OSVDB here&#8217;s a SQL dump (6MB compressed): <a href=\"http:\/\/pentestmonkey.net\/tools\/osvdb\/osvdb-postgres-2008-1-18.sql.bz2\">osvdb-postgres-2008-1-18.sql.bz2<\/a> .<\/p>\n<p>To restore it:<\/p>\n<pre>$ createdb -U postgres osvdb<\/pre>\n<pre>$ psql -U postgres osvdb -c 'create user osvdb'<\/pre>\n<pre>$ bzcat osvdb-postgres-2008-1-18.sql.bz2 | psql -U postgres osvdb<\/pre>\n<p>It&#8217;s a 41MB SQL file, so the import should take a while.<\/p>\n<h3>Searching the Schema<\/h3>\n<p>I haven&#8217;t got round to writing a tool yet!\u00a0 I&#8217;ll make another post when I get round to this.\u00a0\u00a0 (See update at and of this post for SQLite \/ Ruby on rails tool).<\/p>\n<h3>Legal Stuff<\/h3>\n<p>In order to comply with section 5 of the <a href=\"http:\/\/osvdb.org\/license\">OSVDB Free License<\/a> I&#8217;m required to inform you of the following with regard to the SQL schema and SQL dump above:<\/p>\n<p>&#8220;This product includes data from the Open Source Vulnerability Database developed by OSVDB (www.osvdb.org) and its contributors.&#8221;<\/p>\n<p>It&#8217;s also interesting to note that you may need to comply with the <a href=\"http:\/\/osvdb.org\/license\">license<\/a> depending on what you want to do with the database.\u00a0 Section 8 of the license says it best:<\/p>\n<p>&#8220;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.&#8221;<\/p>\n<h3>UPDATE 2007-02-08<\/h3>\n<p>Not long after I made this post, OSVDB release their database in <a href=\"http:\/\/osvdb.org\/file\/dumps\">CSV, MySQL dump and SQLite format<\/a> (you need to create an account first).\u00a0 This is great news for pentesters and other professions who need access to OSVDB offline.\u00a0 There&#8217;s even an offline tools for browsing the SQLite database called <a href=\"http:\/\/osvdb.org\/tools\">OSVDB Personal Edition<\/a> .<\/p>\n<p>OSVDB Personal Edition is a ruby on rails web server which uses a SQLite backend.\u00a0 It&#8217;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.\u00a0 It&#8217;s meant as a PoC, but it&#8217;s still useful.\u00a0 Pentesters beware, though: the web server binds to 0.0.0.0 instead of 127.0.0.1which is probably not what you want. \ud83d\ude42<\/p>\n<p>Great work OSVDB!\u00a0 Keep it up.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I was looking at the Open Source Vulnerbility Database (OSVDB) recently.\u00a0 If you haven&#8217;t come across it before, it&#8217;s a source vulnerability information, similar to bugtraq or secunia. OSVDB has a good web frontend which is easy to search.\u00a0 I was investigating if the database could be downloaded and searched offline during onsite pentests when [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5],"tags":[95,84],"_links":{"self":[{"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/posts\/39"}],"collection":[{"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/comments?post=39"}],"version-history":[{"count":2,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/posts\/39\/revisions"}],"predecessor-version":[{"id":381,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/posts\/39\/revisions\/381"}],"wp:attachment":[{"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/media?parent=39"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/categories?post=39"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pentestmonkey.net\/wp-json\/wp\/v2\/tags?post=39"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}