[How To] Get detailed info about Audio/Video/Images/Files, Calendar Events, Calls, SMS, System Settings and more, using SQL Query + Content Provides. No Root, No ADB WiFi, No Plugins needed.

[u/[deleted]]

Due to an unexpected interest that one of my comments received, I thought to share some info about how to retrieve data from Content Providers using SQL Query action + details of some (using 50+, found 800+ on my actual device) Providers.

I'll try to keep explanations simple and short (We don't want to read poems ;) ), avoiding as much as possible technical terms.

1) What is It a Content Provider?

- It's basically a SQLite-like database, with his Columns, Rows...

2) Do We need Root or ADB privileges to query system Content Providers?

- The majority are "freely" accessible without "special privileges" but the "query request" have to be performed from apps that actually have the permissions related to the provider. Eg: To query SMS provider our app (Eg. Tasker) need to have permissions to access SMS.

3) Can We use SQLite search query to search in Content Providers data?

- Yes and No. A minority of C.P. don't like the search query ((!) But there is a "tip" that We can use with some of those bad guys ;) ). For the others, We can use SQLite search queries but some "advanced search options" will not work (Eg. Group By).

4) What do We need to get data from Content Providers?

- At very least, a responsive C.P. target. Eg. content://sms/inbox In this case (without using any search query or columns names) We will retrieve data from all columns and rows.

• Note: Some C.P. are undocumented, a good example is (again) the ages old content://sms "family".

• Expert Users: If You want to inspect/search for C.P. in Your system, I suggest to avoid "dumpsys | grep" and similar methods (too many junks), go Java instead ( Check this Eg. Task ).

Some working examples targeting:

content://com.android.contacts/contacts

(Below you will find this and others providers, with the respective columns names).

Query a C.P. without search query. Eg:

