• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
1 /*
2  * Copyright (C) 2006 The Android Open Source Project
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  *      http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 
17 package com.android.providers.media.util;
18 
19 import static android.content.ContentResolver.QUERY_ARG_SQL_GROUP_BY;
20 import static android.content.ContentResolver.QUERY_ARG_SQL_HAVING;
21 import static android.content.ContentResolver.QUERY_ARG_SQL_LIMIT;
22 import static android.content.ContentResolver.QUERY_ARG_SQL_SELECTION;
23 import static android.content.ContentResolver.QUERY_ARG_SQL_SELECTION_ARGS;
24 import static android.content.ContentResolver.QUERY_ARG_SQL_SORT_ORDER;
25 
26 import static com.android.providers.media.util.DatabaseUtils.bindSelection;
27 
28 import android.annotation.NonNull;
29 import android.annotation.Nullable;
30 import android.content.ContentValues;
31 import android.database.Cursor;
32 import android.database.DatabaseUtils;
33 import android.database.sqlite.SQLiteDatabase;
34 import android.os.Build;
35 import android.os.Bundle;
36 import android.os.CancellationSignal;
37 import android.os.OperationCanceledException;
38 import android.provider.BaseColumns;
39 import android.provider.MediaStore.MediaColumns;
40 import android.text.TextUtils;
41 import android.util.ArrayMap;
42 import android.util.Log;
43 
44 import androidx.annotation.VisibleForTesting;
45 
46 import com.android.providers.media.DatabaseHelper;
47 
48 import com.google.common.base.Strings;
49 
50 import java.util.Arrays;
51 import java.util.Collection;
52 import java.util.Iterator;
53 import java.util.Locale;
54 import java.util.Map;
55 import java.util.Map.Entry;
56 import java.util.Objects;
57 import java.util.Set;
58 import java.util.regex.Matcher;
59 import java.util.regex.Pattern;
60 
61 /**
62  * This is a convenience class that helps build SQL queries to be sent to
63  * {@link SQLiteDatabase} objects.
64  */
65 public class SQLiteQueryBuilder {
66 
67     public static final String ROWID_COLUMN = "rowid";
68 
69     private static final String TAG = "SQLiteQueryBuilder";
70 
71     private static final Pattern sAggregationPattern = Pattern.compile(
72             "(?i)(AVG|COUNT|MAX|MIN|SUM|TOTAL|GROUP_CONCAT|UNICODE)\\((.+)\\)");
73 
74     /**
75      * Narrow concessions to support legacy apps that aren't using proper SQL
76      * string substitution; these values come from specific bugs.
77      */
78     private static final Pattern sPattern154193772 = Pattern.compile(
79             "(?i)%\\.(wmv|wm|wtv|asf|hls|mp4|m4v|mov|mp4v|3g2|3gp|3gp2|3gpp|mj2|qt|external|"
80                     + "mov|asf|avi|divx|mpg|mpeg|mkv|webm|mk3d|mks|3gp|mpegts|ts|m2ts|m2t)");
81     private static final Pattern sPattern156832140 = Pattern.compile(
82             "(?i)%com\\.gopro\\.smarty%");
83     private static final Pattern sPattern158537159 = Pattern.compile("(?i)localized");
84 
85     private static final Pattern sCustomCollatorPattern = Pattern.compile(
86             "(?i)custom_[a-zA-Z]+");
87 
88     private Map<String, String> mProjectionMap = null;
89     private Collection<Pattern> mProjectionGreylist = null;
90 
91     private String mTables = "";
92     private StringBuilder mWhereClause = null;  // lazily created
93     private boolean mDistinct;
94 
95     private static final int STRICT_PARENTHESES = 1 << 0;
96     private static final int STRICT_COLUMNS = 1 << 1;
97     private static final int STRICT_GRAMMAR = 1 << 2;
98 
99     private int mStrictFlags;
100 
101     private int mTargetSdkVersion = Build.VERSION_CODES.CUR_DEVELOPMENT;
102 
setTargetSdkVersion(int targetSdkVersion)103     public void setTargetSdkVersion(int targetSdkVersion) {
104         mTargetSdkVersion = targetSdkVersion;
105     }
106 
107     /**
108      * Raw SQL clause to obtain the value of {@link MediaColumns#_ID} from custom database function
109      * {@code _GET_ID} for INSERT operation.
110      */
111     private static final String GET_ID_FOR_INSERT_CLAUSE = "_GET_ID(?)";
112 
113     /**
114      * Raw SQL clause to obtain the value of {@link MediaColumns#_ID} from custom database function
115      * {@code _GET_ID} for UPDATE operation.
116      */
117     private static final String GET_ID_FOR_UPDATE_CLAUSE = "ifnull(_GET_ID(?), _id)";
118 
SQLiteQueryBuilder()119     public SQLiteQueryBuilder() {
120         mDistinct = false;
121     }
122 
123     /**
124      * Mark the query as {@code DISTINCT}.
125      *
126      * @param distinct if true the query is {@code DISTINCT}, otherwise it isn't
127      */
setDistinct(boolean distinct)128     public void setDistinct(boolean distinct) {
129         mDistinct = distinct;
130     }
131 
132     /**
133      * Get if the query is marked as {@code DISTINCT}, as last configured by
134      * {@link #setDistinct(boolean)}.
135      */
isDistinct()136     public boolean isDistinct() {
137         return mDistinct;
138     }
139 
140     /**
141      * Returns the list of tables being queried
142      *
143      * @return the list of tables being queried
144      */
getTables()145     public @Nullable String getTables() {
146         return mTables;
147     }
148 
149     /**
150      * Sets the list of tables to query. Multiple tables can be specified to perform a join.
151      * For example:
152      *   setTables("foo, bar")
153      *   setTables("foo LEFT OUTER JOIN bar ON (foo.id = bar.foo_id)")
154      *
155      * @param inTables the list of tables to query on
156      */
setTables(@ullable String inTables)157     public void setTables(@Nullable String inTables) {
158         mTables = inTables;
159     }
160 
161     /**
162      * Append a chunk to the {@code WHERE} clause of the query. All chunks appended are surrounded
163      * by parenthesis and {@code AND}ed with the selection passed to {@link #query}. The final
164      * {@code WHERE} clause looks like:
165      * <p>
166      * WHERE (&lt;append chunk 1>&lt;append chunk2>) AND (&lt;query() selection parameter>)
167      *
168      * @param inWhere the chunk of text to append to the {@code WHERE} clause.
169      */
appendWhere(@onNull CharSequence inWhere)170     public void appendWhere(@NonNull CharSequence inWhere) {
171         if (mWhereClause == null) {
172             mWhereClause = new StringBuilder(inWhere.length() + 16);
173         }
174         mWhereClause.append(inWhere);
175     }
176 
177     /**
178      * Append a chunk to the {@code WHERE} clause of the query. All chunks appended are surrounded
179      * by parenthesis and ANDed with the selection passed to {@link #query}. The final
180      * {@code WHERE} clause looks like:
181      * <p>
182      * WHERE (&lt;append chunk 1>&lt;append chunk2>) AND (&lt;query() selection parameter>)
183      *
184      * @param inWhere the chunk of text to append to the {@code WHERE} clause. it will be escaped
185      * to avoid SQL injection attacks
186      */
appendWhereEscapeString(@onNull String inWhere)187     public void appendWhereEscapeString(@NonNull String inWhere) {
188         if (mWhereClause == null) {
189             mWhereClause = new StringBuilder(inWhere.length() + 16);
190         }
191         DatabaseUtils.appendEscapedSQLString(mWhereClause, inWhere);
192     }
193 
194     /**
195      * Add a standalone chunk to the {@code WHERE} clause of this query.
196      * <p>
197      * This method differs from {@link #appendWhere(CharSequence)} in that it
198      * automatically appends {@code AND} to any existing {@code WHERE} clause
199      * already under construction before appending the given standalone
200      * expression wrapped in parentheses.
201      *
202      * @param inWhere the standalone expression to append to the {@code WHERE}
203      *            clause. It will be wrapped in parentheses when it's appended.
204      */
appendWhereStandalone(@onNull CharSequence inWhere)205     public void appendWhereStandalone(@NonNull CharSequence inWhere) {
206         if (mWhereClause == null) {
207             mWhereClause = new StringBuilder(inWhere.length() + 16);
208         }
209         if (mWhereClause.length() > 0) {
210             mWhereClause.append(" AND ");
211         }
212         mWhereClause.append('(').append(inWhere).append(')');
213     }
214 
215     /**
216      * Sets the projection map for the query.  The projection map maps
217      * from column names that the caller passes into query to database
218      * column names. This is useful for renaming columns as well as
219      * disambiguating column names when doing joins. For example you
220      * could map "name" to "people.name".  If a projection map is set
221      * it must contain all column names the user may request, even if
222      * the key and value are the same.
223      *
224      * @param columnMap maps from the user column names to the database column names
225      */
setProjectionMap(@ullable Map<String, String> columnMap)226     public void setProjectionMap(@Nullable Map<String, String> columnMap) {
227         if (columnMap != null) {
228             mProjectionMap = new ArrayMap<String, String>();
229             for (Entry<String, String> entry : columnMap.entrySet()) {
230                 mProjectionMap.put(entry.getKey().toLowerCase(Locale.ROOT), entry.getValue());
231             }
232         } else {
233             mProjectionMap = null;
234         }
235     }
236 
237     /** Adds {@code column} to the projection map. */
allowColumn(String column)238     public void allowColumn(String column) {
239         if (mProjectionMap == null) {
240             mProjectionMap = new ArrayMap<>();
241         }
242         mProjectionMap.put(column, column);
243     }
244 
245     /**
246      * Gets the projection map for the query, as last configured by
247      * {@link #setProjectionMap(Map)}.
248      */
getProjectionMap()249     public @Nullable Map<String, String> getProjectionMap() {
250         return mProjectionMap;
251     }
252 
253     /**
254      * Sets a projection greylist of columns that will be allowed through, even
255      * when {@link #setStrict(boolean)} is enabled. This provides a way for
256      * abusive custom columns like {@code COUNT(*)} to continue working.
257      */
setProjectionGreylist(@ullable Collection<Pattern> projectionGreylist)258     public void setProjectionGreylist(@Nullable Collection<Pattern> projectionGreylist) {
259         mProjectionGreylist = projectionGreylist;
260     }
261 
262     /**
263      * Gets the projection greylist for the query, as last configured by
264      * {@link #setProjectionGreylist}.
265      */
getProjectionGreylist()266     public @Nullable Collection<Pattern> getProjectionGreylist() {
267         return mProjectionGreylist;
268     }
269 
270     /**
271      * When set, the selection is verified against malicious arguments. When
272      * using this class to create a statement using
273      * {@link #buildQueryString(boolean, String, String[], String, String, String, String, String)},
274      * non-numeric limits will raise an exception. If a projection map is
275      * specified, fields not in that map will be ignored. If this class is used
276      * to execute the statement directly using
277      * {@link #query(SQLiteDatabase, String[], String, String[], String, String, String)}
278      * or
279      * {@link #query(SQLiteDatabase, String[], String, String[], String, String, String, String)},
280      * additionally also parenthesis escaping selection are caught. To
281      * summarize: To get maximum protection against malicious third party apps
282      * (for example content provider consumers), make sure to do the following:
283      * <ul>
284      * <li>Set this value to true</li>
285      * <li>Use a projection map</li>
286      * <li>Use one of the query overloads instead of getting the statement as a
287      * sql string</li>
288      * </ul>
289      * <p>
290      * This feature is disabled by default on each newly constructed
291      * {@link SQLiteQueryBuilder} and needs to be manually enabled.
292      */
setStrict(boolean strict)293     public void setStrict(boolean strict) {
294         if (strict) {
295             mStrictFlags |= STRICT_PARENTHESES;
296         } else {
297             mStrictFlags &= ~STRICT_PARENTHESES;
298         }
299     }
300 
301     /**
302      * Get if the query is marked as strict, as last configured by
303      * {@link #setStrict(boolean)}.
304      */
isStrict()305     public boolean isStrict() {
306         return (mStrictFlags & STRICT_PARENTHESES) != 0;
307     }
308 
309     /**
310      * When enabled, verify that all projections and {@link ContentValues} only
311      * contain valid columns as defined by {@link #setProjectionMap(Map)}.
312      * <p>
313      * This enforcement applies to {@link #insert}, {@link #query}, and
314      * {@link #update} operations. Any enforcement failures will throw an
315      * {@link IllegalArgumentException}.
316      * <p>
317      * This feature is disabled by default on each newly constructed
318      * {@link SQLiteQueryBuilder} and needs to be manually enabled.
319      */
setStrictColumns(boolean strictColumns)320     public void setStrictColumns(boolean strictColumns) {
321         if (strictColumns) {
322             mStrictFlags |= STRICT_COLUMNS;
323         } else {
324             mStrictFlags &= ~STRICT_COLUMNS;
325         }
326     }
327 
328     /**
329      * Get if the query is marked as strict, as last configured by
330      * {@link #setStrictColumns(boolean)}.
331      */
isStrictColumns()332     public boolean isStrictColumns() {
333         return (mStrictFlags & STRICT_COLUMNS) != 0;
334     }
335 
336     /**
337      * When enabled, verify that all untrusted SQL conforms to a restricted SQL
338      * grammar. Here are the restrictions applied:
339      * <ul>
340      * <li>In {@code WHERE} and {@code HAVING} clauses: subqueries, raising, and
341      * windowing terms are rejected.
342      * <li>In {@code GROUP BY} clauses: only valid columns are allowed.
343      * <li>In {@code ORDER BY} clauses: only valid columns, collation, and
344      * ordering terms are allowed.
345      * <li>In {@code LIMIT} clauses: only numerical values and offset terms are
346      * allowed.
347      * </ul>
348      * All column references must be valid as defined by
349      * {@link #setProjectionMap(Map)}.
350      * <p>
351      * This enforcement applies to {@link #query}, {@link #update} and
352      * {@link #delete} operations. This enforcement does not apply to trusted
353      * inputs, such as those provided by {@link #appendWhere}. Any enforcement
354      * failures will throw an {@link IllegalArgumentException}.
355      * <p>
356      * This feature is disabled by default on each newly constructed
357      * {@link SQLiteQueryBuilder} and needs to be manually enabled.
358      */
setStrictGrammar(boolean strictGrammar)359     public void setStrictGrammar(boolean strictGrammar) {
360         if (strictGrammar) {
361             mStrictFlags |= STRICT_GRAMMAR;
362         } else {
363             mStrictFlags &= ~STRICT_GRAMMAR;
364         }
365     }
366 
367     /**
368      * Get if the query is marked as strict, as last configured by
369      * {@link #setStrictGrammar(boolean)}.
370      */
isStrictGrammar()371     public boolean isStrictGrammar() {
372         return (mStrictFlags & STRICT_GRAMMAR) != 0;
373     }
374 
375     /**
376      * Build an SQL query string from the given clauses.
377      *
378      * @param distinct true if you want each row to be unique, false otherwise.
379      * @param tables The table names to compile the query against.
380      * @param columns A list of which columns to return. Passing null will
381      *            return all columns, which is discouraged to prevent reading
382      *            data from storage that isn't going to be used.
383      * @param where A filter declaring which rows to return, formatted as an SQL
384      *            {@code WHERE} clause (excluding the {@code WHERE} itself). Passing {@code null} will
385      *            return all rows for the given URL.
386      * @param groupBy A filter declaring how to group rows, formatted as an SQL
387      *            {@code GROUP BY} clause (excluding the {@code GROUP BY} itself). Passing {@code null}
388      *            will cause the rows to not be grouped.
389      * @param having A filter declare which row groups to include in the cursor,
390      *            if row grouping is being used, formatted as an SQL {@code HAVING}
391      *            clause (excluding the {@code HAVING} itself). Passing null will cause
392      *            all row groups to be included, and is required when row
393      *            grouping is not being used.
394      * @param orderBy How to order the rows, formatted as an SQL {@code ORDER BY} clause
395      *            (excluding the {@code ORDER BY} itself). Passing null will use the
396      *            default sort order, which may be unordered.
397      * @param limit Limits the number of rows returned by the query,
398      *            formatted as {@code LIMIT} clause. Passing null denotes no {@code LIMIT} clause.
399      * @return the SQL query string
400      */
buildQueryString( boolean distinct, String tables, String[] columns, String where, String groupBy, String having, String orderBy, String limit)401     public static String buildQueryString(
402             boolean distinct, String tables, String[] columns, String where,
403             String groupBy, String having, String orderBy, String limit) {
404         if (TextUtils.isEmpty(groupBy) && !TextUtils.isEmpty(having)) {
405             throw new IllegalArgumentException(
406                     "HAVING clauses are only permitted when using a groupBy clause");
407         }
408 
409         StringBuilder query = new StringBuilder(120);
410 
411         query.append("SELECT ");
412         if (distinct) {
413             query.append("DISTINCT ");
414         }
415         if (columns != null && columns.length != 0) {
416             appendColumns(query, columns);
417         } else {
418             query.append("* ");
419         }
420         query.append("FROM ");
421         query.append(tables);
422         appendClause(query, " WHERE ", where);
423         appendClause(query, " GROUP BY ", groupBy);
424         appendClause(query, " HAVING ", having);
425         appendClause(query, " ORDER BY ", orderBy);
426         appendClause(query, " LIMIT ", limit);
427 
428         return query.toString();
429     }
430 
appendClause(StringBuilder s, String name, String clause)431     private static void appendClause(StringBuilder s, String name, String clause) {
432         if (!TextUtils.isEmpty(clause)) {
433             s.append(name);
434             s.append(clause);
435         }
436     }
437 
438     /**
439      * Add the names that are non-null in columns to s, separating
440      * them with commas.
441      */
appendColumns(StringBuilder s, String[] columns)442     public static void appendColumns(StringBuilder s, String[] columns) {
443         int n = columns.length;
444 
445         for (int i = 0; i < n; i++) {
446             String column = columns[i];
447 
448             if (column != null) {
449                 if (i > 0) {
450                     s.append(", ");
451                 }
452                 s.append(column);
453             }
454         }
455         s.append(' ');
456     }
457 
query(DatabaseHelper helper, String[] projectionIn, Bundle queryArgs, CancellationSignal cancellationSignal)458     public Cursor query(DatabaseHelper helper, String[] projectionIn, Bundle queryArgs,
459             CancellationSignal cancellationSignal) {
460         final String selection = queryArgs.getString(QUERY_ARG_SQL_SELECTION);
461         final String[] selectionArgs = queryArgs.getStringArray(QUERY_ARG_SQL_SELECTION_ARGS);
462         final String groupBy = queryArgs.getString(QUERY_ARG_SQL_GROUP_BY);
463         final String having = queryArgs.getString(QUERY_ARG_SQL_HAVING);
464         final String sortOrder = queryArgs.getString(QUERY_ARG_SQL_SORT_ORDER);
465         final String limit = queryArgs.getString(QUERY_ARG_SQL_LIMIT);
466         return query(helper, projectionIn, selection, selectionArgs, groupBy, having, sortOrder,
467                 limit, cancellationSignal);
468     }
469 
query(DatabaseHelper helper, String[] projectionIn, String selection, String[] selectionArgs, String groupBy, String having, String sortOrder, String limit, CancellationSignal cancellationSignal)470     public Cursor query(DatabaseHelper helper, String[] projectionIn,
471             String selection, String[] selectionArgs, String groupBy,
472             String having, String sortOrder, String limit, CancellationSignal cancellationSignal) {
473         return helper.runWithoutTransaction((db) -> {
474             return query(db, projectionIn, selection, selectionArgs, groupBy,
475                     having, sortOrder, limit, cancellationSignal);
476         });
477     }
478 
479     /**
480      * Perform a query by combining all current settings and the
481      * information passed into this method.
482      *
483      * @param db the database to query on
484      * @param projectionIn A list of which columns to return. Passing
485      *   null will return all columns, which is discouraged to prevent
486      *   reading data from storage that isn't going to be used.
487      * @param selection A filter declaring which rows to return,
488      *   formatted as an SQL {@code WHERE} clause (excluding the {@code WHERE}
489      *   itself). Passing null will return all rows for the given URL.
490      * @param selectionArgs You may include ?s in selection, which
491      *   will be replaced by the values from selectionArgs, in order
492      *   that they appear in the selection. The values will be bound
493      *   as Strings.
494      * @param groupBy A filter declaring how to group rows, formatted
495      *   as an SQL {@code GROUP BY} clause (excluding the {@code GROUP BY}
496      *   itself). Passing null will cause the rows to not be grouped.
497      * @param having A filter declare which row groups to include in
498      *   the cursor, if row grouping is being used, formatted as an
499      *   SQL {@code HAVING} clause (excluding the {@code HAVING} itself).  Passing
500      *   null will cause all row groups to be included, and is
501      *   required when row grouping is not being used.
502      * @param sortOrder How to order the rows, formatted as an SQL
503      *   {@code ORDER BY} clause (excluding the {@code ORDER BY} itself). Passing null
504      *   will use the default sort order, which may be unordered.
505      * @param limit Limits the number of rows returned by the query,
506      *   formatted as {@code LIMIT} clause. Passing null denotes no {@code LIMIT} clause.
507      * @param cancellationSignal A signal to cancel the operation in progress, or null if none.
508      * If the operation is canceled, then {@link OperationCanceledException} will be thrown
509      * when the query is executed.
510      * @return a cursor over the result set
511      * @see android.content.ContentResolver#query(android.net.Uri, String[],
512      *      String, String[], String)
513      */
query(SQLiteDatabase db, String[] projectionIn, String selection, String[] selectionArgs, String groupBy, String having, String sortOrder, String limit, CancellationSignal cancellationSignal)514     public Cursor query(SQLiteDatabase db, String[] projectionIn,
515             String selection, String[] selectionArgs, String groupBy,
516             String having, String sortOrder, String limit, CancellationSignal cancellationSignal) {
517         if (mTables == null) {
518             return null;
519         }
520 
521         final String sql;
522         final String unwrappedSql = buildQuery(
523                 projectionIn, selection, groupBy, having,
524                 sortOrder, limit);
525 
526         if (isStrictColumns()) {
527             enforceStrictColumns(projectionIn);
528         }
529         if (isStrictGrammar()) {
530             enforceStrictGrammar(selection, groupBy, having, sortOrder, limit);
531         }
532         if (isStrict() && hasUserWhere(selection)) {
533             // Validate the user-supplied selection to detect syntactic anomalies
534             // in the selection string that could indicate a SQL injection attempt.
535             // The idea is to ensure that the selection clause is a valid SQL expression
536             // by compiling it twice: once wrapped in parentheses and once as
537             // originally specified. An attacker cannot create an expression that
538             // would escape the SQL expression while maintaining balanced parentheses
539             // in both the wrapped and original forms.
540 
541             // NOTE: The ordering of the below operations is important; we must
542             // execute the wrapped query to ensure the untrusted clause has been
543             // fully isolated.
544 
545             // Validate the unwrapped query
546             db.validateSql(unwrappedSql, cancellationSignal); // will throw if query is invalid
547 
548             // Execute wrapped query for extra protection
549             final String wrappedSql = buildQuery(projectionIn, wrap(selection), groupBy,
550                     wrap(having), sortOrder, limit);
551             sql = wrappedSql;
552         } else {
553             // Execute unwrapped query
554             sql = unwrappedSql;
555         }
556 
557         final String[] sqlArgs = selectionArgs;
558         if (Log.isLoggable(TAG, Log.DEBUG)) {
559             if (Logging.IS_DEBUGGABLE) {
560                 Log.d(TAG, sql + " with args " + Arrays.toString(sqlArgs));
561             } else {
562                 Log.d(TAG, sql);
563             }
564         }
565         return db.rawQueryWithFactory(
566                 null, sql, sqlArgs,
567                 SQLiteDatabase.findEditTable(mTables),
568                 cancellationSignal); // will throw if query is invalid
569     }
570 
insert(@onNull DatabaseHelper helper, @NonNull ContentValues values)571     public long insert(@NonNull DatabaseHelper helper, @NonNull ContentValues values) {
572         // We force wrap in a transaction to ensure that all mutations increment
573         // the generation counter
574         return helper.runWithTransaction((db) -> {
575             return insert(db, values);
576         });
577     }
578 
579     /**
580      * Perform an insert by combining all current settings and the
581      * information passed into this method.
582      *
583      * @param db the database to insert on
584      * @return the row ID of the newly inserted row, or -1 if an error occurred
585      */
586     public long insert(@NonNull SQLiteDatabase db, @NonNull ContentValues values) {
587         Objects.requireNonNull(mTables, "No tables defined");
588         Objects.requireNonNull(db, "No database defined");
589         Objects.requireNonNull(values, "No values defined");
590 
591         if (isStrictColumns()) {
592             enforceStrictColumns(values);
593         }
594 
595         final String sql = buildInsert(values);
596 
597         final ArrayMap<String, Object> rawValues = com.android.providers.media.util.DatabaseUtils
598                 .getValues(values);
599         final int valuesLength = rawValues.size();
600         final Object[] sqlArgs = new Object[valuesLength];
601         for (int i = 0; i < sqlArgs.length; i++) {
602             sqlArgs[i] = rawValues.valueAt(i);
603         }
604         if (Log.isLoggable(TAG, Log.DEBUG)) {
605             if (Logging.IS_DEBUGGABLE) {
606                 Log.d(TAG, sql + " with args " + Arrays.toString(sqlArgs));
607             } else {
608                 Log.d(TAG, sql);
609             }
610         }
611         return com.android.providers.media.util.DatabaseUtils.executeInsert(db, sql, sqlArgs);
612     }
613 
614     public int update(@NonNull DatabaseHelper helper, @NonNull ContentValues values,
615             @Nullable String selection, @Nullable String[] selectionArgs) {
616         // We force wrap in a transaction to ensure that all mutations increment
617         // the generation counter
618         return helper.runWithTransaction((db) -> {
619             return update(db, values, selection, selectionArgs);
620         });
621     }
622 
623     /**
624      * Perform an update by combining all current settings and the
625      * information passed into this method.
626      *
627      * @param db the database to update on
628      * @param selection A filter declaring which rows to return,
629      *   formatted as an SQL {@code WHERE} clause (excluding the {@code WHERE}
630      *   itself). Passing null will return all rows for the given URL.
631      * @param selectionArgs You may include ?s in selection, which
632      *   will be replaced by the values from selectionArgs, in order
633      *   that they appear in the selection. The values will be bound
634      *   as Strings.
635      * @return the number of rows updated
636      */
637     public int update(@NonNull SQLiteDatabase db, @NonNull ContentValues values,
638             @Nullable String selection, @Nullable String[] selectionArgs) {
639         Objects.requireNonNull(mTables, "No tables defined");
640         Objects.requireNonNull(db, "No database defined");
641         Objects.requireNonNull(values, "No values defined");
642 
643         final String sql;
644         final String unwrappedSql = buildUpdate(values, selection);
645 
646         if (isStrictColumns()) {
647             enforceStrictColumns(values);
648         }
649         if (isStrictGrammar()) {
650             enforceStrictGrammar(selection, null, null, null, null);
651         }
652         if (isStrict() && hasUserWhere(selection)) {
653             // Validate the user-supplied selection to detect syntactic anomalies
654             // in the selection string that could indicate a SQL injection attempt.
655             // The idea is to ensure that the selection clause is a valid SQL expression
656             // by compiling it twice: once wrapped in parentheses and once as
657             // originally specified. An attacker cannot create an expression that
658             // would escape the SQL expression while maintaining balanced parentheses
659             // in both the wrapped and original forms.
660 
661             // NOTE: The ordering of the below operations is important; we must
662             // execute the wrapped query to ensure the untrusted clause has been
663             // fully isolated.
664 
665             // Validate the unwrapped query
666             db.validateSql(unwrappedSql, null); // will throw if query is invalid
667 
668             // Execute wrapped query for extra protection
669             final String wrappedSql = buildUpdate(values, wrap(selection));
670             sql = wrappedSql;
671         } else {
672             // Execute unwrapped query
673             sql = unwrappedSql;
674         }
675 
676         if (selectionArgs == null) {
677             selectionArgs = new String[0];
678         }
679         final ArrayMap<String, Object> rawValues = com.android.providers.media.util.DatabaseUtils
680                 .getValues(values);
681         final int valuesLength = rawValues.size();
682         final Object[] sqlArgs = new Object[valuesLength + selectionArgs.length];
683         for (int i = 0; i < sqlArgs.length; i++) {
684             if (i < valuesLength) {
685                 sqlArgs[i] = rawValues.valueAt(i);
686             } else {
687                 sqlArgs[i] = selectionArgs[i - valuesLength];
688             }
689         }
690         if (Log.isLoggable(TAG, Log.DEBUG)) {
691             if (Logging.IS_DEBUGGABLE) {
692                 Log.d(TAG, sql + " with args " + Arrays.toString(sqlArgs));
693             } else {
694                 Log.d(TAG, sql);
695             }
696         }
697         return com.android.providers.media.util.DatabaseUtils.executeUpdateDelete(db, sql, sqlArgs);
698     }
699 
700     public int delete(@NonNull DatabaseHelper helper, @Nullable String selection,
701             @Nullable String[] selectionArgs) {
702         // We force wrap in a transaction to ensure that all mutations increment
703         // the generation counter
704         return helper.runWithTransaction((db) -> {
705             return delete(db, selection, selectionArgs);
706         });
707     }
708 
709     /**
710      * Perform a delete by combining all current settings and the
711      * information passed into this method.
712      *
713      * @param db the database to delete on
714      * @param selection A filter declaring which rows to return,
715      *   formatted as an SQL {@code WHERE} clause (excluding the {@code WHERE}
716      *   itself). Passing null will return all rows for the given URL.
717      * @param selectionArgs You may include ?s in selection, which
718      *   will be replaced by the values from selectionArgs, in order
719      *   that they appear in the selection. The values will be bound
720      *   as Strings.
721      * @return the number of rows deleted
722      */
723     public int delete(@NonNull SQLiteDatabase db, @Nullable String selection,
724             @Nullable String[] selectionArgs) {
725         Objects.requireNonNull(mTables, "No tables defined");
726         Objects.requireNonNull(db, "No database defined");
727 
728         final String sql;
729         final String unwrappedSql = buildDelete(selection);
730 
731         if (isStrictGrammar()) {
732             enforceStrictGrammar(selection, null, null, null, null);
733         }
734         if (isStrict() && hasUserWhere(selection)) {
735             // Validate the user-supplied selection to detect syntactic anomalies
736             // in the selection string that could indicate a SQL injection attempt.
737             // The idea is to ensure that the selection clause is a valid SQL expression
738             // by compiling it twice: once wrapped in parentheses and once as
739             // originally specified. An attacker cannot create an expression that
740             // would escape the SQL expression while maintaining balanced parentheses
741             // in both the wrapped and original forms.
742 
743             // NOTE: The ordering of the below operations is important; we must
744             // execute the wrapped query to ensure the untrusted clause has been
745             // fully isolated.
746 
747             // Validate the unwrapped query
748             db.validateSql(unwrappedSql, null); // will throw if query is invalid
749 
750             // Execute wrapped query for extra protection
751             final String wrappedSql = buildDelete(wrap(selection));
752             sql = wrappedSql;
753         } else {
754             // Execute unwrapped query
755             sql = unwrappedSql;
756         }
757 
758         final String[] sqlArgs = selectionArgs;
759         if (Log.isLoggable(TAG, Log.DEBUG)) {
760             if (Logging.IS_DEBUGGABLE) {
761                 Log.d(TAG, sql + " with args " + Arrays.toString(sqlArgs));
762             } else {
763                 Log.d(TAG, sql);
764             }
765         }
766         return com.android.providers.media.util.DatabaseUtils.executeUpdateDelete(db, sql, sqlArgs);
767     }
768 
769     private static boolean hasUserWhere(@Nullable String selection) {
770         return !Strings.isNullOrEmpty(selection);
771     }
772 
773     private void enforceStrictColumns(@Nullable String[] projection) {
774         Objects.requireNonNull(mProjectionMap, "No projection map defined");
775         if (!isStrictColumns()) {
776             return;
777         }
778 
779         computeProjection(projection);
780     }
781 
782     private void enforceStrictColumns(@NonNull ContentValues values) {
783         Objects.requireNonNull(mProjectionMap, "No projection map defined");
784         if (!isStrictColumns()) {
785             return;
786         }
787 
788         final ArrayMap<String, Object> rawValues = com.android.providers.media.util.DatabaseUtils
789                 .getValues(values);
790         for (int i = 0; i < rawValues.size(); i++) {
791             final String column = rawValues.keyAt(i);
792             if (!mProjectionMap.containsKey(column)) {
793                 throw new IllegalArgumentException("Invalid column " + column);
794             }
795         }
796     }
797 
798     @VisibleForTesting
799     void enforceStrictGrammar(@Nullable String selection, @Nullable String groupBy,
800             @Nullable String having, @Nullable String sortOrder, @Nullable String limit) {
801         SQLiteTokenizer.tokenize(selection, SQLiteTokenizer.OPTION_NONE,
802                 this::enforceStrictToken);
803         SQLiteTokenizer.tokenize(groupBy, SQLiteTokenizer.OPTION_NONE,
804                 this::enforceStrictToken);
805         SQLiteTokenizer.tokenize(having, SQLiteTokenizer.OPTION_NONE,
806                 this::enforceStrictToken);
807         SQLiteTokenizer.tokenize(sortOrder, SQLiteTokenizer.OPTION_NONE,
808                 this::enforceStrictToken);
809         SQLiteTokenizer.tokenize(limit, SQLiteTokenizer.OPTION_NONE,
810                 this::enforceStrictToken);
811     }
812 
813     private void enforceStrictToken(@NonNull String token) {
814         if (TextUtils.isEmpty(token)) return;
815         if (isTableOrColumn(token)) return;
816         if (isCustomCollator(token)) return;
817         if (SQLiteTokenizer.isFunction(token)) return;
818         if (SQLiteTokenizer.isType(token)) return;
819 
820         // Carefully block any tokens that are attempting to jump across query
821         // clauses or create subqueries, since they could leak data that should
822         // have been filtered by the trusted where clause
823         boolean isAllowedKeyword = SQLiteTokenizer.isKeyword(token);
824         switch (token.toUpperCase(Locale.ROOT)) {
825             case "SELECT":
826             case "FROM":
827             case "WHERE":
828             case "GROUP":
829             case "HAVING":
830             case "WINDOW":
831             case "VALUES":
832             case "ORDER":
833             case "LIMIT":
834                 isAllowedKeyword = false;
835                 break;
836         }
837         if (isAllowedKeyword) return;
838 
839         if (mTargetSdkVersion < Build.VERSION_CODES.R) {
840             // Narrow concessions to support legacy apps that aren't using
841             // proper SQL string substitution
842             if (sPattern154193772.matcher(token).matches()) return;
843             if (sPattern156832140.matcher(token).matches()) return;
844             if (sPattern158537159.matcher(token).matches()) return;
845         }
846 
847         throw new IllegalArgumentException("Invalid token " + token);
848     }
849 
850     /**
851      * Construct a {@code SELECT} statement suitable for use in a group of
852      * {@code SELECT} statements that will be joined through {@code UNION} operators
853      * in buildUnionQuery.
854      *
855      * @param projectionIn A list of which columns to return. Passing
856      *    null will return all columns, which is discouraged to
857      *    prevent reading data from storage that isn't going to be
858      *    used.
859      * @param selection A filter declaring which rows to return,
860      *   formatted as an SQL {@code WHERE} clause (excluding the {@code WHERE}
861      *   itself).  Passing null will return all rows for the given
862      *   URL.
863      * @param groupBy A filter declaring how to group rows, formatted
864      *   as an SQL {@code GROUP BY} clause (excluding the {@code GROUP BY} itself).
865      *   Passing null will cause the rows to not be grouped.
866      * @param having A filter declare which row groups to include in
867      *   the cursor, if row grouping is being used, formatted as an
868      *   SQL {@code HAVING} clause (excluding the {@code HAVING} itself).  Passing
869      *   null will cause all row groups to be included, and is
870      *   required when row grouping is not being used.
871      * @param sortOrder How to order the rows, formatted as an SQL
872      *   {@code ORDER BY} clause (excluding the {@code ORDER BY} itself). Passing null
873      *   will use the default sort order, which may be unordered.
874      * @param limit Limits the number of rows returned by the query,
875      *   formatted as {@code LIMIT} clause. Passing null denotes no {@code LIMIT} clause.
876      * @return the resulting SQL {@code SELECT} statement
877      */
878     public String buildQuery(
879             String[] projectionIn, String selection, String groupBy,
880             String having, String sortOrder, String limit) {
881         String[] projection = computeProjection(projectionIn);
882         String where = computeWhere(selection);
883 
884         return buildQueryString(
885                 mDistinct, mTables, projection, where,
886                 groupBy, having, sortOrder, limit);
887     }
888 
889     /** {@hide} */
890     public String buildInsert(ContentValues values) {
891         if (values == null || values.isEmpty()) {
892             throw new IllegalArgumentException("Empty values");
893         }
894 
895         StringBuilder sql = new StringBuilder(120);
896         sql.append("INSERT INTO ");
897         sql.append(SQLiteDatabase.findEditTable(mTables));
898         sql.append(" (");
899 
900         final boolean hasGeneration = Objects.equals(mTables, "files");
901         if (hasGeneration) {
902             values.remove(MediaColumns.GENERATION_ADDED);
903             values.remove(MediaColumns.GENERATION_MODIFIED);
904         }
905 
906         final ArrayMap<String, Object> rawValues = com.android.providers.media.util.DatabaseUtils
907                 .getValues(values);
908         for (int i = 0; i < rawValues.size(); i++) {
909             if (i > 0) {
910                 sql.append(',');
911             }
912             sql.append(rawValues.keyAt(i));
913         }
914         if (hasGeneration) {
915             sql.append(',');
916             sql.append(MediaColumns.GENERATION_ADDED);
917             sql.append(',');
918             sql.append(MediaColumns.GENERATION_MODIFIED);
919         }
920         if (shouldAppendRowId(values)) {
921             sql.append(',');
922             sql.append(MediaColumns._ID);
923         }
924 
925         sql.append(") VALUES (");
926         for (int i = 0; i < rawValues.size(); i++) {
927             if (i > 0) {
928                 sql.append(',');
929             }
930             sql.append('?');
931         }
932         if (hasGeneration) {
933             sql.append(',');
934             sql.append('(');
935             sql.append(DatabaseHelper.CURRENT_GENERATION_CLAUSE);
936             sql.append(')');
937             sql.append(',');
938             sql.append('(');
939             sql.append(DatabaseHelper.CURRENT_GENERATION_CLAUSE);
940             sql.append(')');
941         }
942         if (shouldAppendRowId(values)) {
943             sql.append(',');
944             sql.append(bindSelection(GET_ID_FOR_INSERT_CLAUSE,
945                     values.getAsString(MediaColumns.DATA)));
946         }
947         sql.append(")");
948         return sql.toString();
949     }
950 
951     /** {@hide} */
952     public String buildUpdate(ContentValues values, String selection) {
953         if (values == null || values.isEmpty()) {
954             throw new IllegalArgumentException("Empty values");
955         }
956 
957         StringBuilder sql = new StringBuilder(120);
958         sql.append("UPDATE ");
959         sql.append(SQLiteDatabase.findEditTable(mTables));
960         sql.append(" SET ");
961 
962         final boolean hasGeneration = Objects.equals(mTables, "files");
963         if (hasGeneration) {
964             values.remove(MediaColumns.GENERATION_ADDED);
965             values.remove(MediaColumns.GENERATION_MODIFIED);
966         }
967 
968         final ArrayMap<String, Object> rawValues = com.android.providers.media.util.DatabaseUtils
969                 .getValues(values);
970         for (int i = 0; i < rawValues.size(); i++) {
971             if (i > 0) {
972                 sql.append(',');
973             }
974             sql.append(rawValues.keyAt(i));
975             sql.append("=?");
976         }
977         if (hasGeneration) {
978             sql.append(',');
979             sql.append(MediaColumns.GENERATION_MODIFIED);
980             sql.append('=');
981             sql.append('(');
982             sql.append(DatabaseHelper.CURRENT_GENERATION_CLAUSE);
983             sql.append(')');
984         }
985         if (shouldAppendRowId(values)) {
986             sql.append(',');
987             sql.append(MediaColumns._ID);
988             sql.append('=');
989             sql.append(bindSelection(GET_ID_FOR_UPDATE_CLAUSE,
990                     values.getAsString(MediaColumns.DATA)));
991         }
992 
993         final String where = computeWhere(selection);
994         appendClause(sql, " WHERE ", where);
995         return sql.toString();
996     }
997 
998     /** {@hide} */
999     public String buildDelete(String selection) {
1000         StringBuilder sql = new StringBuilder(120);
1001         sql.append("DELETE FROM ");
1002         sql.append(SQLiteDatabase.findEditTable(mTables));
1003 
1004         final String where = computeWhere(selection);
1005         appendClause(sql, " WHERE ", where);
1006         return sql.toString();
1007     }
1008 
1009     private static @NonNull String maybeWithOperator(@Nullable String operator,
1010             @NonNull String column) {
1011         if (operator != null) {
1012             return operator + "(" + column + ")";
1013         } else {
1014             return column;
1015         }
1016     }
1017 
1018     /** {@hide} */
1019     public @Nullable String[] computeProjection(@Nullable String[] projectionIn) {
1020         if (projectionIn != null && projectionIn.length > 0) {
1021             String[] projectionOut = new String[projectionIn.length];
1022             for (int i = 0; i < projectionIn.length; i++) {
1023                 projectionOut[i] = computeSingleProjectionOrThrow(projectionIn[i]);
1024             }
1025             return projectionOut;
1026         } else if (mProjectionMap != null) {
1027             // Return all columns in projection map.
1028             Set<Entry<String, String>> entrySet = mProjectionMap.entrySet();
1029             String[] projection = new String[entrySet.size()];
1030             Iterator<Entry<String, String>> entryIter = entrySet.iterator();
1031             int i = 0;
1032 
1033             while (entryIter.hasNext()) {
1034                 Entry<String, String> entry = entryIter.next();
1035 
1036                 // Don't include the _count column when people ask for no projection.
1037                 if (entry.getKey().equals(BaseColumns._COUNT)) {
1038                     continue;
1039                 }
1040                 projection[i++] = entry.getValue();
1041             }
1042             return projection;
1043         }
1044         return null;
1045     }
1046 
1047     private @NonNull String computeSingleProjectionOrThrow(@NonNull String userColumn) {
1048         final String column = computeSingleProjection(userColumn);
1049         if (column != null) {
1050             return column;
1051         } else {
1052             if (isStrictColumns()) {
1053                 throw new IllegalArgumentException("Invalid column " + userColumn);
1054             }
1055             return userColumn;
1056         }
1057     }
1058 
1059     private @Nullable String computeSingleProjection(@NonNull String userColumn) {
1060         // When no mapping provided, anything goes
1061         if (mProjectionMap == null) {
1062             return userColumn;
1063         }
1064 
1065         String operator = null;
1066         String column = mProjectionMap.get(userColumn.toLowerCase(Locale.ROOT));
1067 
1068         // When no direct match found, look for aggregation
1069         if (column == null) {
1070             final Matcher matcher = sAggregationPattern.matcher(userColumn);
1071             if (matcher.matches()) {
1072                 operator = matcher.group(1);
1073                 userColumn = matcher.group(2);
1074                 column = mProjectionMap.get(userColumn.toLowerCase(Locale.ROOT));
1075             }
1076         }
1077 
1078         if (column != null) {
1079             return maybeWithOperator(operator, column);
1080         }
1081 
1082         if (mStrictFlags == 0 &&
1083                 (userColumn.contains(" AS ") || userColumn.contains(" as "))) {
1084             /* A column alias already exist */
1085             return maybeWithOperator(operator, userColumn);
1086         }
1087 
1088         // If greylist is configured, we might be willing to let
1089         // this custom column bypass our strict checks.
1090         if (mProjectionGreylist != null) {
1091             boolean match = false;
1092             for (Pattern p : mProjectionGreylist) {
1093                 if (p.matcher(userColumn).matches()) {
1094                     match = true;
1095                     break;
1096                 }
1097             }
1098 
1099             if (match) {
1100                 Log.w(TAG, "Allowing abusive custom column: " + userColumn);
1101                 return maybeWithOperator(operator, userColumn);
1102             }
1103         }
1104 
1105         return null;
1106     }
1107 
1108     private boolean isTableOrColumn(String token) {
1109         if (mTables.equals(token)) return true;
1110         return computeSingleProjection(token) != null;
1111     }
1112 
1113     private boolean isCustomCollator(String token) {
1114         return sCustomCollatorPattern.matcher(token).matches();
1115     }
1116 
1117     /** {@hide} */
1118     public @Nullable String computeWhere(@Nullable String selection) {
1119         final boolean hasInternal = !TextUtils.isEmpty(mWhereClause);
1120         final boolean hasExternal = !TextUtils.isEmpty(selection);
1121 
1122         if (hasInternal || hasExternal) {
1123             final StringBuilder where = new StringBuilder();
1124             if (hasInternal) {
1125                 where.append('(').append(mWhereClause).append(')');
1126             }
1127             if (hasInternal && hasExternal) {
1128                 where.append(" AND ");
1129             }
1130             if (hasExternal) {
1131                 where.append('(').append(selection).append(')');
1132             }
1133             return where.toString();
1134         } else {
1135             return null;
1136         }
1137     }
1138 
1139     /**
1140      * Wrap given argument in parenthesis, unless it's {@code null} or
1141      * {@code ()}, in which case return it verbatim.
1142      */
1143     private @Nullable String wrap(@Nullable String arg) {
1144         if (TextUtils.isEmpty(arg)) {
1145             return arg;
1146         } else {
1147             return "(" + arg + ")";
1148         }
1149     }
1150 
1151     @VisibleForTesting
1152     boolean shouldAppendRowId(ContentValues values) {
1153         // When no projectionMap provided, don't add the row
1154         final boolean hasIdInProjectionMap = mProjectionMap != null && mProjectionMap.containsKey(
1155                 MediaColumns._ID) && TextUtils.equals(mProjectionMap.get(MediaColumns._ID),
1156                 MediaColumns._ID);
1157         return !values.containsKey(MediaColumns._ID) && values.containsKey(MediaColumns.DATA)
1158                 && hasIdInProjectionMap;
1159     }
1160 }
1161