Google Chrome Download History

Google Chrome keeps a wealth of data that is of interest to the forensic examiner.  There are tools that look at the browser history and cache, but tools that examine the download history are less frequent.  Part of the reason it is overlooked by examiner's, I suspect, is that the download history is a table in the 'History' SQLite database, and not a separate file itself.

The History database, found in '/home//.config/chromium/Default' for the open version of chrome, i.e., Chromium, contains the following tables:
$ sqlite3 History .tables
downloads             presentation          urls
keyword_search_terms  segment_usage         visit_source
meta                  segments              visits

The downloads table is the focus of this post.  It is structured as follows:

$ sqlite3 History .schema | grep downloads
CREATE TABLE downloads (id INTEGER PRIMARY KEY,full_path LONGVARCHAR NOT NULL,url LONGVARCHAR NOT NULL,start_time INTEGER NOT NULL,received_bytes INTEGER NOT NULL,total_bytes INTEGER NOT NULL,state INTEGER NOT NULL);

For the uninitiated, the table schema tells us that each record contains 'id', 'full_path', 'url', 'start_time', 'received_bytes', 'total_bytes', and 'state' fields.  We can extract the data in csv format, as follows:

sqlite -header -csv History "SELECT * FROM downloads"

That's nice, but the dates are unixepoch and we don't know what the 'state' integer means, though it can be deduced from the 'received_bytes' and 'total_bytes' fields and studying a known source of data.  I determined that state 1 and 2 mean "complete" and "incomplete" download respectively.

To convert unixepoch to local time, we use the SQLite datetime function.   We tell datetime to take the start_time integer, interpret it as unixepoch time, and translate to localtime.  Exerpted from the rest of the query, it looks like this:

datetime(start_time,'unixepoch','localtime')

For clarity in the output, it would be nice to convert the state integer to its text equivalent.  We can do this with the case command.  The case command is the if/then statement of SQLite.  In its simplest application it takes the form 'CASE  WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END' where x is the field data, w is a comparison value, and r is the result that is returned.  It is easiest to understand when we apply it to our current circumstance:

CASE WHEN state=1 THEN 'complete' WHEN state=2 THEN 'incomplete' ELSE 'unknown' END

Putting it all together, we can obtain nicely formatted output from the downloads table of the Chrome History database with the following command:

sqlite3 -header -csv History "SELECT id,full_path, url, datetime(start_time,'unixepoch','localtime') AS date, received_bytes, total_bytes, CASE WHEN state=1 THEN 'complete' WHEN state=2 THEN 'incomplete' ELSE 'unknown' END AS state FROM downloads"

The caps in the commands are not required, but are designed to make reading the SQLite operators easier.  You may have noted the "AS" operators after the datetime function and case expressions.  It serves to replace the expressions as the header for that column in your csv output.

Your output should look like:

id,full_path,url,date,received_bytes,total_bytes,state
1,/home/slosleuth/Downloads/keepnote_0.7.3-1_all.deb,http://keepnote.org/keepnote/download/keepnote_0.7.3-1_all.deb,"2011-06-23 12:04:46",500022,500022,complete
2,/home/slosleuth/Downloads/iso/nbcaine2.0.dd.gz,http://www.caine-live.net/Downloads/nbcaine2.0.dd.gz,"2011-06-24 13:25:42",85962197,0,incomplete
3,/home/slosleuth/Downloads/iso/nbcaine2.0gz.md5.txt,http://www.caine-live.net/Downloads/nbcaine2.0gz.md5.txt,"2011-06-24 13:27:23",51,51,complete

I hope this helps you with SQLite queries in general and specifically to parse the data in the Google Chrome downloads table of the History SQLite database. 

Extending gThumb for forensics

I've searched high and low, and many times, for a good image viewer to be used in Linux-based forensics.  In the end, and despite some shortcomings, I always end up using gThumb, a GUI image viewer and browser.  The advantages of gThumb are that it integrates well with the Gnome desktop environment, has a robust search facility, finds files recursively, and can be extended by the user.

The focus of this post is extending gThumb.  Today, many images are rich in EXIF data, much of which gThumb displays in a property window.  However, it misses data like GPS coordinates that can be found in some images like those taken by the iPhone.  My favorite tool for reading EXIF data is exiftool by Phil Harvey, but it's a command line tool.  So, how does one integrate command line data with a GUI tool like gThumb?  Yad!

