Ruby, Nmap XML, and Databases

So I had a requirement to take some output from nmap scans, shove it into a database and then be able to run some queries on that data.

Wait, isn't there something that already does that?!

Actually PBNJ and nmap_xml2sql.pl will do this but uses (eeeek!) perl to do it. I wanted to do it in Ruby.

Your options for Ruby & Nmap parsing are:

-rubynmap http://rubynmap.sourceforge.net/
-ruby-nmap http://ruby-nmap.rubyforge.org/
-metasploit has its own nmap xml parser
-writing your own

I started with rubynmap for my parsing gem.
(Note: use the svn version. the version # hasn't changed but the svn version works alot better)

I stole the schema from nmap_xml2sql and added a few things and a scripts table for nmap scripts output and tried shoving that into a sqlite3 database.

TABLE nmap (
               sid INTEGER PRIMARY KEY AUTOINCREMENT,
               version TEXT,
               xmlversion TEXT,
               args TEXT,
               types TEXT,
               starttime INTEGER,
               startstr TEXT,
               endtime INTEGER,
               endstr TEXT,
               numservices INTEGER)

TABLE hosts (
               sid INTEGER,
               hid INTEGER PRIMARY KEY AUTOINCREMENT,
               ip4 TEXT,
               ip4num INTEGER,
               hostname TEXT,
               status TEXT,
               tcpcount INTEGER,
               udpcount INTEGER,
               mac TEXT,
               vendor TEXT,
               ip6 TEXT,
               distance INTEGER,
               uptime TEXT,
               upstr TEXT)

----SNIP----

This "works" but sqlite3 doesn't seem to actually support foreign keys. So while I was correctly assigning a SID value in nmap that value wasn't linking up in hosts and the HID value in subsequent tables. If I'm wrong here please let me know if this works for you as written. For me in populates with nulls and I don't see how its linking back to the tables.

cg@ihatesql:~$ sqlite3 nmap
SQLite version 3.6.21
sqlite> .dump nmap
CREATE TABLE nmap (
                sid INTEGER PRIMARY KEY AUTOINCREMENT,
                nmapversion TEXT,
                xmlversion TEXT,
                args TEXT,
                types TEXT,
                starttime INTEGER,
                startstr TEXT,
                endtime INTEGER,
                endstr TEXT,
                numservices INTEGER);
INSERT INTO "nmap" VALUES(1,'4.90RC1','1.03','nmap -A -oX test.xml 209.20.85.250','connect',1262181807,'Wed Dec 30 09:03:27 2009',1262181814,'Wed Dec 30 09:03:34 2009',1000);
COMMIT;

sqlite> .dump hosts
CREATE TABLE hosts (
                sid INTEGER ,
                hid INTEGER PRIMARY KEY AUTOINCREMENT,
                ip4num INTEGER,  
                hostname TEXT,
                status TEXT,           
                mac TEXT,
                vendor TEXT,
                ip6 TEXT,   
                distance INTEGER,
                uptime TEXT,
                starttime INTEGER,
                endtime INTEGER,
        );
INSERT INTO "hosts" VALUES(NULL,1,'209.20.85.250','209-20-85-250.slicehost.net','up','','','','','',1262181807,1262181814);
COMMIT;

so we can see that the SID and HID are correctly auto incrementing but the SID didn't make it into the hosts table

**Actually sqlite3 as of 3.6.19 supports foreign keys...by adding a
FOREIGN KEY(sid) REFERENCES nmap(sid) to the hosts table and so on. And by declaring PRAGMA foreign_keys = ON.

BUT I still couldn't get it to work.

doing a db.execute("PRAGMA foreign_keys = ON") wasn't working for me. I received no errors but doing a dump on the table would list the foreign key support as OFF :-( maybe its a gem issue?

So to cheat I added ip4num, ip6, hostname to tables i knew I'd be querying a lot like ports and scripts.

      CREATE TABLE ports (
                  hid INTEGER,
                  ip4num INTEGER,
                  ip6 TEXT,
                  port INTEGER,
                  state TEXT,
                  reason TEXT,
                  name TEXT,
                  tunnel TEXT,
                  product TEXT,
                  version TEXT,
                  extra TEXT,
                  confidence INTEGER,
                  method TEXT,
                  proto TEXT,
                  owner TEXT,   
                  rpcnum TEXT,
                  fingerprint TEXT,
          FOREIGN KEY(hid) REFERENCES hosts(hid)
          )

That way, querying for open ports or specific versions of a service were possible and I could still get an IP associated with that. A bit harder to pull all that information together but still there and a select * from ports; or select ip4num from ports where port = 1521; would return quick results.

So code or it didn't happen...

nmap-parse takes an nmap xml file and spits out some of the results
http://carnal0wnage.attackresearch.com/sites/default/files/nmap-parse.txt

rubynmapsqlite3 takes an nmapfile and database name (optional), creates or connects to the database, populates the tables if it needs to, parses the nmap xml and puts it into its appropriate tables.
http://carnal0wnage.attackresearch.com/sites/default/files/nmapsqlite3.txt

ruby-nmap-parse uses the ruby-nmap gem to parse nmap xml files
http://carnal0wnage.attackresearch.com/sites/default/files/ruby-nmap-parse.txt

caveats:
-my ruby coding sucks.
-my SQL coding sucks worse.
-code is released in "works for me" status
-send diffs not complaints :-) unless you go crazy with it, in which case just send me a link to your code

Next up pushing that data into a postgres database instead of sqlite3.