[Replicant] Extracting text messages the hard way since apps don't work.

A. F. Cano afc54 at comcast.net
Wed Sep 1 19:46:21 UTC 2021

On Mon, Aug 30, 2021 at 11:47:01PM +0200, Denis 'GNUtoo' Carikli wrote:
> On Fri, 27 Aug 2021 15:22:30 -0400
> "A. F. Cano via Replicant" <replicant at osuosl.org> wrote:
> > Does anyone have any shortcuts? already written code to do this? or
> > an app that actually works?
> F-droid has also an "archive" repository with older applications that
> are not anymore in f-droid. Note that we didn't finish yet the review
> of any applications in f-droid repositories or of the f-droid
> client[1].

Good to know that.

> ...
> We have several wiki articles that are scattered around about this
> topic, some of which need help:
> - AndroidSystemKeyMigration[2] is about application signing keys and
>   access to applications data within the Android security system.
> - DataPartition[3] is about the Data Partition.
> - BackupTheDataPartition[4] is about backuping the Data partition.
> - RestoreApplicationInternalData[5] is about restoring the data of an
>   application.

Given that the 2 phones I'm doing this with are different releases:
old phone: Cyanogenmod 10.1 (android 4.2.2)
new phone: vendor-supplied android 4.2.2
even though the're both based on android 4.2.2, I doubt it'd be a good
idea to backup/restore partitions at the FS level.  There are
differences, visible and not.  The one I found that is directly
applicable to the issue at hand: database versions, might aleady be
causing me problems.

For the new phone,


int name="db_version" value="732"

for the old phone:

int name="db_version" value="706"

> The last one may be able to help you here, and the other ones could
> help understanding better the context or making a backup of the data
> partition to more easily work on it.
> As for restoring data to non-rooted phones, it might be possible to
> (ab)use backup permissions and/or (ab)use adb backup to do that once
> you managed to backup application data in this way. We didn't have the
> time to investigate these, but we would be interested in knowing more
> and/or getting some contributions to the wiki about that.

I've already managed to use adb shell on the non-rooted phone (the new
one) and then su to get access to directories and files not accessible
with adb.  I'm taking lots of detailed notes, so I might have something
useful for the wiki at some point.
> PS: If you want to contribute information on the wiki (that would be
>     super useful) you need to first create an account on it and then ask
>     us (in this mailing list or on IRC for instance) to grant wiki
>     editions permissions to your account.

I'll do that when I have some useful notes to contribute.  Thanks.

In the meantime, I have found where the call log is.  It is in:


in a table called "calls".  After extracting this file to the computer
(this (old) phone is rooted, so it was possible to do it directly with adb):

sqlite> .schema calls
CREATE TABLE calls (_id INTEGER PRIMARY KEY AUTOINCREMENT,number TEXT,date INTEGER,duration INTEGER,type INTEGER,new INTEGER,name TEXT,numbertype INTEGER,numberlabel TEXT,countryiso TEXT,voicemail_uri TEXT,is_read INTEGER,geocoded_location TEXT,lookup_uri TEXT,matched_number TEXT,normalized_number TEXT,photo_id INTEGER NOT NULL DEFAULT 0,formatted_number TEXT,_data TEXT,has_content INTEGER,mime_type TEXT,source_data TEXT,source_package TEXT,state INTEGER);

The schema on both phones is identical, so I figured if I dump the

> sqlite3 contacts2.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .output call-log.sql
sqlite> .dump --preserve-rowids --newlines
sqlite> .exit

I could then import it into the equivalent contacts2.db on the new
phone.  The first attempt gave lots of

/tmp/call-log.sql:15946: INSERT failed: datatype mismatch
/tmp/call-log.sql:15947: expected 24 columns but found 1 - filling the rest with NULL
/tmp/call-log.sql:15947: INSERT failed: datatype mismatch

This is how the import operation ended, even though I had set .bail on
and .echo on.

Looking over the dumped sql file, I see it has lots of CREATE TABLE and
other parts that shouldn't be necessary since the databases and tables
are supposedly created already in the new phone, so I deleted everything
there that was not an INSERT INTO calls.  The contact list has already
been imported via DAVDroid.

I ended up deleting everything dealing with the following tables:

contacts, raw_contacts, stream_items, stream_item_photos, photo_files,
packages, mimetypes, data, phone_lookup, name_lookup, nickname_lookup,
visible_contacts, default_directory, voicemail_status, properties,
directories, data_usage_stat, v1_settings, sqlite_stat1, sqlite_master,
search_index_content, search_index_segments, search_index_segdir,
search_index_docsize, search_index_stat, sqlite_sequence,

Only retaining the INSERT INTO calls VALUES(...) lines.  Then tried

> sqlite3 contacts2.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .echo on
sqlite> .bail on
.bail on
sqlite> .import call-log-insert-only.sql calls
.import call-log-insert-only.sql calls
call-log-insert-only.sql:1: expected 24 columns but found 1 - filling the rest with NULL
call-log-insert-only.sql:1: INSERT failed: datatype mismatch
call-log-insert-only.sql:501: expected 24 columns but found 1 - filling the rest with NULL
call-log-insert-only.sql:501: INSERT failed: datatype mismatch
sqlite> .exit

Even with .bail on and .echo on I don't get any useful detail about why
it failed, and it didn't bail after the first failure, it gave the same
error message 501 times.

I'll keep investigating.  Does anyone see something obvious?


> References:
> -----------
> [1]https://redmine.replicant.us/projects/replicant/wiki/F-DroidAndApplications
> [2]https://redmine.replicant.us/projects/replicant/wiki/AndroidSystemKeyMigration
> [3]https://redmine.replicant.us/projects/replicant/wiki/DataPartition
> [4]https://redmine.replicant.us/projects/replicant/wiki/BackupTheDataPartition
> [5]https://redmine.replicant.us/projects/replicant/wiki/RestoreApplicationInternalData
> Denis.

More information about the Replicant mailing list