No, I didn't just utter an explicative.  Yad stands for "yet another dialog" and is a fork of the zenity project.  Zenity is a simple to use GUI front end for command line scripts.  However, yad has many more features and is under active development.  Yad had many stock dialog boxes, and I will illustrate its use here as I extend gThumb with exiftool by displaying the exiftool output in a yad dialog box.

gThumb has the option to run user scripts, accessed through the toolbar: Tools | Personalize.

This opens the "Command" dialog box.  Clicking the "New" button opens the "New Command" dialog where the user command is inputted.

Assuming both yad and exiftool are installed in you operating system, you can create an exiftool extention as follows:

Name: exiftool

Command: exiftool %F | yad --text-info --title="exiftool: %B" --fontname="Monospace 10" --width=600 --height=800

Shortcut: <select from drop down if a shortcut key is desired>

Terminal command (shell script): leave checked

Save the command and close the main Command box.  You can select your new command from the tools menu or by the number pad shortcut you created.

The results appear in a yad "text-info" dialog box:

If you made a mistake, or need to edit your command use the Command window through "Tool | Personalize."

With this basic format, gThumb extensions are only limited by your creativity!

SD Card Construction, (or Burning Ants with a Magnifying Glass)

A coworker brought me an SD card today because she could not delete any files from it.  I noticed the lock switch was missing from the card, and after inserting the card into a portable reader, I confirmed the card was write protected.  This got me curious: just how does the switch on an SD card work?  I've relied on it for write protecting evidence, but I didn't really know how reliable the switch was.

With great gusto, I snapped the card in half to examine its contents.  I did it with as much joy of discovery as a young boy has when first focusing the suns rays on ants with a magnifying glass.  What I found inside surprised me: a very simple chip occupying about 1/3 of the card housing.  I determined--by examining similar cards--that the switch did not bridge any electrical contacts on the chip, so write protection was not a function on the card itself.

I turned my attention to several card readers I have lying about.  One has a very shallow card well, which makes visualizing the electrical contacts quite easy.  I noted that on the side of the reader, in a location corresponding to the switch on an SD card, there was a spring-loaded pin.  Through  experimentation, I determined that the pin, when depressed, allows data to be written to the card.  When the pin is extended, write-blocking occurs.  The position of the "lock" switch on the card determines the position of the pin.

Thus, (and to my surprise) write blocking is a really function of the reader, not the card.  It is possible for a reader to be constructed or damaged such that the lock switch has no effect!  Frequent inspection and testing of a card reader used for forensic analysis is warranted.

Lesson: know your equipment!

Defeating the Droid: Let the Pillaging Begin

In my last post, Defeating the Droid, I explained how to root an Android phone, at least through version 2.2.  Like other mobile devices, Android is a moving target and what works on one phone or one OS version may not work on the next.  We left off with a running adb shell with root privileges.  But how do we get the data we seek.

Bringing Up the Past

I mentioned previously that the adb shell is an ASH shell without auto-completion, inline editing, or command history.  In other words, this car is a base model without all the bells and whistles with which you might be accustomed.  Add to that a limited set of binary tools (no copy, tar, or find commands, for example) and its a bit like standing at the base of a cliff without any climbing tools.

Plan of Attack

We need tools, and we need them bad.  But we don't want to change any more data on the phone than necessary.  So, what to do?

Android phones use SD cards to store user media, like photos and music.  This card should be removed and imaged, following standard forensic imaging protocols, as part of your examination.  While the card can be imaged from within the phone, the interface is slow (took 45 minutes to image 16gb) and requires that the phone be placed in USB mass storage mode for the workstation to see it.  A locked phone may make this impossible.

Significantly, removing the card allows us to replace it with one of our own containing tools for accessing data in the phone's internal memory.  You can get by quite handily with one small tool: busybox.

You can copy busybox to the card before inserting it in the phone, but its not a necessity.  You can also use the Android Debug Bridge (adb) utility to copy busybox to an installed card using the command:

# adb push busybox /sdcard

Finally, the card you use should have enough space to capture the full internal memory of the phone.  The adb shell allows us to copy files within the device, but not to our workstations.  So, we must copy files to the SD card.

The size of our SD card can very, depending on the amount of physical memory found in the phone. This can be quite small, as in the case of the original Motorola Droid which has only 256mb of internal storage.  However newer devices, like the Droid 2, have significantly more memory with 8gb of internal storage capacity.

Sequential Processing: Starting with "dd"

