Use contentprovider to implement groupby Data Query and ICS modification (original)

Many people want to use groupby query in the contentprovider query method. However, the SDK does not provide such a function. Therefore, some cool people are inspired by hacker injection cracking. The procedure is as follows:

For example, to implement such an SQL statement:

Select _ id, number, date, duration, type, name, numbertype,

Numberlabel, count (*) from callgroup by number, type, date/8640000

Order by date DESC

If this function can run normally in general SQL compilation tools, it may be different in contentrosolver.


Use the query in contentrosolver to write the statement as follows:


Private Static string calls_count = "calls_count ";

Static final string [] call_log_projection = new string [] {

CILS. _ id,

CILS. Number,

CILS. date,

CILS. duration,

CILS. type,

CILS. cached_name,

CILS. cached_number_type,

CILS. cached_number_label,

"Count (*) as" + calls_count

};


String selection = "0 = 0) group by (" +

CILS. Number + "), (" +

CILS. Type + "), (" +

CILS. Date + "/86400000 ";


Rosolver. Query (query_token, null, CILS. content_uri,

Call_log_projection, selection, null, CILS. default_sort_order );


Note:

1 case sensitivity of the keyword "count, as, group"

2 count (*) and then follow the ***

3. Android integrates the parameters in the query into an SQL statement, and automatically adds a bracket to the selection string to form a "Where

(*******), So pay special attention to the fact that the selection contains parentheses.

4 fields after group by should be enclosed in parentheses and separated by commas.


The correct format for converting to SQL statements should be as follows:

Select _ id, number, date, duration, type, name, numbertype,

Numberlabel, count (*) from callwhere (0 = 0) group

(Number), (type), (Date/8640000) order by date DESC

============================================================== =

Unfortunately! In ICS, this method is not applicable anymore! (Why ...)

After tracking the source code, the corresponding provider (such as calllogprovider) in ICS and the sqlitequerybuilder class in the Framework have already completed this vulnerability

First, a parameter mstrict is added, which is set to true in the calllogprovider query method.

Sqlitequerybuilder also detected:

 private String[] computeProjection(String[] projectionIn) {        if (projectionIn != null && projectionIn.length > 0) {            if (mProjectionMap != null) {                String[] projection = new String[projectionIn.length];                int length = projectionIn.length;                for (int i = 0; i < length; i++) {                    String userColumn = projectionIn[i];                    String column = mProjectionMap.get(userColumn);                    if (column != null) {                        projection[i] = column;                        continue;                    }                     if (!mStrict &&                            ( userColumn.contains(" AS ") || userColumn.contains(" as "))) {                        /* A column alias already exist */                        projection[i] = userColumn;                        continue;                    }                    throw new IllegalArgumentException("Invalid column "                            + projectionIn[i]);                }                return projection;            } else {                return projectionIn;            }        } else if (mProjectionMap != null) {            // Return all columns in projection map.            Set<Entry<String, String>> entrySet = mProjectionMap.entrySet();            String[] projection = new String[entrySet.size()];            Iterator<Entry<String, String>> entryIter = entrySet.iterator();            int i = 0;            while (entryIter.hasNext()) {                Entry<String, String> entry = entryIter.next();                // Don't include the _count column when people ask for no projection.                if (entry.getKey().equals(BaseColumns._COUNT)) {                    continue;                }                projection[i++] = entry.getValue();            }            return projection;        }         return null;    }

As you can see from the code before and after the red mark, the column in the projection defined by yourself is directly thrown an exception.

Even if mstrict is set to false, seleciton will still be added with parentheses during the next splicing process to make up for the possibility of being inject.

After a brief look, this modification was not described in the ICS change description. After all, it was just a vulnerability for Google.

If a student's app uses this method, it is estimated that the compatibility under ICS should be changed.

Back to the problem itself, how to implement groupby in contentprovider. If the contentprovider is implemented by itself or the provider of the system can be modified, the sqlitequerybuilder class provides the query interface with groupby:

public Cursor query(SQLiteDatabase db, String[] projectionIn,            String selection, String[] selectionArgs, String groupBy,            String having, String sortOrder, String limit) 

If the system contentprovider is used, such as calllogprovider and contactsprovider, there is no direct query method as far as I know. The alternative method is to use the matrixcursor class, first query all the data, then traverse the cursor, and process and splice a matrixcursor. Note that the matrixcursor splicing is inefficient, when the data volume is large, it takes a lot of time and requires algorithm optimization and asynchronous processing.

Reference:

Http://yelinsen.iteye.com/blog/836935

Http://www.eoeandroid.com/thread-31662-1-1.html