Search

Recent Posts

Tags


« | Main | »

Android Nexus 4 – using Sqlite to ’empty’ Pop Email ‘trash’

By Dale Reagan | April 7, 2013

Empty POP Email ‘trash’ (Android)

Ok, after some digging and a bit of thought:

Simple solutions:

  1. Don’t use Google Android Nexus 4 Pop email…
  2. Use some other Android email for Pop access…
  3. Learn a little Sqlite3 and put together a few ‘steps’ or a script to deal with this problem – that’s what I will explore later in this post.

In my previous post on this topic I provided the output of the CSV headers for the Sqlite3 Database files that appear to be used for Pop email on the Google Android Nexus 4.  Note that this issue/post is being used with Android 4.2.2 (in early 2013) so things could be different if you are using an older/newer version of the Android OS and tools.

Prerequisites:

  1. Working Android Phone w/working POP email setup AND/Or Working AVM (Android Virtual Machine)
  2. Android SDK with working ‘adb’ connection (I’m using USB)
  3. Linux (or other) system with a working install of Sqlite3
  4. copies of the Email database files for ‘testing’ (yep, gonna ‘test’ first…)

Pull your Email DB files to your PC

In my case the files are:

/data/data/com.google.android.email/databases/EmailProviderBody.db
/data/data/com.google.android.email/databases/EmailProvider.db

If you try to ‘pull’ these files you will (most likely) get an error:

$ adb pull /data/data/com.google.android.email/databases/EmailProvider.db
remote object ‘/data/data/com.google.android.email/databases/EmailProvider.db’ does not exist

Ok, I know that it exists – what if I try a file listing?

$ adb shell ls -l /data/data/com.google.android.email/databases/EmailProvider.db
/data/data/com.google.android.email/databases/EmailProvider.db: Permission denied

Ok, this makes sense – access is restricted – so how do I get a copy for testing?

Since this phone is ‘rooted’ I:

  1. connect via adb shell
  2. then use ‘su -‘
  3. copy the file to an ‘open’ location
  4. exit su & adb
  5. now copy the file(s) via adb pull

Wait, wait – what about ‘adb root’?  Here is what I get – it suggests that you would be fine working on your AVD but guessing it would NOT work on your Android phone:

$ adb root
adbd cannot run as root in production builds

Adb example session to place files in a ‘readable’ location:

$ adb shell
shell@android:/ $ su -
root@android:/ # cp -v /data/data/com.google.android.email/databases/EmailProvider.db /sdcard/temp/EmailProvider.db
root@android:/ # exit
shell@android:/ $ exit


$ adb pull /sdcard/temp/EmailProvider.db .
2699 KB/s (1093632 bytes in 0.395s)


$ ls -l EmailProvider.db
-rw-r--r-- 1 dale dale 1093632 Mar 31 10:49 EmailProvider.db

Now cleaup/remove the file(s) from ~/temp folder:

$ adb shell rm /sdcard/temp/EmailProvider.db


Ok, time to review the table structures and figure out what to delete/remove/save.  One item of concern:

POP email passwords are stored in ‘plain text’.

This may be a non-issue for most folks but I don’t think that it is a ‘best practice’ for any Application to use ‘plain text’ for any type of password storage – JMO.  I’m sure folks will argue that since the DB file is in ‘restricted space’ that this is not a concern – I’m not buying it (and I don’t have a solution other than to strongly suggest that any sensitive data should be encrypted – and a suggestion that you don’t use your ‘phone email’ as your ‘primary email’ and don’t use a cell phone if you are concerned about privacy and/or security – cell phones, cell phone networks and most likely, cell phone applications are NOT secure or private…)

Ok, this seems to work but is not confirmed (yet) – this is a one-way op so backups are suggested…

Caveats – this method does NOT deal with email messages that have attachments!  If you take this approach then you will most likely have ‘remnant’ files laying about.  First, you have to determine which ‘mailbox’ to delete from:

$ sqlite3 EmailProvider.db
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode csv
sqlite> select * from Mailbox;
6,INBOX,INBOX,,-1,2,0,0,,0,0,0,6,1,24,25,,6,1364663778896,0,0,0,0,0,4347
7,Drafts,Drafts,,-1,2,3,0,,0,-1,0,0,1,8,25,,3,1357505032257,0,0,0,0,0,0
8,Outbox,Outbox,,-1,2,4,0,,0,-1,0,0,1,8,25,,0,1363452298650,0,0,0,0,0,0
9,Sent,Sent,,-1,2,5,0,,0,-1,0,0,1,8,25,,5,1357505032257,0,0,0,0,0,0
10,Trash,Trash,,-1,2,6,0,,0,-1,0,1266,1,8,25,,1795,1364656359354,0,0,0,0,0,0