I'm experienced in fingerprint development as well as computer forensics.  In fingerprint processing, there is a phenomenon known as "Sequential Processing."  Sequential processing simply means to do processing in such an order so that the first step does not inhibit the second step, and so on.  We actually do this every day in computer forensics: we image a device before before we conduct an examination, and we use the image for the examination so as to not harm the original evidence.

It is my opinion that we should do physical imaging of the device before logical.  The device we are working on has a running operating system and files are changing and being created and deleted as part of the normal process of the phone.  Further, a physical image means we can recover deleted files, so we want the physical images before we do anything that could overwrite data.  "dd" is the tool of choice here, and it can be deployed quickly (it is part of the resident operating system) and there is no set up involved, i.e., remounting of partitions.

1. Root the device.  See my previous post if you don't know what I'm talking about.
2. Start the adb shell if its not already running.  Look for the telltale root prompt to be sure you have "rooted" the device.
   
   # adb shell
   #  <-- adb shell prompt
   
3. Determine the devices you want to image.  In Android, the devices are called mtdblock devices, and are listed in /proc/mtd.
   
   # cat /proc/mtd
   dev:    size   erasesize  name
   mtd0: 00180000 00020000 "pds"
   mtd1: 00060000 00020000 "misc"
   mtd2: 00380000 00020000 "boot"
   mtd3: 00480000 00020000 "recovery"
   mtd4: 08c60000 00020000 "system"
   mtd5: 05ca0000 00020000 "cache"
   mtd6: 105c0000 00020000 "userdata"
   mtd7: 00200000 00020000 "kpanic"
   
   
4. Ideally, we want to image everything.  But of particular interest are the "cache" and "userdata" block devices.  The size of each device is listed in hexadecimal code.  In the above case, the "cache" device is  97124352 bytes [echo $((0x5ca0000))], and the "userdata" device is 274464768 bytes [echo $((0x105c0000))].  We need to know how the devices are addressed:
   
   # mount
   rootfs / rootfs ro,relatime 0 0
   ...
   /dev/block/mtdblock4 /system yaffs2 ro,relatime 0 0
   /dev/block/mtdblock6 /data yaffs2 rw,nosuid,nodev,relatime 0 0
   /dev/block/mtdblock5 /cache yaffs2 rw,nosuid,nodev,relatime 0 0
   /dev/block/mtdblock0 /config yaffs2 ro,relatime 0 0
   ...
   
   
5. We see that the "cache" mtdblock device is addressed as /dev/block/mtdblock5 and "userdata" as /dev/block/mtdblock6.  We can make a dd image of each as follows:
   
   # dd if=/dev/block/mtdblock5 of=/mnt/sdcard/cache.dd
   189696+0 records in
   189696+0 records out
   97124352 bytes transferred in 24.001 secs (4046679 bytes/sec)
   # dd if=/dev/block/mtdblock6 of=/sdcard/data.dd
   /dev/block/mtdblock6: read error: I/O error
   83808+0 records in
   83808+0 records out
   42909696 bytes transferred in 8.653 secs (4958938 bytes/sec)
   
   Uh, oh, an error.  Normally, we overcome dd errors by adding the "conv=noerror" argument, which tells dd to continue on read error.  But the Android dd does not support that option.  So, what do we do in such a circumstance?  Give up?  Hardly!

Phase Two: Busybox

Busybox can be described as "the swiss army knife of embedded Linux."  It is a single executable file, about 1mb in size, containing many common Unix utilities, including cp, find, tar, and yes, dd!  In order to use it, we need a version compiled for Android, but I will not discuss how to compile here. It is readily available on the Web and in Android Root kits, but I'd recommend compiling a trusted version if you can.  One things certain, you cannot use the busybox binary from your workstation.  

There's a catch to using busybox.  We want to place it on our SD card so we don't change anything on the phone's internal memory, but busybox cannot be run from the SD card.  "Why?" you ask.  I'll show you:

# mount  
 ...
/dev/block/vold/179:1 /mnt/sdcard vfat rw,dirsync,nosuid,nodev,noexec,relatime,uid=1000,gid=1015,fmask=0702,dmask=0702,allow_utime=0020,codepage=cp437,iocharset=iso8859-1,shortname=mixed,utf8,errors=remount-ro 0 0

So, to make the busybox on the SD card executable, we need to remount the card with the ability to execute binary files.

1. If busybox was not already on your card, push it to the card from a terminal on your workstation.
   
   # adb push busybox /mnt/sdcard
   1255 KB/s (1745016 bytes in 1.356s)
   
   
