Showing posts with label getting. Show all posts
Showing posts with label getting. Show all posts

Wednesday, September 20, 2017

Getting to know the Relatives SQLite Databases

Getting to know the Relatives SQLite Databases


I was contacted by a colleague who needed some help analyzing a SQLite database.  It was the myspace.messaging.database#database located in the "Usersappdatalocalgooglechromeuserdatadefaultplugin datagoogle gearsmessaging.myspace.comhttp_80" folder.  I didnt and still dont know a whole lot about this file, but it appears to contain myspace email messages.

The Challenges of SQLite

Lets face it: SQLite is everywhere.  Understanding it is essential to good examinations, and a big part of that understanding come from learning SQL statements. There are many good online sources for learning SQL, and one of my favorites is w3schools.com.

But, for digital forensics practitioners, there is another challenge beyond understanding SQL commands--understanding the construction and relationships of the tables.  SQLite is a relational database, and the tables are meant to be related to one another to produce a result no possible or impractical from a single table.  Knowing how the table was intended to be used can be very difficult... after all, a SQLite database is more akin to a file cabinet, not a secretary who uses the file cabinet.

For example, the secretary can place company bank records in a file called "Financial Records" or she can put them in a file called "Artichokes".  It really doesnt matter, because she knows what goes in the file.  Someone coming along behind her wont have much trouble finding the bank records in the Financial Records file, but might overlook them them entirely in the Artichokes file.  The point is, without the secretary, it might be very hard to understand the filing system.

SQLite databases can be a lot like that.  You can see the structure, or the schema as it is called, very easily.  But what is not so easily understood is how the structure is intended to be used.  That mystery is usually locked up in the application that utilizes the database, but it is not explained in the database itself.

Getting a Clue

To be sure, there can be hints about how tables in a database interrelate.  Table and field names often speak volumes.  A database called "AddressBook.db" with two tables called "Names" and "Addresses" that have a field in common called "SubjectID" isnt too hard to fathom.  If we are lucky enough to be able to run the application that uses the database and test our inferences based on the applications output, our confidence grows (and our understanding, if supported by the outcome, would now be considered reliable).

My favorite hints by far are SQL view statements.  These are virtual tables that draw their data from other tables in the database (or an attached database).  By studying a view statement, you get insight from the database creator how the database was intended to be used... at least in one capacity.  Think of a view as a macro: it saves the database user the trouble of repeated typing a frequently used query.  And, if the query is frequently used, then you have a good sense of how the database was intended to be used.

What if There are No Clues?
What about circumstances in which there are no clues in the database to help us understand its use.  Well, if there are really no clues, then the only safe answer is we look at the data flat, that is to say, we look at the tables individually and we dont relate them in any way.  But, there are often less obvious clues than can reveal an underlying relationship... which brings me to the point of this article.

Latent Rows

Latent fingerprint examiners know the term "latent" means hidden or invisible.  Latent fingerprints must be revealed to be seen by some external method, such as fingerprint powder.  SQLite tables have a latent field, so to speak.  And, we can reveal it to help us form relations in a SQLite database.

Consider the myspace.messaging.database#database I mentioned in the open paragraph. It has the following schema:

CREATE VIRTUAL TABLE AuthorData USING fts2(AuthorDisplayName, AuthorUserName); 
CREATE TABLE AuthorData_content(c0AuthorDisplayName, c1AuthorUserName); 
CREATE TABLE AuthorData_segdir(  level integer,  idx integer,  start_block integer,  leaves_end_block integer,  end_block integer,  root blob,  primary key(level, idx)); 
CREATE TABLE AuthorData_segments(block blob); 
CREATE TABLE AuthorMetaData (AuthorId INTEGER PRIMARY KEY, AuthorImageUrl TEXT); 
CREATE VIRTUAL TABLE MessageData USING fts2(Subject, Body); 
CREATE TABLE MessageData_content(c0Subject, c1Body); 
CREATE TABLE MessageData_segdir(  level integer,  idx integer,  start_block integer,  leaves_end_block integer,  end_block integer,  root blob,  primary key(level, idx)); 
CREATE TABLE MessageData_segments(block blob); 
CREATE TABLE MessageMetaData (MessageId INTEGER PRIMARY KEY, RecipientId INTEGER, AuthorId INTEGER, Folder INTEGER, Status INTEGER, CreatedDate INTEGER); 
CREATE TABLE UserSettings (UserId INTEGER PRIMARY KEY, MachineId TEXT, Enabled INTEGER, TimeStamp INTEGER, LastSyncTimeStamp INTEGER, FirstRunIndexPass INTEGER, FirstRunIndexTargetCount INTEGER, OldestMessageId INTEGER, LastServerTotalCount INTEGER); 
CREATE INDEX AuthorIdIndex ON MessageMetaData (AuthorId, RecipientId); 
CREATE INDEX StatusIndex ON MessageMetaData (Status, CreatedDate);