The fields for this table (and others) are listed in a  previous post – to just review the ‘Mailbox’ table:

$ sqlite3 EmailProvider.db
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode line
sqlite> select * from Mailbox where _id == 10;
                     _id = 10
             displayName = Trash
                serverId = Trash
          parentServerId = 
               parentKey = -1
              accountKey = 2
                    type = 6
               delimiter = 0
                 syncKey = 
            syncLookback = 0
            syncInterval = -1
                syncTime = 0
             unreadCount = 1266
             flagVisible = 1
                   flags = 8
            visibleLimit = 25
              syncStatus = 
            messageCount = 1795
         lastTouchedTime = 1364656359354
            uiSyncStatus = 0
        uiLastSyncResult = 0
  lastNotifiedMessageKey = 0
lastNotifiedMessageCount = 0
              totalCount = 0
      lastSeenMessageKey = 0
Ok, the serverId 'key' may make more sense - but what you are after is the key value
that you can use with the Message table.
sqlite> select * from Mailbox where serverId == "Trash";
10,Trash,Trash,,-1,2,6,0,,0,-1,0,1266,1,8,25,,1795,1364656359354,0,0,0,0,0,0

Delete data from email database files – this is a one-way ticket! so make sure you are sure!  Note that we are deleting from the Message table but using

$ sqlite3 EmailProvider.db

sqlite> Delete from Message where mailboxKey == 10 ;
sqlite> .exit

and now we remove any ‘body’ table entry that has a value greater than zero.

$ sqlite3 EmailBody.db

sqlite> Delete from Body where messageKey > 0 ;
sqlite> .exit

Retain ‘Empty’ Email Database files to ‘reset’

Provided that Android POP Email data/files are not stored in other files/locations you can ‘reset’ your Android POP email by simply copying the ’empty’ database files (that contain your POP email setup info) over to replace your existing, real-time, POP email database files.

Caveats

There *should be* a simpler way to deal with the Android POP email Trash issue but I have not found it.  When I started this post I was thinking that I might use a script to copy the ’empty’ files into place but I have not tested this yet – perhaps someone will provide a ‘better’ way before I get a script tested/online.

If you have/use more than one POP email account then this approach may need some tweaking; I will guess that the second POP email account would use a ‘Mailbox ID’ that was different (i.e. a higher number/value.)

Another approach – use the ‘system steps’ to ‘delete an account’ but then add them back by using Sqlite3 commands [NOTE – I did NOT test this so caution is advised…], i.e. after clearing the POP email database files you create two entries Per POP email account – one for reading email and one for sending email:

INSERT INTO HostAuth VALUES(3,’pop3′,’pop.YOUR-Domain.yyy’,110,14,’EMAIL_USER’,’READ_EMAIL_PW’,NULL,0,NULL);
INSERT INTO HostAuth VALUES(4,’smtp’,’smtp.YOUR-Domain.yyy’,587,14,’EMAIL_USER’,’SEND_EMAIL_PW’,NULL,0,NULL);

<< HostAuth – Sqlite3 table structure>>
1 | CREATE TABLE HostAuth (_id integer primary key autoincrement
2 |  protocol text
3 |  address text
4 |  port integer
5 |  flags integer
6 |  login text
7 |  password text
8 |  domain text
9 |  accountKey integer
10 | certAlias text);

Other thoughts – there may also already be Sqlite3 tools in the Android ‘store’ that allow you to edit the database files on your Android device – that would probably be ‘better’ than the approaches discussed above.

Even better – perhaps the *next* POP email client for Android will provide more comprehensive data-file management and not leave you with the potential to fill your file system with ‘trash emails’…

Have you created/found some other, better solution? – please share.

As always, your mileage should vary – at  least a bit.  🙂

Topics: Computer Technology, Mobile Web, Reviews|Comments|Feedback, Unix-Linux-Os, Web Problem Solving | Comments Off on Android Nexus 4 – using Sqlite to ’empty’ Pop Email ‘trash’

Comments are closed.


________________________________________________
YOUR GeoIP Data | Ip: 73.21.121.1
Continent: NA | Country Code: US | Country Name: United States
Region: | State/Region Name: | City:
(US only) Area Code: 0 | Postal code/Zip:
Latitude: 38.000000 | Longitude: -97.000000
Note - if using a mobile device your physical location may NOT be accurate...
________________________________________________

Georgia-USA.Com - Web Hosting for Business
____________________________________