Wednesday, May 16, 2018

Book Review: SQLite Forensics by Paul Sanderson

One of the two books I've been the most excited for came out over the weekend.* SQLite Forensics by @sandersonforens is available now and can be purchased via Amazon.

After arriving home from work I went straight to my mailbox and read the first 3 chapters in one sitting. The rest I finished the next day. Don't get me wrong, this book is not a beginners book by any means but the  way the book is structured makes learning easy even though it is impossible to get it all in one reading. The conceptual explanations are succinct and highly visual with many examples. As long as the reader knows a little about offsets and hex it is possible to follow along with no problems.

Author and Technical Editors - Do note!

As can be seen in the cover the technical editors are of the highest quality. Most folks in the DFIR space have either studied technical articles, read blog posts, taken courses from, or used tools developed by these individuals. The author of SQLite itself is here so the technical support comes straight from the source. The author himself is extremely well known in the community for his expertise and for his development of the Forensic Browser for SQLite, one of the best if not the best tool to forensically deal with the subject matter at hand. For me this book couldn't be more timely considering that my caseload is becoming more and more mobile forensics heavy as time goes by. As mentioned in the book and as experience has shown me, there can easily be over a hundred SQLite databases in just one mobile phone. There is a real need for reference material on this topic and it is great for practitioners to now have a book that pools all this knowledge in one place. It feels to me like the SQLite version of Brian Carrier's File System Forensics Analysis book. In depth and accessible all at the same time.

The book starts with a breakdown of SQLite SQL basics. The target audience for this book should be intimately familiar with SQL itself hence the explanations are short and to the point but still with enough detail to carry readers with little experience through the book. From database pages and b-trees to record recovery and schema definition the author gives us a low level understanding of all these topics.

Regarding the style of the book I appreciate the time taken to explain the process used to reach conclusions on how to interpret the data. The constant reinforcement of testing, validation, and the need to avoid hasty assumptions is made throughout. For example when dealing with record recovery Sanderson states, "With an understanding of what was present we can then provide methods for recovering data..." (83) This to me is the heart of forensics. The understanding of data we know and how it behaves in all states serving as the foundation for understanding datasets generated by others in those already understood states. In the book Sanderson proceeds to demonstrate all the cases to be considered in the record recovery process for SQLite databases. Both beginner and experienced forensicators will be well served by following such an approach in all things.

If I were to pick my favorite 3 sections I would choose the following:
  • Journals & Write-Ahead Logs
    • It is my opinion that the most used forensic applications in the mobile forensics space lack a clear way of presenting WAL file content and corresponding context. As a result plenty of possibly pertinent data goes unexamined. I fully expect to use the knowledge and the tools in this section for case work and teaching/trainer duties.
  • Time Conversions
    • Although I have been acquainted with the different time conversions used in SQLite for a while now this book is the best central repository of them I've come across. Forensic Browser for SQLite datetime SQL extensions and display functions make conversion easy work.
  • Case Study
    • Having a case study putting it all together is not only the best way of making sure the concepts are understood but also serves as a template the reader can use when approaching SQLite databases for analysis. Super useful.
For me the biggest take-away from the book is the detailed understanding of what the tools we use do and how they do it. There is no practical way to do all the correlation and analysis shown in the book by hand on every single database on every single case. Yet with the knowledge gained by this book I am able to validate any particular tool finding that is relevant to a case I might be working on. The why of the data can be just as important as the fact that data has been found. Maybe even more. This books give you the tools to dig and uncover both.

Bottom line: Highly recommended. 10/10 would buy again.

For any questions or comments I can be reached via twitter here: @AlexisBrignoni

---------------
* The other book I'm looking forward to will be on Bitcoin/cryptocurrency forensic investigations by Brett Shavers. There is no publication date set that I know of.

Saturday, May 12, 2018

Android Remote Desktop Apps - TeamViewer Remote Control

Short version:

The TeamViewer Remote Control app for Android keeps pertinent data in the following files and locations:
  • Client configuration log filename and location:
    • /userdata/data/com.teamviewer.teamviewer.market.mobile/files/client.conf
    • Contains username and email account for the logged in TeamViewer user.
  • Global configuration log filename and location:
    • /userdata/data/com.teamviewer.teamviewer.market.mobile/files/global.conf
    • Contains certificate information and client version number.
  • Connection information log filename and location:
    • /data/com.teamviewer.teamviewer.market.mobile/files/connection.txt
    • Contains the remote id number of the device being connected to, the start and end of connection timestamps, the purpose of the connection, the Android's device name and a GUID like number.
  • Activity log filename and location:
    • /userdata/media/0/Android/data/com.teamviewer.teamviewer.market.mobile/files/TVLog.html
    • Text search the term 'participant' within the file to get the partner's device name.
    • Text search the term 'creating file' to obtain the names of transferred files.
    • Text search the term 'a=' to obtain the connection IP address.
  • File transfer default save location:
    • /userdata/media/0/Download/transferedfilename.extension
    • /storage/emulated/0/Download/transferedfilename.extension
Long version:

Recently I was catching up on some great digital forensics tutorial episodes at 13cubed, whic are made by @davidrichardg. In one of the videos he was talking about RDP cache data left on Windows systems. (I highly recommend his content and hope you can also become a Patreon supporter here.) His video left me wondering what type of artifacts could be found by the use of RDP and remote control apps in Android systems. Hence the idea of starting a small series on remote control artifacts in Android. I plan to look at both client and hosts apps. To start I decided to look at the TeamViewer remote control app.


App in the Google Play Store.

TeamViewer (TV) is a really well know remote control platform that has been around for quite a while. The current Android remote control version in the Google Play Store has over 10 million downloads and they state that over 1 billion devices have had their software installed in order to enable remote connections to them.

For this analysis I used the tools and equipment described here.

First step was to install the app on the target device. A Windows Surface laptop served as the device to be connected to. After logging in to TV in both devices I was able to successfully remote control the laptop from the Android device. I was also able to transfer a text file from the laptop to the Android device.

Remote control successful.

After obtaining a physical image of the target device with Magnet Forensics Acquire I reviewed the pertinent TV app folders with Autopsy digital forensics software. The app folder structure is shown in the following image:

/userdata/data/com.teamviewer.teamviewer.market.mobile/

After reviewing the contents of the folders it seemed that most of the pertinent artifacts the app generates are located in plain log and configuration text files. Most of these files were located in the properly named 'files' folder.

Files folder contents.

The following is a quick description of the most salient files in the folder.

  • Connection.txt

    This text file contains the remote id number of the device being connected to, the start and end of connection timestamps, the purpose of the connection, the Android's device name and a GUID like number.
Love timestamps.
  • Global.conf
    This configuration file contains certificate information and client version number.
Global stuff.
  • Client.conf
    Contains username and email account for the logged in teamviewer user.

Client stuff.

After transferring a text file from the laptop to the Android device I could not find any artifact within the TV app folder that would clue me in that a transfer had been completed. Within Autopsy I did a text search of the filename of the transferred file. The two most relevant files can be seen in the image below:

TVlog.html & Transferred file
The TVlog.html file is really interesting because one can access the contents of it via the app itself under the log section in the app menu. The second file is the transferred file itself. Both files reside in external/emulated storage but in different folders. The TVlog.html has a lot of information. Of the multiple pieces of information the log keeps I decided to focus on the following three:

  • Participants
    By text searching the file using the word 'participant' one can find the device names involved in the communication. For example:

    AddParticipant: [xxxxxxx,x] type=6 name=samsung_SM-G530T_R58H41XF9AH
    AddParticipant: [xxxxxxxxx,xxxxxxxx] type=3 name=BRIGNO-SURF

    The Xs represent the client ids involved in the communication.
  • Creating file
    By text searching the file using the phrase 'creating file' one can find the filename and location of transferred files. For example:

    creating File: /storage/emulated/0/Download/Tutorail FTK imager.txt
  • A=
    By text searching the phrase 'a=' one can find the connection IP address and port. For example:

    a=xxx.xxx.xx.x:9617: (*)
Each line in the TVlog.html file has a corresponding timestamp which I did not show in the previous examples due to formatting issues. Depending on the device in question, the TVlog.html file could reside in an the external SD card. 

For upcoming posts I will review the Android TeamViewer host app followed by the Microsoft RDP client for Android.

For any questions or comments I can be reached via twitter here: @alexisbrignoni