Now look more closely at two tables of interest,MessageMetaData and MessageData_content:

CREATE TABLE MessageMetaData (MessageId INTEGER PRIMARY KEY, RecipientId INTEGER, AuthorId INTEGER, Folder INTEGER, Status INTEGER, CreatedDate INTEGER);
CREATE TABLE MessageData_content(c0Subject, c1Body)

It would seem from the table names that MessageMetaData contains information about the messages, and MessageData_content contains the messages themselves.  But, they dont share any fields that allow the two tables to be related. In other words, which rows of the metadata table correspond to which row of the content table?  Do they even correspond at all?

Lets look at our first hint or correspondence:

$ sqlite3 myspace.messaging.database#database.db select count(*) from MessageMetaData;
1358 
$ sqlite3 myspace.messaging.database#database.db select count(*) from MessageData_content;
1358

Both tables have the same number of records.  Hmm, a clue?  Quite likely, especially upon study of the table content and the remaining tables contents.  In fact conducting a similar study, we find another set of table correspondence: AuthorMetaData and AuthorData_content also have an equal number of records (172, to be exact) but no obvious, interrelated fields.

Unless youve studied SQLite construction in any depth, you probably dont know that it creates a rowid field for every table to act as a primary key.  If a table is created with a defined primary key, that primary key is just a alias to the builtin rowid (with one exception outside the scope of this discussion).  But the rowid is not represented in the table or database schema, which is probably why you didnt know about it (at least, I didnt until I bought a SQLite book).

Knowing about the rowid, i can now check to see if the two tables have matching rowid fields:

$ sqlite3 myspace.messaging.database#database.db select count(*) from MessageMetaData m, MessageData_content c where m.rowid = c.rowid
1358 

We dont have to trust the count function, take a look for yourself:

$ sqlite3 myspace.messaging.database#database.db select m.rowid, c.rowid from MessageMetaData m, MessageData_content c where m.rowid = c.rowid
...
81407357|81407357
81416917|81416917
81504605|81504605
81505714|81505714
81530947|81530947
81569294|81569294

Well, now this is even more interesting.  We not only have two tables with the same number of rows, but we have two tables with fields in relation, i.e., rowid!  

Understand that rowid is simply an autoincrementing, unique, 64-bit integer unless specifically declared otherwise by insert and update commands.  But is this just a coincidence?  Lets consider: we have non-sequential rowids throughout both tables.  That might be explained by dropped rows from the tables.  But two tables, each with 1358 rows, and each row having a matching rowid in the other table?  That is more than coincidence--its programatic.  The application populating the tables is assigning the rowids.

The Proof is in the Pudding

My assertion is that the myspace.messaging.database#database.db is assigning the rowids as it populates the related tables and links the rows by matching rowid.  Let me demonstrate how rowid can be assigned:

sqlite> create table numbers(digit integer);
sqlite> insert into numbers (digit) values(1);
sqlite> insert into numbers (digit) values(2);
sqlite> insert into numbers (digit) values(3);
sqlite> select rowid, digit from numbers;
1|1
2|2
3|3
4|3
sqlite> insert into numbers (rowid, digit) values (1000, 4);
sqlite> select rowid, digit from numbers;
1|1
2|2
3|3
4|3
1000|4

I created at table called "numbers" with one field called "digit."  I then inserted three rows in the table with the values 1, 2, and 3 respectively.    If youve been following along, you now know that every SQLite table also has a rowid field, even if not expressly created in the table by the user.  The first select statemnt shows the autogenerated rowid and along with the digits I inserted.

The final insert statement is different.  Here I assign the rowid, rather than let it be automatically populated by the SQLite engine.  And, as you an see in the final select statement, I succeed in setting an non-sequential rowid.

