SinFP: Reading the Signatures

I learned about SinFP from when I first read it in taosecurity blog here, then today I read that it is integrated into Nessus in tenable blog here. Therefore it raises my interest to look at it. Basically SinFP is available in HeX, instead of trying its features, I'm particularly curious about its signature database. I download the latest signature database from its home site -

shell>wget http://www.gomor.org/files/sinfp-latest.db

My habit is using file command to identify what file type it is before openning, and I'm surprised it is not in plain text format like other tools, it's the SQLite 3.x database file.

shell>file sinfp-latest.db

sinfp-latest.db: SQLite 3.x database

Now this is killing me, I'm worse than beginner and totally have no experience in SQLite. I go to its main site and figure there are many big companies using it and it tells how popular it is here. So I need to try it out, the manual really helps but I don't want to read it for casual use, so I need to write it down for my reference in my blog. Below are what I have gone through.

To show the current default setting value -

shell>sqlite3 sinfp-latest.db ".show"

echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
width:

To list the name and file of the database -

shell>sqlite3 sinfp-latest.db ".databases"
seq name file
--- --------------- ----------------------------------------------------------
0 main /home/geek00l/Desktop/sinfp-latest.db

To show all the tables in the database -

shell>sqlite3 sinfp-latest.db ".table"
IpVersion OsVersionFamily PatternTcpOptions Vendor
Os PatternBinary PatternTcpWindow
OsVersion PatternTcpFlags Signature
OsVersionChildren PatternTcpMss SystemClass

Then select everything from the table Os, you can see what OS is available -

shell>sqlite3 sinfp-latest.db "select * from Os"
1|FreeBSD
2|Linux
3|NetBSD
4|OpenBSD
5|SunOS
6|Windows
7|IOS
Output truncated ...

You can also select everything from multiple tables at the same time with comma, I pipe it to less for navigation purpose -

shell>sqlite3 sinfp-latest.db "select * from Os ,OsVersionFamily, Vendor, PatternBinary, Signature;" | less

You can also dump the signature into raw file and examine it later since it is in plain text form, here I dump it to sinfp-raw.sig file -

shell>sqlite3 sinfp-latest.db "select * from signature" > sinfp-raw.sig

The sqlite3 command line is quite flexible, you can turn on or off its setting in command line directly, as I want to see the header(title of the column) and table in column form, I add -header and -column when running it, for the sql syntax, you can see I use limit 5, this is quite basic stuff for experience SQL database administrator -

shell>sqlite3 -header -column sinfp-latest.db "select * from Os limit 5"

idOs os
---------- ----------
1 FreeBSD
2 Linux
3 NetBSD
4 OpenBSD
5 SunOS

You can also turn it into CSV format using -separator ',' -

shell>sqlite3 -header -separator ',' sinfp-latest.db "select * from Os, Signature limit 5"

idOs,os,idSignature,trusted,idIpVersion,idSystemClass,idVendor,idOs,
idOsVersion,idOsVersionFamily,idP1PatternBinary,idP1PatternTcpFlags,
idP1PatternTcpWindow,idP1PatternTcpOptions,idP1PatternTcpMss,
idP2PatternBinary,idP2PatternTcpFlags,idP2PatternTcpWindow,
idP2PatternTcpOptions,idP2PatternTcpMss,idP3PatternBinary,
idP3PatternTcpFlags,idP3PatternTcpWindow,idP3PatternTcpOptions,
idP3PatternTcpMss
1,FreeBSD,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,2,2,2,3,2
1,FreeBSD,2,1,2,1,1,1,3,2,1,1,1,1,1,1,1,1,4,1,2,2,2,3,2
1,FreeBSD,3,1,2,2,1,2,5,3,3,1,3,1,1,3,1,4,5,1,2,2,2,3,2
1,FreeBSD,4,1,2,2,1,2,7,4,3,1,3,1,1,3,1,4,6,1,2,2,2,3,2
1,FreeBSD,5,1,2,1,1,3,9,5,3,1,5,1,3,3,1,5,7,3,4,2,2,3,2

If you want to learn how the data is inserted to the database, using .dump to dump them in SQL text format. I figure this is useful especially to beginner like me -

shell>sqlite3 sinfp-latest.db ".dump Os"

shell>sqlite3 sinfp-latest.db ".dump Signature"

Of course this is not something I'm really good at, but learning something new is always fun, I think if you are SQL database administrator or developer, you can take advantage of SQLite quickly. I learn MySQL while using Sguil and now I take a brief look at SQLite while trying out Sinfp, all I can say is you always learn something else.

Enjoy ;]