2. Remount your card with executable permission.
   
   # adb shell
   # mount -o remount,rw /mnt/sdcard /mnt/sdcard
   # mount
   /dev/block/vold/179:1 /mnt/sdcard vfat rw,dirsync,relatime,uid=1000,gid=1015,fmask=0702,dmask=0702,allow_utime=0020,codepage=cp437,iocharset=iso8859-1,shortname=mixed,utf8,errors=remount-ro 0 0
   
   
3. Change directories to the SD card and optionally start a busybox shell.  By starting a busybox shell, we attain inline editting and a shell history.  In other words, entering commands becomes easier.
   
   # cd /mnt/sdcard
   # ./busybox sh
   
   
4. Now we can dd that damaged "userdata" mdtblock again, this time passing conv=noerror because we will use the busybox dd.  Note: because busybox is not installed on the system or in the path, we have to prepend busybox utilities with ./busybox .  To see a list of available busybox tools, issue "./busybox" alone.
   
   # ./busybox dd if=/dev/block/mtdblock6 of=/mnt/sdcard/data.dd conv=noerror
   
   
5. With busybox, we can also create archives of the logical files on the device.  Again, to simplify the discussion, we'll focus on the /cache and /data directories where we saw the respective mtdblock devices were mounted.  Why should we archive the logical files when we have physical images made with dd?  Because the file systems in the images are "yaffs" and present special problems I won't discuss here.  It is enough to say, for now, that your workstation likely doesn't support yaffs by default and you cannot mount and examine the images you created.  They are only useful file carving.
   
   Therefore, we'll use tar to make archives of the /cache and /data directories.
   
   # ./busybox tar czvf cache.tar.gz /cache
   tar: removing leading '/' from member names
   cache/
   cache/downloadfile-30.jpeg
   cache/downloadfile-15.jpeg
   ...
   cache/app/I-Status_Monitor.apk
   # ./busybox tar czvf data.tar.gz /data
   tar: removing leading '/' from member names
   data/
   data/tombstones/
   data/tombstones/tombstone_05
   data/tombstones/tombstone_00
   ...
   data/lost+found/

Wrapping Things Up

When you've acquired the images/files that you seek, you need to get the data from the SD card to your workstation.  The easiest way is to turn off the phone and remove the card, the read it directly.  But you can pull the files from the card with adb, too.  You might want to do this if you need the data quickly, or to free up space on a smaller card for more images/archives.  To pull the files from the card to your workstation:

1. Use "adb pull" to move files from the SD installed in the phone to your workstation.  The command is run in a terminal on your workstation, NOT in the adb shell.  You can exit adb shell or open a new terminal window if you want to remain in the adb shell environment.
   
   # exit  <-- exits the busybox shell
   # exit  <-- exits the adb shell to your terminal
   # adb pull /mnt/sdcard/cache.tar.gz /some/evidence/directory/
   # adb pull /mnt/sdcard/cache.dd /some/evidence/directory/
   ...
   
2. When you are done pulling all the files you plan to transfer, remove the exploit from the install directory.
   
   # adb shell rm /data/local/tmp/ratc.bin
   
   Note: I chose this method of removing the exploit to demonstrate that the adb shell command can pass arguments to the adbd daemon on the phone without dropping into the shell.  I hope to use this ability to script the process we've been discussing.
   
3. Reboot the phone to kill the exploit and the phone is back in its pre-exam condition.

I'll leave the examination of the data you've recovered to you for now.  Maybe in a future post, I'll highlight some key files and how to process them (hint: mmssms.db contains text messaging, and cache.cell contains GPS data).

Defeating the Droid

I received a Motorola Droid 2 for analysis.  Processing the 16gb SD card was trivial as it is removable and subject to traditional imaging techniques.  But what of the internal phone memory?  After a lot of researching, I found there just wasn't a lot of information out there for forensic examiners.  I settled on a procedure called "rooting" which I'll document here.

Caveat 1: I wouldn't call this a "forensically sound" approach, per se, in that it makes changes to the phone. But I wouldn't say this leads to automatic disqualification of the evidence, either.  The technique was a necessary approach under the circumstance of current technology to meet the needs of my investigation.  The techniques and their impacts on the evidence can be explained and therefore may be admissible.  Your mileage may vary.


Caveat 2: The "rooting" process I describe here borrows from common techniques found on the Web that were developed by users of Android phones to gain full access to their devices.  But, the process described here differs significantly. Do not follow the user-oriented tutorials for rooting devices as they require you to install files unnecessary to forensics and make significant changes to the device, sometimes erasing user data!

