I Thought I Saw A Twittering TweetDeck Monkey

Girl, Unallocated recently asked why I wasn't on Twitter. I'll admit that when Twitter first came out I was pretty skeptical - "Who would want to read about my over-ripe bananas?" and "140 characters isn't nearly enough to insult someone (creatively)". Not having a Twitter capable mobile device was just another nail in the coffin. However, after GU's recent questioning, I've since discovered that a lot of forensicky update notifications occur on Twitter and it's also a great way to stalk meet new people (I am obviously joking ... I don't really care about forensicky updates ;).
Anyhoo, I started looking into a couple of PC Twitter clients called TweetDeck and MetroTwit. A recent Youtube demo video showed MetroTwit using 600 Mb RAM just in normal ops. When you consider that the ancient WinXP PC I'm using has 2 GB RAM and I also like to run SIFT on VMWare, that made the decision pretty easy. Hello, TweetDeck! *I also noticed that Corey Harrell has used it - so I know at least one other person might find this post useful/semi-interesting ;)

I did a brief check after installation, and TweetDeck "only" uses ~100 Mb. A bit steep for 140 character messages, but methinks my trusty old PC can live with that. Also, as TweetDeck has been bought by Twitter, you kind of hope they have their functionality and security all sorted. Except ... recently there was an issue with TweetDeck granting a user access to other people's TweetDeck/Facebook profiles. This user wasn't actively trying to subvert the system, he just opened up TweetDeck and there it all was. So TweetDeck shut down for a while and they (presumably) fixed the issue(s). Not particularly inspiring stuff eh?

Which brings me to the topic of this post - I was initially interested to see what artifacts (if any) TweetDeck leaves behind. And it was while monkeying around with TweetDeck that I discovered that the Twitter web search functions also return some interesting information.

TweetDeck v1.3 (on WinXP)

The first thing TweetDeck wants you to do after installing, is register an email address and password for the TweetDeck interface. Chances are, most people will use the same login/password as their Twitter account. Tsk, tsk.

After launching TweetDeck and adding my Twitter profile, I ran SysInternals TCPView and found that TweetDeck connects via TCP over port 443 (HTTPS) to both "twitter.com" and "amazon.com" domains. Presumably, Twitter has outsourced their storage of tweets/images to Amazon's cloud storage. These connections seemed to timeout if nothing is browsed.
I wasn't going to go hardcore and network capture the traffic because a shortish time ago, in a University not so far away, Mike Wilkinson told my Network Forensics class that intercepting live network traffic is best left to the cats with warrants (in this part of The Empire anyway).

So then I ran SysInternals ProcMon (v2.96) and tried to see if TweetDeck was reading/writing any account info to the Windows Registry ... No such luck! Either I missed the entries (not uncommon) or they just weren't there. I did however, notice that the following file got accessed quite a lot:

C:\Documents and Settings\USER\Local Settings\Application Data\twitter\TweetDeck\localStorage\qrc__0.localstorage

I have obviously changed the profile name to protect the guilty ;)

So firing up our trusty WinHex hex editor revealed the following file header snippet in "qrc__0.localstorage":

SQLite3 DB

Look familiar? Those sneaky TweetDeck Developers!
So I copied the "qrc__0.localstorage" over to SANS SIFT to play around with it a little more (using the SQLite Manager Firefox Plugin) and here's what I found:
- The file contains one SQLite table called "ItemTable"
- "ItemTable" consists of a "rowid", "key" and "value" column fields
- "rowid" seems to increase with use - presumably as key/value fields are updated

Most of the "value" fields appear to be JSON encoded. More information about JSON is available here. But the 1 minute summary is:

- JSON is an acronym for "JavaScript Object Notation"
- JSON is a text based encoding system based on name/value pairs
- Possible value types are: strings (delimited by ""), numbers (base 10, can be negative/decimal fractions/base 10 exponentials), objects, arrays, true, false, null
- An object is defined using { } to enclose a name separated by a colon and then the value.
eg {object_name: object_value}
- An array is defined using [ ] to enclose a series of comma separated values.
eg [array_value1, array_value2]
- It is possible to nest values. So as we will see later, you can have an array of tweet objects each of which has an array of key/value objects. This can make it difficult to sort out all the different brackets and commas.

So what are the actual key/value fields in the "ItemTable" database?

Normally, there are 7 key/value pairs in the "ItemTable" database table:
"tweetdeck_account" = An "email" object representing the TweetDeck user's login (eg "user@email.com").
"columns" = Suspect it represents user column arrangement preferences for the GUI.
"hoard" = An object with it's name derived from the "email" object value (eg "user@email.com"). Used to store a 312 character alphanumeric string which I suspect is a hash/encrypted string (or maybe a group of such strings).
"clients" = Several objects representing account information plus the last 4 TweetDeck search terms.
"__version__" = 2.
"_session" = A 44 character alphanumeric string which probably represents a session key. It seems to change with each launch.
"feeds" = Suspect it has to do with the user defined columns each having its own data connection.

Additional testing revealed that if "Keep Me Signed In" is ticked on the TweetDeck login page and then TweetDeck is closed, these artifacts remain readable in "ItemTable" (using SQLite Manager).
If TweetDeck is closed and "Keep Me Signed In" is NOT ticked, then "ItemTable" appears empty (in SQLite Manager) . However, the data is still appears to be (partially) viewable with WinHex (looking at "qrc__0.localstorage").

