A couple sqlite notes:
default
Running sqlite3 interactively
-----------------------------
sqlite3
Run sqlite3 interactively
.mode column
.headers on
Turn on columns and headers for better
readability.
create table tester (id integer primary key, notes text);
Create a table called 'tester' with two fields: id and notes.
create view seealltester AS select * from tester;
Create a view called: seealltester, that displays
everything from table tester.
.tables
Display all tables and views in a database
.tables t%
Display an tables that start with a t.
.indices tester
Display indexes for tester table.
.schema tester
Show syntax table was created with.
insert into tester (notes) values('Read: Gray Hat Python');
Insert some data into the tester table, inside the notes field.
select * from tester;
See the inserted data.
.databases
Show all databases.
.output /bkup/tester.sql
.dump
.output stdout
1) Redirects any new output to the file: tester.sql
2) Dump all data in current database
3) Switch back to outputting to stdout, instead of a file.
.read /bkup/tester.sql
Read a file into the current database.
.show
Displays info about current database settings.
.seperator ,
Change the seperator from the default '|' to ','
Useful for importing csv files.
.import myCsvFile.csv
Imports a csv file into the current database.
drop index myindex
Deletes index called: myindex
drop table tester
Deletes the tester table.
drop trigger bangbang
Drops a trigger.
drop view crapview
Drops a view.
.exit
Get the hell out of sqlite3
end-sqlite3-interactive
-----------------------
Working with sqlite3 from the shell
-----------------------------------
sqlite3 --help
Display help
sqlite3 -header -column tester.db 'select * from tester'
Display all from table tester, using columns and headers
sqlite3 tester.db '.dump' > /bkup/newestbkup
Dump a database
sqlite3 tester.db '.dump' | gzip -c /bkup/newestbkup.gz
Dump tester database and gzip it.
sqlite3 tester.db < mybestdatabase.sql
Import a sql file into database tester.
gunzip -c /bkup/newestbkup.gz | sqlite3 tester.db
Import database gunziping it first
end-sqlite-from-shell
---------------------
Some notes on extracting texts from an iphone backup:
-----------------------------------------------------
First get a backup of your phone. If you don't know
how just use itunes. If you are on a windows machine
it should back it up to something like:
c:\users\yourUserName\appdata\roaming\apple computer\
mobilesync\backup\someLongRetardedFolderName\
Inside here you will see a bunch of crap. It seems that
the iphone's text messages are stored in the one that
starts with:
3d0d
I'm not sure if it always is, but so far it has for me.
This may change depending on iOS version.
FYI: Contacts start with:
31bb
If you have a hex editor you could open this file and
see that it starts with: SQLite format 3.
If you don't have a hex editor, you could try an text
editor. If you file is not that big you could even
view it in notepad. Your messages are in here, and
you could see them by starting towards the bottom of
the file, but there is a bunch of other crap in here
so we are going to use sqlite3 to see them instead.
I am not on a windows box, but there is a windows
version. Actually there is one for Cygwin also,
so use that if you use cygwin. If you don't, start!
Else just jump on a *nix box and let's get after it.
Let's start by copying it to a reasonable name, so
we can keep the orginal intact:
cp 3d0dblabblahblahblahblahblah iphtexts.sqlite
sqlite3 iphtexts.sqlite
This will open the database inside sqlite3, in
interactive mode. We could do all from the command
line also, perhaps another time.
.tables
This will list the tables inside the database:
_SqliteDatabaseProperties chat_message_join
attachment handle
chat message
chat_handle_join message_attachment_join
.tables mess%
Seeing some hand message starting the name. We can
use this to only show the tables starting with 'mess'.
.schema message
Just out of curiousity; we can see how the message
table was created.
pragma table_info(message);
This will show all column names in a table.
select * from message;
This will show us all the texts inside the message
table. It is in a rather ugly format, but at least
I can tell it is my text messages.
.mode line
This will turn on line mode, which will unscramble
the texts by putting each field on a seperate
line.
select * from message;
Let's look at this again, now that line mode is on.
First off we can see that the 'text' field has the
actual messages that we want.
Scrolling through, it looks like the iphone is using
'handle_id' to know who we are texting. As it is the
same for messages that came from the same person.
I'm only interested in extracting texts from one
person at the momment, and I can see that their
handle_id is: 51
*** If you are using a program like 'screen' or
'tmux' you should be able to scroll up/down.
If your terminal: xterm, urxvt, aterm, etc
does not allow you to scroll, you may try
shift+PageUp/PageDown, to scroll. On freebsd
you can hit Scroll Lock (Scr Lk) and then
PageUp/PageDown, hitting scroll lock again
to exit. ***
select * from message limit 1;
To get a cleaner look at one record looks like.
Letting us see all the fields.
select text from message;
This shows all the text messages unsorted.
select handle_id from message;
This will show all the handles, but it is giving us
duplicates.
select distinct handle_id from message;
This will show the handle_id of everyone that
has texted you. Does not actually show who it
is, but let's us grab text from one person only.
The distinct keyword removes duplicates from the
query.
select distinct handle_id from message limit 15;
You can use limit, to show a limited amount of
records.
select * from handle;
Remeber? There was a handle table also.
Here you can see phone numbers under 'id'.
The ROWID matches the handle_id.
Use:
.tables
to see all the tables.
select * from handle where id like '%0055%';
This will match anyones number ending in 0055.
So we can find the ROWID to use as the handle_id.
There may be more than one record, not sure if it
has to do with changing someones name, or adding
them as a contact after getting a message first.
In any case this will let you know what handle_id
to use. There are no contact names in here, just
the phone numbers. Contacts are stored in another
file!
select text from message where handle_id='51' limit 15;
Display the first 15 messages from handle_id 51. If you
can not fit 15 on your screen change it to 5, 10, etc.
This will let me see the first message from that person
to make sure I have the right handle_id. I could see
the last message earlier, as without a limit it goes to
the last.
*** Keep in mind that in bash and in cygwin you can hit:
Control+L (lowercase 'l') to clear the screen, if things
are getting to cluttered for you. ***
.mode column
Change to column mode before we export. Else we will have
' text =' Starting on every text message.
This will bunch all messages together, So if you prefer
them seperate, don't change it. Then you could use awk,
cut, vim, emacs, or any other program to remove the
unwanted ' text = ' and all the message would come out
seperated by a blank line.
*** I did not do this because I want it to say:
' text = 'at the start of every message. Then I can run:
awk 'BEGIN { FS= " = "}; {print $2}' jm-txts.txt > jm-txts-awked.txt
To get it prettier looking.
.output /home/joedaddy/bkup/iphone/joemammy-texts.txt
Now that we know how to get the texts, export them.
This line redirects the output from the screen to a file.
I have a bkup folder with an iphone directory and am
going to save my file there.
select text from message where handle_id='51';
Shoot the info I want to my bkup file. Nothing
should be printed to the screen as we are now
printing to a file.
.output stdout
Change output back to stdout (terminal) so we don't
add more stuff to our file if we dig around some more.
end-notes-on-extracting-texts-from-an-iphone-backup
---------------------------------------------------
^
default