diff options
| -rw-r--r-- | api/current.txt | 8 | ||||
| -rw-r--r-- | core/java/android/database/sqlite/SQLiteQueryBuilder.java | 523 | ||||
| -rw-r--r-- | core/java/android/database/sqlite/SQLiteStatementBuilder.java | 1036 |
3 files changed, 1104 insertions, 463 deletions
diff --git a/api/current.txt b/api/current.txt index 9f3655de0a7c..f5504d7b8688 100644 --- a/api/current.txt +++ b/api/current.txt @@ -12670,30 +12670,22 @@ package android.database.sqlite { ctor public SQLiteQueryBuilder(); method public static void appendColumns(java.lang.StringBuilder, java.lang.String[]); method public void appendWhere(java.lang.CharSequence); - method public void appendWhere(java.lang.CharSequence, java.lang.String...); method public void appendWhereEscapeString(java.lang.String); - method public void appendWhereEscapeString(java.lang.String, java.lang.String...); - method public void appendWhereExpression(java.lang.CharSequence); - method public void appendWhereExpression(java.lang.CharSequence, java.lang.String...); method public java.lang.String buildQuery(java.lang.String[], java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String); method public deprecated java.lang.String buildQuery(java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String, java.lang.String); method public static java.lang.String buildQueryString(boolean, java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String); method public java.lang.String buildUnionQuery(java.lang.String[], java.lang.String, java.lang.String); method public java.lang.String buildUnionSubQuery(java.lang.String, java.lang.String[], java.util.Set<java.lang.String>, int, java.lang.String, java.lang.String, java.lang.String, java.lang.String); method public deprecated java.lang.String buildUnionSubQuery(java.lang.String, java.lang.String[], java.util.Set<java.lang.String>, int, java.lang.String, java.lang.String, java.lang.String[], java.lang.String, java.lang.String); - method public int delete(android.database.sqlite.SQLiteDatabase, java.lang.String, java.lang.String[]); method public java.lang.String getTables(); method public android.database.Cursor query(android.database.sqlite.SQLiteDatabase, java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String); method public android.database.Cursor query(android.database.sqlite.SQLiteDatabase, java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String, java.lang.String); - method public android.database.Cursor query(android.database.sqlite.SQLiteDatabase, java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, android.os.CancellationSignal); method public android.database.Cursor query(android.database.sqlite.SQLiteDatabase, java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String, java.lang.String, android.os.CancellationSignal); - method public android.database.Cursor query(android.database.sqlite.SQLiteDatabase, java.lang.String[], android.os.Bundle, android.os.CancellationSignal); method public void setCursorFactory(android.database.sqlite.SQLiteDatabase.CursorFactory); method public void setDistinct(boolean); method public void setProjectionMap(java.util.Map<java.lang.String, java.lang.String>); method public void setStrict(boolean); method public void setTables(java.lang.String); - method public int update(android.database.sqlite.SQLiteDatabase, android.content.ContentValues, java.lang.String, java.lang.String[]); } public class SQLiteReadOnlyDatabaseException extends android.database.sqlite.SQLiteException { diff --git a/core/java/android/database/sqlite/SQLiteQueryBuilder.java b/core/java/android/database/sqlite/SQLiteQueryBuilder.java index b52c76154f56..c6c676f81758 100644 --- a/core/java/android/database/sqlite/SQLiteQueryBuilder.java +++ b/core/java/android/database/sqlite/SQLiteQueryBuilder.java @@ -16,39 +16,17 @@ package android.database.sqlite; -import static android.content.ContentResolver.QUERY_ARG_SQL_GROUP_BY; -import static android.content.ContentResolver.QUERY_ARG_SQL_HAVING; -import static android.content.ContentResolver.QUERY_ARG_SQL_LIMIT; -import static android.content.ContentResolver.QUERY_ARG_SQL_SELECTION; -import static android.content.ContentResolver.QUERY_ARG_SQL_SELECTION_ARGS; -import static android.content.ContentResolver.QUERY_ARG_SQL_SORT_ORDER; - -import android.annotation.NonNull; -import android.annotation.Nullable; -import android.content.ContentResolver; -import android.content.ContentValues; import android.database.Cursor; import android.database.DatabaseUtils; -import android.os.Build; -import android.os.Bundle; import android.os.CancellationSignal; import android.os.OperationCanceledException; import android.provider.BaseColumns; import android.text.TextUtils; -import android.util.ArrayMap; import android.util.Log; -import com.android.internal.util.ArrayUtils; - -import dalvik.system.VMRuntime; - -import libcore.util.EmptyArray; - -import java.util.Arrays; import java.util.Iterator; import java.util.Map; import java.util.Map.Entry; -import java.util.Objects; import java.util.Set; import java.util.regex.Pattern; @@ -56,7 +34,8 @@ import java.util.regex.Pattern; * This is a convenience class that helps build SQL queries to be sent to * {@link SQLiteDatabase} objects. */ -public class SQLiteQueryBuilder { +public class SQLiteQueryBuilder +{ private static final String TAG = "SQLiteQueryBuilder"; private static final Pattern sLimitPattern = Pattern.compile("\\s*\\d+\\s*(,\\s*\\d+\\s*)?"); @@ -64,7 +43,6 @@ public class SQLiteQueryBuilder { private Map<String, String> mProjectionMap = null; private String mTables = ""; private StringBuilder mWhereClause = null; // lazily created - private String[] mWhereArgs = EmptyArray.STRING; private boolean mDistinct; private SQLiteDatabase.CursorFactory mFactory; private boolean mStrict; @@ -104,131 +82,43 @@ public class SQLiteQueryBuilder { mTables = inTables; } - /** {@hide} */ - public @Nullable String getWhere() { - return (mWhereClause != null) ? mWhereClause.toString() : null; - } - - /** {@hide} */ - public String[] getWhereArgs() { - return mWhereArgs; - } - - /** - * Append a chunk to the {@code WHERE} clause of the query. All chunks - * appended are surrounded by parenthesis and {@code AND}ed with the - * selection passed to {@link #query}. The final {@code WHERE} clause looks - * like: - * - * <pre> - * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) - * </pre> - * - * @param inWhere the chunk of text to append to the {@code WHERE} clause. - */ - public void appendWhere(@NonNull CharSequence inWhere) { - appendWhere(inWhere, EmptyArray.STRING); - } - /** - * Append a chunk to the {@code WHERE} clause of the query. All chunks - * appended are surrounded by parenthesis and {@code AND}ed with the - * selection passed to {@link #query}. The final {@code WHERE} clause looks - * like: + * Append a chunk to the WHERE clause of the query. All chunks appended are surrounded + * by parenthesis and ANDed with the selection passed to {@link #query}. The final + * WHERE clause looks like: * - * <pre> * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) - * </pre> * - * @param inWhere the chunk of text to append to the {@code WHERE} clause. - * @param inWhereArgs list of arguments to be bound to any '?' occurrences - * in the where clause. + * @param inWhere the chunk of text to append to the WHERE clause. */ - public void appendWhere(@NonNull CharSequence inWhere, String... inWhereArgs) { + public void appendWhere(CharSequence inWhere) { if (mWhereClause == null) { mWhereClause = new StringBuilder(inWhere.length() + 16); } - mWhereClause.append(inWhere); - mWhereArgs = ArrayUtils.concat(String.class, mWhereArgs, inWhereArgs); - } - - /** - * Append a standalone expression to the {@code WHERE} clause of this query. - * <p> - * This method differs from {@link #appendWhere(CharSequence)} in that it - * automatically appends {@code AND} to any existing {@code WHERE} clause - * already under construction before appending the given standalone - * expression. - * - * @param inWhere the standalone expression to append to the {@code WHERE} - * clause. It will be wrapped in parentheses when it's appended. - */ - public void appendWhereExpression(@NonNull CharSequence inWhere) { - appendWhereExpression(inWhere, EmptyArray.STRING); - } - - /** - * Append a standalone expression to the {@code WHERE} clause of this query. - * <p> - * This method differs from {@link #appendWhere(CharSequence)} in that it - * automatically appends {@code AND} to any existing {@code WHERE} clause - * already under construction before appending the given standalone - * expression. - * - * @param inWhere the standalone expression to append to the {@code WHERE} - * clause. It will be wrapped in parentheses when it's appended. - * @param inWhereArgs list of arguments to be bound to any '?' occurrences - * in the standalone expression. - */ - public void appendWhereExpression(@NonNull CharSequence inWhere, String... inWhereArgs) { - if (mWhereClause == null) { - mWhereClause = new StringBuilder(inWhere.length() + 16); - } - if (mWhereClause.length() > 0) { - mWhereClause.append(" AND "); + if (mWhereClause.length() == 0) { + mWhereClause.append('('); } - mWhereClause.append('(').append(inWhere).append(')'); - mWhereArgs = ArrayUtils.concat(String.class, mWhereArgs, inWhereArgs); - } - - /** - * Append a chunk to the {@code WHERE} clause of the query. All chunks - * appended are surrounded by parenthesis and {@code AND}ed with the - * selection passed to {@link #query}. The final {@code WHERE} clause looks - * like this: - * - * <pre> - * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) - * </pre> - * - * @param inWhere the chunk of text to append to the {@code WHERE} clause. - * It will be escaped to avoid SQL injection attacks. - */ - public void appendWhereEscapeString(@NonNull String inWhere) { - appendWhereEscapeString(inWhere, EmptyArray.STRING); + mWhereClause.append(inWhere); } /** - * Append a chunk to the {@code WHERE} clause of the query. All chunks - * appended are surrounded by parenthesis and {@code AND}ed with the - * selection passed to {@link #query}. The final {@code WHERE} clause looks - * like this: + * Append a chunk to the WHERE clause of the query. All chunks appended are surrounded + * by parenthesis and ANDed with the selection passed to {@link #query}. The final + * WHERE clause looks like: * - * <pre> * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) - * </pre> * - * @param inWhere the chunk of text to append to the {@code WHERE} clause. - * It will be escaped to avoid SQL injection attacks. - * @param inWhereArgs list of arguments to be bound to any '?' occurrences - * in the where clause. + * @param inWhere the chunk of text to append to the WHERE clause. it will be escaped + * to avoid SQL injection attacks */ - public void appendWhereEscapeString(@NonNull String inWhere, String... inWhereArgs) { + public void appendWhereEscapeString(String inWhere) { if (mWhereClause == null) { mWhereClause = new StringBuilder(inWhere.length() + 16); } + if (mWhereClause.length() == 0) { + mWhereClause.append('('); + } DatabaseUtils.appendEscapedSQLString(mWhereClause, inWhere); - mWhereArgs = ArrayUtils.concat(String.class, mWhereArgs, inWhereArgs); } /** @@ -278,8 +168,8 @@ public class SQLiteQueryBuilder { * </ul> * By default, this value is false. */ - public void setStrict(boolean strict) { - mStrict = strict; + public void setStrict(boolean flag) { + mStrict = flag; } /** @@ -373,7 +263,7 @@ public class SQLiteQueryBuilder { * information passed into this method. * * @param db the database to query on - * @param projection A list of which columns to return. Passing + * @param projectionIn A list of which columns to return. Passing * null will return all columns, which is discouraged to prevent * reading data from storage that isn't going to be used. * @param selection A filter declaring which rows to return, @@ -398,14 +288,10 @@ public class SQLiteQueryBuilder { * @see android.content.ContentResolver#query(android.net.Uri, String[], * String, String[], String) */ - public @Nullable Cursor query(@NonNull SQLiteDatabase db, - @Nullable String[] projection, - @Nullable String selection, - @Nullable String[] selectionArgs, - @Nullable String groupBy, - @Nullable String having, - @Nullable String sortOrder) { - return query(db, projection, selection, selectionArgs, groupBy, having, sortOrder, + public Cursor query(SQLiteDatabase db, String[] projectionIn, + String selection, String[] selectionArgs, String groupBy, + String having, String sortOrder) { + return query(db, projectionIn, selection, selectionArgs, groupBy, having, sortOrder, null /* limit */, null /* cancellationSignal */); } @@ -414,7 +300,7 @@ public class SQLiteQueryBuilder { * information passed into this method. * * @param db the database to query on - * @param projection A list of which columns to return. Passing + * @param projectionIn A list of which columns to return. Passing * null will return all columns, which is discouraged to prevent * reading data from storage that isn't going to be used. * @param selection A filter declaring which rows to return, @@ -441,15 +327,10 @@ public class SQLiteQueryBuilder { * @see android.content.ContentResolver#query(android.net.Uri, String[], * String, String[], String) */ - public @Nullable Cursor query(@NonNull SQLiteDatabase db, - @Nullable String[] projection, - @Nullable String selection, - @Nullable String[] selectionArgs, - @Nullable String groupBy, - @Nullable String having, - @Nullable String sortOrder, - @Nullable String limit) { - return query(db, projection, selection, selectionArgs, + public Cursor query(SQLiteDatabase db, String[] projectionIn, + String selection, String[] selectionArgs, String groupBy, + String having, String sortOrder, String limit) { + return query(db, projectionIn, selection, selectionArgs, groupBy, having, sortOrder, limit, null); } @@ -458,42 +339,7 @@ public class SQLiteQueryBuilder { * information passed into this method. * * @param db the database to query on - * @param projection A list of which columns to return. Passing - * null will return all columns, which is discouraged to prevent - * reading data from storage that isn't going to be used. - * @param selection A filter declaring which rows to return, - * formatted as an SQL WHERE clause (excluding the WHERE - * itself). Passing null will return all rows for the given URL. - * @param selectionArgs You may include ?s in selection, which - * will be replaced by the values from selectionArgs, in order - * that they appear in the selection. The values will be bound - * as Strings. - * @param sortOrder How to order the rows, formatted as an SQL - * ORDER BY clause (excluding the ORDER BY itself). Passing null - * will use the default sort order, which may be unordered. - * @param cancellationSignal A signal to cancel the operation in progress, or null if none. - * If the operation is canceled, then {@link OperationCanceledException} will be thrown - * when the query is executed. - * @return a cursor over the result set - * @see android.content.ContentResolver#query(android.net.Uri, String[], - * String, String[], String) - */ - public @Nullable Cursor query(@NonNull SQLiteDatabase db, - @Nullable String[] projection, - @Nullable String selection, - @Nullable String[] selectionArgs, - @Nullable String sortOrder, - @Nullable CancellationSignal cancellationSignal) { - return query(db, projection, selection, selectionArgs, null, null, sortOrder, null, - cancellationSignal); - } - - /** - * Perform a query by combining all current settings and the - * information passed into this method. - * - * @param db the database to query on - * @param projection A list of which columns to return. Passing + * @param projectionIn A list of which columns to return. Passing * null will return all columns, which is discouraged to prevent * reading data from storage that isn't going to be used. * @param selection A filter declaring which rows to return, @@ -523,69 +369,14 @@ public class SQLiteQueryBuilder { * @see android.content.ContentResolver#query(android.net.Uri, String[], * String, String[], String) */ - public @Nullable Cursor query(@NonNull SQLiteDatabase db, - @Nullable String[] projection, - @Nullable String selection, - @Nullable String[] selectionArgs, - @Nullable String groupBy, - @Nullable String having, - @Nullable String sortOrder, - @Nullable String limit, - @Nullable CancellationSignal cancellationSignal) { - final Bundle queryArgs = new Bundle(); - maybePutString(queryArgs, QUERY_ARG_SQL_SELECTION, selection); - maybePutStringArray(queryArgs, QUERY_ARG_SQL_SELECTION_ARGS, selectionArgs); - maybePutString(queryArgs, QUERY_ARG_SQL_GROUP_BY, groupBy); - maybePutString(queryArgs, QUERY_ARG_SQL_HAVING, having); - maybePutString(queryArgs, QUERY_ARG_SQL_SORT_ORDER, sortOrder); - maybePutString(queryArgs, QUERY_ARG_SQL_LIMIT, limit); - return query(db, projection, queryArgs, cancellationSignal); - } - - /** - * Perform a query by combining all current settings and the information - * passed into this method. - * - * @param db the database to query on - * @param projection A list of which columns to return. Passing null will - * return all columns, which is discouraged to prevent reading - * data from storage that isn't going to be used. - * @param queryArgs A collection of arguments for the query, defined using - * keys such as {@link ContentResolver#QUERY_ARG_SQL_SELECTION} - * and {@link ContentResolver#QUERY_ARG_SQL_SELECTION_ARGS}. - * @param cancellationSignal A signal to cancel the operation in progress, - * or null if none. If the operation is canceled, then - * {@link OperationCanceledException} will be thrown when the - * query is executed. - * @return a cursor over the result set - */ - public Cursor query(@NonNull SQLiteDatabase db, - @Nullable String[] projection, - @Nullable Bundle queryArgs, - @Nullable CancellationSignal cancellationSignal) { - Objects.requireNonNull(db, "No database defined"); - - if (VMRuntime.getRuntime().getTargetSdkVersion() >= Build.VERSION_CODES.Q) { - Objects.requireNonNull(mTables, "No tables defined"); - } else if (mTables == null) { + public Cursor query(SQLiteDatabase db, String[] projectionIn, + String selection, String[] selectionArgs, String groupBy, + String having, String sortOrder, String limit, CancellationSignal cancellationSignal) { + if (mTables == null) { return null; } - if (queryArgs == null) { - queryArgs = Bundle.EMPTY; - } - - // Final SQL that we will execute - final String sql; - - final String unwrappedSql = buildQuery(projection, - queryArgs.getString(QUERY_ARG_SQL_SELECTION), - queryArgs.getString(QUERY_ARG_SQL_GROUP_BY), - queryArgs.getString(QUERY_ARG_SQL_HAVING), - queryArgs.getString(QUERY_ARG_SQL_SORT_ORDER), - queryArgs.getString(QUERY_ARG_SQL_LIMIT)); - - if (mStrict) { + if (mStrict && selection != null && selection.length() > 0) { // Validate the user-supplied selection to detect syntactic anomalies // in the selection string that could indicate a SQL injection attempt. // The idea is to ensure that the selection clause is a valid SQL expression @@ -593,136 +384,25 @@ public class SQLiteQueryBuilder { // originally specified. An attacker cannot create an expression that // would escape the SQL expression while maintaining balanced parentheses // in both the wrapped and original forms. - - // NOTE: The ordering of the below operations is important; we must - // execute the wrapped query to ensure the untrusted clause has been - // fully isolated. - - // TODO: decode SORT ORDER and LIMIT clauses, since they can contain - // "expr" inside that need to be validated - - final String wrappedSql = buildQuery(projection, - wrap(queryArgs.getString(QUERY_ARG_SQL_SELECTION)), - queryArgs.getString(QUERY_ARG_SQL_GROUP_BY), - queryArgs.getString(QUERY_ARG_SQL_HAVING), - queryArgs.getString(QUERY_ARG_SQL_SORT_ORDER), - queryArgs.getString(QUERY_ARG_SQL_LIMIT)); - - // Validate the unwrapped query - db.validateSql(unwrappedSql, cancellationSignal); - - // Execute wrapped query for extra protection - sql = wrappedSql; - } else { - // Execute unwrapped query - sql = unwrappedSql; + String sqlForValidation = buildQuery(projectionIn, "(" + selection + ")", groupBy, + having, sortOrder, limit); + db.validateSql(sqlForValidation, cancellationSignal); // will throw if query is invalid } - final String[] sqlArgs = ArrayUtils.concat(String.class, - queryArgs.getStringArray(QUERY_ARG_SQL_SELECTION_ARGS), mWhereArgs); + String sql = buildQuery( + projectionIn, selection, groupBy, having, + sortOrder, limit); - if (Build.IS_DEBUGGABLE && Log.isLoggable(TAG, Log.DEBUG)) { - Log.d(TAG, sql + " with args " + Arrays.toString(sqlArgs)); + if (Log.isLoggable(TAG, Log.DEBUG)) { + Log.d(TAG, "Performing query: " + sql); } - return db.rawQueryWithFactory( - mFactory, sql, sqlArgs, + mFactory, sql, selectionArgs, SQLiteDatabase.findEditTable(mTables), cancellationSignal); // will throw if query is invalid } /** - * Perform an update by combining all current settings and the - * information passed into this method. - * - * @param db the database to update on - * @param selection A filter declaring which rows to return, - * formatted as an SQL WHERE clause (excluding the WHERE - * itself). Passing null will return all rows for the given URL. - * @param selectionArgs You may include ?s in selection, which - * will be replaced by the values from selectionArgs, in order - * that they appear in the selection. The values will be bound - * as Strings. - * @return the number of rows updated - */ - public int update(@NonNull SQLiteDatabase db, @NonNull ContentValues values, - @Nullable String selection, @Nullable String[] selectionArgs) { - Objects.requireNonNull(mTables, "No tables defined"); - Objects.requireNonNull(db, "No database defined"); - Objects.requireNonNull(values, "No values defined"); - - if (mStrict) { - // Validate the user-supplied selection to detect syntactic anomalies - // in the selection string that could indicate a SQL injection attempt. - // The idea is to ensure that the selection clause is a valid SQL expression - // by compiling it twice: once wrapped in parentheses and once as - // originally specified. An attacker cannot create an expression that - // would escape the SQL expression while maintaining balanced parentheses - // in both the wrapped and original forms. - final String sql = buildUpdate(values, wrap(selection)); - db.validateSql(sql, null); // will throw if query is invalid - } - - final ArrayMap<String, Object> rawValues = values.getValues(); - final String[] updateArgs = new String[rawValues.size()]; - for (int i = 0; i < updateArgs.length; i++) { - final Object arg = rawValues.valueAt(i); - updateArgs[i] = (arg != null) ? arg.toString() : null; - } - - final String sql = buildUpdate(values, selection); - final String[] sqlArgs = ArrayUtils.concat(String.class, updateArgs, - ArrayUtils.concat(String.class, selectionArgs, mWhereArgs)); - - if (Build.IS_DEBUGGABLE && Log.isLoggable(TAG, Log.DEBUG)) { - Log.d(TAG, sql + " with args " + Arrays.toString(sqlArgs)); - } - - return db.executeSql(sql, sqlArgs); - } - - /** - * Perform a delete by combining all current settings and the - * information passed into this method. - * - * @param db the database to delete on - * @param selection A filter declaring which rows to return, - * formatted as an SQL WHERE clause (excluding the WHERE - * itself). Passing null will return all rows for the given URL. - * @param selectionArgs You may include ?s in selection, which - * will be replaced by the values from selectionArgs, in order - * that they appear in the selection. The values will be bound - * as Strings. - * @return the number of rows deleted - */ - public int delete(@NonNull SQLiteDatabase db, @Nullable String selection, - @Nullable String[] selectionArgs) { - Objects.requireNonNull(mTables, "No tables defined"); - Objects.requireNonNull(db, "No database defined"); - - if (mStrict) { - // Validate the user-supplied selection to detect syntactic anomalies - // in the selection string that could indicate a SQL injection attempt. - // The idea is to ensure that the selection clause is a valid SQL expression - // by compiling it twice: once wrapped in parentheses and once as - // originally specified. An attacker cannot create an expression that - // would escape the SQL expression while maintaining balanced parentheses - // in both the wrapped and original forms. - final String sql = buildDelete(wrap(selection)); - db.validateSql(sql, null); // will throw if query is invalid - } - - final String sql = buildDelete(selection); - final String[] sqlArgs = ArrayUtils.concat(String.class, selectionArgs, mWhereArgs); - - if (Build.IS_DEBUGGABLE && Log.isLoggable(TAG, Log.DEBUG)) { - Log.d(TAG, sql + " with args " + Arrays.toString(sqlArgs)); - } - - return db.executeSql(sql, sqlArgs); - } - - /** * Construct a SELECT statement suitable for use in a group of * SELECT statements that will be joined through UNION operators * in buildUnionQuery. @@ -754,10 +434,28 @@ public class SQLiteQueryBuilder { String[] projectionIn, String selection, String groupBy, String having, String sortOrder, String limit) { String[] projection = computeProjection(projectionIn); - String where = computeWhere(selection); + + StringBuilder where = new StringBuilder(); + boolean hasBaseWhereClause = mWhereClause != null && mWhereClause.length() > 0; + + if (hasBaseWhereClause) { + where.append(mWhereClause.toString()); + where.append(')'); + } + + // Tack on the user's selection, if present. + if (selection != null && selection.length() > 0) { + if (hasBaseWhereClause) { + where.append(" AND "); + } + + where.append('('); + where.append(selection); + where.append(')'); + } return buildQueryString( - mDistinct, mTables, projection, where, + mDistinct, mTables, projection, where.toString(), groupBy, having, sortOrder, limit); } @@ -774,42 +472,6 @@ public class SQLiteQueryBuilder { return buildQuery(projectionIn, selection, groupBy, having, sortOrder, limit); } - /** {@hide} */ - public String buildUpdate(ContentValues values, String selection) { - if (values == null || values.isEmpty()) { - throw new IllegalArgumentException("Empty values"); - } - - StringBuilder sql = new StringBuilder(120); - sql.append("UPDATE "); - sql.append(mTables); - sql.append(" SET "); - - final ArrayMap<String, Object> rawValues = values.getValues(); - for (int i = 0; i < rawValues.size(); i++) { - if (i > 0) { - sql.append(','); - } - sql.append(rawValues.keyAt(i)); - sql.append("=?"); - } - - final String where = computeWhere(selection); - appendClause(sql, " WHERE ", where); - return sql.toString(); - } - - /** {@hide} */ - public String buildDelete(String selection) { - StringBuilder sql = new StringBuilder(120); - sql.append("DELETE FROM "); - sql.append(mTables); - - final String where = computeWhere(selection); - appendClause(sql, " WHERE ", where); - return sql.toString(); - } - /** * Construct a SELECT statement suitable for use in a group of * SELECT statements that will be joined through UNION operators @@ -934,7 +596,7 @@ public class SQLiteQueryBuilder { return query.toString(); } - private @Nullable String[] computeProjection(@Nullable String[] projectionIn) { + private String[] computeProjection(String[] projectionIn) { if (projectionIn != null && projectionIn.length > 0) { if (mProjectionMap != null) { String[] projection = new String[projectionIn.length]; @@ -957,7 +619,7 @@ public class SQLiteQueryBuilder { } throw new IllegalArgumentException("Invalid column " - + projectionIn[i] + " from tables " + mTables); + + projectionIn[i]); } return projection; } else { @@ -983,53 +645,4 @@ public class SQLiteQueryBuilder { } return null; } - - private @NonNull String computeWhere(@Nullable String selection) { - final boolean hasUser = selection != null && selection.length() > 0; - final boolean hasInternal = mWhereClause != null && mWhereClause.length() > 0; - - if (hasUser || hasInternal) { - final StringBuilder where = new StringBuilder(); - if (hasUser) { - where.append('(').append(selection).append(')'); - } - if (hasUser && hasInternal) { - where.append(" AND "); - } - if (hasInternal) { - where.append('(').append(mWhereClause.toString()).append(')'); - } - return where.toString(); - } else { - return null; - } - } - - /** - * Wrap given argument in parenthesis, unless it's {@code null} or - * {@code ()}, in which case return it verbatim. - */ - private @Nullable String wrap(@Nullable String arg) { - if (arg == null) { - return null; - } else if (arg.equals("")) { - return arg; - } else { - return "(" + arg + ")"; - } - } - - private static void maybePutString(@NonNull Bundle bundle, @NonNull String key, - @Nullable String value) { - if (value != null) { - bundle.putString(key, value); - } - } - - private static void maybePutStringArray(@NonNull Bundle bundle, @NonNull String key, - @Nullable String[] value) { - if (value != null) { - bundle.putStringArray(key, value); - } - } } diff --git a/core/java/android/database/sqlite/SQLiteStatementBuilder.java b/core/java/android/database/sqlite/SQLiteStatementBuilder.java new file mode 100644 index 000000000000..e2efb2f8c39b --- /dev/null +++ b/core/java/android/database/sqlite/SQLiteStatementBuilder.java @@ -0,0 +1,1036 @@ +/* + * Copyright (C) 2006 The Android Open Source Project + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package android.database.sqlite; + +import static android.content.ContentResolver.QUERY_ARG_SQL_GROUP_BY; +import static android.content.ContentResolver.QUERY_ARG_SQL_HAVING; +import static android.content.ContentResolver.QUERY_ARG_SQL_LIMIT; +import static android.content.ContentResolver.QUERY_ARG_SQL_SELECTION; +import static android.content.ContentResolver.QUERY_ARG_SQL_SELECTION_ARGS; +import static android.content.ContentResolver.QUERY_ARG_SQL_SORT_ORDER; + +import android.annotation.NonNull; +import android.annotation.Nullable; +import android.content.ContentResolver; +import android.content.ContentValues; +import android.database.Cursor; +import android.database.DatabaseUtils; +import android.os.Build; +import android.os.Bundle; +import android.os.CancellationSignal; +import android.os.OperationCanceledException; +import android.provider.BaseColumns; +import android.text.TextUtils; +import android.util.ArrayMap; +import android.util.Log; + +import com.android.internal.util.ArrayUtils; + +import dalvik.system.VMRuntime; + +import libcore.util.EmptyArray; + +import java.util.Arrays; +import java.util.Iterator; +import java.util.Map; +import java.util.Map.Entry; +import java.util.Objects; +import java.util.Set; +import java.util.regex.Pattern; + +/** + * This is a convenience class that helps build SQL queries to be sent to + * {@link SQLiteDatabase} objects. + * @hide + */ +public class SQLiteStatementBuilder { + private static final String TAG = "SQLiteStatementBuilder"; + private static final Pattern sLimitPattern = + Pattern.compile("\\s*\\d+\\s*(,\\s*\\d+\\s*)?"); + + private Map<String, String> mProjectionMap = null; + private String mTables = ""; + private StringBuilder mWhereClause = null; // lazily created + private String[] mWhereArgs = EmptyArray.STRING; + private boolean mDistinct; + private SQLiteDatabase.CursorFactory mFactory; + private boolean mStrict; + + public SQLiteStatementBuilder() { + mDistinct = false; + mFactory = null; + } + + /** + * Mark the query as DISTINCT. + * + * @param distinct if true the query is DISTINCT, otherwise it isn't + */ + public void setDistinct(boolean distinct) { + mDistinct = distinct; + } + + /** + * Returns the list of tables being queried + * + * @return the list of tables being queried + */ + public String getTables() { + return mTables; + } + + /** + * Sets the list of tables to query. Multiple tables can be specified to perform a join. + * For example: + * setTables("foo, bar") + * setTables("foo LEFT OUTER JOIN bar ON (foo.id = bar.foo_id)") + * + * @param inTables the list of tables to query on + */ + public void setTables(String inTables) { + mTables = inTables; + } + + /** {@hide} */ + public @Nullable String getWhere() { + return (mWhereClause != null) ? mWhereClause.toString() : null; + } + + /** {@hide} */ + public String[] getWhereArgs() { + return mWhereArgs; + } + + /** + * Append a chunk to the {@code WHERE} clause of the query. All chunks + * appended are surrounded by parenthesis and {@code AND}ed with the + * selection passed to {@link #query}. The final {@code WHERE} clause looks + * like: + * + * <pre> + * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) + * </pre> + * + * @param inWhere the chunk of text to append to the {@code WHERE} clause. + */ + public void appendWhere(@NonNull CharSequence inWhere) { + appendWhere(inWhere, EmptyArray.STRING); + } + + /** + * Append a chunk to the {@code WHERE} clause of the query. All chunks + * appended are surrounded by parenthesis and {@code AND}ed with the + * selection passed to {@link #query}. The final {@code WHERE} clause looks + * like: + * + * <pre> + * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) + * </pre> + * + * @param inWhere the chunk of text to append to the {@code WHERE} clause. + * @param inWhereArgs list of arguments to be bound to any '?' occurrences + * in the where clause. + */ + public void appendWhere(@NonNull CharSequence inWhere, String... inWhereArgs) { + if (mWhereClause == null) { + mWhereClause = new StringBuilder(inWhere.length() + 16); + } + mWhereClause.append(inWhere); + mWhereArgs = ArrayUtils.concat(String.class, mWhereArgs, inWhereArgs); + } + + /** + * Append a standalone expression to the {@code WHERE} clause of this query. + * <p> + * This method differs from {@link #appendWhere(CharSequence)} in that it + * automatically appends {@code AND} to any existing {@code WHERE} clause + * already under construction before appending the given standalone + * expression. + * + * @param inWhere the standalone expression to append to the {@code WHERE} + * clause. It will be wrapped in parentheses when it's appended. + */ + public void appendWhereExpression(@NonNull CharSequence inWhere) { + appendWhereExpression(inWhere, EmptyArray.STRING); + } + + /** + * Append a standalone expression to the {@code WHERE} clause of this query. + * <p> + * This method differs from {@link #appendWhere(CharSequence)} in that it + * automatically appends {@code AND} to any existing {@code WHERE} clause + * already under construction before appending the given standalone + * expression. + * + * @param inWhere the standalone expression to append to the {@code WHERE} + * clause. It will be wrapped in parentheses when it's appended. + * @param inWhereArgs list of arguments to be bound to any '?' occurrences + * in the standalone expression. + */ + public void appendWhereExpression(@NonNull CharSequence inWhere, String... inWhereArgs) { + if (mWhereClause == null) { + mWhereClause = new StringBuilder(inWhere.length() + 16); + } + if (mWhereClause.length() > 0) { + mWhereClause.append(" AND "); + } + mWhereClause.append('(').append(inWhere).append(')'); + mWhereArgs = ArrayUtils.concat(String.class, mWhereArgs, inWhereArgs); + } + + /** + * Append a chunk to the {@code WHERE} clause of the query. All chunks + * appended are surrounded by parenthesis and {@code AND}ed with the + * selection passed to {@link #query}. The final {@code WHERE} clause looks + * like this: + * + * <pre> + * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) + * </pre> + * + * @param inWhere the chunk of text to append to the {@code WHERE} clause. + * It will be escaped to avoid SQL injection attacks. + */ + public void appendWhereEscapeString(@NonNull String inWhere) { + appendWhereEscapeString(inWhere, EmptyArray.STRING); + } + + /** + * Append a chunk to the {@code WHERE} clause of the query. All chunks + * appended are surrounded by parenthesis and {@code AND}ed with the + * selection passed to {@link #query}. The final {@code WHERE} clause looks + * like this: + * + * <pre> + * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) + * </pre> + * + * @param inWhere the chunk of text to append to the {@code WHERE} clause. + * It will be escaped to avoid SQL injection attacks. + * @param inWhereArgs list of arguments to be bound to any '?' occurrences + * in the where clause. + */ + public void appendWhereEscapeString(@NonNull String inWhere, String... inWhereArgs) { + if (mWhereClause == null) { + mWhereClause = new StringBuilder(inWhere.length() + 16); + } + DatabaseUtils.appendEscapedSQLString(mWhereClause, inWhere); + mWhereArgs = ArrayUtils.concat(String.class, mWhereArgs, inWhereArgs); + } + + /** + * Sets the projection map for the query. The projection map maps + * from column names that the caller passes into query to database + * column names. This is useful for renaming columns as well as + * disambiguating column names when doing joins. For example you + * could map "name" to "people.name". If a projection map is set + * it must contain all column names the user may request, even if + * the key and value are the same. + * + * @param columnMap maps from the user column names to the database column names + */ + public void setProjectionMap(Map<String, String> columnMap) { + mProjectionMap = columnMap; + } + + /** + * Sets the cursor factory to be used for the query. You can use + * one factory for all queries on a database but it is normally + * easier to specify the factory when doing this query. + * + * @param factory the factory to use. + */ + public void setCursorFactory(SQLiteDatabase.CursorFactory factory) { + mFactory = factory; + } + + /** + * When set, the selection is verified against malicious arguments. + * When using this class to create a statement using + * {@link #buildQueryString(boolean, String, String[], String, String, String, String, String)}, + * non-numeric limits will raise an exception. If a projection map is specified, fields + * not in that map will be ignored. + * If this class is used to execute the statement directly using + * {@link #query(SQLiteDatabase, String[], String, String[], String, String, String)} + * or + * {@link #query(SQLiteDatabase, String[], String, String[], String, String, String, String)}, + * additionally also parenthesis escaping selection are caught. + * + * To summarize: To get maximum protection against malicious third party apps (for example + * content provider consumers), make sure to do the following: + * <ul> + * <li>Set this value to true</li> + * <li>Use a projection map</li> + * <li>Use one of the query overloads instead of getting the statement as a sql string</li> + * </ul> + * By default, this value is false. + */ + public void setStrict(boolean strict) { + mStrict = strict; + } + + /** + * Build an SQL query string from the given clauses. + * + * @param distinct true if you want each row to be unique, false otherwise. + * @param tables The table names to compile the query against. + * @param columns A list of which columns to return. Passing null will + * return all columns, which is discouraged to prevent reading + * data from storage that isn't going to be used. + * @param where A filter declaring which rows to return, formatted as an SQL + * WHERE clause (excluding the WHERE itself). Passing null will + * return all rows for the given URL. + * @param groupBy A filter declaring how to group rows, formatted as an SQL + * GROUP BY clause (excluding the GROUP BY itself). Passing null + * will cause the rows to not be grouped. + * @param having A filter declare which row groups to include in the cursor, + * if row grouping is being used, formatted as an SQL HAVING + * clause (excluding the HAVING itself). Passing null will cause + * all row groups to be included, and is required when row + * grouping is not being used. + * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause + * (excluding the ORDER BY itself). Passing null will use the + * default sort order, which may be unordered. + * @param limit Limits the number of rows returned by the query, + * formatted as LIMIT clause. Passing null denotes no LIMIT clause. + * @return the SQL query string + */ + public static String buildQueryString( + boolean distinct, String tables, String[] columns, String where, + String groupBy, String having, String orderBy, String limit) { + if (TextUtils.isEmpty(groupBy) && !TextUtils.isEmpty(having)) { + throw new IllegalArgumentException( + "HAVING clauses are only permitted when using a groupBy clause"); + } + if (!TextUtils.isEmpty(limit) && !sLimitPattern.matcher(limit).matches()) { + throw new IllegalArgumentException("invalid LIMIT clauses:" + limit); + } + + StringBuilder query = new StringBuilder(120); + + query.append("SELECT "); + if (distinct) { + query.append("DISTINCT "); + } + if (columns != null && columns.length != 0) { + appendColumns(query, columns); + } else { + query.append("* "); + } + query.append("FROM "); + query.append(tables); + appendClause(query, " WHERE ", where); + appendClause(query, " GROUP BY ", groupBy); + appendClause(query, " HAVING ", having); + appendClause(query, " ORDER BY ", orderBy); + appendClause(query, " LIMIT ", limit); + + return query.toString(); + } + + private static void appendClause(StringBuilder s, String name, String clause) { + if (!TextUtils.isEmpty(clause)) { + s.append(name); + s.append(clause); + } + } + + /** + * Add the names that are non-null in columns to s, separating + * them with commas. + */ + public static void appendColumns(StringBuilder s, String[] columns) { + int n = columns.length; + + for (int i = 0; i < n; i++) { + String column = columns[i]; + + if (column != null) { + if (i > 0) { + s.append(", "); + } + s.append(column); + } + } + s.append(' '); + } + + /** + * Perform a query by combining all current settings and the + * information passed into this method. + * + * @param db the database to query on + * @param projection A list of which columns to return. Passing + * null will return all columns, which is discouraged to prevent + * reading data from storage that isn't going to be used. + * @param selection A filter declaring which rows to return, + * formatted as an SQL WHERE clause (excluding the WHERE + * itself). Passing null will return all rows for the given URL. + * @param selectionArgs You may include ?s in selection, which + * will be replaced by the values from selectionArgs, in order + * that they appear in the selection. The values will be bound + * as Strings. + * @param groupBy A filter declaring how to group rows, formatted + * as an SQL GROUP BY clause (excluding the GROUP BY + * itself). Passing null will cause the rows to not be grouped. + * @param having A filter declare which row groups to include in + * the cursor, if row grouping is being used, formatted as an + * SQL HAVING clause (excluding the HAVING itself). Passing + * null will cause all row groups to be included, and is + * required when row grouping is not being used. + * @param sortOrder How to order the rows, formatted as an SQL + * ORDER BY clause (excluding the ORDER BY itself). Passing null + * will use the default sort order, which may be unordered. + * @return a cursor over the result set + * @see android.content.ContentResolver#query(android.net.Uri, String[], + * String, String[], String) + */ + public @Nullable Cursor query(@NonNull SQLiteDatabase db, + @Nullable String[] projection, + @Nullable String selection, + @Nullable String[] selectionArgs, + @Nullable String groupBy, + @Nullable String having, + @Nullable String sortOrder) { + return query(db, projection, selection, selectionArgs, groupBy, having, sortOrder, + null /* limit */, null /* cancellationSignal */); + } + + /** + * Perform a query by combining all current settings and the + * information passed into this method. + * + * @param db the database to query on + * @param projection A list of which columns to return. Passing + * null will return all columns, which is discouraged to prevent + * reading data from storage that isn't going to be used. + * @param selection A filter declaring which rows to return, + * formatted as an SQL WHERE clause (excluding the WHERE + * itself). Passing null will return all rows for the given URL. + * @param selectionArgs You may include ?s in selection, which + * will be replaced by the values from selectionArgs, in order + * that they appear in the selection. The values will be bound + * as Strings. + * @param groupBy A filter declaring how to group rows, formatted + * as an SQL GROUP BY clause (excluding the GROUP BY + * itself). Passing null will cause the rows to not be grouped. + * @param having A filter declare which row groups to include in + * the cursor, if row grouping is being used, formatted as an + * SQL HAVING clause (excluding the HAVING itself). Passing + * null will cause all row groups to be included, and is + * required when row grouping is not being used. + * @param sortOrder How to order the rows, formatted as an SQL + * ORDER BY clause (excluding the ORDER BY itself). Passing null + * will use the default sort order, which may be unordered. + * @param limit Limits the number of rows returned by the query, + * formatted as LIMIT clause. Passing null denotes no LIMIT clause. + * @return a cursor over the result set + * @see android.content.ContentResolver#query(android.net.Uri, String[], + * String, String[], String) + */ + public @Nullable Cursor query(@NonNull SQLiteDatabase db, + @Nullable String[] projection, + @Nullable String selection, + @Nullable String[] selectionArgs, + @Nullable String groupBy, + @Nullable String having, + @Nullable String sortOrder, + @Nullable String limit) { + return query(db, projection, selection, selectionArgs, + groupBy, having, sortOrder, limit, null); + } + + /** + * Perform a query by combining all current settings and the + * information passed into this method. + * + * @param db the database to query on + * @param projection A list of which columns to return. Passing + * null will return all columns, which is discouraged to prevent + * reading data from storage that isn't going to be used. + * @param selection A filter declaring which rows to return, + * formatted as an SQL WHERE clause (excluding the WHERE + * itself). Passing null will return all rows for the given URL. + * @param selectionArgs You may include ?s in selection, which + * will be replaced by the values from selectionArgs, in order + * that they appear in the selection. The values will be bound + * as Strings. + * @param sortOrder How to order the rows, formatted as an SQL + * ORDER BY clause (excluding the ORDER BY itself). Passing null + * will use the default sort order, which may be unordered. + * @param cancellationSignal A signal to cancel the operation in progress, or null if none. + * If the operation is canceled, then {@link OperationCanceledException} will be thrown + * when the query is executed. + * @return a cursor over the result set + * @see android.content.ContentResolver#query(android.net.Uri, String[], + * String, String[], String) + */ + public @Nullable Cursor query(@NonNull SQLiteDatabase db, + @Nullable String[] projection, + @Nullable String selection, + @Nullable String[] selectionArgs, + @Nullable String sortOrder, + @Nullable CancellationSignal cancellationSignal) { + return query(db, projection, selection, selectionArgs, null, null, sortOrder, null, + cancellationSignal); + } + + /** + * Perform a query by combining all current settings and the + * information passed into this method. + * + * @param db the database to query on + * @param projection A list of which columns to return. Passing + * null will return all columns, which is discouraged to prevent + * reading data from storage that isn't going to be used. + * @param selection A filter declaring which rows to return, + * formatted as an SQL WHERE clause (excluding the WHERE + * itself). Passing null will return all rows for the given URL. + * @param selectionArgs You may include ?s in selection, which + * will be replaced by the values from selectionArgs, in order + * that they appear in the selection. The values will be bound + * as Strings. + * @param groupBy A filter declaring how to group rows, formatted + * as an SQL GROUP BY clause (excluding the GROUP BY + * itself). Passing null will cause the rows to not be grouped. + * @param having A filter declare which row groups to include in + * the cursor, if row grouping is being used, formatted as an + * SQL HAVING clause (excluding the HAVING itself). Passing + * null will cause all row groups to be included, and is + * required when row grouping is not being used. + * @param sortOrder How to order the rows, formatted as an SQL + * ORDER BY clause (excluding the ORDER BY itself). Passing null + * will use the default sort order, which may be unordered. + * @param limit Limits the number of rows returned by the query, + * formatted as LIMIT clause. Passing null denotes no LIMIT clause. + * @param cancellationSignal A signal to cancel the operation in progress, or null if none. + * If the operation is canceled, then {@link OperationCanceledException} will be thrown + * when the query is executed. + * @return a cursor over the result set + * @see android.content.ContentResolver#query(android.net.Uri, String[], + * String, String[], String) + */ + public @Nullable Cursor query(@NonNull SQLiteDatabase db, + @Nullable String[] projection, + @Nullable String selection, + @Nullable String[] selectionArgs, + @Nullable String groupBy, + @Nullable String having, + @Nullable String sortOrder, + @Nullable String limit, + @Nullable CancellationSignal cancellationSignal) { + final Bundle queryArgs = new Bundle(); + maybePutString(queryArgs, QUERY_ARG_SQL_SELECTION, selection); + maybePutStringArray(queryArgs, QUERY_ARG_SQL_SELECTION_ARGS, selectionArgs); + maybePutString(queryArgs, QUERY_ARG_SQL_GROUP_BY, groupBy); + maybePutString(queryArgs, QUERY_ARG_SQL_HAVING, having); + maybePutString(queryArgs, QUERY_ARG_SQL_SORT_ORDER, sortOrder); + maybePutString(queryArgs, QUERY_ARG_SQL_LIMIT, limit); + return query(db, projection, queryArgs, cancellationSignal); + } + + /** + * Perform a query by combining all current settings and the information + * passed into this method. + * + * @param db the database to query on + * @param projection A list of which columns to return. Passing null will + * return all columns, which is discouraged to prevent reading + * data from storage that isn't going to be used. + * @param queryArgs A collection of arguments for the query, defined using + * keys such as {@link ContentResolver#QUERY_ARG_SQL_SELECTION} + * and {@link ContentResolver#QUERY_ARG_SQL_SELECTION_ARGS}. + * @param cancellationSignal A signal to cancel the operation in progress, + * or null if none. If the operation is canceled, then + * {@link OperationCanceledException} will be thrown when the + * query is executed. + * @return a cursor over the result set + */ + public Cursor query(@NonNull SQLiteDatabase db, + @Nullable String[] projection, + @Nullable Bundle queryArgs, + @Nullable CancellationSignal cancellationSignal) { + Objects.requireNonNull(db, "No database defined"); + + if (VMRuntime.getRuntime().getTargetSdkVersion() >= Build.VERSION_CODES.Q) { + Objects.requireNonNull(mTables, "No tables defined"); + } else if (mTables == null) { + return null; + } + + if (queryArgs == null) { + queryArgs = Bundle.EMPTY; + } + + // Final SQL that we will execute + final String sql; + + final String unwrappedSql = buildQuery(projection, + queryArgs.getString(QUERY_ARG_SQL_SELECTION), + queryArgs.getString(QUERY_ARG_SQL_GROUP_BY), + queryArgs.getString(QUERY_ARG_SQL_HAVING), + queryArgs.getString(QUERY_ARG_SQL_SORT_ORDER), + queryArgs.getString(QUERY_ARG_SQL_LIMIT)); + + if (mStrict) { + // Validate the user-supplied selection to detect syntactic anomalies + // in the selection string that could indicate a SQL injection attempt. + // The idea is to ensure that the selection clause is a valid SQL expression + // by compiling it twice: once wrapped in parentheses and once as + // originally specified. An attacker cannot create an expression that + // would escape the SQL expression while maintaining balanced parentheses + // in both the wrapped and original forms. + + // NOTE: The ordering of the below operations is important; we must + // execute the wrapped query to ensure the untrusted clause has been + // fully isolated. + + // TODO: decode SORT ORDER and LIMIT clauses, since they can contain + // "expr" inside that need to be validated + + final String wrappedSql = buildQuery(projection, + wrap(queryArgs.getString(QUERY_ARG_SQL_SELECTION)), + queryArgs.getString(QUERY_ARG_SQL_GROUP_BY), + queryArgs.getString(QUERY_ARG_SQL_HAVING), + queryArgs.getString(QUERY_ARG_SQL_SORT_ORDER), + queryArgs.getString(QUERY_ARG_SQL_LIMIT)); + + // Validate the unwrapped query + db.validateSql(unwrappedSql, cancellationSignal); + + // Execute wrapped query for extra protection + sql = wrappedSql; + } else { + // Execute unwrapped query + sql = unwrappedSql; + } + + final String[] sqlArgs = ArrayUtils.concat(String.class, + queryArgs.getStringArray(QUERY_ARG_SQL_SELECTION_ARGS), mWhereArgs); + + if (Build.IS_DEBUGGABLE && Log.isLoggable(TAG, Log.DEBUG)) { + Log.d(TAG, sql + " with args " + Arrays.toString(sqlArgs)); + } + + return db.rawQueryWithFactory( + mFactory, sql, sqlArgs, + SQLiteDatabase.findEditTable(mTables), + cancellationSignal); // will throw if query is invalid + } + + /** + * Perform an update by combining all current settings and the + * information passed into this method. + * + * @param db the database to update on + * @param selection A filter declaring which rows to return, + * formatted as an SQL WHERE clause (excluding the WHERE + * itself). Passing null will return all rows for the given URL. + * @param selectionArgs You may include ?s in selection, which + * will be replaced by the values from selectionArgs, in order + * that they appear in the selection. The values will be bound + * as Strings. + * @return the number of rows updated + */ + public int update(@NonNull SQLiteDatabase db, @NonNull ContentValues values, + @Nullable String selection, @Nullable String[] selectionArgs) { + Objects.requireNonNull(mTables, "No tables defined"); + Objects.requireNonNull(db, "No database defined"); + Objects.requireNonNull(values, "No values defined"); + + if (mStrict) { + // Validate the user-supplied selection to detect syntactic anomalies + // in the selection string that could indicate a SQL injection attempt. + // The idea is to ensure that the selection clause is a valid SQL expression + // by compiling it twice: once wrapped in parentheses and once as + // originally specified. An attacker cannot create an expression that + // would escape the SQL expression while maintaining balanced parentheses + // in both the wrapped and original forms. + final String sql = buildUpdate(values, wrap(selection)); + db.validateSql(sql, null); // will throw if query is invalid + } + + final ArrayMap<String, Object> rawValues = values.getValues(); + final String[] updateArgs = new String[rawValues.size()]; + for (int i = 0; i < updateArgs.length; i++) { + final Object arg = rawValues.valueAt(i); + updateArgs[i] = (arg != null) ? arg.toString() : null; + } + + final String sql = buildUpdate(values, selection); + final String[] sqlArgs = ArrayUtils.concat(String.class, updateArgs, + ArrayUtils.concat(String.class, selectionArgs, mWhereArgs)); + + if (Build.IS_DEBUGGABLE && Log.isLoggable(TAG, Log.DEBUG)) { + Log.d(TAG, sql + " with args " + Arrays.toString(sqlArgs)); + } + + return db.executeSql(sql, sqlArgs); + } + + /** + * Perform a delete by combining all current settings and the + * information passed into this method. + * + * @param db the database to delete on + * @param selection A filter declaring which rows to return, + * formatted as an SQL WHERE clause (excluding the WHERE + * itself). Passing null will return all rows for the given URL. + * @param selectionArgs You may include ?s in selection, which + * will be replaced by the values from selectionArgs, in order + * that they appear in the selection. The values will be bound + * as Strings. + * @return the number of rows deleted + */ + public int delete(@NonNull SQLiteDatabase db, @Nullable String selection, + @Nullable String[] selectionArgs) { + Objects.requireNonNull(mTables, "No tables defined"); + Objects.requireNonNull(db, "No database defined"); + + if (mStrict) { + // Validate the user-supplied selection to detect syntactic anomalies + // in the selection string that could indicate a SQL injection attempt. + // The idea is to ensure that the selection clause is a valid SQL expression + // by compiling it twice: once wrapped in parentheses and once as + // originally specified. An attacker cannot create an expression that + // would escape the SQL expression while maintaining balanced parentheses + // in both the wrapped and original forms. + final String sql = buildDelete(wrap(selection)); + db.validateSql(sql, null); // will throw if query is invalid + } + + final String sql = buildDelete(selection); + final String[] sqlArgs = ArrayUtils.concat(String.class, selectionArgs, mWhereArgs); + + if (Build.IS_DEBUGGABLE && Log.isLoggable(TAG, Log.DEBUG)) { + Log.d(TAG, sql + " with args " + Arrays.toString(sqlArgs)); + } + + return db.executeSql(sql, sqlArgs); + } + + /** + * Construct a SELECT statement suitable for use in a group of + * SELECT statements that will be joined through UNION operators + * in buildUnionQuery. + * + * @param projectionIn A list of which columns to return. Passing + * null will return all columns, which is discouraged to + * prevent reading data from storage that isn't going to be + * used. + * @param selection A filter declaring which rows to return, + * formatted as an SQL WHERE clause (excluding the WHERE + * itself). Passing null will return all rows for the given + * URL. + * @param groupBy A filter declaring how to group rows, formatted + * as an SQL GROUP BY clause (excluding the GROUP BY itself). + * Passing null will cause the rows to not be grouped. + * @param having A filter declare which row groups to include in + * the cursor, if row grouping is being used, formatted as an + * SQL HAVING clause (excluding the HAVING itself). Passing + * null will cause all row groups to be included, and is + * required when row grouping is not being used. + * @param sortOrder How to order the rows, formatted as an SQL + * ORDER BY clause (excluding the ORDER BY itself). Passing null + * will use the default sort order, which may be unordered. + * @param limit Limits the number of rows returned by the query, + * formatted as LIMIT clause. Passing null denotes no LIMIT clause. + * @return the resulting SQL SELECT statement + */ + public String buildQuery( + String[] projectionIn, String selection, String groupBy, + String having, String sortOrder, String limit) { + String[] projection = computeProjection(projectionIn); + String where = computeWhere(selection); + + return buildQueryString( + mDistinct, mTables, projection, where, + groupBy, having, sortOrder, limit); + } + + /** + * @deprecated This method's signature is misleading since no SQL parameter + * substitution is carried out. The selection arguments parameter does not get + * used at all. To avoid confusion, call + * {@link #buildQuery(String[], String, String, String, String, String)} instead. + */ + @Deprecated + public String buildQuery( + String[] projectionIn, String selection, String[] selectionArgs, + String groupBy, String having, String sortOrder, String limit) { + return buildQuery(projectionIn, selection, groupBy, having, sortOrder, limit); + } + + /** {@hide} */ + public String buildUpdate(ContentValues values, String selection) { + if (values == null || values.isEmpty()) { + throw new IllegalArgumentException("Empty values"); + } + + StringBuilder sql = new StringBuilder(120); + sql.append("UPDATE "); + sql.append(mTables); + sql.append(" SET "); + + final ArrayMap<String, Object> rawValues = values.getValues(); + for (int i = 0; i < rawValues.size(); i++) { + if (i > 0) { + sql.append(','); + } + sql.append(rawValues.keyAt(i)); + sql.append("=?"); + } + + final String where = computeWhere(selection); + appendClause(sql, " WHERE ", where); + return sql.toString(); + } + + /** {@hide} */ + public String buildDelete(String selection) { + StringBuilder sql = new StringBuilder(120); + sql.append("DELETE FROM "); + sql.append(mTables); + + final String where = computeWhere(selection); + appendClause(sql, " WHERE ", where); + return sql.toString(); + } + + /** + * Construct a SELECT statement suitable for use in a group of + * SELECT statements that will be joined through UNION operators + * in buildUnionQuery. + * + * @param typeDiscriminatorColumn the name of the result column + * whose cells will contain the name of the table from which + * each row was drawn. + * @param unionColumns the names of the columns to appear in the + * result. This may include columns that do not appear in the + * table this SELECT is querying (i.e. mTables), but that do + * appear in one of the other tables in the UNION query that we + * are constructing. + * @param columnsPresentInTable a Set of the names of the columns + * that appear in this table (i.e. in the table whose name is + * mTables). Since columns in unionColumns include columns that + * appear only in other tables, we use this array to distinguish + * which ones actually are present. Other columns will have + * NULL values for results from this subquery. + * @param computedColumnsOffset all columns in unionColumns before + * this index are included under the assumption that they're + * computed and therefore won't appear in columnsPresentInTable, + * e.g. "date * 1000 as normalized_date" + * @param typeDiscriminatorValue the value used for the + * type-discriminator column in this subquery + * @param selection A filter declaring which rows to return, + * formatted as an SQL WHERE clause (excluding the WHERE + * itself). Passing null will return all rows for the given + * URL. + * @param groupBy A filter declaring how to group rows, formatted + * as an SQL GROUP BY clause (excluding the GROUP BY itself). + * Passing null will cause the rows to not be grouped. + * @param having A filter declare which row groups to include in + * the cursor, if row grouping is being used, formatted as an + * SQL HAVING clause (excluding the HAVING itself). Passing + * null will cause all row groups to be included, and is + * required when row grouping is not being used. + * @return the resulting SQL SELECT statement + */ + public String buildUnionSubQuery( + String typeDiscriminatorColumn, + String[] unionColumns, + Set<String> columnsPresentInTable, + int computedColumnsOffset, + String typeDiscriminatorValue, + String selection, + String groupBy, + String having) { + int unionColumnsCount = unionColumns.length; + String[] projectionIn = new String[unionColumnsCount]; + + for (int i = 0; i < unionColumnsCount; i++) { + String unionColumn = unionColumns[i]; + + if (unionColumn.equals(typeDiscriminatorColumn)) { + projectionIn[i] = "'" + typeDiscriminatorValue + "' AS " + + typeDiscriminatorColumn; + } else if (i <= computedColumnsOffset + || columnsPresentInTable.contains(unionColumn)) { + projectionIn[i] = unionColumn; + } else { + projectionIn[i] = "NULL AS " + unionColumn; + } + } + return buildQuery( + projectionIn, selection, groupBy, having, + null /* sortOrder */, + null /* limit */); + } + + /** + * @deprecated This method's signature is misleading since no SQL parameter + * substitution is carried out. The selection arguments parameter does not get + * used at all. To avoid confusion, call + * {@link #buildUnionSubQuery} + * instead. + */ + @Deprecated + public String buildUnionSubQuery( + String typeDiscriminatorColumn, + String[] unionColumns, + Set<String> columnsPresentInTable, + int computedColumnsOffset, + String typeDiscriminatorValue, + String selection, + String[] selectionArgs, + String groupBy, + String having) { + return buildUnionSubQuery( + typeDiscriminatorColumn, unionColumns, columnsPresentInTable, + computedColumnsOffset, typeDiscriminatorValue, selection, + groupBy, having); + } + + /** + * Given a set of subqueries, all of which are SELECT statements, + * construct a query that returns the union of what those + * subqueries return. + * @param subQueries an array of SQL SELECT statements, all of + * which must have the same columns as the same positions in + * their results + * @param sortOrder How to order the rows, formatted as an SQL + * ORDER BY clause (excluding the ORDER BY itself). Passing + * null will use the default sort order, which may be unordered. + * @param limit The limit clause, which applies to the entire union result set + * + * @return the resulting SQL SELECT statement + */ + public String buildUnionQuery(String[] subQueries, String sortOrder, String limit) { + StringBuilder query = new StringBuilder(128); + int subQueryCount = subQueries.length; + String unionOperator = mDistinct ? " UNION " : " UNION ALL "; + + for (int i = 0; i < subQueryCount; i++) { + if (i > 0) { + query.append(unionOperator); + } + query.append(subQueries[i]); + } + appendClause(query, " ORDER BY ", sortOrder); + appendClause(query, " LIMIT ", limit); + return query.toString(); + } + + private @Nullable String[] computeProjection(@Nullable 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] + " from tables " + mTables); + } + 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; + } + + private @NonNull String computeWhere(@Nullable String selection) { + final boolean hasUser = selection != null && selection.length() > 0; + final boolean hasInternal = mWhereClause != null && mWhereClause.length() > 0; + + if (hasUser || hasInternal) { + final StringBuilder where = new StringBuilder(); + if (hasUser) { + where.append('(').append(selection).append(')'); + } + if (hasUser && hasInternal) { + where.append(" AND "); + } + if (hasInternal) { + where.append('(').append(mWhereClause.toString()).append(')'); + } + return where.toString(); + } else { + return null; + } + } + + /** + * Wrap given argument in parenthesis, unless it's {@code null} or + * {@code ()}, in which case return it verbatim. + */ + private @Nullable String wrap(@Nullable String arg) { + if (arg == null) { + return null; + } else if (arg.equals("")) { + return arg; + } else { + return "(" + arg + ")"; + } + } + + private static void maybePutString(@NonNull Bundle bundle, @NonNull String key, + @Nullable String value) { + if (value != null) { + bundle.putString(key, value); + } + } + + private static void maybePutStringArray(@NonNull Bundle bundle, @NonNull String key, + @Nullable String[] value) { + if (value != null) { + bundle.putStringArray(key, value); + } + } +} |