Putting it All Together

Ive demonstrated a "hidden" way that tables in SQLite databases can be related.  It takes some knowledge in SQLite structure and the SQL query language to unveil this data, however.  If you are in the habit of relying on SQLite browsers and looking at tables without relating them, then you are really missing out on a wealth of data.

Again, let me illustrate using the myspace.messaging.database#database.  Lets look at one row in each of the tables I mentioned previously:

$ sqlite3 -header myspace.messaging.database#database.db select * from MessageMetaData limit 1;
MessageId|RecipientId|AuthorId|Folder|Status|CreatedDate
1289081|544962655|41265701|0|2|1280870820000 

$ sqlite3 -header myspace.messaging.database#database.db select * from MessageData_content limit 1;
c0Subject|c1Body
Hi|Hey, whats up? 

$ sqlite3 -header myspace.messaging.database#database.db select * from AuthorMetaData limit 1;
AuthorId|AuthorImageUrl
-1930729470|http://some_url/img/some_image.png 

$ sqlite3 -header myspace.messaging.database#database.db select * from AuthorData_content limit 1;
c0AuthorDisplayName|c1AuthorUserName
A User|auser

The only hint of relationship, besides table names, is the AuthorID field in MessageMetaData and AuthorMetaData.  But there is still no obvious way to tie the metadata to the content we are most interested in.  Your favorite GUI browser maybe make the display prettier, but its just as impotent.

But, now that you have knowledge of the rowid, and have a link to a tutorial on SQLite statements, youre not too far from being able to do this:

sqlite3 -header myspace.messaging.database#database.db select messageid, datetime(createddate/1000, "unixepoch", "localtime") as Date, mm.AuthorID, c0AuthorDisplayName as "Author Display Name", c1AuthorUserName as "Author Username", c0subject as Subject, c1Body as Body from  MessageMetaData mm, MessageData_content mc, AuthorData_Content ac, AuthorMetaData am where mm.AuthorID = am.AuthorID and am.rowid = ac.rowid and mm.rowid = mc.rowid limit 2;
MessageId|Date|AuthorId|Author Display Name|Author Username|Subject|Body1289081|2010-08-03 14:27:00|41265701|A User|auser|Hi|Hey, whats up?

I ask you, on which output would you rather examine and report?

Addendum

That last query is really not so scary.  Its just long because were grabbing seven fields from four tables, and converting a date stamp.  But, in reality, its very straight forward.

Lets take a look:

select
     messageid,
     datetime(createddate/1000, "unixepoch", "localtime") as Date,
     mm.AuthorID,
     c0AuthorDisplayName as "Author Display Name",
     c1AuthorUserName as "Author Username",
     c0subject as Subject,
     c1Body as Body 
from 
     MessageMetaData mm,
     MessageData_content mc,
     AuthorMetaData am,
    AuthorData_Content ac 
where
     mm.AuthorID = am.AuthorID
     and am.rowid = ac.rowid
     and mm.rowid = mc.rowid;

The select clause simply picks the fields we want to display.  The datetime function converts the unixepoch time, which is recorded in milliseconds, to local time.  The as statements are naming the columns something more user friendly and are not required.

The from statement simply declares what tables to query for the fields we are trying to display.  Each table is followed by an alias I chose to make easier reference to field names common to more than one table.  For example, AuthorID is found in both the MessageMetaData and AuthorMetaData tables.  By giving MessageMetaData the alias of mm, I can now reference the MessageMetaData.AuthorID field as mm.AuthorID.

The where statement is a filter.  It keeps the tables aligned, so to speak.  It ensures that only the correct author content and message content is returned for each row.  This post is a lot long in the tooth, so I wont go into detail describing how it works.  But, very succinctly, the MessageMetaData record is matched to a AuthorMetaData record by AuthorID.  The the AuthorMetaData record is matched to its corresponding AuthorData_Content record by rowid.  Finally, the MessageMetaData record is matched to its corresponding MessageData_content, also by rowid.


download file now

Read more »

Getting Back to Real Life yeah we work and stuff

Getting Back to Real Life yeah we work and stuff


Before we left for Sussex a couple of weeks ago, I was talking to a friend about the upcoming trip. As I was describing some activity we were anticipating, she cut me off to ask a very fair and very pertinent question. She couldnt have cared less about the hike I was trying to sell her. What she wanted to know was how I got all the weekend chores done and was able to go back to work on the Monday after a trip without the whole house being in a state of chaos.

