I noticed a forum post earlier today asking about a file which was a Java SQL file. I didn't know there was such a thing as a Java SQL file?!
A quick Google revealed that the poster was referring to the H2 Database Engine. Shame on me, but I'd never even heard of it. The homepage is actually very well documented and a lot of information is right at your fingertips, so this blog post is really an introductory how-to for taking a look at the database files.
In short, a h2.db file (as is their extension) is much like a SQLite database file, in as much as it's impressive support for an SQL database contained within a single file. It has been designed for use with Java, specifically, the JDBC API - which explains why the poster referred to it as a Java SQL file.
The examples of its use that led to the forum post were in FrostWire version 5. My clean install of FrostWire 5.5.4 contained three obvious .h2.db files:
For the developers amongst you, a jar file is available to use with Java programs, but for those of us in the forensic world, we just really want to look at the data. This is easy too.
The easiest way I found to do this was to download the Windows Installer from the homepage - at the time of writing, version 1.3.170, ~4MB. By following the installer I ended up with a H2 group in my Start Menu and three shortcuts:
Documentation takes you to a local copy of the HTML help files from the website - very informative. The only difference between the other two is that one launches the H2 Console using the java command and the other using the javaw command. According to IBM's java documentation, "The javaw command is identical to java, except that javaw has no associated console window." However, in my experience, neither launched a console.
Both commands fire a bat file which start a Java application that puts an icon in the system tray and then opened my default browser, pointing to a service on localhost, port 8082. This website is the 'client' to interact with the h2.db (SQL) file:
As you can see, the interface is set up to connect to a file called: ~/test. (I'm sure you know that ~ means "the currently logged in user's home folder".) So on my machine this maps to: C:\Users\Win7Pro\test.h2.db. Yes, note that the .h2.db extension(s) is not needed! If you do add .h2.db to the path, the client will add another .h2.db to the path on your behalf and will end up looking for test.h2.db.h2.db, which it won't find. Because it can't find it, it will helpfully create it and then open it.
Which is lovely, but means you're then left exploring an empty database file, so some people might take a few minutes to realise they aren't looking at the file they thought they were looking at. I'd imagine.
To show a database that actually has some tables, I'm going to point at a FrostWire database I prepared earlier, so I set my JDBC URL to:
Which maps to:
Note the use of / and the removal of the .h2.db extension. The jdbc: prefix simply means that we are connecting using java database connection.
By clicking 'Connect', and if all goes to plan, you should be met with the starting page:
From here you can navigate around the tables using the collapse/expand icons on the left-hand side, or type SQL queries straight into the textbox. Using it is very much like any other SQL client you've probably used.
For the forensics people, the source code of the library shows that the file signature looks like this:
It's the same 16 bytes repeated three times, in case you're entering it into your scalpel or EnCase signatures table, you can copy and paste it from here:
It'll be interesting to see where else these h2.db files crop up, but I know that I'll very shortly be doing some research on FrostWire 5.
A quick Google revealed that the poster was referring to the H2 Database Engine. Shame on me, but I'd never even heard of it. The homepage is actually very well documented and a lot of information is right at your fingertips, so this blog post is really an introductory how-to for taking a look at the database files.
In short, a h2.db file (as is their extension) is much like a SQLite database file, in as much as it's impressive support for an SQL database contained within a single file. It has been designed for use with Java, specifically, the JDBC API - which explains why the poster referred to it as a Java SQL file.
The examples of its use that led to the forum post were in FrostWire version 5. My clean install of FrostWire 5.5.4 contained three obvious .h2.db files:
- C:\Users\Win7Pro\.frostwire5\dbs\sharefiles.1\sharefiles.h2.db
- C:\Users\Win7Pro\.frostwire5\library_db\library_db.h2.db
- C:\Users\Win7Pro\.frostwire5\search_db\search_db.h2.db
For the developers amongst you, a jar file is available to use with Java programs, but for those of us in the forensic world, we just really want to look at the data. This is easy too.
The easiest way I found to do this was to download the Windows Installer from the homepage - at the time of writing, version 1.3.170, ~4MB. By following the installer I ended up with a H2 group in my Start Menu and three shortcuts:
- H2 Console (Command Line)
- H2 Console
- Documentation
Documentation takes you to a local copy of the HTML help files from the website - very informative. The only difference between the other two is that one launches the H2 Console using the java command and the other using the javaw command. According to IBM's java documentation, "The javaw command is identical to java, except that javaw has no associated console window." However, in my experience, neither launched a console.
Both commands fire a bat file which start a Java application that puts an icon in the system tray and then opened my default browser, pointing to a service on localhost, port 8082. This website is the 'client' to interact with the h2.db (SQL) file:
As you can see, the interface is set up to connect to a file called: ~/test. (I'm sure you know that ~ means "the currently logged in user's home folder".) So on my machine this maps to: C:\Users\Win7Pro\test.h2.db. Yes, note that the .h2.db extension(s) is not needed! If you do add .h2.db to the path, the client will add another .h2.db to the path on your behalf and will end up looking for test.h2.db.h2.db, which it won't find. Because it can't find it, it will helpfully create it and then open it.
Which is lovely, but means you're then left exploring an empty database file, so some people might take a few minutes to realise they aren't looking at the file they thought they were looking at. I'd imagine.
To show a database that actually has some tables, I'm going to point at a FrostWire database I prepared earlier, so I set my JDBC URL to:
jdbc:h2:D:/research/FrostWire/.frostwire5/search_db/search_db
Which maps to:
D:\research\FrostWire\.frostwire5\search_db\search_db.h2.db
Note the use of / and the removal of the .h2.db extension. The jdbc: prefix simply means that we are connecting using java database connection.
From here you can navigate around the tables using the collapse/expand icons on the left-hand side, or type SQL queries straight into the textbox. Using it is very much like any other SQL client you've probably used.
For the forensics people, the source code of the library shows that the file signature looks like this:
It's the same 16 bytes repeated three times, in case you're entering it into your scalpel or EnCase signatures table, you can copy and paste it from here:
\x2D\x2D\x20\x48\x32\x20\x30\x2E\x35\x2F\x42\x20\x2D\x2D\x20\x0A
It'll be interesting to see where else these h2.db files crop up, but I know that I'll very shortly be doing some research on FrostWire 5.
Hi Jimmy, I've replied to you by email, but for others: crawldb.h2.db does open just fine (you remembered to remove the .h2.db bit right?). There is just the one table contained therein: CACHEDATA(ID, KEY, DATA, DATEADDED). The tricky one is DATA; this stores data as 'bytes as ascii' that is if you have 6164616D it's actually 0x61, 0x64, 0x61, 0x6D, which you can paste directly into a hex editor. HTH.
ReplyDeleteThanks for the post. Google tipped me to your this article while search for reading h2.db file. I was just lloking for to find out way to analyse frostwire library_db.h2.db ,etc.
ReplyDeleteAwesome - glad it was useful. Yeah, there's a lot of data to find! Let me know if you have any problems.
Delete