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