Lab 4.2, we are going to look at an iOS 15 database.
We are going to provide you with queries
that worked before iOS 14 and 15,
and you need to fix them to make this query work.
And then answer key questions about Photos.sqlite.
I'm going to load this into DB Browser for SQLite.
I'm going to click Open Database, Lab4.2,
Photos.sqlite.
What you are going to have to do is
go in and start browsing the data
and see if you can find where data is even stored.
Because it could be a table difference.
It's going to be all kinds of things.
That is what I recommend for your starting point.
Make sure you are leveraging the Electronic
Workbook because you can copy and paste
the queries if you want to.
So if we go to Exercises, 4, 4.2,
question one is, take the query provided
for parsing iOS 13 and prior databases, and convert it.
So we actually provide you the query.
I recommend you just copy it and paste it in to DB Browser
because then it's telling you, hey, this is what used to work.
And then it's really unhappy, but it's telling you
something very important.
There is no such table called zgenericasset anymore.
So now you can go into Browse, and find where
you think that data lives.
We are now going to review lab 4.2.
If you're still working on it, simply
press pause, and then resume when you're ready.
In question one, we tell you that we
provide a query that worked for iOS 13 and prior.
When you try that query on this iOS 15,
you get the message that no table zgenericasset exists.
So what you need to do is consider a few things.
If you look at some of the file column names,
you can always look for a table that includes that.
You could go back into a tool and do a hex search on it.
But what I want to do is look at anything like this.
It used to say zgenericasset, and now it's ZASSET.
So what you have to do to make this work is,
you have to change where it says zgenericasset to ZASSET.
So if we go in here, and we simply remove the name generic.
It works.
A simple change, just the wording of one table
changes everything.
Now we want to look at hidden images.
We talked about this a little bit in section 3.
How many images were hidden from the gallery on the device?
So we can see right here, we have Is Hidden.
So what I'm going to do is, I'm going to sort.
I'm going to do order by Is Hidden, ascending.
And we have two pictures are Hidden, IMG_0010 and IMG_0011.
All right, now we have another situation
where we provide a query.
On May 24, 2020 at 15:46, a moment
was captured on the device.
We provide the file of interest.
Where was the photo taken, city and state?
So I'm going to go back into the Workbook.
I'm going to grab that query, the one that worked prior.
I'm going to Copy it.
I'm going to put it in DB Browser, press a New Tab,
and you're going to see it's broken again.
ZGENERICASSET is still there.
So what you can do is, we can do a Find and Replace in here.
I'm going to actually--
I think I can do just Control-F. And I'm
going to do zgenericasset, but spell it correctly.
And we need to change it.
That is not what I want to do.
There is a way to do find and replace.
Tools.
No, so OK.
I don't know where the find and replace is,
so what I'm going to do I'm going
to copy this I'm going to put it into a text file,
do it outside and bring it in.
I know there's a find and replace in there somewhere,
but it's not working for me.
So I'm going to create a text document, Query, Paste.
And I'm going to do--
a Find and Replace z-- let me Zoom in--
genericasset.
And the reason you have to do this
is, there are multiple instances of it.
Otherwise, you're going to manually
be typing it, so zasset.
Replace All.
And now I'm going to do Control-A, Control-C,
and I'm going to put this into DB Browser.
So I'll create a New Tab, Paste, there we go.
So now we know the file of interest is 56054f45.
So we could sort by this.
We could do order by.
I'm going to order by file name where--
actually, I'll just do order by filename ascending.
That should be good.
We can also do, if we need to search by Where.
But as long as I do that, it should be able to come across.
Quickly, the 560 one, it's right here.
And now we need these coordinates.
Now, you're going to see, you have latitude, longitude,
but then you also have approximate latitude
and longitude.
Both of them will work.
So I'm going to copy this.
And I'm going to open good old Google Maps.
And I'm going to paste, put a comma, go grab the other one.
Let's find it.
So we can see here, it looks like a whole bunch
of beach houses.
It shows that it's on Pine Island.
And you can Zoom in on this.
You can do many things, but let's Zoom in.
So this is actually in Outer Banks North Carolina.
We could see its Corrolla, North Carolina to be specific.
But the house we were staying in was called Mariner's Compass.
You would also find associated Wi-Fi to it.
It's very accurate information.
And I'm going to not save my query.
So that's one way of doing it.
Now, I want to show you the Class Notebook.
So if we go out to for585.com/notebook,
you're going to see a bunch of queries.
You can just do View the Notebook.
You can Join the Notebook.
Firefox and Cloud-- these are ones
that I use when I teach Forensics 500,
but they would work on mobile devices if you find them.
Here, we have Android Queries.
Here we have Scripts, Blogs, and Groups
that I recommend you join and follow, the Free Books,
and then iOS SQL queries.
So as you look down in iOS SQL queries,
you're going to find a lot for Photos.sqlite,
depending on what you want to do with them.
And you'll see the different versions in iOS and more.
So remember, for585.com/notebook.