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