WEBVTT

00:00:01.733 --> 00:00:07.563 align:middle line:84%
Lab 4.2, we are going to
look at an iOS 15 database.

00:00:07.563 --> 00:00:09.543 align:middle line:84%
We are going to provide
you with queries

00:00:09.543 --> 00:00:12.123 align:middle line:84%
that worked before
iOS 14 and 15,

00:00:12.123 --> 00:00:15.273 align:middle line:84%
and you need to fix them
to make this query work.

00:00:15.273 --> 00:00:18.903 align:middle line:84%
And then answer key questions
about Photos.sqlite.

00:00:18.903 --> 00:00:24.703 align:middle line:84%
I'm going to load this
into DB Browser for SQLite.

00:00:24.703 --> 00:00:33.573 align:middle line:84%
I'm going to click
Open Database, Lab4.2,

00:00:33.573 --> 00:00:36.683 align:middle line:90%
Photos.sqlite.

00:00:36.683 --> 00:00:38.423 align:middle line:84%
What you are going
to have to do is

00:00:38.423 --> 00:00:40.583 align:middle line:84%
go in and start
browsing the data

00:00:40.583 --> 00:00:43.283 align:middle line:84%
and see if you can find
where data is even stored.

00:00:43.283 --> 00:00:44.933 align:middle line:84%
Because it could be
a table difference.

00:00:44.933 --> 00:00:46.475 align:middle line:84%
It's going to be
all kinds of things.

00:00:46.475 --> 00:00:49.343 align:middle line:84%
That is what I recommend
for your starting point.

00:00:49.343 --> 00:00:54.703 align:middle line:84%
Make sure you are
leveraging the Electronic

00:00:54.703 --> 00:00:56.863 align:middle line:84%
Workbook because you
can copy and paste

00:00:56.863 --> 00:00:58.433 align:middle line:90%
the queries if you want to.

00:00:58.433 --> 00:01:04.493 align:middle line:84%
So if we go to
Exercises, 4, 4.2,

00:01:04.493 --> 00:01:07.133 align:middle line:84%
question one is, take
the query provided

00:01:07.133 --> 00:01:11.813 align:middle line:84%
for parsing iOS 13 and prior
databases, and convert it.

00:01:11.813 --> 00:01:13.943 align:middle line:84%
So we actually
provide you the query.

00:01:13.943 --> 00:01:18.783 align:middle line:84%
I recommend you just copy it
and paste it in to DB Browser

00:01:18.783 --> 00:01:22.643 align:middle line:84%
because then it's telling you,
hey, this is what used to work.

00:01:22.643 --> 00:01:24.803 align:middle line:84%
And then it's really
unhappy, but it's telling you

00:01:24.803 --> 00:01:26.793 align:middle line:90%
something very important.

00:01:26.793 --> 00:01:30.003 align:middle line:84%
There is no such table
called zgenericasset anymore.

00:01:30.003 --> 00:01:33.605 align:middle line:84%
So now you can go into
Browse, and find where

00:01:33.605 --> 00:01:34.688 align:middle line:90%
you think that data lives.

00:01:37.443 --> 00:01:39.411 align:middle line:84%
We are now going
to review lab 4.2.

00:01:39.411 --> 00:01:40.953 align:middle line:84%
If you're still
working on it, simply

00:01:40.953 --> 00:01:44.343 align:middle line:84%
press pause, and then
resume when you're ready.

00:01:44.343 --> 00:01:46.833 align:middle line:84%
In question one,
we tell you that we

00:01:46.833 --> 00:01:50.283 align:middle line:84%
provide a query that worked
for iOS 13 and prior.

00:01:50.283 --> 00:01:53.283 align:middle line:84%
When you try that
query on this iOS 15,

00:01:53.283 --> 00:01:58.273 align:middle line:84%
you get the message that no
table zgenericasset exists.

00:01:58.273 --> 00:02:01.053 align:middle line:84%
So what you need to do
is consider a few things.

00:02:01.053 --> 00:02:05.133 align:middle line:84%
If you look at some of
the file column names,

00:02:05.133 --> 00:02:07.863 align:middle line:84%
you can always look for a
table that includes that.

00:02:07.863 --> 00:02:10.363 align:middle line:84%
You could go back into a tool
and do a hex search on it.

00:02:10.363 --> 00:02:14.353 align:middle line:84%
But what I want to do is
look at anything like this.

00:02:14.353 --> 00:02:19.263 align:middle line:84%
It used to say zgenericasset,
and now it's ZASSET.

00:02:19.263 --> 00:02:21.873 align:middle line:84%
So what you have to do
to make this work is,

00:02:21.873 --> 00:02:26.763 align:middle line:84%
you have to change where it
says zgenericasset to ZASSET.

00:02:26.763 --> 00:02:30.803 align:middle line:84%
So if we go in here, and we
simply remove the name generic.

00:02:34.093 --> 00:02:35.473 align:middle line:90%
It works.

00:02:35.473 --> 00:02:38.863 align:middle line:84%
A simple change, just
the wording of one table