This is such a mom perspective. I remember one of our first weekend trips began with just a bit of tension when I returned home from work on a Friday afternoon and Jeremy proudly proclaimed that we were all set to go. My eyes whirled around in my head moving from the mugs in the sink to the crumbs under the dining room table to the full recycling bin. I was sputtering, huffing, and banging my way around the house cleaning up while Jeremy looked on in a state of total bewilderment. He honestly could not understand what the big deal was. If we werent there, who cared if the house was a little messy?

Well, I did and I still do. And God love him, he has come a long way on this one. He now knows that the camper isnt pulling out of the driveway if there is any food debris whatsoever in the kitchen sink strainer. He loves our weekend trips enough not to fight city hall on this one.

Another strategy for making these short trips manageable is that we never get home late on Sundays. We always drive back during afternoon nap and usually pull in around 3:30 or so. This gives us a good two hours before dinner to pull things together around the house. I am inside unpacking and doing laundry, Jeremy is outside hosing out the truck and mowing the lawn, and the boys are revisiting every toy that they missed over that long 48 hours. It sounds like we are doing our best to fulfill every suburban family stereotype out there...

So we have our routine down, and it usually goes pretty smoothly. The only thing I really struggle with every time is Sunday dinner. I never have a lot in the fridge since I try to use everything up before we go away. And I always want something very quick and simple since I am also trying to get all those chores done.

Last week I came up with the perfect solution: sweet potato risotto. I chopped up an onion and some garlic and two great, big sweet potatoes and sauteed them for about five minutes. Then I added two cups of risotto and sauteed it for another five minutes. I had a box of chicken stock in the pantry and I just kept adding it to the rice (about a half a cup at a time) until the risotto was soft and creamy (al dente is over-rated with toddlers in the house). A handful of Parmesan, a side of arugula salad, and dinner was served. You can get pretty precious with risotto, and it can be a time consuming dish if you are really particular about the results. Lucky for me, this wasnt an episode of Chopped, and my boys--all three of them--gobbled it up.

My extra special bonus: there was plenty left over for everyones lunch the next day. Clean up was a snap, the boys were in bed, and I was on the couch watching The Amazing Race by eight that evening. The perfect end to a perfect weekend.



download file now

Read more »

Wednesday, September 13, 2017

Enabling Android Pay on Lineage i e getting a Custom ROM to pass SafetyNet checks

Enabling Android Pay on Lineage i e getting a Custom ROM to pass SafetyNet checks


One issue with running a Custom ROM is that applications which require operation in a secure environment can use Androids SafetyNet API, which is part of Google Play Services, to check whether the device has been tampered with - i.e. whether it is running with root, has an unlocked bootloader or is running an unapproved ROM.

Such checks are performed by Banking applications, Android Pay and even some games such as Pokemon Go and others.

One way we can bypass this check is by using Magisk. One of the functions of Magisk is the ability to hide aspects of the system from SafetyNet, allowing applications that use these checks to function correctly on a device that would otherwise fail the checks.

Installing Magisk

Magisk comes in two parts - a flashable zip file, which should be installed by a custom Recovery such as TWRP, and an APK to control its behaviour.

At the time of writing, the latest stable version of Magisk is 12.0. If you are using a newer version, the exact steps required may be slightly different to that outlined below.

1. Download both the Zip file and APK from the XDA thread directly on the device - ideally in Chrome.
2. Shut down the phone and boot into TWRP with Volume Up plus Power
3. Go to Install and locate the Zip file, which is likely to be in the Download folder
4. Once flashed, reboot the phone
5. Ensure that Unknown Sources is enabled in Settings / Security.
6. In a file manager application of your choice, install the MagiskManager apk file, which is likely to be in the Download folder.
7. Turn off Unknown Sources if required

Configuring Magisk

1. Run Magisk Manager
2. Click on the Status burger menu and go to Settings
3. Enable Magisk Hide
4. Tick Magisk Core Only Mode
5. Reboot the phone, as requested

Test for a SafetyNet pass

You can test for a SafetyNet pass either inside Magisk Manager itself (Tap to start SafetyNet check) or via third party apps such as SafetyNet test

Once you have checked that SafetyNet passes, try the application that previously gave issues - hopefully it will now run as expected!