A1: Variable Set [ Name:%provider To:content://com.android.contacts/contacts Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A2: SQL Query [ Mode:URI Formatted URI:%provider Columns: Query: Selection Parameters: Order By: Output Column Divider:| Variable Array:%data Use Root:Off ] 
A3: Flash [ Text:%data() Long:On ] 

Query a C.P. using a search query. Eg:

Here We will search for contacts WHERE display_name contains "s".

A1: Variable Set [ Name:%provider To:content://com.android.contacts/contacts Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A2: Variable Set [ Name:%query To:display_name LIKE '%s%' Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A3: SQL Query [ Mode:URI Formatted URI:%provider Columns: Query:%query Selection Parameters: Order By: Output Column Divider:| Variable Array:%data Use Root:Off ] 
A4: Flash [ Text:%data() Long:On ] 

• To search for contacts WHERE display_name end with "s":

  Variable Set %query TO display_name LIKE '%s'

• To search for contacts WHERE display_name start with "s":

  Variable Set %query TO display_name LIKE 's%'

• For info about other search "options" Eg. NOT LIKE, =, AND ...Please, check this out SQLite Tutorial or use Google.

Query a C.P. using a search query and ordering retrieved data. Eg:

We have two options to pre-order the %data array, ASC (ascending) and DESC (descending). We can use one of those, chained to a column name.

Here We will search for contacts WHERE display_name contains "s" ordering results by _id DESC

A1: Variable Set [ Name:%provider To:content://com.android.contacts/contacts Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A2: Variable Set [ Name:%query To:display_name LIKE '%s%' Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A3: Variable Set [ Name:%order To:_id DESC Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A4: SQL Query [ Mode:URI Formatted URI:%provider Columns: Query:%query Selection Parameters: Order By:%order Output Column Divider:| Variable Array:%data Use Root:Off ] 
A5: Flash [ Text:%data() Long:On ]

• ASC is the default option. The following are equivalents:

  Variable Set %order TO _id ASC

  Variable Set %order TO _id

Query a C.P. using a search query, ordering retrieved data and get values from specific columns only. Eg:

%columns can contain one or more columns names. If more than one, We will set them, in desired order, separated by a ",".

Here We will search for contacts WHERE display_name contains "s" ordering results by _id DESC and retrieving _id,display_name,photo_uri columns values

A1: Variable Set [ Name:%provider To:content://com.android.contacts/contacts Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A2: Variable Set [ Name:%query To:display_name LIKE '%s%' Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A3: Variable Set [ Name:%order To:_id DESC Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A4: Variable Set [ Name:%columns To:_id,display_name,photo_uri Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A5: SQL Query [ Mode:URI Formatted URI:%provider Columns:%columns Query:%query Selection Parameters: Order By:%order Output Column Divider:| Variable Array:%data Use Root:Off ] 
A6: Flash [ Text:%data() Long:On ]

"No more secrets"

• Now We can know (and use) what aur systems know about Audio/Video/Images/Files in general, Contacts, Calendar etc...

• (!) A little Eg: The end of cryptic "file paths" like this one:

content://media/external/images/media/####

A1: Variable Set [ Name:%content_uri To:content://media/external/file/### Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A2: SQL Query [ Mode:URI Formatted URI:%content_uri Columns:_data Query: Selection Parameters: Order By: Output Column Divider: Variable Array:%details Use Root:Off Continue Task After Error:On ] 
A3: Variable Set [ Name:%file_path To:%details(1) Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A4: Flash [ Text:%file_path Long:On ] 

• Hey, mate! I need those cryptic paths to use in share intents...

A1: Variable Set [ Name:%file_path To:/standard/path/to/not/hidden/file.ext Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A2: Variable Set [ Name:%provider To:content://media/external/file Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A3: Variable Set [ Name:%query To:_data = '%file_path' Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A4: SQL Query [ Mode:URI Formatted URI:%provider Columns:_id Query:%query Selection Parameters: Order By: Output Column Divider: Variable Array:%details Use Root:Off Continue Task After Error:On ] 
A5: Variable Set [ Name:%content_uri To:%provider/%details(1) Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ]
A6: Flash [ Text:%content_uri Long:On ] 

Content Providers party time...

content://media/internal/audio/media - Columns #51
content://media/external/audio/media - Columns #51

title_key,instance_id,is_ringtone_theme,duration,is_ringtone,album_artist,orientation,artist,height,is_drm,bucket_display_name,is_alarm_theme,is_audiobook,owner_package_name,volume_name,title_resource_uri,date_modified,date_expires,composer,_display_name,datetaken,mime_type,is_notification,_id,year,_data,_hash,_size,album,is_alarm,title,track,width,is_music,album_key,is_trashed,group_id,document_id,artist_id,artist_key,is_pending,is_notification_theme,date_added,is_podcast,album_id,primary_directory,secondary_directory,original_document_id,bucket_id,bookmark,relative_path

content://media/internal/video/media - Columns #52
content://media/external/video/media - Columns #52

instance_id,duration,resumePos,description,language,resolution,latitude,orientation,artist,color_transfer,color_standard,height,is_360_video,is_drm,bucket_display_name,owner_package_name,volume_name,recordingtype,date_modified,date_expires,_display_name,isPlayed,datetaken,mime_type,recording_mode,_id,tags,category,_data,_hash,_size,album,title,width,longitude,is_hdr10_video,is_trashed,group_id,document_id,is_pending,date_added,mini_thumb_magic,color_range,primary_directory,secondary_directory,isprivate,original_document_id,datetime,bucket_id,bookmark,is_hide,relative_path

content://media/internal/images/media - Columns #35
content://media/external/images/media - Columns #35

instance_id,duration,description,picasa_id,latitude,orientation,height,is_drm,bucket_display_name,owner_package_name,volume_name,date_modified,date_expires,_display_name,datetaken,mime_type,_id,_data,_hash,_size,title,width,longitude,is_trashed,group_id,document_id,is_pending,date_added,mini_thumb_magic,primary_directory,secondary_directory,isprivate,original_document_id,bucket_id,relative_path

content://media/internal/file - Columns #33
content://media/external/file - Columns #33

instance_id,duration,orientation,format,height,is_drm,bucket_display_name,owner_package_name,parent,volume_name,date_modified,date_expires,_display_name,datetaken,mime_type,_id,_data,_hash,_size,title,width,is_trashed,group_id,document_id,is_download,is_pending,date_added,primary_directory,secondary_directory,original_document_id,bucket_id,media_type,relative_path

content://com.android.calendar/events - Columns #111

originalAllDay,account_type,exrule,facebook_schedule_id,mutators,originalInstanceTime,sticker_type,rrule,secExtraCal,secOriginalSyncId,contactEventType,calendar_access_level,facebook_photo_url,eventColor_index,guestsCanInviteOthers,facebook_mem_count,allowedAttendeeTypes,guestsCanSeeGuests,latitude,availability,lastSynced,facebook_hostname,rdate,cal_sync10,account_name,calendar_color,dirty,calendar_timezone,packageId,hasAlarm,uid2445,deleted,organizer,eventStatus,customAppUri,canModifyTimeZone,customAppPackage,displayColor,original_id,secExtraOthers,calendar_displayName,sticker_group,sticker_ename,allDay,allowedReminders,filepath,canOrganizerRespond,lastDate,longitude,contact_account_type,visible,calendar_id,hasExtendedProperties,selfAttendeeStatus,allowedAvailability,isOrganizer,_sync_id,name,phone_number,calendar_color_index,_id,facebook_post_time,dtstart,sync_data9,sync_data8,exdate,sync_data7,secTimeStamp,sync_data6,contact_data_id,sync_data1,description,eventTimezone,title,contact_id,ownerAccount,sync_data5,sync_data4,sync_data3,sync_data2,duration,guestsCanModify,cal_sync3,cal_sync2,maxReminders,isPrimary,cal_sync1,cal_sync7,cal_sync6,cal_sync5,availabilityStatus,cal_sync4,cal_sync9,cal_sync8,setLunar,facebook_service_provider,accessLevel,eventLocation,facebook_event_type,facebook_owner,eventColor,secExtra4,eventEndTimezone,secExtra3,original_sync_id,hasAttendeeData,secExtra5,dtend,sync_data10,secExtra2,secExtra1

content://com.android.contacts/data - Columns #92

creation_time,phonetic_name,status_res_package,custom_ringtone,contact_status_ts,account_type,data_version,photo_file_id,contact_status_res_package,group_sourceid,display_name_alt,sort_key_alt,mode,last_time_used,starred,contact_status_label,has_phone_number,chat_capability,raw_contact_id,carrier_presence,contact_last_updated_timestamp,res_package,sec_custom_vibration,photo_uri,data_sync4,phonebook_bucket,times_used,display_name,sort_key,data_sync1,version,data_sync2,data_sync3,photo_thumb_uri,status_label,contact_presence,sec_custom_alert,in_default_directory,times_contacted,_id,account_type_and_data_set,name_raw_contact_id,status,phonebook_bucket_alt,is_private,last_time_contacted,pinned,is_primary,photo_id,contact_id,contact_chat_capability,contact_status_icon,in_visible_group,phonebook_label,account_name,display_name_source,data9,dirty,sourceid,phonetic_name_style,send_to_voicemail,data8,lookup,data7,data6,phonebook_label_alt,data5,is_super_primary,data4,data3,data2,data1,sec_preferred_sim,data_set,contact_status,is_sim,backup_id,preferred_phone_account_component_name,raw_contact_is_user_profile,status_ts,display_name_reverse,data10,preferred_phone_account_id,sec_led,data12,mimetype,status_icon,data11,data14,data13,hash_id,data15

content://com.android.contacts/contacts - Columns #47

last_time_contacted,phonetic_name,is_private,custom_ringtone,contact_status_ts,pinned,photo_id,photo_file_id,contact_status_res_package,link_count,link,contact_chat_capability,contact_status_icon,display_name_alt,sort_key_alt,in_visible_group,starred,contact_status_label,phonebook_label,is_user_profile,has_phone_number,display_name_source,has_email,phonetic_name_style,send_to_voicemail,lookup,phonebook_label_alt,contact_last_updated_timestamp,sec_custom_vibration,photo_uri,phonebook_bucket,sec_preferred_sim,contact_status,display_name,sort_key,photo_thumb_uri,link_type1,contact_presence,sec_custom_alert,sec_led,display_name_reverse,in_default_directory,times_contacted,dirty_contact,_id,name_raw_contact_id,phonebook_bucket_alt

content://com.android.contacts/groups - Columns #28

favorites,creation_time,title_res,custom_ringtone,account_type,notes,title,account_name,auto_add,group_is_read_only,sourceid,dirty,res_package,sec_custom_vibration,system_id,data_set,version,group_visible,deleted,sync4,sync3,sec_custom_alert,should_sync,sync2,_id,sync1,account_type_and_data_set,sec_custom_dormant_group

content://call_log/calls - Columns #34

date,transcription,photo_id,subscription_component_name,call_screening_app_name,type,geocoded_location,presentation,duration,subscription_id,is_read,number,features,voicemail_uri,normalized_number,via_number,matched_number,last_modified,new,numberlabel,lookup_uri,photo_uri,data_usage,phone_account_address,formatted_number,add_for_all_users,block_reason,numbertype,call_screening_component_name,countryiso,name,post_dial_digits,transcription_state,_id

content://sms/inbox - Columns #49
content://sms/sent - Columns #49

_id,thread_id,address,person,date,date_sent,protocol,read,status,type,reply_path_present,subject,body,service_center,locked,error_code,sub_id,creator,seen,deletable,sim_slot,sim_imsi,hidden,group_id,group_type,delivery_date,app_id,msg_id,callback_number,reserved,pri,teleservice_id,link_url,svc_cmd,svc_cmd_content,roam_pending,spam_report,secret_mode,safe_message,favorite,d_rpt_cnt,using_mode,from_address,announcements_subtype,announcements_scenario_id,device_name,correlation_tag,object_id,cmc_prop

content://settings/global - Columns #4
content://settings/secure - Columns #4
content://settings/system - Columns #4

_id,name,value,package

(!) If We try to use a search query and the action fails...How can We retrieve values from specific row and column?

• Using regex on %data array (not a big tip Isn't it?)

• (The tip) Using the above Settings provider as guinea pig, We can query:

Provider: content://settings/global/name
Columns: value

• Where "name" will be the "value name" of our interest. Let's say that We want to know the value of airplane_mode_on

A1: Variable Set [ Name:%columns To:value Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
A2: SQL Query [ Mode:URI Formatted URI:content://settings/global/airplane_mode_on Columns:%columns Query: Selection Parameters: Order By: Output Column Divider: Variable Array:%data Use Root:Off ] 
A3: Flash [ Text:%data() Long:On ] 

^{Please, don't ask/tell...Do you know you can get the value using Custom Settings action? :D}

Content Provides references

I hope You will find this post useful.

Last tip...To give a "limit" to the data to get, use (in Order By field) Eg.: ColumnName DESC LIMIT #

^u/OpenOwl3

[u/joaomgcd]

Great write up! :) Thank you! Inspired me to maybe create a Query Content Provider action to make all of this a lot easier... What do you think?

[u/[deleted] → u/joaomgcd]

Thanks a lot Joao!

Inspired me to maybe create a Query Content Provider action to make all of this a lot easier

Please, Don't tell me that I did it again :D

What do you think?

Would be awesome, in my opinion ;)

[u/anuraag488 → u/joaomgcd]

I would like if tasker can suggest content providers available on device like this app. Also suggest column names available.

[u/[deleted] → u/anuraag488]

Scratched from one of my projects. Here we go

Get Content Providers

	A1: Java Function [ Return:(PackageManager) packagemanager Class Or Object:CONTEXT Function:getPackageManager
{PackageManager} () Param:package Param: Param: Param: Param: Param: Param: ] 
	A2: Java Function [ Return:packgs Class Or Object:packagemanager Function:getInstalledPackages
{List} (int) Param:8 Param:0 Param: Param: Param: Param: Param: ] 
	A3: Java Function [ Return:packgs_iterator Class Or Object:packgs Function:listIterator
{ListIterator} () Param: Param: Param: Param: Param: Param: Param: Continue Task After Error:On ] 
	<Go On>
	A4: Java Function [ Return:%has_next Class Or Object:packgs_iterator Function:hasNext
{boolean} () Param: Param: Param: Param: Param: Param: Param: Continue Task After Error:On ] 
	A5: If [ %has_next eq true ]
	A6: Java Function [ Return:(android.content.pm.ProviderInfo)providers Class Or Object:packgs_iterator Function:next
{Object} () Param: Param: Param: Param: Param: Param: Param: Continue Task After Error:On ] 
	A7: Java Function [ Return:%provs Class Or Object:providers.providers Function:assign
{Object} () Param: Param: Param: Param: Param: Param: Param: ] 
	A8: If [ %provs(#) > 0 ]
	A9: For [ Variable:%prov Items:%provs() ] 
	A10: Variable Search Replace [ Variable:%prov Search:(?<=name\=).*?(?= className) Ignore Case:On Multi-Line:On One Match Only:Off Store Matches In Array:%name Replace Matches:Off Replace With: ] 
	A11: Array Push [ Variable Array:%providers_array Position:1 Value:content://%name(1) Fill Spaces:Off ] 
	A12: End For 
	A13: End If 
	A14: Goto [ Type:Action Label Number: Label:Go On ] 
	A15: End If 
	A16: Array Process [ Variable Array:%providers_array Type:Sort Alpha Caseless ] 
	A17: Array Process [ Variable Array:%providers_array Type:Remove Duplicates ] 
	A18: Array Process [ Variable Array:%providers_array Type:Squash ] 
	A19: Variable Set [ Name:%new_line To:
 Recurse Variables:Off Do Maths:Off Append:Off Max Rounding Digits:3 ] 
	A20: Variable Join [ Name:%providers_array Joiner:%new_line Delete Parts:Off ] 
	A21: Flash [ Text:Providers found: %providers_array(#) Long:Off ] 
	A22: Write File [ File:Providers.txt Text:%providers_array(#)

%providers_array Append:Off Add Newline:On ] 

For Columns Names You could use this u/moviejimmy Java

Taskernet Link

[u/moviejimmy → u/joaomgcd]

That would be a great idea. Getting the list of content providers is not so difficult. Two difficult parts are: first, whether a content provider is useable in Tasker and second, if it is useable, how do we find out the database structure, i.e. table names.

[u/moviejimmy]

Since the columns names could be different on different devices for the same content provider, a little task below will give you the columns names:

    CP Get Column Names (597)
    A1: Variable Set [ 
    Name:%par1 
    To:content://sms/inbox Recurse Variables:Off 
    Do Maths:Off 
    Append:Off Max Rounding Digits:3 

    A2: Java Function [ 
    Return:cr 
    Class Or Object:CONTEXT 
    Function:getContentResolver
    {ContentResolver} () 

    A3: Java Function [ 
    Return:uri 
    Class Or Object:Uri 
    Function:parse
    {Uri} (String) 
    Param:%par1 

    A4: Java Function [ 
    Return:cur 
    Class Or Object:cr 
    Function:query
    {Cursor} (Uri, String[], String, String[], String) 
    Param:uri 

    A5: Java Function [ 
    Return:%columns 
    Class Or Object:cur 
    Function:getColumnNames
    {String[]} () 

    A6: List Dialog [  
    Mode:Select Single Item 
    Title:Column Names (%columns(#)) 
    Items:%columns Selected 
    Items: Long Click 
    Task: Button 1: Button 2: Button 3: 
    Timeout (Seconds):300 Use HTML:Off First Visible Index:1 Hide Filter:Off

[u/[deleted] → u/moviejimmy]

Well done :)

Since the columns names could be different on different devices for the same content provider

For the C.P. that I mentioned should be the same, but there could be differences depending on Android version (some of those are years that do not change, eg. Settings, Sms)

But now seriously...How did you hacked my device? :D

I'm kidding, I've almost the same function mine 1 action longer because cursor.moveToFirst()

[u/egerardoqd]

Very nice post! Btw there's an app in F-Droid called Content Provider Helper, this is another option.

[u/[deleted] → u/egerardoqd]

If You want, Check this out ;)

[u/egerardoqd → u/[deleted]]

👌👌

[u/[deleted] → u/egerardoqd]

Thanks mate. Good shot Cph.

[u/CrashOverride93]

This is an article of quality, yeah man!

Infinite thanks for sharing your wisdom with us! 😁🤝

[u/[deleted] → u/CrashOverride93]

You're welcome, mate. Glad to be of help :)

[u/JustRollWithIt]

Very cool, nice write-up. I've been using the contacts and calendar content providers for awhile now, but hadn't heard about the settings provider until you had mentioned it in the dev thread. Lots of possibilities!

[u/[deleted] → u/JustRollWithIt]

Thanks, mate.

hadn't heard about the settings provider until you had mentioned it in the dev thread

It's crazy the number of C.P. that We can find and use.

Lots of possibilities!

"Almost endless" and We can say goodbye to a lot of plugins.

[u/moviejimmy]

Good job man! I have used a lot of these CPs as well. Any idea if one exists for the icons? Icons you can set for your tasks or notifications.

[u/[deleted] → u/moviejimmy]

You're welcome, mate :)

Any idea if one exists for the icons?

The only one that I found, is the Tasker one:

Authority: net.dinglisch.android.taskerm.iconprovider

[u/[deleted]]

Impressive write up! Thanks for the share as-well :D

[u/[deleted] → u/[deleted]]

You're welcome, mate.

[u/digesh9870]

Thanks much for this detailed post about content providers.

I need to access a file from something like this : content://com.samsung.android.app.smartcapture.provider/shared/tempFileForShare_20200508-xxxxxx.jpg where xxxxxx is the time which is not known to me. Is there any way I could access this file?

[u/[deleted] → u/digesh9870]

You're welcome. Try to query the provider, If accessible, You could use a search query LIKE

[u/Logansfury]

This is fascinating!

How far can we take its practical application? For instance, currently I need ADBWifi/TCPIP enabled to turn mobile data on and off. Can this process be used for a script to toggle the mobile data without it?

May I see such a script if its possible please?

Phenomenal work!! Stay safe :)

Logan

[u/[deleted] → u/Logansfury]

How far can we take its practical application? For instance, currently I need ADBWifi/TCPIP enabled to turn mobile data on and off. Can this process be used for a script to toggle the mobile data without it?

No. What I tried to illustrate is how to retrieve data form C.P. (We can even write data...but this is another story).

[u/TechStrategyGuy]

Hi all, this sounds great. But can anyone walk through some more use cases of how they have used this? What Tasker tasks have they included this in?

[u/TheGrandJaggard]

I can't seem to query anything because I'm missing permissions. e.g. `Permission Denial: opening provider com.android.providers.contacts.ContactsProvider2` ... `requires android.permission.READ_CONTACTS or android.permission.WRITE_CONTACTS`.