00:02:38.863 --> 00:02:41.333 align:middle line:90%
changes everything.

00:02:41.333 --> 00:02:43.163 align:middle line:84%
Now we want to look
at hidden images.

00:02:43.163 --> 00:02:46.133 align:middle line:84%
We talked about this a
little bit in section 3.

00:02:46.133 --> 00:02:49.983 align:middle line:84%
How many images were hidden
from the gallery on the device?

00:02:49.983 --> 00:02:52.473 align:middle line:84%
So we can see right
here, we have Is Hidden.

00:02:52.473 --> 00:02:55.453 align:middle line:84%
So what I'm going to do
is, I'm going to sort.

00:02:55.453 --> 00:03:04.513 align:middle line:84%
I'm going to do order
by Is Hidden, ascending.

00:03:04.513 --> 00:03:16.803 align:middle line:84%
And we have two pictures are
Hidden, IMG_0010 and IMG_0011.

00:03:16.803 --> 00:03:19.263 align:middle line:84%
All right, now we
have another situation

00:03:19.263 --> 00:03:21.003 align:middle line:90%
where we provide a query.

00:03:21.003 --> 00:03:24.303 align:middle line:84%
On May 24, 2020
at 15:46, a moment

00:03:24.303 --> 00:03:26.583 align:middle line:90%
was captured on the device.

00:03:26.583 --> 00:03:29.163 align:middle line:90%
We provide the file of interest.

00:03:29.163 --> 00:03:31.723 align:middle line:84%
Where was the photo
taken, city and state?

00:03:31.723 --> 00:03:34.643 align:middle line:84%
So I'm going to go
back into the Workbook.

00:03:34.643 --> 00:03:38.723 align:middle line:84%
I'm going to grab that query,
the one that worked prior.

00:03:38.723 --> 00:03:40.823 align:middle line:90%
I'm going to Copy it.

00:03:40.823 --> 00:03:45.643 align:middle line:84%
I'm going to put it in DB
Browser, press a New Tab,

00:03:45.643 --> 00:03:47.393 align:middle line:84%
and you're going to
see it's broken again.

00:03:47.393 --> 00:03:49.863 align:middle line:90%
ZGENERICASSET is still there.

00:03:49.863 --> 00:03:59.555 align:middle line:84%
So what you can do is, we can
do a Find and Replace in here.

00:03:59.555 --> 00:04:00.513 align:middle line:90%
I'm going to actually--

00:04:00.513 --> 00:04:03.743 align:middle line:84%
I think I can do just
Control-F. And I'm

00:04:03.743 --> 00:04:10.883 align:middle line:84%
going to do zgenericasset,
but spell it correctly.

00:04:17.503 --> 00:04:18.553 align:middle line:90%
And we need to change it.

00:04:18.553 --> 00:04:19.803 align:middle line:90%
That is not what I want to do.

00:04:19.803 --> 00:04:21.458 align:middle line:84%
There is a way to
do find and replace.

00:04:21.458 --> 00:04:21.958 align:middle line:90%
Tools.

00:04:48.733 --> 00:04:49.663 align:middle line:90%
No, so OK.

00:04:49.663 --> 00:04:50.923 align:middle line:84%
I don't know where the
find and replace is,

00:04:50.923 --> 00:04:51.613 align:middle line:84%
so what I'm going
to do I'm going

00:04:51.613 --> 00:04:53.923 align:middle line:84%
to copy this I'm going to
put it into a text file,

00:04:53.923 --> 00:04:55.915 align:middle line:90%
do it outside and bring it in.

00:04:55.915 --> 00:04:58.123 align:middle line:84%
I know there's a find and
replace in there somewhere,

00:04:58.123 --> 00:05:00.353 align:middle line:90%
but it's not working for me.

00:05:00.353 --> 00:05:07.563 align:middle line:84%
So I'm going to create a
text document, Query, Paste.

00:05:11.843 --> 00:05:12.743 align:middle line:90%
And I'm going to do--

00:05:15.433 --> 00:05:18.743 align:middle line:84%
a Find and Replace
z-- let me Zoom in--

00:05:21.843 --> 00:05:22.676 align:middle line:90%
genericasset.

00:05:22.676 --> 00:05:24.093 align:middle line:84%
And the reason you
have to do this

00:05:24.093 --> 00:05:26.043 align:middle line:84%
is, there are multiple
instances of it.

00:05:26.043 --> 00:05:27.501 align:middle line:84%
Otherwise, you're
going to manually

00:05:27.501 --> 00:05:30.263 align:middle line:90%
be typing it, so zasset.

00:05:30.263 --> 00:05:32.343 align:middle line:90%
Replace All.

00:05:32.343 --> 00:05:36.263 align:middle line:84%
And now I'm going to do
Control-A, Control-C,

00:05:36.263 --> 00:05:39.423 align:middle line:84%
and I'm going to put
this into DB Browser.

00:05:39.423 --> 00:05:44.163 align:middle line:84%
So I'll create a New
Tab, Paste, there we go.

