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 (<append chunk 1><append chunk2>) AND (<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 (<append chunk 1><append chunk2>) AND (<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