4

I need to make a query to ContactsContract.Data table and values in CONTACT_ID column would be different (distinct).

Code:

final Uri uri = ContactsContract.Data.CONTENT_URI;
final String[] projection = new String[] {//
    ContactsContract.Data.CONTACT_ID, //
    ContactsContract.Data._ID, //
    ContactsContract.Data.DISPLAY_NAME,//
    ContactsContract.Data.LOOKUP_KEY //
};
final StringBuilder selectionBuilder = new StringBuilder();
selectionBuilder.append(ContactsContract.CommonDataKinds.GroupMembership.GROUP_ROW_ID);
selectionBuilder.append("= ? AND ");
selectionBuilder.append(ContactsContract.Data.MIMETYPE);
selectionBuilder.append("= ? ");
final String selection = selectionBuilder.toString();
final String[] selectionArgs = new String[] {//
    String.valueOf(groupId), //
    ContactsContract.CommonDataKinds.GroupMembership.CONTENT_ITEM_TYPE //
};
return context.getContentResolver().query(uri, projection, selection, selectionArgs, null);

First of all, I've tried to add "DISTINCT " to ContactsContract.Data.CONTACT_ID in projection. But there was an exception: java.lang.IllegalArgumentException: Invalid column DISTINCT contact_id

Then, I write this way: 

"'DISTINCT "+ContactsContract.Data.CONTACT_ID+"'".
java.lang.IllegalArgumentException: Invalid column 'DISTINCT contact_id'

Then, I add to selectionBuilder:

selectionBuilder.append(" GROUP BY ").append(ContactsContract.Data.CONTACT_ID);

Once again, an exception: android.database.sqlite.SQLiteException: near "GROUP": syntax error: , while compiling: SELECT contact_id, _id, display_name, lookup FROM view_data_restricted data WHERE (1) AND (data1= ? AND mimetype= ? GROUP BY contact_id) ORDER BY display_name ASC

At last, I've append "group by" statement right after sortOrder, but: 

android.database.sqlite.SQLiteException: near "GROUP": syntax error: , while compiling: SELECT contact_id, _id, display_name, lookup FROM view_data_restricted data WHERE (1) AND (data1= ? AND mimetype= ? ) ORDER BY display_name ASC GROUP BY contact_id

Is it ever possible to make query with distinct? Maybe, I should append something to URI?

Natali
  • 2,934
  • 4
  • 39
  • 53
QuickNick
  • 1,921
  • 2
  • 15
  • 30

1 Answers1

5

If you are targeting devices below ICS, you can use the GROUP_BY clause by adding a ) before the group by and a ( after:

selectionBuilder.append(") GROUP BY (")

As of ICS and above, the query interpretor is smarter and closes any unclosed parenthesis to prevent injection.

However, I don't see why you need distinct contact_ids here. A contact should probably have only one Data to make the association with one group, so you probably receive a different contact on each line.

Also, there may be something to do with http://developer.android.com/reference/android/provider/ContactsContract.Contacts.html#CONTENT_GROUP_URI it is not documented, but given its position, it may well be a direct access to Contacts belonging to a Group. You would use that Uri :

Uri uri = ContentUri.withAppendedId(ContactsContract.Contacts.CONTENT_GROUP_URI, groupId);

And then query it like the Contacts.CONTENT_URI

njzk2
  • 38,969
  • 7
  • 69
  • 107
  • Yes, I've found your question and your searches for injection of GROUP BY. I've found it very interesting :) Too pity it's a hack and it was blocked :) However, I seek common decision, not only for GroupMembership. It must be also ported to CommonDataKinds.Phone - I know about its CONTENT_FILTER_URI, but my customer want to filter by ANY portion of phone numbers. – QuickNick Mar 09 '12 at 15:02
  • I liked you answer, but I want to open bounty, cause this question didn't receive enough attention. Maybe, your answer would be the best. – QuickNick Mar 12 '12 at 11:16