Preparing the Phone

Luckily, the phone I received was unlocked. I accessed the settings, disabled wireless networking, and placed the device in airplane mode to minimize changes and prevent remote access. I also disabled the password and automatic screen locking, and I determined the OS version (v2.2.2) through "Settings | About Phone", in this case 2.2.2. Two other settings are required: set the device to enter USB debugging mode when connected by USB and set it to stay awake while charging  through "Settings | Applications | Development."

I have since learned that the technique I describe below will work on locked Andriod phones that have USB debugging enabled.  Working with another investigator, we rooted a locked Virgin Mobile LG Optimus and a locked HTC Incredible.

Preparing the Workstation

I downloaded and installed the Android SDK which includes the very useful Android Debug Bridge (adb) utility.  Installation of the Linux version involves extracting the compressed archive (tar) and copying to a directory of your choice.  In my case, I chose '/usr/local'.

# tar xzvf android-sdk_r11-linux_x86.tgz.tar -C /usr/local

Update the SDK to ensure you have the latest tools

# ./usr/local/android-sdk-linux_x86/tools/android update sdk

Creating a link to adb somewhere in your path, such as /usr/local/bin makes running the utility easy and is recommended.  I created a link with the following command:

# ln -s /usr/local/android-sdk-linux_x86/platform-tools/adb /usr/local/bin/adb

Working with the Android Debug Bridge

With the SDK installed and the device connected by USB cable, the command 'adb devices', run as root, will start the adb server and show the serial number of a USB-attached Android device.  If the serial number is represented by a series of question marks, kill the sever with 'adb kill-server' and restart with 'adb root'.  The Android version and phone model influence whether or not you can start the adbd daemon as root. I recommend attaching only one device at a time so that subsequent commands need not specify the device on which to operate.  Use 'adb help' to list all possible commands and a brief description of their use.

Default ADB Shell Access

There is limited access to the device file system by dropping into the adb shell with the command 'adb shell'.  This is true even if you are root when you start the adb server and if you start the adbd daemon with 'adb root'.  You'll note that you have the standard '$' user prompt.  In other words, when you launch the adb shell, you are dropped into in a shell console on the running phone with standard user privileges.

The adb shell is an ASH shell with limited binary tool availability.  For example, there are no copy or find commands.  There is no inline command editing or shell history.  Your limited privileges prevent you from accessing the /data and /cache directories which contain the bulk of the user data you are likely seeking.  Other available adb commands (not part of adb shell), can be used to recursively copy files from the device to your local system (adb pull) or place local files into the Android file system (adb push), but your limited privileges still impede you.

Escalating Permissions with Rageagainstthecage

The solution is to "root" the device.  Rooting, in this case, means to escalate your privileges in the adb shell to super-user status.  I could not find a rooting method that prevented any changes to the phone's file system (the exploit has to be loaded into a partition on the phone), but  I settled on the small 5k 'rageagainstthecage' exploit,  which is an adb setuid exhaustion attack. In a nutshell, the exploit crashes the adbd daemon on the phone and prevents it from deescalating root privileges when it restarts.  I know of no 'trusted' exploits for forensics, and this seemed the soundest approach to minimize changes to the running operating system on the device.

A variety of exploits can be downloaded from the Android Devices Website, but it is not the only location for rooting information.  The xda-developers website is another good resource.  In the case of the Motorola Droid 2, I'll use the rageagainstthecage binary file from the developer, which includes the source code for review.

Installing and Running the Exploit

The process of rooting is straight forward:  Push the file to the device, make it executable, and run.  The result is a crashed adbd daemon, that when restarted, allows the adb shell to start with root permissions.

1. Extract the the rageagainstthecage-arm5.bin bin from the tar file.
   
   # tar xzvf RageAgainstTheCage.tgz.tar rageagainstthecage-arm5.bin
   
2. (optional) Rename rageaginstthecage-arm5.bin to something easy to type, like ratc.bin.
   
   # mv rageagainstthecage-arm5.bin ratc.bin
   
3. Push the file to a user-accessible location on the phone where execution of files is permitted.  I'd prefer a different location than the /data partition, but so far this has been my best option.
   
   # adb push ratc.bin /data/local/tmp/ratc.bin
   