00:05:44.163 --> 00:05:49.703 align:middle line:84%
So now we know the file
of interest is 56054f45.

00:05:49.703 --> 00:05:51.023 align:middle line:90%
So we could sort by this.

00:05:51.023 --> 00:05:52.463 align:middle line:90%
We could do order by.

00:05:52.463 --> 00:06:04.988 align:middle line:84%
I'm going to order
by file name where--

00:06:09.033 --> 00:06:11.180 align:middle line:84%
actually, I'll just do
order by filename ascending.

00:06:11.180 --> 00:06:12.013 align:middle line:90%
That should be good.

00:06:12.013 --> 00:06:14.655 align:middle line:84%
We can also do, if we
need to search by Where.

00:06:14.655 --> 00:06:17.113 align:middle line:84%
But as long as I do that, it
should be able to come across.

00:06:17.113 --> 00:06:22.443 align:middle line:84%
Quickly, the 560
one, it's right here.

00:06:22.443 --> 00:06:23.933 align:middle line:84%
And now we need
these coordinates.

00:06:23.933 --> 00:06:27.143 align:middle line:84%
Now, you're going to see,
you have latitude, longitude,

00:06:27.143 --> 00:06:29.303 align:middle line:84%
but then you also have
approximate latitude

00:06:29.303 --> 00:06:30.143 align:middle line:90%
and longitude.

00:06:30.143 --> 00:06:31.703 align:middle line:90%
Both of them will work.

00:06:31.703 --> 00:06:34.083 align:middle line:90%
So I'm going to copy this.

00:06:34.083 --> 00:06:36.243 align:middle line:84%
And I'm going to open
good old Google Maps.

00:06:43.353 --> 00:06:47.843 align:middle line:84%
And I'm going to paste, put a
comma, go grab the other one.

00:07:01.033 --> 00:07:03.295 align:middle line:90%
Let's find it.

00:07:03.295 --> 00:07:05.253 align:middle line:84%
So we can see here, it
looks like a whole bunch

00:07:05.253 --> 00:07:06.783 align:middle line:90%
of beach houses.

00:07:06.783 --> 00:07:10.023 align:middle line:84%
It shows that it's
on Pine Island.

00:07:10.023 --> 00:07:11.623 align:middle line:90%
And you can Zoom in on this.

00:07:11.623 --> 00:07:15.623 align:middle line:84%
You can do many things,
but let's Zoom in.

00:07:15.623 --> 00:07:21.243 align:middle line:84%
So this is actually in
Outer Banks North Carolina.

00:07:21.243 --> 00:07:24.033 align:middle line:84%
We could see its Corrolla,
North Carolina to be specific.

00:07:24.033 --> 00:07:27.033 align:middle line:84%
But the house we were staying
in was called Mariner's Compass.

00:07:27.033 --> 00:07:29.703 align:middle line:84%
You would also find
associated Wi-Fi to it.

00:07:29.703 --> 00:07:31.983 align:middle line:90%
It's very accurate information.

00:07:40.745 --> 00:07:42.203 align:middle line:84%
And I'm going to
not save my query.

00:07:45.123 --> 00:07:46.373 align:middle line:90%
So that's one way of doing it.

00:07:49.093 --> 00:07:52.883 align:middle line:84%
Now, I want to show
you the Class Notebook.

00:07:52.883 --> 00:08:00.423 align:middle line:84%
So if we go out to
for585.com/notebook,

00:08:00.423 --> 00:08:02.103 align:middle line:84%
you're going to see
a bunch of queries.

00:08:02.103 --> 00:08:03.520 align:middle line:84%
You can just do
View the Notebook.

00:08:03.520 --> 00:08:05.873 align:middle line:90%
You can Join the Notebook.

00:08:05.873 --> 00:08:08.063 align:middle line:84%
Firefox and Cloud--
these are ones

00:08:08.063 --> 00:08:10.463 align:middle line:84%
that I use when I
teach Forensics 500,

00:08:10.463 --> 00:08:13.853 align:middle line:84%
but they would work on mobile
devices if you find them.

00:08:13.853 --> 00:08:16.893 align:middle line:90%
Here, we have Android Queries.

00:08:16.893 --> 00:08:19.113 align:middle line:84%
Here we have Scripts,
Blogs, and Groups

00:08:19.113 --> 00:08:25.313 align:middle line:84%
that I recommend you join
and follow, the Free Books,

00:08:25.313 --> 00:08:26.903 align:middle line:90%
and then iOS SQL queries.

00:08:26.903 --> 00:08:29.123 align:middle line:84%
So as you look down
in iOS SQL queries,

00:08:29.123 --> 00:08:31.463 align:middle line:84%
you're going to find a
lot for Photos.sqlite,

00:08:31.463 --> 00:08:34.313 align:middle line:84%
depending on what you
want to do with them.

00:08:34.313 --> 00:08:37.863 align:middle line:84%
And you'll see the different
versions in iOS and more.

00:08:37.863 --> 00:08:45.693 align:middle line:84%
So remember,
for585.com/notebook.