By chance, I copied an active/open "qrc__0.localstorage" to SIFT and noticed that there were 2 extra key/value fields in "ItemTable" when TweetDeck is running. These field names are prepended with a ":" (indicating they're in shared memory) and are called:
":CACHED_HASHTAGS"  = Had no values
":CACHED_TWITTER_USERS_V2" = Appears to be a cache of Twitter user profiles that the TweetDeck user is "following". Example format follows:
{"lowercase_handle1":["123456781", "CapitalizedHandle1", "Proper Name1", "http link to profile1 pic"], "lowercase_handle2":["123456782", "CapitalizedHandle2", "Proper Name2", "http link to profile2 pic"]}

Note: The numerical fields turn out to be Twitter user ID numbers.

I was only able to see these additional fields after copying an active "qrc__0.localstorage". The weird thing was, after closing/reopening/closing (staying logged in) TweetDeck, I was still able to see these fields in both SQLite Manager and WinHex. After I subsequently opened/logged out/closed TweetDeck, these fields were no longer visible in SQLite Manager but some of the data still appears viewable with WinHex.
Perhaps copying the active file meant the contents of shared memory were also auto-magically written to the new file copy. The subsequent opening(s) of this database file then showed the previously recorded ":" fields in the database.
And perhaps TweetDeck/SQLite doesn't delete/overwrite all the data in a table when "deleting" a table's contents (eg after logging out). Perhaps it just deletes the addressing mechanism? Hence, while you can't see the table in SQLite Manager, you can still see some data using WinHex? To quote Doris Day et al: "Perhaps, perhaps, perhaps".

OK, so we've taken a look at "ItemTable" - now what?

One of the more interesting things I noticed in "ItemTable" was that the "columns", "clients" and "feeds" fields mention a recurring multiple digit number sequence throughout each. There were also similar looking numbers present in ":CACHED_TWITTER_USERS_V2".
Suspecting this number to be a Twitter user ID number, I went to the Twitter Developer site and found this article regarding the Twitter REST API (API = Application Programming Interface). It lists lots of searches that you can perform from the comfort of your favourite browser. These search queries are run against the Twitter database via a Twitter web interface. The number of searches may be limited per hour and may not be all-inclusive.
So to confirm that the multi-digit number I was seeing in my "ItemTable" table was a valid Twitter user ID number, I typed the following into Firefox on SIFT.

https://api.twitter.com/1/users/lookup.json?user_id=XXXXXXXXX
where XXXXXXXXX = Twitter user ID number

SIFT then auto-magically launched a save dialog for the returned JSON file. I then used gedit to display it and saw a bunch of JSON text. The first key/value field was "id":XXXXXXXXX followed later by the corresponding "name" and "screen_name" values of the TweetDeck user. The JSON also showed various other profile info which is described later in the testing section below.

So, given a viable TweetDeck "qrc__0.localstorage" database file, we can derive a TweetDeck user's Twitter user ID number. We can then use the Twitter REST API with that user ID number to determine that user's handle name. Subsequently, I also ran the lookup query using the numbers found in the ":CACHED_TWITTER_USERS_V2" user profiles. These numbers were found to correspond to their surrounding handle name.


More Twitter REST API Searches

We can also use the Twitter user ID number/handle to perform other queries with the Twitter REST API:

For retrieving a Twitter user's Profile info we can use:
https://api.twitter.com/1/users/show.json?user_id=XXXXXXXXX
OR
https://api.twitter.com/1/users/show.json?screen_name=TwitterHandle
Note: the "screen_name" argument is not case sensitive
For more info see here.

To list a Twitter user's "follower" ID numbers (eg who is following user_id=XXXXXXXXX?) we can use:
https://api.twitter.com/1/followers/ids.json?user_id=XXXXXXXXX
OR
https://api.twitter.com/1/followers/ids.json?screen_name=TwitterHandle
For more info see here.

To list a Twitter user's friend ID numbers (eg who is user_id=XXXXXXXXX following?) we can use:
https://api.twitter.com/1/friends/ids.json?user_id=XXXXXXXXX
OR
https://api.twitter.com/1/friends/ids.json?screen_name=TwitterHandle
For more info see here.

Given a list of Twitter user ID numbers (eg from a friend/follower lookup), you can use a comma separated list to retrieve each Twitter user's profile info via:
https://api.twitter.com/1/users/lookup.json?user_id=XXXXXXXXX, YYYYYYYYY, ZZZZZZZZZ
For more info see here.

You can also search Twitter by term:
http://search.twitter.com/search.json?q=TwitterHandle
This will return the last weeks worth of tweets containing the given TwitterHandle (eg tweets at/from/mentioning). It might not return 100% of all possible results. For more info see here.
Alternatively, you can perform a conventional web search at:
http://twitter.com/#!/search
This will return the results within the browser. If you need to record these results, having a JSON file (via the first search method) might make documentation easier rather than a screenshot/printout for the second method.

Additional Resources (Stuff I didn't know where else to put)

This article describes how each tweet has its own unique ID number.
This website allows you to retrieve a Twitter user's handle (eg Batman) from their Twitter user ID number or vice-versa. The website also states that the numeric user ID is like a primary key (unique number) that does not change. So if a user changes their Twitter handle, they can still be found via their unique ID number.
I also found out about these similar sites (here and here) which translate from a Twitter user's handle to a Twitter user's ID number (one way only).
From my limited testing, the results from these sites appear to be consistent with each other. Given a choice however, I would trust the Twitter REST API over these webpages. All that stuff about about straight from the horses mouth etc.
Whilst doing a search for TweetDeck artifacts, I came across this since superseded article. It describes the database schema for a previous version of TweetDeck. It's interesting that the developers have since changed to a more generic (and harder to understand) schema of key/JSON value pairs. An attempt at extra security through obscurity?

Enter the Twit-Monkey!

Manually parsing through all this JSON text was making me cross(eyed) and I vaguely remember Harlan Carvey mentioning he had written a Perl script that read JSONs. Just FYI, I'm going for a name drop record in this post :)
Anyhoo, I thought it would take me as long to read through some of these search results as it would to write a Perl script to print out JSONs in a more readable format (how's that for cocky?). So then I *strutted* to CPAN and found the aptly named JSON Perl package. This package has functions to both encode/decode JSONs and best of all, it has a function to "pretty print" JSONs in a more human readable format.

To install it on SIFT I typed:
"sudo cpan JSON"
and then said yes to any dependency installs.

Now we should be ready to write the script. As usual, my code is pretty hack-tacular but it seems to work OK for each of the search types listed above.

The Code

#CODE BEGINS ON LINE BELOW
#!/usr/bin/perl -w

# Perl script to pretty print JSON files

use strict;

use Getopt::Long;
use JSON;

my $version = "json-printer.pl v2012-04-08";
my $help = 0;
my $filename="";
my @jsonlist;

GetOptions('help|h' => \$help,
            'f=s' => \$filename);

if ($help || $filename eq "")
{
    print("\nHelp for $version\n\n");
    print("Perl script to pretty print JSON files.\n");
    print("Example: json-printer.pl -f search-5.json\n");
    exit;
}

open(my $tfile, "<", $filename) or die "Can't Open $filename JSON File!";
@jsonlist = <$tfile>; # extract each line to a list element
chomp(@jsonlist); # get rid of newlines
close($tfile);

my $json_string = join(' ', @jsonlist); # join list elements into one big happy scalar string

my $json = JSON->new->allow_nonref; # create a new JSON object which converts non-references into their values for encoding
my $perl_scalar = $json->decode($json_string); # converts JSON string into Perl hash(es)

# at this point (if you wanted to) you can add code to iterate thru the hash(es) to extract/use values.

my $pretty_printed = $json->pretty->encode( $perl_scalar ); # re-encode the hash just so we can then pretty print it (hack-tacular!)
print $pretty_printed;

#CODE ENDS ON LINE ABOVE

Code Summary

This short script ("json-printer.pl") lets the JSON Perl package do all the heavy lifting. First, it reads the user specified JSON file into a scalar string ("$json_string") and then it calls "$json->decode" to convert the string into a Perl hash data structure. The script then calls "$json->pretty->encode" to convert that hash data structure into a more human readable string format. It's a round-about way of doing things but it seems to work.

Note: "$perl_scalar" is a reference to a hash data structure. If you wanted to, you could use it to iterate through the hash structure and retrieve any of the individual values.

Code Testing

You'll just have to trust me on this and/or try out the code yourself. The script above is able to read the JSON data returned by the various Twitter REST API searches listed previously.
I don't feel comfortable displaying other people's tweeting information here (eg their user id, what method they use to tweet). Its probably no more detailed than you could get by searching www.twitter.com/search but I just don't think it's my place.

Anyhoo, as some of the REST API searches mentioned previously return information about individual tweets (eg in a user profile's "status" field), I thought it would be interesting to have a look at these returned tweet fields (without divulging real data). Each tweet in a JSON appears to have the following format:

"to_user_id" = Destination Twitter user ID number (in unquoted numeric format).
"source" = Source URL which can show how tweet was posted (eg via twitter.com, tweetdeck, tweethopper etc). Some URL arguments  may also list the device (eg iPad, Android).
"profile_image_url" = HTTP URL showing the source's avatar filename.
"profile_image_url_https" = HTTPS URL showing the source's avatar filename.
"created_at" = Date and Time of tweet creation.
"text" = Tweet text including @ addressing (eg "@Superman How do you keep your tights so clean?").
"id" = Unique tweet ID number (in "" quoted string format).
"in_reply_to_status_id" = When available, shows the tweet ID number this tweet is replying to (in "" quoted string format).
"from_user_id_str" = Source's Twitter user ID number (in "" quoted string format).
"from_user" = Source's Twitter user handle (eg "Batman").
"geo" = Source's(?) location information.
"from_user_id" = Source's Twitter user ID number (in unquoted numeric format).
"id_str" = Unique tweet ID number (in "" quoted string format).
"iso_language_code" = Presumably the language used to tweet (eg "en" = English).
"to_user_id_str" = Destination's Twitter user ID number (in "" quoted string format). Can also be null.
"to_user" = Destination's Twitter user handle string (eg "Superman"). Can also be null.
"from_user_name" = Source's Twitter user personal name string (eg "Bruce Wayne")
"to_user_name" = Destination's Twitter user personal name string (eg "Clark Kent"). Can also be null.

Note: This list is not all inclusive (eg there's some optional reply fields I've left out).

Twitter help uses the term "Username" to denote the addressing mechanism name (eg @Batman). It then uses "Name" to represent your personal name ID (eg "Bruce Wayne"). Got it? Now here's where it might get a little confusing ...
In the above tweet message, the "to_user_name" and "from_user_name" contain the personal ID Names (eg "Bruce Wayne"). The "to_user" and "from_user" contain the addressing mechanism names (eg "Batman").

Thanks to Frank McClain for sending me a "Mice" tweet to analyse/help confirm my findings. Quack-tacular!

Some of the REST API searches mentioned previously may return a Twitter user's Profile information. Here are some selected Profile fields I found interesting:

"friends_count" = How many Twitter users this user is following (in unquoted numeric format).
"profile_image_url" = HTTP URL string showing user's avatar image filename.
"profile_background_image_url_https" = HTTPS URL string showing user's profile background image filename.
"url" = User's website URL string (eg "www.blog.com").
"id" = User's Twitter user ID number (in unquoted numeric format).
"screen_name" = User's handle/screen name (eg "Batman").
"location" = User's location string (eg "Gotham").
"lang" = Presumably the user's tweet language (eg "en" for English).
"followers_count" = How many Twitter users are following this user (in unquoted numeric format).
"name" = User's personal name ID (eg "Bruce Wayne").
"description" = User's witty description of themselves (eg "The Dark Knight").
"status" = Object containing the user's most recent (unprotected) sent tweet (see above tweet format for more details).
"profile_image_url_https" = HTTPS URL string showing user's avatar image filename.
"created_at" = Date and Time when the user's account was created.
"utc_offset" = Appears to be the numeric timezone offset in seconds from GMT (eg -21600).
"profile_background_image_url" = HTTP URL string showing user's profile background image filename.
"protected" = Boolean presumably representing whether the user's tweets are protected (eg true/false).
"id_str" = User's Twitter user ID number (in "" quoted string format).
"time_zone" = Appears to be string based representation of time zone (eg "Central Time (US & Canada)").
"geo_enabled" = Boolean presumably representing if the user's location is disclosed.
"profile_use_background_image" = Boolean representing if the user is using their background profile picture.

Adding to the name confusion mentioned earlier, we now see the "screen_name" and "name" keys. In this case, "screen_name" seems to refer to the addressing mechanism name (eg "Batman") and "name" refers to the personal ID name (eg "Bruce Wayne"). And while not explicitly present in these fields, there's also the commonly used "handle" term (eg "Batman"). Also note that "id" in a tweet message refers to the unique tweet ID number where as "id" in a profile represents a Twitter user's ID number ... Consistency? Pffft! We don't need no stinkin' consistency! Everyone's a critic huh?

Summary

We've installed TweetDeck and seen that it can store/leave behind the last 4 user search terms and multiple Twitter user ID number artifacts. These user ID numbers can then be used to find corresponding Twitter handles/addressing mechanisms via a Twitter REST API web search. The Twitter REST API can also be used to retrieve other information about the user's followers/leaders/profile.
We have also coded a small Perl script ("json-printer.pl") to make it easier to read the JSON formatted results returned from the Twitter REST API.
Any comments/suggestions/requests for further name droppage (the record now stands at 5 / 6 if you count Doris) are welcome.
FYI This will probably be my last post for a few weeks (month?) as I have to swap my PC for some renovation work boots. I will still be contactable via email / Twitter though. Hopefully, I'll be back with some new ideas and all of my digits in the near future ;)

Creating a RegRipper Plugins Maintenance Perl Script

I recently asked Corey Harrell about any unfulfilled programming ideas he might have and he told me about his idea for a RegRipper Plugins maintenance Perl script.

He was after a script that would go into the plugins directory and then tell the user what plugins were missing from the various "Plugins" text files. Note: I am using "Plugins" to refer to the "ntuser", "software", "sam", "system", "security" and "all" text files. These contain a list of Perl (*.pl) plugins that a user can use to batch run against a particular hive.
He also mentioned that it might be handy to be able to exclude certain files (eg malware plugins) from being considered so if a user has deleted certain entries, they aren't constantly being reminded "Hey! There's a difference between what's in the "Plugins" file and what's actually available (ie the *.pl plugins)".

UPDATE: This script was not intended to be used to blindly load every possible *.pl plugin into their relevant "Plugins" text file. It was intended for use as a diagnostic tool "What (*.pl) plugins are missing from my current "Plugins" file?" (eg "ntuser"). Users can then look at the missing plugins list and determine if they should/should not include them in their "Plugins" text file.

UPDATE: Script ("PrintMissingPlugins" function) has been updated so commented-out entries (eg #ccleaner) in "Plugins" text files are not used in comparisons for missing *.pl plugins. Thanks Francesco!

UPDATE: Script ("PrintMissingPlugins" function) has been updated so blank lines in "Plugins" text files are not used in comparisons for missing *.pl plugins Have also updated the Code and Testing sections.

So let's see how we can implement Corey's idea ...

The Code

# CODE STARTS ON LINE BELOW
#!/usr/bin/perl
# Note: I removed the "-w" above because the eval/require/getHive section was generating numerous variable masking warnings
# This shouldn't affect us as we only call the plugin's getHive method.

# Perl script to list updates for RegRipper plugin files (ntuser, software, sam, system, security, all)
# Original Idea by Corey Harrell
# Coded by cheeky4n6monkey@gmail.com
# Note: Probably best if you do NOT run this script from the RegRipper Plugins directory
# as it could affect the *.pl plugin processing.

# Created (2012-04-02)
# Modified (2012-04-03) for handling "#" commented out lines in "Plugins" text files (Thanks Francesco!)
# Modified (2012-04-04) for handling blank lines in "Plugins" text files (Thanks Francesco!)
# Modified (2012-04-04) re-instated "No missing plugins message"

use strict;

use Getopt::Long;
use File::Spec::Functions qw/catfile/;

my $version = "regripper-maint.pl v2012-04-04";
my $help = 0;
my $plugindir = "";
my $excludefile = "";
my @pluginslist;

GetOptions('help|h' => \$help,
    'dir=s' => \$plugindir,
    'x=s' => \$excludefile,
    'p=s@' => \@pluginslist);

if ($help || @pluginslist == 0  || $plugindir eq "")
{
    print("\nHelp for $version\n\n");
    print("Perl script to list discrepancies for RegRipper Plugin files (ntuser, software, sam, system, security, all)\n");
    print("\nUsage: regripper-maint.pl [-h|help] [-dir plugins_dir] [-x exclude_files] [-p plugins_list]\n");
    print("-h|help ............ Help (print this information). Does not run anything else.\n");
    print("-dir plugins_dir ... Plugins directory.\n");
    print("-x exclude_file .... Exclude text file which lists plugins to exclude from any checks.\n");
    print("-p plugins_list .... Plugins file(s) to be checked (eg all, ntuser).\n");
    print("\nExamples: \n");
    print("regripper-maint.pl -dir /usr/local/src/regripper/plugins/ -p ntuser -p sam\n");
    print("regripper-maint.pl -dir /usr/local/src/regripper/plugins/ -x regripper-maint-exclude -p all\n\n");
    exit;
}

print "\nRunning $version\n\n";

# List of plugins derived from the default (ntuser, software, sam, system, security, all) plugins text files
my @ntuser_plugins;
my @software_plugins;
my @sam_plugins;
my @system_plugins;
my @security_plugins;
my @all_plugins;

# Directory file listing of *.pl files in user's plugins directory
my @readinplugins;
# @readinplugins list broken up by hive
my @ntuser_actual;
my @software_actual;
my @sam_actual;
my @system_actual;
my @security_actual;
my @all_actual;

my @excludelist;

# Extract entries from user nominated exclude file
if (not $excludefile eq "")
{
    open(my $xfile, "<", $excludefile) or die "Can't Open $excludefile Exclude File!";
    @excludelist = <$xfile>; # extract each line to a list element
    chomp(@excludelist); # get rid of newlines
    close($xfile);
    foreach my $ig (@excludelist)
    {
        print "Ignoring the $ig plugin for any comparisons\n";
    }
    print "\n";
}

# Read in the entries in the default Plugins text file(s)
# Plugin files have lowercase names
foreach my $plugin (@pluginslist)
{
    open(my $pluginsfile, "<", catfile($plugindir,$plugin) ) or die "Can't Open $plugin Plugins File!";
    if ($plugin =~ /ntuser/)
    {
        print "Reading the ntuser Plugins File\n";
        @ntuser_plugins = <$pluginsfile>; # extract each line to a list element
        chomp(@ntuser_plugins); # get rid of newlines
    }
    if ($plugin =~ /software/)
    {
        print "Reading the software Plugins File\n";
        @software_plugins = <$pluginsfile>;
        chomp(@software_plugins);
    }
    if ($plugin =~ /sam/)
    {
        print "Reading the sam Plugins File\n";
        @sam_plugins = <$pluginsfile>;
        chomp(@sam_plugins);
    }
    if ($plugin =~ /system/)
    {
        print "Reading the system Plugins File\n";
        @system_plugins = <$pluginsfile>;
        chomp(@system_plugins);
    }
    if ($plugin =~ /security/)
    {
        print "Reading the security Plugins File\n";
        @security_plugins = <$pluginsfile>;
        chomp(@security_plugins);
    }
    if ($plugin =~ /all/)
    {
        print "Reading the all Plugins File\n";
        @all_plugins = <$pluginsfile>;
        chomp(@all_plugins);
    }
    close $pluginsfile;
}

# This code for determining a package's hive was cut / pasted / edited from "rip.pl" lines 42-67.
# Reads in the *.pl plugin files from the plugins directory and store plugin names in hive related "actual" lists
# Note: the "all_actual" list will later be a concatenation of all of the "actual" lists populated below
opendir(DIR, $plugindir) || die "Could Not Open $plugindir: $!\n";
@readinplugins = readdir(DIR);
closedir(DIR);

foreach my $p (@readinplugins)
{
    my $hive;
    next unless ($p =~ m/\.pl$/); # gonna skip any files which don't end in .pl
    my $pkg = (split(/\./,$p,2))[0]; # extract the package name (by removing the .pl)
    $p = catfile($plugindir, $p); # catfile is used to create absolute path filename (from File::Spec::Functions)
    eval
    {
        require $p; # "require" needs to be inside an eval in order to import package functions ?
        $hive = $pkg->getHive(); # hive name should be UPPERCASE but could be mixed *sigh*
    };
    print "Error: $@\n" if ($@);

    if ($hive =~ /NTUSER/i )
    {
        push(@ntuser_actual, $pkg);
    }
    elsif ($hive =~ /SOFTWARE/i )
    {
        push(@software_actual, $pkg);
    }
    elsif ($hive =~ /SAM/i )
    {
        push(@sam_actual, $pkg);
    }
    elsif ($hive =~ /SYSTEM/i )
    {
        push(@system_actual, $pkg);
    }
    elsif ($hive =~ /SECURITY/i )
    {
        push(@security_actual, $pkg);
    }
    elsif ($hive =~ /ALL/i )
    {
        push(@all_actual, $pkg); # some .pl plugins have "All" listed as their hive
    }
}

# Calls PrintMissingPlugins to compare a Plugins text file list with an "actual" *.pl plugins list
foreach my $plugin (@pluginslist)
{
    if ($plugin =~ /ntuser/)
    {
        PrintMissingPlugins("NTUSER", "ntuser", \@ntuser_actual, \@ntuser_plugins);
    }
    if ($plugin =~ /software/)
    {
        PrintMissingPlugins("SOFTWARE", "software", \@software_actual, \@software_plugins);
    }
    if ($plugin =~ /sam/)
    {
        PrintMissingPlugins("SAM", "sam", \@sam_actual, \@sam_plugins);
    }
    if ($plugin =~ /system/)
    {
        PrintMissingPlugins("SYSTEM", "system", \@system_actual, \@system_plugins);
    }
    if ($plugin =~ /security/)
    {
        PrintMissingPlugins("SECURITY", "security", \@security_actual, \@security_plugins);
    }
    if ($plugin =~ /all/)
    {
        PrintMissingPlugins("ALL", "all", \@all_actual, \@all_plugins);
    }
}

# End Main

sub PrintMissingPlugins
{
    my $hive = shift; # hive name
    my $name = shift; # Plugins text file name
    my $actual_plugins  = shift; # reference to list of plugins derived from *.pl files
    my $listed_plugins = shift; # reference to list of plugins derived from Plugins text file

    my @missing_plugins; # list stores *.pl files which are NOT declared in a given Plugins text file
    my @missing_pl; # list stores Plugin entries which do NOT have a corresponding .pl file

    print "\nThere are ".scalar(@$actual_plugins)." $hive plugins in $plugindir\n";
    # We say "lines" because there can accidental multiple declarations *sigh*
    print "There are ".scalar(@$listed_plugins)." plugin lines listed in the $name Plugins file\n";
    print scalar(@excludelist)." plugins are being ignored\n";

    # Handle the "all" Plugin case discrepancy
    # There's a large mixture of different hive plugins listed in the "all" Plugins text file
    # and only a handful of plugins who actually return "All" as their hive.
    # At this point, @all_actual should only contain plugins which list "All" as their hive
    # In a fit of hacktacular-ness, we'll now also add the contents from the other "actual" arrays to @all_actual.
    # Otherwise, when we compare the list of "All" hive plugins (@$actual_plugins) with
    # the lines in the "all" Plugin (@$listed_plugins), there will be a lot of differences reported.
    if ($hive eq "ALL")
    {
        push(@$actual_plugins, @ntuser_actual);
        push(@$actual_plugins, @software_actual);
        push(@$actual_plugins, @sam_actual);
        push(@$actual_plugins, @system_actual);
        push(@$actual_plugins, @security_actual);
    }
    # From here on, @all_actual / @$actual_plugins will contain a list of every processed type of .pl plugin file

    # For each *.pl plugin file, check that it has a corresponding entry in the given Plugins text file
    foreach my $pkg (@$actual_plugins)
    {
        my $res = scalar(grep(/^($pkg)$/, @$listed_plugins)); # is this .pl listed in Plugins file ?
        my $ignore = scalar(grep(/^($pkg)$/, @excludelist)); # is this .pl being ignored ?
        if ( ($res eq 0) and ($ignore eq 0) )
        {
            push(@missing_plugins, $pkg);
        }
    }
    if (@missing_plugins)
    {
        print "\nThere are ".scalar(@missing_plugins)." plugins missing from the $name Plugins file:\n";
        foreach my $miss (@missing_plugins)
        {
            print $miss."\n";
        }
    }

    # For each line in the Plugins text file, check that it has a corresponding *.pl file
    foreach my $plug (@$listed_plugins)
    {
        # If this Plugins entry has been commented out (by a preceding "#") OR if it starts with a whitespace (newline),
        # skip to next entry so we don't get told there's no corresponding .pl file
        if ( ($plug =~ /^#/) or ( not $plug =~ /^\S/ ) )
        {
            next;
        }
        my $res = scalar (grep(/^($plug)$/, @$actual_plugins)); # does this Plugin entry have a corresponding .pl file ?
        my $ignore = scalar(grep(/^($plug)$/, @excludelist)); # is this Plugin entry being ignored ?
        if ( ($res eq 0) and ($ignore eq 0) )
        {
            push(@missing_pl, $plug);
        }
    }
    if (@missing_pl)
    {
        print "\nThere are ".scalar(@missing_pl)." plugins declared in the $name Plugins file with no corresponding .pl file:\n";
        foreach my $miss (@missing_pl)
        {
            print $miss."\n";
        }
    }

    if ( (@missing_plugins eq 0) and (@missing_pl eq 0) )
    {
        print "No missing plugins detected\n";
    }

}

# CODE ENDS HERE

Code Summary

Here's a high level overview of the code:
- Parses command line arguments using "GetOptions"
- Prints out Help message (if required)
- Extract entries from user nominated exclude text file (if one is specified)
- Read in the entries in the specified "Plugins" text file(s)
- Reads in the *.pl plugin names from the plugins directory and store them in hive related "actual" lists
- Calls "PrintMissingPlugins" subroutine to compare the differences between a "Plugins" text file list with an "actual" *.pl plugins list. Function also allows for commented out lines (eg "# COMMENT") and blank lines in "Plugin" files.

If you have RegRipper installed, you should already have the necessary Perl packages installed (eg File::Spec::Functions).

It's not the most concise/efficient code. If in doubt, I tried to make it more readable (at least for me!). I also made more comments in the code so I wouldn't have to write a lot in this section. I think that might prove more convenient than scrolling up/down between the summary and the code.

Finally, I should mention that I kept my "regripper-maint.pl" script (and my exclusion text file "regripper-maint-exclude") in "/home/sansforensics/". I didn't want the script to parse itself when looking for .pl plugin files. I suspect I could have just as easily called "regripper-maint.pl" from "/usr/local/src/regripper/". Meh.

Testing

We'll start testing (on SIFT v2.12 using the default RegRipper install) with the 2 examples given in the script's Help message.


sansforensics@SIFT-Workstation:~$ ./regripper-maint.pl -dir /usr/local/src/regripper/plugins/ -p ntuser -p sam

Running regripper-maint.pl v2012-04-04

Reading the ntuser Plugins File
Reading the sam Plugins File

There are 98 NTUSER plugins in /usr/local/src/regripper/plugins/
There are 97 plugin lines listed in the ntuser Plugins file
0 plugins are being ignored

There are 1 plugins missing from the ntuser Plugins file:
ccleaner

There are 1 SAM plugins in /usr/local/src/regripper/plugins/
There are 1 plugin lines listed in the sam Plugins file
0 plugins are being ignored
No missing plugins detected
sansforensics@SIFT-Workstation:~$

Looks OK! I have not added my "ccleaner.pl" script to the "ntuser" Plugins file so the results make sense. We can check the number of lines in the "ntuser" Plugins file using the following:

sansforensics@SIFT-Workstation:~$ wc -l /usr/local/src/regripper/plugins/ntuser
97 /usr/local/src/regripper/plugins/ntuser
sansforensics@SIFT-Workstation:~$

Just as reported, there's 97 lines in the "ntuser" Plugins file. As for the SAM results:

sansforensics@SIFT-Workstation:~$ wc -l /usr/local/src/regripper/plugins/sam
1 /usr/local/src/regripper/plugins/sam
sansforensics@SIFT-Workstation:~$

Which seems OK as there's only one "sam" Plugins entry ("samparse").
Now let's try the second help example ...

sansforensics@SIFT-Workstation:~$ ./regripper-maint.pl -dir /usr/local/src/regripper/plugins/ -x regripper-maint-exclude -p all

Running regripper-maint.pl v2012-04-04

Ignoring the ccleaner plugin for any comparisons

Reading the all Plugins File

There are 3 ALL plugins in /usr/local/src/regripper/plugins/
There are 204 plugin lines listed in the all Plugins file
1 plugins are being ignored

There are 2 plugins missing from the all Plugins file:
winlivemail
winlivemsn

There are 2 plugins declared in the all Plugins file with no corresponding .pl file:
port_dev
wlm_cu
sansforensics@SIFT-Workstation:~$

So this example shows the script referring to our "regripper-maint-exclude" exclusion file (which has one line containing "ccleaner"). Hence, it ignores any "ccleaner" plugin comparisons.
We can also see that while only 3 "ALL" *.pl plugins were found, there are 204 lines declared in the "all" Plugins file. The "all" Plugins file is a special case in that it can contain plugins which refer to more than one type of hive. See the code comments for more info on how I dealt with this (it ain't particularly pretty).
Anyhoo, we can also see that there are 2 .pl plugins which are NOT declared in the "all" Plugins file. Open the "all" file and verify it for yourself - there is no "winlivemail" or "winlivemsn" entry.
There are also 2 entries in the "all" Plugins file which don't have corresponding .pl files (ie "port_dev.pl" and "wlm_cu.pl" do not exist). That's gonna make it a bit hard to call those plugins eh?

And here is the same example test except WITHOUT using the "regripper-maint-exclude" exception file:

sansforensics@SIFT-Workstation:~$ ./regripper-maint.pl -dir /usr/local/src/regripper/plugins/  -p all

Running regripper-maint.pl v2012-04-04

Reading the all Plugins File

There are 3 ALL plugins in /usr/local/src/regripper/plugins/
There are 204 plugin lines listed in the all Plugins file
0 plugins are being ignored

There are 3 plugins missing from the all Plugins file:
winlivemail
winlivemsn
ccleaner

There are 2 plugins declared in the all Plugins file with no corresponding .pl file:
port_dev
wlm_cu
sansforensics@SIFT-Workstation:~$

You can see that the "ccleaner" plugin is now included in the missing plugins.
Just to prove this isn't all smoke and mirrors, here are the results for all 6 hives (with no exclusions):

sansforensics@SIFT-Workstation:~$ ./regripper-maint.pl -dir /usr/local/src/regripper/plugins/ -p ntuser -p software -p sam -p system -p security -p all

Running regripper-maint.pl v2012-04-04

Reading the ntuser Plugins File
Reading the software Plugins File
Reading the sam Plugins File
Reading the system Plugins File
Reading the security Plugins File
Reading the all Plugins File

There are 98 NTUSER plugins in /usr/local/src/regripper/plugins/
There are 97 plugin lines listed in the ntuser Plugins file
0 plugins are being ignored

There are 1 plugins missing from the ntuser Plugins file:
ccleaner

There are 54 SOFTWARE plugins in /usr/local/src/regripper/plugins/
There are 54 plugin lines listed in the software Plugins file
0 plugins are being ignored
No missing plugins detected

There are 1 SAM plugins in /usr/local/src/regripper/plugins/
There are 1 plugin lines listed in the sam Plugins file
0 plugins are being ignored
No missing plugins detected

There are 44 SYSTEM plugins in /usr/local/src/regripper/plugins/
There are 44 plugin lines listed in the system Plugins file
0 plugins are being ignored
No missing plugins detected

There are 3 SECURITY plugins in /usr/local/src/regripper/plugins/
There are 3 plugin lines listed in the security Plugins file
0 plugins are being ignored
No missing plugins detected

There are 3 ALL plugins in /usr/local/src/regripper/plugins/
There are 204 plugin lines listed in the all Plugins file
0 plugins are being ignored

There are 3 plugins missing from the all Plugins file:
winlivemail
winlivemsn
ccleaner

There are 2 plugins declared in the all Plugins file with no corresponding .pl file:
port_dev
wlm_cu
sansforensics@SIFT-Workstation:~$

I subsequently viewed the relevant Plugin files and confirmed the number of plugin lines declared matched what was printed above. I could also have used the "wc -l" trick mentioned previously as well. Meh.

Summary

Using Corey Harrell's idea, we've coded a RegRipper maintenance script which can detect discrepancies between whats declared in Plugin text files and what .pl plugins actually exist.
While this script doesn't help you if you don't use the Plugin text files, it has still been an interesting programming exercise. I can feel my Perl-fu growing stronger by the day ... muhahaha!

And for those that don't know, Cheeky4n6Monkey is now (intermittently) on Twitter (@Cheeky4n6Monkey) ... and co-incidentally(?) getting a lot less work done! Thankyou to all those who have already extended their welcome.

As always, please leave a comment if you found this post helpful / interesting / snake oil. PS I will send a copy of this script to the RegRipper folks just in case they find it useful (Thanks Brett and Francesco!).

Using Perl to Copy AppID Data from HTML to an SQLite Database

Someday You'll Find It ... The Jumplist Connection!

So all this talk of Windows 7 Jumplists (eg here and here) got me thinking - What if you needed to look up an AppID and didn't have access to the Internet? Also, Is there any way we can import a list of AppIDs so we can then access them from some future scripts (eg for processing Jumplists)?
I then had my "Aha!" moment (no, nothing to do with that 80s band), and thought "SQLite!"
SQLite also has the benefit of being cross-platform - so an AppID List generated on SIFT should work on a PC etc. By using a standard database, we can also ensure that our fearless forensicator lab uses a common set of (unchanging) source data.

So how exactly can we do this (using the SIFT VM)?
1. We create an empty SQLite Database.
2. We parse the ForensicsWiki AppID HTML page for data.
3. We populate the Database




Creating The Empty Database

Creating an (empty) SQLite AppID Database is pretty straight forward. I opted to do this part manually using SIFT's "sqlite3" command line tool.

1. At a terminal command line, I typed:
"sqlite3 WinAppIDs.sqlite"
2. At the resultant "sqlite3" prompt, I typed (without the quotes):
"CREATE TABLE IF NOT EXISTS WinAppIDs (AppID TEXT, AppDescrip TEXT, DateAdded NUMERIC, Source TEXT);" 
3. At the next "sqlite3" prompt, I typed (without the quotes):
"CREATE UNIQUE INDEX IF NOT EXISTS AppIdx ON WinAppIDs(AppID, AppDescrip);"
4. Finally, at the "sqlite3" prompt, I typed (without the quotes):
".quit"

There should now be a "WinAppIDs.sqlite" database file in the current directory (eg "/home/sansforensics"). This file contains a "WinAppIDs" table which has AppId, AppDescription, DateAdded and Source fields (just like the ForensicsWiki page tables).
Additionally, I have hacked/cheated a bit and created a Unique Index called "AppIdx". The purpose of the index is to ensure that we cannot insert duplicate entries. That is, we can't insert an entry with the same AppID AND the same AppDescription as an existing one. You wouldn't think this would be a problem with the AppId tables right? Wrong! In the tables there are entries such as:

Different AppIds, Same Descriptions
b8c29862d9f95832    Microsoft Office InfoPath 2010 x86
d64d36b238c843a3    Microsoft Office InfoPath 2010 x86

Same AppIDs, Different Descriptions
23646679aaccfae0    Adobe Reader 9.
23646679aaccfae0    Adobe Reader 9 x64

So rather than calling a SELECT query to check before every INSERT (there's 400+ AppIDs to insert), we'll just make the INSERT fail (via "AppIdx") when there's already an existing entry with the same AppID and AppDescription. For more on SQLite indexing read here. Specifically it says, "If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. Any attempt to insert a duplicate entry will result in an error."


Parsing the ForensicsWiki AppID HTML page for data and Populating the Database

Parsing/extracting the AppIDs from HTML is easy once you find out somebody's already written a CPAN package for that exact purpose! Its called "HTML::TableExtract" and you can read all about it here.
Additionally, the folks at ForensicsWiki have also included a "Source" link for each AppID. To extract the link we can use "HTML::LinkExtor". You can read about it here.
As we have seen in previous posts, we can use the Perl "DBI" package to interface with an SQLite Database. So knowing all this, we should now be able to insert our newly extracted data.
To simplify this exercise, we will extract the data and then enter it into the database from one script ("4n6wiki-parser.pl").
Incidentally, the StackOverflow website is a great searchable resource for Perl questions - this was where I not only heard about the previously mentioned packages but also saw example code of their use. Awesome!

Finally(!), we should write a short query script ("4n6wiki-query.pl") so that we can search our SQLite database by either AppID or AppDescrip.

Now let's take a look at what this monkey passes off for code ...

"4n6wiki-parser.pl" Code

#CODE BEGINS ON NEXT LINE
#!/usr/bin/perl -w

# Perl script to parse http://www.forensicswiki.org/wiki/List_of_Jump_List_IDs and import it into an existing SQLite Database

use strict;

use Getopt::Long;
use HTML::TableExtract;
use HTML::LinkExtor;
use DBI;

my $version = "4n6wiki-parser.pl v2012-03-27";
my $help = 0;
my $filename = "";
my $database = "";

GetOptions('help|h' => \$help,
    'file=s' => \$filename,
    'db=s' => \$database);

if ($help || $filename eq "" || $database eq "")
{
    print("\nHelp for $version\n\n");
    print("Perl script to parse http://www.forensicswiki.org/wiki/List_of_Jump_List_IDs and import it into an existing SQLite Database\n");
    print("\nUsage: 4n6wiki-parser.pl [-h|help] [-file filename] [-db database]\n");
    print("-h|help .......... Help (print this information). Does not run anything else.\n");
    print("-file filename ... Copy of HTML file to be parsed.\n");
    print("-db database ..... Target SQLite Database.\n");
    print("\nExample: 4n6wiki-parser.pl -file List_of_Jump_List_IDs.html -db WinAppIDs.sqlite\n");
    exit;
}

print "\nRunning $version\n\n";

open(my $htmlfile, "<".$filename) || die("Unable to open $filename for parsing\n");
my @lines = <$htmlfile>; # extract each line to a list element
my $html_string = join(' ', @lines); # join list elements into one big happy scalar string
close($htmlfile);

my $tblextract = HTML::TableExtract->new(keep_html => 1, headers => [qw(AppID Application Date Source)] );
$tblextract->parse($html_string);

my $lnkextr = HTML::LinkExtor->new();

my @bigarray; # will be a all table cells merged into one [row][cell] type array
my $rowcount = 0; # running count of rows in all tables

foreach my $tbl ($tblextract->tables)
{
    my @rows = $tbl->rows;
    my @cols = $tbl->columns;
    print "Extracted Table Size = ".scalar(@rows)." x ".scalar(@cols)."\n";

    for my $rownum (0 .. (@rows - 1))
    {
        # Ass-ume always 4 columns, last one (3) contains link info, col 2 contains date eg 8/22/2011
        for my $colnum (0 .. (@cols - 1))
        {
            if ($colnum == (@cols - 2) )
            {
                #reformat date into form yyyy-mm-dd
                my $date = $tbl->cell($rownum, $colnum);
                $date =~ /(\d+)\/(\d+)\/(\d+)/;
                my $year = $3;
                my $month = $1;
                my $day = $2;
                if ($day < 10)
                {
                    $day = "0".$day;
                }
                if ($month < 10)
                {
                    $month = "0".$month;
                }
                my $newdate = $year."-".$month."-".$day;
                $bigarray[$rowcount][$colnum] = $newdate;
            }
            elsif ($colnum == (@cols - 1))
            {       
                # Extract a link entry eg "http://social.technet.microsoft.com/Forums/" for col 3
                $lnkextr->parse($tbl->cell($rownum, $colnum));
                for my $link_tag ( $lnkextr->links )
                {
                    my ($tag, %links) = @$link_tag;
                    foreach my $key (keys %links)
                    {
                        $bigarray[$rowcount][$colnum] = $links{$key};
                        last; # only do it for one link then bail out
                    }
                }
            }
            else
            {
                #Record each of the other column fields for this row ie col 0, 1, 2
                $bigarray[$rowcount][$colnum] = $tbl->cell($rownum, $colnum);
            }
        }
        $rowcount = $rowcount + 1;       
    }
}

print "Number of Rows Extracted from HTML = $rowcount\n";

my $db = DBI->connect("dbi:SQLite:dbname=$database","","") || die( "Unable to connect to database\n" );
my $sth;

for my $currow (0 .. ($rowcount-1))
{
    my $id = $bigarray[$currow][0];
    my $descrip = $bigarray[$currow][1];
    my $date = $bigarray[$currow][2];
    my $source = $bigarray[$currow][3];
    $sth =  $db->prepare_cached("INSERT INTO WinAppIDs (AppID, AppDescrip, DateAdded, Source) VALUES (?, ?, ?, ?)"); #or die "Couldn't prepare statement: ".$db->errstr;
    $sth->execute($id, $descrip, $date, $source); #or die "Couldn't execute statement: ".$sth->errstr;
}
$sth->finish;
$db->disconnect;
print "$database Update complete ...\n"

# END CODE

"4n6wiki-parser.pl" Code Summary

Before you use this script, ensure that you have installed the following Perl packages: HTML::TableExtract, HTML::LinkExtor, DBI (eg type "sudo cpan HTML::TableExtract" etc.).

The first section calls "GetOptions" to parse the user's input arguments and then the following section is the Help printout.

We then read in the user specified HTML file into one long scalar variable ("$html_string").

Next we create a new HTML::TableExtract object to parse the table data. We use the argument "keep_html => 1" so we can later parse any web links in the table (using HTML::LinkExtor). The other argument, "headers => [qw(AppID Application Date Source)]" tells HTML::TableExtract the column headings so it can figure out how to extract data.
Note: I just used the first word of each column heading and it seemed to work OK. I don't think you can specify something like "Application Description" for a heading because the whitespace will confuse HTML::TableExtract. If they ever change the column headings to use the same first word (eg "AppID" and "AppID Description"), we're in trouble.
Anyhow, next we call the "parse" function of HTML::TableExtract to do all the heavy lifting and extract the data.

Now we create a new HTML::LinkExtor object to extract any HTML links in the table. We then proceed to loop through each row/column of each table that HTML::TableExtract has found (currently, there are 10 tables). We store the result in a huge array of arrays ("bigarray[rownum][colnum]").
For half of the columns (ie "AppId" and "Application Description"), we will just copy the data straight into "bigarray". The other 2 columns ("Date Added" and "Source") require a bit of massaging before entry (heheh).
ForensicsWiki lists the dates in the U.S. format of month/day/year (I DARE GU to say it's a better format! LOL) with no preceding zeros for single digits. SQLite uses the yyyy-mm-dd format in it's time calculations. So, just in case we want to sort later by date, our script has to re-arrange the date before storing it in "bigarray". We use a regular expression to extract the various parts of the date and then zero pad them if required before re-assembling them into the SQLite format. The regular expression code is:

"$date =~ /(\d+)\/(\d+)\/(\d+)/;"

 In the line before this, "$date" was assigned the value from the date cell (eg "8/22/2011"). The "\d+"s indicate a field of multiple digits. The surrounding "( )"s indicate we are extracting the various values (in order) to $1, $2, $3. We also need to "\" the date separators "/" so they don't confuse the regular expression delimiters. More information on Perl Regular expressions is available here.

I found the basic code for the Link Extraction here. However, we call the HTML::LinkExtor's "parse" function with the table cell content (not the whole row). I found it quite difficult to initially understand this snippet, so I will try to explain using our actual code below.

$lnkextr->parse($tbl->cell($rownum, $colnum));
for my $link_tag ( $lnkextr->links )
{
    my ($tag, %links) = @$link_tag;
    foreach my $key (keys %links)
    {
        $bigarray[$rowcount][$colnum] = $links{$key};
        last; # only do it for one link then bail out
    }
}

To get the results of the HTML::LinkExtor's "parse" call, we have to call the "$lnkextr->links" method. According to the CPAN documentation, this returns data in the form:
"[$tag, $attr => $url1, $attr2 => $url2,...]".
The enclosing "[ ]"s denote an array containing the "$tag" scalar variable and what looks like a hash list ("$attr => $url1, $attr =>$url2").
Theoretically, there should only be one "$link_tag" result for our case and therefore, only one for ("my $link_tag") loop iteration.

In the loop, we now see: "my ($tag, %links) = @$link_tag;"
The right hand hand side, "@$link_tag" is saying treat this "$link_tag" scalar as a reference to an array (hence the "@"). This array will be the one containing the "$tag" and the hash list mentioned previously.
The left hand side assigns values for the various parts of the array. So we end up with "$tag" and a hash list array (now called "%links").
The next step is to iterate through "%links" and for the first key/URL it finds, store that link in "bigarrray".
For more information on Perl References see here.

OK, now we should have all the data we need in "bigarray" and we also have an SQLite database file ("WinAppIDs.sqlite") containing an empty table ("WinAppIDs"). Time to feed the machine!
As we've seen in previous posts, we first call DBI's "connect" to interface with the Database. If it doesn't work we quit and nothing gets inserted.
Next we loop through each "row" of "bigarray" and store the column values in temporary scalars ($id, $descrip, $date, $source). We then prepare our SQLite INSERT statement:

$sth =  $db->prepare_cached("INSERT INTO WinAppIDs (AppID, AppDescrip, DateAdded, Source) VALUES (?, ?, ?, ?)"); #or die "Couldn't prepare statement: ".$db->errstr;

We're using "prepare_cached" because it apparently minimises resources for our repeated INSERTs (seriously?!) compared to a normal "prepare".
The "?" arguments are placeholders for our temporary values which get assigned in the next line.

$sth->execute($id, $descrip, $date, $source); #or die "Couldn't execute statement: ".$sth->errstr;

Note: I've commented out the ("die") error handling parts for "prepare_cached" and "execute" because if we fail to INSERT, it could be due to a duplicate. We should keep looping though "bigarray" because there might be new non-duplicate information later in "bigarray" (eg we might be updating an existing database).

After we've finished looping through "bigarray", we call "finish" and "close" and print out an "Update Complete" message.


"4n6wiki-query.pl" Code

# CODE BEGINS ON NEXT LINE
#!/usr/bin/perl -w

# Perl script to query an existing SQLite Database for Windows AppID info
# Intended to be used in conjunction with the 4n6wiki-parser.pl script.

use strict;

use Getopt::Long;
use DBI;

my $version = "4n6wiki-query.pl v2012-03-27";
my $help = 0;
my $appid = "";
my $descrip = "";
my $database = "";

GetOptions('help|h' => \$help,
    'appid=s' => \$appid,
    'descrip=s' => \$descrip,
    'db=s' => \$database);

if ($help || $database eq "" || ($descrip eq "" && $appid eq "") || ($descrip ne "" && $appid ne "") )
{
    print("\nHelp for $version\n\n");
    print("Perl script to query an existing SQLite Database for Windows AppID info\n");
    print("Intended to be used in conjunction with the 4n6wiki-parser.pl script.\n"); 
    print("\nUsage: 4n6wiki-query.pl [-h|help] [-appid APPID] [-descrip Description] [-db database]\n");
    print("-h|help .......... Help (print this information). Does not run anything else.\n");
    print("-appid APPID ..... Search Database for match(es) for this APPID.\n");
    print("-descrip Description ... Search Database for match(es) \"like\" this Description.\n");
    print("-db database ..... Target SQLite Database.\n");
    print("\nExamples: \n");
    print("4n6wiki-parser.pl -appid 3dc02b55e44d6697 -db WinAppIDs.sqlite\n");
    print("4n6wiki-parser.pl -descrip \"Adobe Flash\" -db WinAppIDs.sqlite\n");
    print("Note: Listing BOTH -appid and -descrip will not work / prints this message\n\n");
    exit;
}

print "\nRunning $version\n\n";

my $db = DBI->connect("dbi:SQLite:dbname=$database","","") || die( "Unable to connect to database\n" );
my $sth;
if ($appid ne "")
{
    $sth =  $db->prepare("SELECT AppID, AppDescrip FROM WinAppIDs WHERE AppID=?") or die "Couldn't prepare statement: ".$db->errstr;
    $sth->execute($appid) or die "Couldn't execute statement: ".$sth->errstr;
    PrintHeadings($sth);
    PrintResults($sth);
    if ($sth->rows == 0)
    {
        print "No Matching AppIDs found!\n";
    }
    else
    {   
        print $sth->rows." Matches returned\n";
    }
}
elsif ($descrip ne "")
{
    my $likestr = "%".$descrip."%";
    $sth =  $db->prepare("SELECT AppID, AppDescrip FROM WinAppIDs WHERE AppDescrip LIKE ?") or die "Couldn't prepare statement: ".$db->errstr;
    $sth->execute($likestr) or die "Couldn't execute statement: ".$sth->errstr;
    PrintHeadings($sth);
    PrintResults($sth);
    if ($sth->rows == 0)
    {
        print "No Matching Descriptions found!\n";
    }
    else
    {   
        print $sth->rows." Matches returned\n";
    }
}

$sth->finish;
$db->disconnect;

# End Main

sub PrintHeadings
{
    my $sth = shift;

    # Print field headings
    for (my $i = 0; $i <= $sth->{NUM_OF_FIELDS}-1; $i++)
    {
        if ($i == $sth->{NUM_OF_FIELDS} - 1)
        {
            print $sth->{NAME}->[$i]."\n"; #last item adds a newline char
        }
        else
        {   
            print $sth->{NAME}->[$i]." | ";
        }
    }
}

sub PrintResults
{
    my $sth = shift;
    my @rowarray;

    # Prints row by row / field by field
    while (@rowarray = $sth->fetchrow_array() )
    {
        for (my $i = 0; $i <= $sth->{NUM_OF_FIELDS}-1; $i++)
        {
            if ($i == $sth->{NUM_OF_FIELDS} - 1 )
            {
                print $rowarray[$i]."\n"; #last field in row adds newline
            }
            else
            {
                if ($rowarray[$i])
                {
                    print $rowarray[$i]." | ";
                }
                else
                {
                    print " | "; # field returned could be UNDEFINED, just print separator
                }
            }
        }
    }
}

# CODE ENDS

"4n6wiki-query.pl" Code Summary

This script is very similar to our previous "ffparser.pl" script. It will use DBI to run SELECT queries against a user nominated SQLite Database. There will be 2 types of queries - a search by AppID and a search for LIKE terms in AppDescrip. We won't use an exact match for AppDescrip because the analyst might not know the exact description (eg they just know it's for CCleaner).
As usual, it starts off with "GetOptions" and the Help printout sections.
We then call DBI's "connect" and one of the versions of "prepare" (depending on the user's input arguments).
Next we call "PrintHeadings" and "PrintResults" (we cut and pasted these functions from "ffparser.pl").
Finally, we call "finish" and "disconnect".

Testing

To test our scripts we will save a copy of the current ForensicsWiki AppID page  as "List_of_Jump_List_IDs.html". We will then edit/remove the entry for the "Win32 cmd.exe" in the last row of the last table and re-save it as "List_of_Jump_List_IDs-withoutCMD.html".
Now we can simulate an update by first running "4n6wiki-parser.pl" with "List_of_Jump_List_IDs-withoutCMD.html" and then again with "List_of_Jump_List_IDs.html". After our first run, our SQLite Database (created previously) should not contain our "Win32 cmd.exe" entry and then after the second run, it should contain our "Win32 cmd.exe" entry.

OK, here we run "4n6wiki-parser.pl" with "List_of_Jump_List_IDs-withoutCMD.html". (ie no "Win32 cmd.exe" entry)

sansforensics@SIFT-Workstation:~$ ./4n6wiki-parser.pl -file List_of_Jump_List_IDs-withoutCMD.html -db WinAppIDs.sqlite


Running 4n6wiki-parser.pl v2012-03-27


Extracted Table Size = 53 x 4
Extracted Table Size = 31 x 4
Extracted Table Size = 10 x 4
Extracted Table Size = 87 x 4
Extracted Table Size = 50 x 4
Extracted Table Size = 16 x 4
Extracted Table Size = 66 x 4
Extracted Table Size = 30 x 4
Extracted Table Size = 2 x 4
Extracted Table Size = 55 x 4
Extracted Table Size = 7 x 4
Number of Rows Extracted from HTML = 407
WinAppIDs.sqlite Update complete ...
sansforensics@SIFT-Workstation:~$


OK now we run a search for the "cmd" term in our "WinAppIDs.sqlite" database.

sansforensics@SIFT-Workstation:~$ ./4n6wiki-query.pl -descrip "cmd" -db WinAppIDs.sqlite


Running 4n6wiki-query.pl v2012-03-27


AppID | AppDescrip
6728dd69a3088f97 | Windows Command Processor - cmd.exe (64-bit)
1 Matches returned
sansforensics@SIFT-Workstation:~$

As expected, it only returns the value for the 64bit cmd.exe.
Now let's run "4n6wiki-parser.pl" with "List_of_Jump_List_IDs.html" (ie includes "Win32 cmd.exe" entry).

sansforensics@SIFT-Workstation:~$ ./4n6wiki-parser.pl -file List_of_Jump_List_IDs.html -db WinAppIDs.sqlite


Running 4n6wiki-parser.pl v2012-03-27


Extracted Table Size = 53 x 4
Extracted Table Size = 31 x 4
Extracted Table Size = 10 x 4
Extracted Table Size = 87 x 4
Extracted Table Size = 50 x 4
Extracted Table Size = 16 x 4
Extracted Table Size = 66 x 4
Extracted Table Size = 30 x 4
Extracted Table Size = 2 x 4
Extracted Table Size = 55 x 4
Extracted Table Size = 8 x 4
Number of Rows Extracted from HTML = 408
DBD::SQLite::st execute failed: columns AppID, AppDescrip are not unique at ./4n6wiki-parser.pl line 114.
DBD::SQLite::st execute failed: columns AppID, AppDescrip are not unique at ./4n6wiki-parser.pl line 114.
DBD::SQLite::st execute failed: columns AppID, AppDescrip are not unique at ./4n6wiki-parser.pl line 114.
[... Lines Edited Out]
DBD::SQLite::st execute failed: columns AppID, AppDescrip are not unique at ./4n6wiki-parser.pl line 114.
WinAppIDs.sqlite Update complete ...
sansforensics@SIFT-Workstation:~$

OK so we are getting some errors about duplicate entries but the last table seems to have an extra entry 8 x 4 (not 7 x 4). This looks promising!
We'll run a query and check ...

sansforensics@SIFT-Workstation:~$ ./4n6wiki-query.pl -descrip "cmd" -db WinAppIDs.sqlite


Running 4n6wiki-query.pl v2012-03-27


AppID | AppDescrip
6728dd69a3088f97 | Windows Command Processor - cmd.exe (64-bit)
bc0c37e84e063727 | Windows Command Processor - cmd.exe (32-bit)
2 Matches returned
sansforensics@SIFT-Workstation:~$


YAY! The update worked!

Let's run a quick query using the bc0c37e84e063727 AppId (for "Win32 cmd.exe") and see what results we get:

sansforensics@SIFT-Workstation:~$ ./4n6wiki-query.pl -appid bc0c37e84e063727 -db WinAppIDs.sqlite

Running 4n6wiki-query.pl v2012-03-27

AppID | AppDescrip
bc0c37e84e063727 | Windows Command Processor - cmd.exe (32-bit)
1 Matches returned
sansforensics@SIFT-Workstation:~$

OK we found our latest entry.

Just for fun, let's check how many entries we have via "sqlite3".

sansforensics@SIFT-Workstation:~$ sqlite3 WinAppIDs.sqlite
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> select count (AppId) from WinAppIDs;
408
sqlite>

Now let's see if that corresponds with the SQLite Manager Firefox plugin.

Opening our Database with the "SQLite Manager" Firefox Plugin

Note(1): The HTML links have been extracted into the table Source column.
Note(2): There are 408 records reported ie the same as what our previous "sqlite3" query reported. The latest record is our "Win32 cmd.exe" record.

Our last check (with "sqlite3") is to see if  our database's DateAdded field is usable by SQLite (we'll only grab the first 3 records). So here's those records with our inputted date format:

sqlite> select AppId, AppDescrip, DateAdded from WinAppIDs limit 3;
65009083bfa6a094|(app launched via XPMode)|2011-08-22
469e4a7982cea4d4|? (.job)|2011-08-22
b0459de4674aab56|(.vmcx)|2011-08-22
sqlite>

And here's the same results with the date field transformed into "number of days since now":

sqlite> select AppId, AppDescrip, julianday('now') - julianday(DateAdded) from WinAppIDs limit 3;
65009083bfa6a094|(app launched via XPMode)|220.419525995385
469e4a7982cea4d4|? (.job)|220.419525995385
b0459de4674aab56|(.vmcx)|220.419525995385
sqlite>

So we can see from 22 August 2011 until (now) 29 March 2012 has been calculated as ~220 days.
A manual check: 9 remaining days of August + 30 Sept days + 31 Oct days + 30 Nov days + 31 Dec days + 31 Jan days + 29 Feb days + 29 Mar days = 220
Coolio! Our date field is usable by SQLite.

Summary

OK so everything seems to work. We have extracted various fields from the ForensicsWiki AppId web page and then entered that data into an SQLite Database. We have then successfully queried that database for some information.
The code from "4n6wiki-query.pl" would be easy to cut and paste into existing scripts that currently only output the AppID thus possibly saving analysts some time.
I'm not sure if this will be of use to any forensicators but it was interesting from a Perl programming perspective. If you think you might find it useful, please leave a comment.

Perl Parsing an SQLite Database File Header

Previously on Cheeky4n6Monkey ... we used the Perl DBI package to retrieve the contents of Firefox's (v3.5.17 and v11.0) "places.sqlite" and "downloads.sqlite". A secondary discovery was that depending on the version of the sqlite3 command line exe, one might be able to use it to read Firefox 11 ".sqlite" files ... or not. Just to clarify, sqlite3 v3.6.16 cannot read Firefox 11 files but sqlite3 (v3.7.10) can.
Anyway, rather than waste time trying different versions of sqlite3 on various ".sqlite" files, I thought a Perl script to parse the ".sqlite" file header might be handy.
Even if it's not particularly unique/useful (there's already an SQLite Manager Firefox plugin), at least we get more practice with Perl. Also, as far as I know, SQLite Manager does not show the SQLite Version nor does it show a meaningful Schema Format string.

The relevant SQLite file structure is detailed here. Rather than extract all of the fields, I thought I would initially concentrate on 6 fields:
- SQLite Version eg 3007007
- SQLite Schema Format eg SQLite v3.0.0
- Page Size (in bytes)
- Number of Pages
- Number of Pages on Freelist
- Text Encoding used eg UTF-8

The last 4 fields can be verified/viewed using SQLite Manager and the first 2 fields can be verified/viewed using the SIFT's Bless Hex Editor.

So lets get stuck into the coding!


Code

# CODE BEGINS AFTER THIS LINE
#!/usr/bin/perl -w

# Perl script to parse selected SQLite Database header fields
# Based on the Database Header section of http://www.sqlite.org/fileformat2.html

use strict;

use Getopt::Long;
use Encode;

my $version = "sqlite-parser.pl v2012-03-21";
my $help = 0;
my $filename = "";

GetOptions('help|h' => \$help,
    'file=s' => \$filename);

if ($help || $filename eq "")
{
    print("\nHelp for $version\n\n");
    print("Perl script to parse selected SQLite header fields\n");
    print("\nUsage: sqlite-parser.pl [-h|help] [-file filename]\n");
    print("-h|help .......... Help (print this information). Does not run anything else.\n");
    print("-file filename ... sqlite filename to be parsed.\n");
    print("\nExample: sqlite-parser.pl -file /cases/firefox/places.sqlite\n");
    exit;
}

print "\nRunning $version\n\n";

# Try read-only opening the SQLite file to extract various header information
my $rawsqliteheader;

open(my $sqlitefile, "<".$filename) || die("Unable to open $filename for header parsing\n");
#binmode($sqlitefile);
sysread ($sqlitefile, $rawsqliteheader, 100) || die("Unable to read $filename for header parsing\n");;
close($sqlitefile);

# First check that we have a valid header 1st 16 bytes should read "SQLite format 3\000" in UTF8
# or alternatively "53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00" in hex
my $rawsqlitestring = substr($rawsqliteheader, 0, 16);
my $sqlitestring = decode("UTF8", $rawsqlitestring);
if ($sqlitestring eq "SQLite format 3\000")
{
    print "SQLite String Parsed OK - Continuing Processing of $filename ...\n";
}
else
{
    print "$filename does NOT have a Valid SQLite String in Header! Bailing out ...\n\n";
    exit;
}


# Extract the database page size in bytes
# Should/Must be a power of two between 512 and 32768 inclusive, or the value 1 representing a page size of 65536
my $rawdbpagesize = substr($rawsqliteheader, 16, 2);
my $dbpagesize = unpack("n", $rawdbpagesize); # Use "n" for 2 byte int
if ($dbpagesize eq 1)
{
    print("Database Page Size (bytes) = 65536\n");
}
else
{
    print("Database Page Size (bytes) = $dbpagesize\n");
}


# Extract the size of the database file in pages.
my $rawnumpages = substr($rawsqliteheader, 28, 4);
my $numpages = unpack("N", $rawnumpages); # use "N" for 4 byte int
if ($numpages ne 0)
{
    # Must check that changecounter = validversionfor
    # as validversionfor stores the current changecounter value after the SQLite version number was written
    # (eg at creation)
    my $rawchangecounter = substr($rawsqliteheader, 24, 4);
    my $changecounter = unpack("N", $rawchangecounter);

    my $rawvalidversionfor = substr($rawsqliteheader, 92, 4);
    my $validversionfor = unpack("N", $rawvalidversionfor);

#    print "changecounter = $changecounter\n";
#    print "validversionfor = $validversionfor\n";

    if ($changecounter eq $validversionfor)
    {
        print("Valid Number of Pages = $numpages\n");
    }
    else
    {
        print("Invalid Number of Pages! (mismatched changecounter value)\n");
    }
}
else
{
    print("Invalid Number of Pages! (zero)\n");
}


# Extract the total number of freelist pages.
my $rawnumfreelistpages = substr($rawsqliteheader, 36, 4);
my $numfreelistpages = unpack("N", $rawnumfreelistpages); # use "N" for 4 byte int
print("Total Number of Freelist Pages = $numfreelistpages\n");


# Extract the schema format number. Supported schema formats are 1, 2, 3, and 4.
my $rawschemaformatnum = substr($rawsqliteheader, 44, 4);
my $schemaformatnum = unpack("N", $rawschemaformatnum); # use "N" for 4 byte int
#print("Schema Format Number = $schemaformatnum\n");
if ($schemaformatnum == 1)
{
    print("Schema Format = SQLite v3.0.0\n");
}
elsif ($schemaformatnum == 2)
{
    print("Schema Format = SQLite v3.1.3 (2005)\n");
}
elsif ($schemaformatnum == 3)
{
    print("Schema Format = SQLite v3.1.4 (2005)\n");
}
elsif ($schemaformatnum == 4)
{
    print("Schema Format = SQLite v3.3.0 (2006) or higher\n");
}
else
{
    print("Invalid Schema Format!\n");
}


# Extract the database text encoding. A value of 1 means UTF-8. A value of 2 means UTF-16le. A value of 3 means UTF-16be.
my $rawtextencode = substr($rawsqliteheader, 56, 4);
my $textencode = unpack("N", $rawtextencode); # use "N" for 4 byte int

#print("Text Encoding = $textencode\n");
if ($textencode == 1)
{
    print("Text Encoding = UTF-8\n");
}
elsif ($textencode == 2)
{
    print("Text Encoding = UTF-16le\n");
}
elsif ($textencode == 3)
{
    print("Text Encoding = UTF-16be\n");
}
else
{
    print("Invalid Text Encoding!\n");
}


# Extract the SQLite Version number as a 4 byte Big Endian Integer at bytes 96-100
# The version number will be in the form (X*1000000 + Y*1000 + Z)
# where X is the major version number (3 for SQLite3), Y is the minor version number and Z is the release number
# eg 3007004 for 3.7.4
my $rawsqliteversion = substr($rawsqliteheader, 96, 4);
my $sqlversion = unpack("N", $rawsqliteversion);
print("SQLite Version is: $sqlversion\n\n");

# CODE ENDS HERE

Code Summary

As ".sqlite" files contain a UTF-8 encoded header string we need to use the "decode" function from the Encode Perl package. So the first thing to note is the "use Encode" statement.
Next, we have the familiar GetOptions and Help sections. Yawn!
And now the journey really begins ...
We call a read-only "open" on the user specified filename and then "sysread" in the first 100 bytes into the "$rawsqliteheader" variable.
Now that we have a copy of what should be the file header, we can now "close" the user specified file.

The first thing we do is look for the UTF-8 encoded "SQLite format 3\000" string. We use "substr" to copy the first 16 bytes into "$rawsqlitestring" from our 100 byte "$rawsqliteheader" buffer.
Next we call "decode" to convert "$rawsqlitestring" into a Perl usable string format and store the result in "$sqlitestring". See here for more information on encoding/decoding with Perl.
Finally, we test the value of "$sqlitestring" and "exit" if it does not match "SQLite format 3\000".
Pretty straight forward eh?

Similarly, for the Database Page Size field - we call "substr" to copy our bytes (in this case only 2) but instead of calling "decode" we use "unpack" for interpreting numbers. There's a bit of a hack going on with this particular field - if it's value is 1, the actual size used is supposed to be 65536 bytes. So we include an "if" statement to handle that.

I could go on but the other parsing sections do pretty much the same thing (except they call "substr" to extract 4 bytes from various offsets).


Testing

Here's the output of our "sqlite-parse.pl" script with a bogus fabricated file we prepared for an earlier post:

sansforensics@SIFT-Workstation:~$ sqlite-parser.pl -file /cases/cheeky-file.c4n6

Running sqlite-parser.pl v2012-03-21

/cases/cheeky-file.c4n6 does NOT have a Valid SQLite String in Header! Bailing out ...

sansforensics@SIFT-Workstation:~$

OK so that shows that our's is a discerning script ... we don't accept any old riff-raff claiming to be SQLite!
Now lets try it with a Firefox 11.0 "places.sqlite" file:

sansforensics@SIFT-Workstation:~$ sqlite-parser.pl -file /cases/firefox11/places.sqlite

Running sqlite-parser.pl v2012-03-21

SQLite String Parsed OK - Continuing Processing of /cases/firefox11/places.sqlite ...
Database Page Size (bytes) = 4096
Valid Number of Pages = 145
Total Number of Freelist Pages = 5
Schema Format = SQLite v3.1.3 (2005)
Text Encoding = UTF-8
SQLite Version is: 3007007

sansforensics@SIFT-Workstation:~$

That looks OK but we should validate our results using the SIFT's SQLite Manager Firefox plugin.

"SQLite Manager" Firefox Plugin Validating Our Script Output

As you can see, the SQLite Manager confirms our scripts "Database Page Size" (4096), "Valid Number of Pages" (145), "Total Number of Freelist Pages" (5) and "Text Encoding" (UTF-8) values.


Now we will use the SIFT's Bless Hex Editor to validate our script's results for the "SQLite Version"  and "Schema Format". You can access Bless from the Applications ... Programming ... Bless Hex Editor menu.

Bless Hex Editor Validating Our Script's outputted SQLite Version (3007007)

Bless Hex Editor Validating Our Script's outputted Schema Format (2)

OK, the Bless screenshots confirm our script's "SQLite Version" (3007007) and "Schema Format" (2, which corresponds to "SQLite v3.1.3"). Dare I say, we've been blessed? heheh

For extra giggity-giggity-giggles, lets now use our script with a Firefox 3.5.17 "places.sqlite" file:

sansforensics@SIFT-Workstation:~$ sqlite-parser.pl -file ffexamples/places.sqlite


Running sqlite-parser.pl v2012-03-21


SQLite String Parsed OK - Continuing Processing of ffexamples/places.sqlite ...
Database Page Size (bytes) = 4096
Valid Number of Pages = 203
Total Number of Freelist Pages = 0
Schema Format = SQLite v3.0.0
Text Encoding = UTF-8
SQLite Version is: 3007004


sansforensics@SIFT-Workstation:~$

We can see that Firefox 3.5.17 and Firefox 11.0 use different schemas and SQLite versions for their respective "places.sqlite" files. For Firefox 11.0, Schema Format = SQLite v3.1.3 (2005), SQLite Version is: 3007007. For Firefox 3.5.17, Schema Format = SQLite v3.0.0, SQLite Version is: 3007004.
This information would have been handy before we started trying to open Firefox 11.0 database files with a superseded copy of sqlite3 eh?

Anyway, so ends another post. Please let me know if you think/find it useful or if you would like any more header fields printed.Cheers!