WEBVTT

00:00:01.822 --> 00:00:06.002 align:middle line:84%
OK, we're going to
play some games now.

00:00:06.002 --> 00:00:09.032 align:middle line:84%
I am going to show you
these slides on how

00:00:09.032 --> 00:00:10.174 align:middle line:90%
to execute these queries.

00:00:10.174 --> 00:00:11.882 align:middle line:84%
And then we're going
to play a game where

00:00:11.882 --> 00:00:14.042 align:middle line:84%
I'm going to make
mistakes and point out

00:00:14.042 --> 00:00:17.832 align:middle line:84%
to you what is going wrong
before you do this lab.

00:00:17.832 --> 00:00:21.482 align:middle line:84%
What you see in black font
and bold, SELECT and FROM,

00:00:21.482 --> 00:00:23.342 align:middle line:90%
those are not optional.

00:00:23.342 --> 00:00:26.862 align:middle line:84%
Every single query
must have that.

00:00:26.862 --> 00:00:32.262 align:middle line:84%
If I did select star
from ZKIKMESSAGE,

00:00:32.262 --> 00:00:34.509 align:middle line:84%
that would provide every
single column possible

00:00:34.509 --> 00:00:35.342 align:middle line:90%
because it's a star.

00:00:35.342 --> 00:00:36.992 align:middle line:90%
It's a wild card.

00:00:36.992 --> 00:00:38.799 align:middle line:90%
Keep that in mind.

00:00:38.799 --> 00:00:40.382 align:middle line:84%
This tool that you're
looking at here,

00:00:40.382 --> 00:00:42.452 align:middle line:84%
this is the DB
Browser for SQLite.

00:00:42.452 --> 00:00:43.572 align:middle line:90%
It is free.

00:00:43.572 --> 00:00:45.132 align:middle line:90%
It is amazing.

00:00:45.132 --> 00:00:48.382 align:middle line:90%
Look at the colorization of it.

00:00:48.382 --> 00:00:51.212 align:middle line:84%
So we could see SELECT and
FROM are the same color.

00:00:51.212 --> 00:00:53.602 align:middle line:84%
It looks like all the
columns are one color.

00:00:53.602 --> 00:00:55.282 align:middle line:84%
And then the tables
are different.

00:00:55.282 --> 00:00:58.522 align:middle line:84%
If the colors don't match,
you probably have a typo,

00:00:58.522 --> 00:01:00.922 align:middle line:84%
or you're making something
up that does not exist.

00:01:00.922 --> 00:01:03.042 align:middle line:90%
It's a great clue.

00:01:03.042 --> 00:01:08.872 align:middle line:84%
What's also important is, if
you put a comma before from,

00:01:08.872 --> 00:01:10.672 align:middle line:84%
you're going to get a
crazy looking error.

00:01:10.672 --> 00:01:11.872 align:middle line:84%
And I'm going to show
you what this error is

00:01:11.872 --> 00:01:12.932 align:middle line:90%
going to look like.

00:01:12.932 --> 00:01:14.062 align:middle line:90%
So I am going to--

00:01:17.782 --> 00:01:20.050 align:middle line:90%
I'm going to export--

00:01:20.050 --> 00:01:21.967 align:middle line:84%
I'm going to choose an
easy table of interest.

00:01:27.602 --> 00:01:28.807 align:middle line:90%
Which one shall I choose?

00:01:32.272 --> 00:01:34.522 align:middle line:84%
I don't want to choose
anything that's super enormous.

00:01:44.979 --> 00:01:46.062 align:middle line:90%
OK this looks very random.

00:01:46.062 --> 00:01:47.812 align:middle line:84%
I don't know why that
table's called Jane,

00:01:47.812 --> 00:01:50.342 align:middle line:84%
but this is the one
we're going to go for.

00:01:50.342 --> 00:01:52.382 align:middle line:84%
I'm going to export just
a table of interest.

00:01:55.082 --> 00:01:58.252 align:middle line:84%
And we're going to examine
this outside of the tool.

00:01:58.252 --> 00:01:59.752 align:middle line:84%
I want to choose
one that I've never

00:01:59.752 --> 00:02:03.472 align:middle line:84%
seen before so you can see how
mistakes are actually made.

00:02:03.472 --> 00:02:07.139 align:middle line:84%
So I'm saving it to my desktop,
and I'm getting rid of it.

00:02:07.139 --> 00:02:08.222 align:middle line:90%
You don't have to do this.

00:02:08.222 --> 00:02:09.842 align:middle line:90%
You're just going to do the lab.

00:02:09.842 --> 00:02:11.552 align:middle line:84%
Under Mobile Device,
Tools, I'm going

00:02:11.552 --> 00:02:15.292 align:middle line:90%
to choose DB Browser for SQLite.

00:02:15.292 --> 00:02:17.762 align:middle line:84%
Now, this next step
is very important.

00:02:17.762 --> 00:02:19.282 align:middle line:90%
Do not choose New.

00:02:19.282 --> 00:02:20.672 align:middle line:90%
This is not a forensic tool.