4. Drop into the shell, make the file executable, and run.  Note the prompt change after executing adb shell as root.
   
   # adb shell  <-- your root terminal
   $ chmod 0755 /data/local/tmp/ratc.bin  <-- adb shell
   $ ./data/local/tmp/ratc.bin
   [*] CVE-2010-EASY Android local root exploit (C) 2010 by 743C
   
   [*] checking NPROC limit ...
   [+] RLIMIT_NPROC={1803, 1803}
   [*] Searching for adb ...
   [+] Found adb as PID 3323
   [*] Spawning children. Dont type anything and wait for reset!
   [*]
   ...
   [*] adb connection will be reset. restart adb server on desktop and re-login.
   $ #  <-- your root terminal again
   
   You will be dumped out of the adb shell back into your terminal.  Be patient, as this can take a few moments.  The exploit is launching processes and creating a race condition to prevent the adb daemon from deescalating privileges on restart.  If you are not dumped from the shell, run the exploit again. 
   
5. Finally, restart the adb shell (note the root prompt indicating you are now the root user on the device). DO NOT restart the adb server, as the last system message indicates.  It will restart on its own , if needed, when you issue the 'adb shell' command.
   
   # adb shell
   #  <-- adb shell prompt
   
   If you receive the "device not found" error when restarting the adb shell, wait a few moments and try again: then adbd daemon on the phone has not finished restarting.

There, you've done it! You have rooted the device.  The root adb shell will remain until you restart the adbd daemon with 'adb kill-server' or by rebooting the phone.  The exploit will remain on the phone until you delete it in the adb shell with:

# rm /data/local/tmp/ratc.bin

You are now ready to start copying partitions and/or logical files for your examination... almost.  You still lack the tools to copy files, (though not partitions), and copying whole partitions with dd, while possible, isn't as straight forward as it is with physical disks, the primary issue being the yaffs filesystem they contain.  Since this post has gotten quite long, I'll discuss those issues if future posts.

Decoding Google Chrome timestamps in SQLite databases

I had occasion to work with Google Chrome histories today.  The timestamp in the History SQLite databases look like unixepoch time at first glance, but they are not. However, the Chrome timestamps are 7 digits longer than unixepoch time.

Unixepoch

Unixepoch time is the number of seconds elapsed since 01/01/1970 00:00:00. It can be converted in a SQLite statement with the datetime() function thusly:

sqlite> SELECT datetime(time, 'unixepoch', 'localtime') AS time FROM table;

"time" is the name of the field containing the (currently) 10-digit date string, e.g., "1307078210", and "table" is the name of the table containing the time field.  The "AS time" displays the column label as "time". Without it, the column takes the full datetime() function as the column name which is a bit ugly and confusing to recipients of the data.

PRTime

The extra seven digits in the Chrome timestamp brought to mind Firefox PRTime. PRTime is the number of microseconds since 01/01/1970 00:00:00. The timestamp needs to be converted to unixepoch format before it can be converted to local time through the SQLite datetime() function. This can be done by dividing the value by one-million. In SQLite, this looks like:

sqlite> SELECT datetime(time/1000000, 'unixepoch', 'localtime') AS time FROM table;

Chrome Time (Webkit Format)

If there is an official name for Chrome time, I don't know it.  EDIT: I have learned the name for the Chrome's format is Webkit format).  What I do know, is that Chrome time IS NOT the same as PRTime.  Though it is also a microsecond calculation, its base time is 01/01/1601 00:00:00. To calculate local time, Chrome time has to be converted to seconds by dividing by one-million, and then the seconds differential between 01/01/1601 00:00:00 and 01/01/1970 00:00:00 must be subtracted. But how do we figure that out?

Two ways, as it turns out.  With SQLite itself, or with the Unix date command.  This is how:

SQLITE:

sqlite> SELECT strftime('%s', '1601-01-01 00:00:00');
-11644473600

DATE:

$ date +%s -d 'Jan 1 00:00:00 UTC 1601'
-11644473600

In both commands above, the "%s" represents unixepoch time.  The commands calculate the number of seconds between unixepoch time (1970) and the subsequent date (Chrome time base, 1601).  Note that the seconds are negative.  Of course, this is because you have to count backwards from 1970 to 1601!  With this information, we can convert Chrome time in SQLite like this:

sqlite> SELECT datetime((time/1000000)-11644473600, 'unixepoch', 'localtime') AS time FROM table;

Unfortunately, there are more time formats out there than these.  You may recall I blogged about Apple's Mac Absolute Time in February.  But, armed with this information, You should be able to convert the times formats I've discussed and possibly discover the solution to any others you might encounter.