download file now

Read more »

Wednesday, September 6, 2017

Getting Started with Robotium

Getting Started with Robotium


Just before getting started, let us discuss few things about Robotium.
Robotium can be used to test applications where source code is available and applications where only APK file is available. 

In this example, let us discuss about the case when source code is available. I will discuss the other scenario in a separate post. let us start with setting up test environment first,

Requirements :

  • Eclipse for building the Test project
  • ADT (Android Development Kit)
  • Android SDK (Software Development Kit)
  • JDK (Java Development Kit)
  • Latest Robotium Jar file


Prerequisites for Creating Test project :

  1. Install JDK, Eclipse, SDK and ADT to your system
  2. After installation, set proper path to Environment variables JAVA_HOME and ANDROID_HOME and add the SDK and JDK installation path to environment variable PATH.
  3. Download the Latest Robotium jar file from http://code.google.com/p/robotium/downloads/list 

Create a Test project in Eclipse:

  • Create the Test project by "File --> New --> Project --> Android --> Android Test Project", the window will open
    • Enter the Project Name, click on Next
    • if project source code is already available/imported in eclipse, in the "Test Target" field, choose An existing Android project and select from the list of Projects, click on Next
    • in the "Build Target", select the application development platform, suppose,  if application is developed on Android 2.2, choose API level as 8 and so on..
    • click on Finish, a new test project will be created in the Eclipse.

Add the Robotium jar file to the Project

Right click on your project, --> Build Path --> Configure Build Path --> select "Libraries" tab
Then select "Add External Jars" -> select the Robotium Jar file downloaded
go to "Order and Export" tab, select the check box next to the Robotium jar file.

Create a Test case :

  • Create New class file:
    • select Project folder  --> "src" directory --> select package, Right click on it, select "New --> Class", enter the class name and click on Finish.
    • extend your class to ActivityInstrumentationTestCase2
    • when eclipse shows error, click on import statement, which will import android.test.ActivityInstrumentationTestCase2
    • copy the following code to your project and do modifications when required
 1: public class <your class> extends ActivityInstrumentationTestCase2{ //no need to copy this line 
 2: private Solo solo;
 3: // replace the "SampleTest()" with your class name ex: MyTest()
 4: // replace "MainActivity.class" with your application projects starting activity class name 
 5: public SampleTest() { 
 6: super(MainActivity.class);
 7: }
 8: //setUp() is run before a test case is started. This is where the solo object is created. 
 9: @Override
 10: public void setUp() throws Exception {
 11: solo = new Solo(getInstrumentation(), getActivity());
 12: }
 13: //tearDown() is run after a test case has finished. 
 14: //finishOpenedActivities() will finish all the activities that have been opened during the test execution.
 15: @Override
 16: public void tearDown() throws Exception {
 17: solo.finishOpenedActivities();
 18: }
 19: // your test case starts here... 
 20: // Test case name starts with keyword "test"followed by your Test case name in Capital Letter
 21: //here, "MyFirstTestcase" is the test case name, change this with your desired name . 
 22: public void testMyFirstTestcase() throws Exception {
 23: // write your test code
 24: } 
 25: } //no need to copy this line

Now you can write your Test case in the test block

Next : How to Run the Test? -- see the Post 


download file now

Read more »

Sunday, September 3, 2017

Getting into and exiting Recovery and Download Mode on the Samsung Galaxy S8 S8

Getting into and exiting Recovery and Download Mode on the Samsung Galaxy S8 S8


Just a few notes on getting into the various boot modes on the Samsung Galaxy S8 and S8+.

Recovery Mode

From Power Off:

1. Hold down the Volume Up and Bixby buttons
2. Press the Power button until the device turns on
3. Keep Volume Up and Bixby held down until the blue "Installing System Update" screen is displayed

After checking for an update, we will get into the Android Recovery mode.

Use the Volume Up & Down buttons to move up or down the list (or scroll on the display) and use Power to select.

Download Mode

From Power Off:


1. Hold down the Volume Down and Bixby buttons
2. Press the Power button until the device turns on

Once powered on, we need to press Volume Up to continue into Download Mode.

To power the device off, hold down Volume Down, Bixby and the Power button for around 6 seconds. This will enter Recovery mode, from which we can reboot or power off.



download file now

Read more »