00:02:20.672 --> 00:02:22.972 align:middle line:84%
This tool lets you
edit databases.

00:02:22.972 --> 00:02:25.317 align:middle line:84%
If you choose new, you're
going to overwrite your data.

00:02:25.317 --> 00:02:26.317 align:middle line:90%
You want to choose Open.

00:02:30.022 --> 00:02:33.512 align:middle line:90%
And I'm going to choose tts.db.

00:02:33.512 --> 00:02:37.112 align:middle line:84%
Now, on the next tab,
under Browse Data,

00:02:37.112 --> 00:02:39.347 align:middle line:90%
we have this table called Jane.

00:02:39.347 --> 00:02:41.972 align:middle line:84%
Not really sure why it's called
that, but this is what we have.

00:02:41.972 --> 00:02:46.322 align:middle line:84%
And in here, it looks like we
have text, paths, update_time,

00:02:46.322 --> 00:02:48.002 align:middle line:90%
storage_type, and text_type.

00:02:48.002 --> 00:02:49.992 align:middle line:90%
Now, this is a perfect example.

00:02:49.992 --> 00:02:55.172 align:middle line:84%
If you were only looking to
see if someone reported oil

00:02:55.172 --> 00:02:57.332 align:middle line:84%
on a road, you don't
have to write a query.

00:02:57.332 --> 00:03:01.962 align:middle line:84%
You could simply type oil, and
then you have those results.

00:03:01.962 --> 00:03:04.412 align:middle line:84%
So you can leverage the
tool very simply like this

00:03:04.412 --> 00:03:06.302 align:middle line:84%
if you're just looking
for a quick answer.

00:03:06.302 --> 00:03:09.962 align:middle line:84%
But if you're looking to include
something, what you want to do

00:03:09.962 --> 00:03:11.552 align:middle line:90%
is go to Execute SQL.

00:03:11.552 --> 00:03:14.405 align:middle line:90%
So we're now over in this tab.

00:03:14.405 --> 00:03:15.572 align:middle line:90%
And I just want to show you.

00:03:15.572 --> 00:03:24.292 align:middle line:84%
So I would type SELECT star FROM
Jane or whatever that table is.

00:03:24.292 --> 00:03:25.602 align:middle line:90%
Do you notice my colorization?

00:03:25.602 --> 00:03:27.672 align:middle line:90%
Everything is happy.

00:03:27.672 --> 00:03:32.429 align:middle line:84%
I press Play, I get results,
the world is a happy place.

00:03:32.429 --> 00:03:33.512 align:middle line:90%
Now, where things go bad--

00:03:37.112 --> 00:03:38.622 align:middle line:90%
boom.

00:03:38.622 --> 00:03:42.342 align:middle line:84%
Underlying red squiggles
means very unhappy with you.

00:03:42.342 --> 00:03:44.172 align:middle line:90%
You have to read the message.

00:03:44.172 --> 00:03:50.502 align:middle line:84%
And down here, it says,
near comma syntax error.

00:03:50.502 --> 00:03:53.152 align:middle line:90%
That means something's wrong.

00:03:53.152 --> 00:03:55.522 align:middle line:84%
So something doesn't
make sense here.

00:03:55.522 --> 00:03:57.922 align:middle line:90%
It does not like that comma.

00:03:57.922 --> 00:04:00.587 align:middle line:84%
You get rid of it, everything
goes back to happy.

00:04:00.587 --> 00:04:01.962 align:middle line:84%
Now, what I'm
going to do here is

00:04:01.962 --> 00:04:03.629 align:middle line:84%
I'm going to get rid
of my star, and I'm

00:04:03.629 --> 00:04:15.412 align:middle line:84%
going to type text comma
path comma update_time.

00:04:15.412 --> 00:04:16.972 align:middle line:90%
And now, it narrowed it down.

00:04:16.972 --> 00:04:20.752 align:middle line:84%
It will put your columns in
the order that you have up top.

00:04:20.752 --> 00:04:23.362 align:middle line:84%
If I put the comma before
from, its really unhappy again.

00:04:23.362 --> 00:04:25.257 align:middle line:84%
It's, like, no, I can't
show you anything.

00:04:25.257 --> 00:04:26.632 align:middle line:84%
So just make sure
you watch that.

00:04:31.098 --> 00:04:33.442 align:middle line:84%
If you don't know
what these items mean,

00:04:33.442 --> 00:04:37.072 align:middle line:84%
this is where you have to do
some research and testing.

00:04:37.072 --> 00:04:39.502 align:middle line:84%
It's very, very important
that you don't just make up

00:04:39.502 --> 00:04:40.792 align:middle line:90%
what you think it means.

00:04:40.792 --> 00:04:43.282 align:middle line:84%
You need to do some
testing in this point.

00:04:43.282 --> 00:04:44.969 align:middle line:84%
Here we're looking
at an iOS device.

00:04:44.969 --> 00:04:45.802 align:middle line:90%
How do we know that?

00:04:45.802 --> 00:04:48.862 align:middle line:84%
We have Z, and we see that
time stamp starts with 4.
