1 /* 2 * Copyright (C) 2019 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_GROUP_COLUMNS; 20 import static android.content.ContentResolver.QUERY_ARG_LIMIT; 21 import static android.content.ContentResolver.QUERY_ARG_OFFSET; 22 import static android.content.ContentResolver.QUERY_ARG_SORT_COLLATION; 23 import static android.content.ContentResolver.QUERY_ARG_SORT_COLUMNS; 24 import static android.content.ContentResolver.QUERY_ARG_SORT_DIRECTION; 25 import static android.content.ContentResolver.QUERY_ARG_SORT_LOCALE; 26 import static android.content.ContentResolver.QUERY_ARG_SQL_GROUP_BY; 27 import static android.content.ContentResolver.QUERY_ARG_SQL_LIMIT; 28 import static android.content.ContentResolver.QUERY_ARG_SQL_SELECTION; 29 import static android.content.ContentResolver.QUERY_ARG_SQL_SELECTION_ARGS; 30 import static android.content.ContentResolver.QUERY_ARG_SQL_SORT_ORDER; 31 import static android.content.ContentResolver.QUERY_SORT_DIRECTION_ASCENDING; 32 import static android.content.ContentResolver.QUERY_SORT_DIRECTION_DESCENDING; 33 34 import static com.android.providers.media.util.Logging.TAG; 35 36 import android.content.ContentResolver; 37 import android.content.ContentValues; 38 import android.database.Cursor; 39 import android.database.SQLException; 40 import android.database.sqlite.SQLiteDatabase; 41 import android.database.sqlite.SQLiteStatement; 42 import android.net.Uri; 43 import android.os.Bundle; 44 import android.os.Trace; 45 import android.text.TextUtils; 46 import android.util.ArrayMap; 47 import android.util.Log; 48 49 import androidx.annotation.NonNull; 50 import androidx.annotation.Nullable; 51 import androidx.annotation.VisibleForTesting; 52 53 import java.util.Locale; 54 import java.util.function.Consumer; 55 import java.util.function.Function; 56 57 public class DatabaseUtils { 58 /** 59 * Bind the given selection with the given selection arguments. 60 * <p> 61 * Internally assumes that '?' is only ever used for arguments, and doesn't 62 * appear as a literal or escaped value. 63 * <p> 64 * This method is typically useful for trusted code that needs to cook up a 65 * fully-bound selection. 66 * 67 * @hide 68 */ bindSelection(@ullable String selection, @Nullable Object... selectionArgs)69 public static @Nullable String bindSelection(@Nullable String selection, 70 @Nullable Object... selectionArgs) { 71 if (selection == null) return null; 72 // If no arguments provided, so we can't bind anything 73 if ((selectionArgs == null) || (selectionArgs.length == 0)) return selection; 74 // If no bindings requested, so we can shortcut 75 if (selection.indexOf('?') == -1) return selection; 76 77 // Track the chars immediately before and after each bind request, to 78 // decide if it needs additional whitespace added 79 char before = ' '; 80 char after = ' '; 81 82 int argIndex = 0; 83 final int len = selection.length(); 84 final StringBuilder res = new StringBuilder(len); 85 for (int i = 0; i < len; ) { 86 char c = selection.charAt(i++); 87 if (c == '?') { 88 // Assume this bind request is guarded until we find a specific 89 // trailing character below 90 after = ' '; 91 92 // Sniff forward to see if the selection is requesting a 93 // specific argument index 94 int start = i; 95 for (; i < len; i++) { 96 c = selection.charAt(i); 97 if (c < '0' || c > '9') { 98 after = c; 99 break; 100 } 101 } 102 if (start != i) { 103 argIndex = Integer.parseInt(selection.substring(start, i)) - 1; 104 } 105 106 // Manually bind the argument into the selection, adding 107 // whitespace when needed for clarity 108 final Object arg = selectionArgs[argIndex++]; 109 if (before != ' ' && before != '=') res.append(' '); 110 switch (DatabaseUtils.getTypeOfObject(arg)) { 111 case Cursor.FIELD_TYPE_NULL: 112 res.append("NULL"); 113 break; 114 case Cursor.FIELD_TYPE_INTEGER: 115 res.append(((Number) arg).longValue()); 116 break; 117 case Cursor.FIELD_TYPE_FLOAT: 118 res.append(((Number) arg).doubleValue()); 119 break; 120 case Cursor.FIELD_TYPE_BLOB: 121 throw new IllegalArgumentException("Blobs not supported"); 122 case Cursor.FIELD_TYPE_STRING: 123 default: 124 if (arg instanceof Boolean) { 125 // Provide compatibility with legacy applications which may pass 126 // Boolean values in bind args. 127 res.append(((Boolean) arg).booleanValue() ? 1 : 0); 128 } else { 129 res.append('\''); 130 // Escape single quote character while appending the string. 131 res.append(arg.toString().replace("'", "''")); 132 res.append('\''); 133 } 134 break; 135 } 136 if (after != ' ') res.append(' '); 137 } else { 138 res.append(c); 139 before = c; 140 } 141 } 142 return res.toString(); 143 } 144 145 /** 146 * Returns data type of the given object's value. 147 *<p> 148 * Returned values are 149 * <ul> 150 * <li>{@link Cursor#FIELD_TYPE_NULL}</li> 151 * <li>{@link Cursor#FIELD_TYPE_INTEGER}</li> 152 * <li>{@link Cursor#FIELD_TYPE_FLOAT}</li> 153 * <li>{@link Cursor#FIELD_TYPE_STRING}</li> 154 * <li>{@link Cursor#FIELD_TYPE_BLOB}</li> 155 *</ul> 156 *</p> 157 * 158 * @param obj the object whose value type is to be returned 159 * @return object value type 160 * @hide 161 */ getTypeOfObject(Object obj)162 public static int getTypeOfObject(Object obj) { 163 if (obj == null) { 164 return Cursor.FIELD_TYPE_NULL; 165 } else if (obj instanceof byte[]) { 166 return Cursor.FIELD_TYPE_BLOB; 167 } else if (obj instanceof Float || obj instanceof Double) { 168 return Cursor.FIELD_TYPE_FLOAT; 169 } else if (obj instanceof Long || obj instanceof Integer 170 || obj instanceof Short || obj instanceof Byte) { 171 return Cursor.FIELD_TYPE_INTEGER; 172 } else { 173 return Cursor.FIELD_TYPE_STRING; 174 } 175 } 176 copyFromCursorToContentValues(@onNull String column, @NonNull Cursor cursor, @NonNull ContentValues values)177 public static void copyFromCursorToContentValues(@NonNull String column, @NonNull Cursor cursor, 178 @NonNull ContentValues values) { 179 final int index = cursor.getColumnIndex(column); 180 if (index != -1) { 181 if (cursor.isNull(index)) { 182 values.putNull(column); 183 } else { 184 values.put(column, cursor.getString(index)); 185 } 186 } 187 } 188 189 /** 190 * Simple attempt to balance the given SQL expression by adding parenthesis 191 * when needed. 192 * <p> 193 * Since this is only used for recovering from abusive apps, we're not 194 * interested in trying to build a fully valid SQL parser up in Java. It'll 195 * give up when it encounters complex SQL, such as string literals. 196 */ maybeBalance(@ullable String sql)197 public static @Nullable String maybeBalance(@Nullable String sql) { 198 if (sql == null) return null; 199 200 int count = 0; 201 char literal = '\0'; 202 for (int i = 0; i < sql.length(); i++) { 203 final char c = sql.charAt(i); 204 205 if (c == '\'' || c == '"') { 206 if (literal == '\0') { 207 // Start literal 208 literal = c; 209 } else if (literal == c) { 210 // End literal 211 literal = '\0'; 212 } 213 } 214 215 if (literal == '\0') { 216 if (c == '(') { 217 count++; 218 } else if (c == ')') { 219 count--; 220 } 221 } 222 } 223 while (count > 0) { 224 sql = sql + ")"; 225 count--; 226 } 227 while (count < 0) { 228 sql = "(" + sql; 229 count++; 230 } 231 return sql; 232 } 233 234 /** 235 * {@link ContentResolver} offers several query arguments, ranging from 236 * helpful higher-level concepts like 237 * {@link ContentResolver#QUERY_ARG_GROUP_COLUMNS} to raw SQL like 238 * {@link ContentResolver#QUERY_ARG_SQL_GROUP_BY}. We prefer the 239 * higher-level concepts when defined by the caller, but we'll fall back to 240 * the raw SQL if that's all the caller provided. 241 * <p> 242 * This method will "resolve" all higher-level query arguments into the raw 243 * SQL arguments, giving us easy values to carry over into 244 * {@link SQLiteQueryBuilder}. 245 */ resolveQueryArgs(@onNull Bundle queryArgs, @NonNull Consumer<String> honored, @NonNull Function<String, String> collatorFactory)246 public static void resolveQueryArgs(@NonNull Bundle queryArgs, 247 @NonNull Consumer<String> honored, 248 @NonNull Function<String, String> collatorFactory) { 249 // We're always going to handle selections 250 honored.accept(QUERY_ARG_SQL_SELECTION); 251 honored.accept(QUERY_ARG_SQL_SELECTION_ARGS); 252 253 resolveGroupBy(queryArgs, honored); 254 resolveSortOrder(queryArgs, honored, collatorFactory); 255 resolveLimit(queryArgs, honored); 256 } 257 resolveGroupBy(@onNull Bundle queryArgs, @NonNull Consumer<String> honored)258 private static void resolveGroupBy(@NonNull Bundle queryArgs, 259 @NonNull Consumer<String> honored) { 260 final String[] columns = queryArgs.getStringArray(QUERY_ARG_GROUP_COLUMNS); 261 if (columns != null && columns.length != 0) { 262 String groupBy = TextUtils.join(", ", columns); 263 honored.accept(QUERY_ARG_GROUP_COLUMNS); 264 265 queryArgs.putString(QUERY_ARG_SQL_GROUP_BY, groupBy); 266 } else { 267 honored.accept(QUERY_ARG_SQL_GROUP_BY); 268 } 269 } 270 resolveSortOrder(@onNull Bundle queryArgs, @NonNull Consumer<String> honored, @NonNull Function<String, String> collatorFactory)271 private static void resolveSortOrder(@NonNull Bundle queryArgs, 272 @NonNull Consumer<String> honored, 273 @NonNull Function<String, String> collatorFactory) { 274 final String[] columns = queryArgs.getStringArray(QUERY_ARG_SORT_COLUMNS); 275 if (columns != null && columns.length != 0) { 276 String sortOrder = TextUtils.join(", ", columns); 277 honored.accept(QUERY_ARG_SORT_COLUMNS); 278 279 if (queryArgs.containsKey(QUERY_ARG_SORT_LOCALE)) { 280 final String collatorName = collatorFactory.apply( 281 queryArgs.getString(QUERY_ARG_SORT_LOCALE)); 282 sortOrder += " COLLATE " + collatorName; 283 honored.accept(QUERY_ARG_SORT_LOCALE); 284 } else { 285 // Interpret PRIMARY and SECONDARY collation strength as no-case collation based 286 // on their javadoc descriptions. 287 final int collation = queryArgs.getInt( 288 QUERY_ARG_SORT_COLLATION, java.text.Collator.IDENTICAL); 289 switch (collation) { 290 case java.text.Collator.IDENTICAL: 291 honored.accept(QUERY_ARG_SORT_COLLATION); 292 break; 293 case java.text.Collator.PRIMARY: 294 case java.text.Collator.SECONDARY: 295 sortOrder += " COLLATE NOCASE"; 296 honored.accept(QUERY_ARG_SORT_COLLATION); 297 break; 298 } 299 } 300 301 final int sortDir = queryArgs.getInt(QUERY_ARG_SORT_DIRECTION, Integer.MIN_VALUE); 302 switch (sortDir) { 303 case QUERY_SORT_DIRECTION_ASCENDING: 304 sortOrder += " ASC"; 305 honored.accept(QUERY_ARG_SORT_DIRECTION); 306 break; 307 case QUERY_SORT_DIRECTION_DESCENDING: 308 sortOrder += " DESC"; 309 honored.accept(QUERY_ARG_SORT_DIRECTION); 310 break; 311 } 312 313 queryArgs.putString(QUERY_ARG_SQL_SORT_ORDER, sortOrder); 314 } else { 315 honored.accept(QUERY_ARG_SQL_SORT_ORDER); 316 } 317 } 318 resolveLimit(@onNull Bundle queryArgs, @NonNull Consumer<String> honored)319 private static void resolveLimit(@NonNull Bundle queryArgs, 320 @NonNull Consumer<String> honored) { 321 final int limit = queryArgs.getInt(QUERY_ARG_LIMIT, Integer.MIN_VALUE); 322 if (limit != Integer.MIN_VALUE) { 323 String limitString = Integer.toString(limit); 324 honored.accept(QUERY_ARG_LIMIT); 325 326 final int offset = queryArgs.getInt(QUERY_ARG_OFFSET, Integer.MIN_VALUE); 327 if (offset != Integer.MIN_VALUE) { 328 limitString += " OFFSET " + offset; 329 honored.accept(QUERY_ARG_OFFSET); 330 } 331 332 queryArgs.putString(QUERY_ARG_SQL_LIMIT, limitString); 333 } else { 334 honored.accept(QUERY_ARG_SQL_LIMIT); 335 } 336 } 337 338 /** 339 * Gracefully recover from abusive callers that are smashing limits into 340 * {@link Uri}. 341 */ recoverAbusiveLimit(@onNull Uri uri, @NonNull Bundle queryArgs)342 public static void recoverAbusiveLimit(@NonNull Uri uri, @NonNull Bundle queryArgs) { 343 final String origLimit = queryArgs.getString(QUERY_ARG_SQL_LIMIT); 344 final String uriLimit = uri.getQueryParameter("limit"); 345 346 if (!TextUtils.isEmpty(uriLimit)) { 347 // Yell if we already had a group by requested 348 if (!TextUtils.isEmpty(origLimit)) { 349 throw new IllegalArgumentException( 350 "Abusive '" + uriLimit + "' conflicts with requested '" + origLimit + "'"); 351 } 352 353 Log.w(TAG, "Recovered abusive '" + uriLimit + "' from '" + uri + "'"); 354 355 queryArgs.putString(QUERY_ARG_SQL_LIMIT, uriLimit); 356 } 357 } 358 359 /** 360 * Gracefully recover from abusive callers that are smashing invalid 361 * {@code GROUP BY} clauses into {@code WHERE} clauses. 362 */ recoverAbusiveSelection(@onNull Bundle queryArgs)363 public static void recoverAbusiveSelection(@NonNull Bundle queryArgs) { 364 final String origSelection = queryArgs.getString(QUERY_ARG_SQL_SELECTION); 365 final String origGroupBy = queryArgs.getString(QUERY_ARG_SQL_GROUP_BY); 366 367 final int index = (origSelection != null) 368 ? origSelection.toUpperCase(Locale.ROOT).indexOf(" GROUP BY ") : -1; 369 if (index != -1) { 370 String selection = origSelection.substring(0, index); 371 String groupBy = origSelection.substring(index + " GROUP BY ".length()); 372 373 // Try balancing things out 374 selection = maybeBalance(selection); 375 groupBy = maybeBalance(groupBy); 376 377 // Yell if we already had a group by requested 378 if (!TextUtils.isEmpty(origGroupBy)) { 379 throw new IllegalArgumentException( 380 "Abusive '" + groupBy + "' conflicts with requested '" + origGroupBy + "'"); 381 } 382 383 Log.w(TAG, "Recovered abusive '" + selection + "' and '" + groupBy + "' from '" 384 + origSelection + "'"); 385 386 queryArgs.putString(QUERY_ARG_SQL_SELECTION, selection); 387 queryArgs.putString(QUERY_ARG_SQL_GROUP_BY, groupBy); 388 } 389 } 390 391 /** 392 * Gracefully recover from abusive callers that are smashing limits into 393 * {@code ORDER BY} clauses. 394 */ recoverAbusiveSortOrder(@onNull Bundle queryArgs)395 public static void recoverAbusiveSortOrder(@NonNull Bundle queryArgs) { 396 final String origSortOrder = queryArgs.getString(QUERY_ARG_SQL_SORT_ORDER); 397 final String origLimit = queryArgs.getString(QUERY_ARG_SQL_LIMIT); 398 399 final int index = (origSortOrder != null) 400 ? origSortOrder.toUpperCase(Locale.ROOT).indexOf(" LIMIT ") : -1; 401 if (index != -1) { 402 String sortOrder = origSortOrder.substring(0, index); 403 String limit = origSortOrder.substring(index + " LIMIT ".length()); 404 405 // Yell if we already had a limit requested 406 if (!TextUtils.isEmpty(origLimit)) { 407 throw new IllegalArgumentException( 408 "Abusive '" + limit + "' conflicts with requested '" + origLimit + "'"); 409 } 410 411 Log.w(TAG, "Recovered abusive '" + sortOrder + "' and '" + limit + "' from '" 412 + origSortOrder + "'"); 413 414 queryArgs.putString(QUERY_ARG_SQL_SORT_ORDER, sortOrder); 415 queryArgs.putString(QUERY_ARG_SQL_LIMIT, limit); 416 } 417 } 418 419 /** 420 * Shamelessly borrowed from {@link ContentResolver}. 421 */ createSqlQueryBundle( @ullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder)422 public static @Nullable Bundle createSqlQueryBundle( 423 @Nullable String selection, 424 @Nullable String[] selectionArgs, 425 @Nullable String sortOrder) { 426 427 if (selection == null && selectionArgs == null && sortOrder == null) { 428 return null; 429 } 430 431 Bundle queryArgs = new Bundle(); 432 if (selection != null) { 433 queryArgs.putString(QUERY_ARG_SQL_SELECTION, selection); 434 } 435 if (selectionArgs != null) { 436 queryArgs.putStringArray(QUERY_ARG_SQL_SELECTION_ARGS, selectionArgs); 437 } 438 if (sortOrder != null) { 439 queryArgs.putString(QUERY_ARG_SQL_SORT_ORDER, sortOrder); 440 } 441 return queryArgs; 442 } 443 getValues(@onNull ContentValues values)444 public static @NonNull ArrayMap<String, Object> getValues(@NonNull ContentValues values) { 445 final ArrayMap<String, Object> res = new ArrayMap<>(); 446 for (String key : values.keySet()) { 447 res.put(key, values.get(key)); 448 } 449 return res; 450 } 451 executeInsert(@onNull SQLiteDatabase db, @NonNull String sql, @Nullable Object[] bindArgs)452 public static long executeInsert(@NonNull SQLiteDatabase db, @NonNull String sql, 453 @Nullable Object[] bindArgs) throws SQLException { 454 Trace.beginSection("executeInsert"); 455 try (SQLiteStatement st = db.compileStatement(sql)) { 456 bindArgs(st, bindArgs); 457 return st.executeInsert(); 458 } finally { 459 Trace.endSection(); 460 } 461 } 462 executeUpdateDelete(@onNull SQLiteDatabase db, @NonNull String sql, @Nullable Object[] bindArgs)463 public static int executeUpdateDelete(@NonNull SQLiteDatabase db, @NonNull String sql, 464 @Nullable Object[] bindArgs) throws SQLException { 465 Trace.beginSection("executeUpdateDelete"); 466 try (SQLiteStatement st = db.compileStatement(sql)) { 467 bindArgs(st, bindArgs); 468 return st.executeUpdateDelete(); 469 } finally { 470 Trace.endSection(); 471 } 472 } 473 bindArgs(@onNull SQLiteStatement st, @Nullable Object[] bindArgs)474 private static void bindArgs(@NonNull SQLiteStatement st, @Nullable Object[] bindArgs) { 475 if (bindArgs == null) return; 476 477 for (int i = 0; i < bindArgs.length; i++) { 478 final Object bindArg = bindArgs[i]; 479 switch (getTypeOfObject(bindArg)) { 480 case Cursor.FIELD_TYPE_NULL: 481 st.bindNull(i + 1); 482 break; 483 case Cursor.FIELD_TYPE_INTEGER: 484 st.bindLong(i + 1, ((Number) bindArg).longValue()); 485 break; 486 case Cursor.FIELD_TYPE_FLOAT: 487 st.bindDouble(i + 1, ((Number) bindArg).doubleValue()); 488 break; 489 case Cursor.FIELD_TYPE_BLOB: 490 st.bindBlob(i + 1, (byte[]) bindArg); 491 break; 492 case Cursor.FIELD_TYPE_STRING: 493 default: 494 if (bindArg instanceof Boolean) { 495 // Provide compatibility with legacy 496 // applications which may pass Boolean values in 497 // bind args. 498 st.bindLong(i + 1, ((Boolean) bindArg).booleanValue() ? 1 : 0); 499 } else { 500 st.bindString(i + 1, bindArg.toString()); 501 } 502 break; 503 } 504 } 505 } 506 bindList(@onNull Object... args)507 public static @NonNull String bindList(@NonNull Object... args) { 508 final StringBuilder sb = new StringBuilder(); 509 sb.append('('); 510 for (int i = 0; i < args.length; i++) { 511 sb.append('?'); 512 if (i < args.length - 1) { 513 sb.append(','); 514 } 515 } 516 sb.append(')'); 517 return DatabaseUtils.bindSelection(sb.toString(), args); 518 } 519 520 /** 521 * Escape the given argument for use in a {@code LIKE} statement. 522 */ escapeForLike(@onNull String arg)523 public static String escapeForLike(@NonNull String arg) { 524 final StringBuilder sb = new StringBuilder(); 525 for (int i = 0; i < arg.length(); i++) { 526 final char c = arg.charAt(i); 527 switch (c) { 528 case '%': sb.append('\\'); 529 break; 530 case '_': sb.append('\\'); 531 break; 532 } 533 sb.append(c); 534 } 535 return sb.toString(); 536 } 537 parseBoolean(@ullable Object value, boolean def)538 public static boolean parseBoolean(@Nullable Object value, boolean def) { 539 if (value instanceof Boolean) { 540 return (Boolean) value; 541 } else if (value instanceof Number) { 542 return ((Number) value).intValue() != 0; 543 } else if (value instanceof String) { 544 final String stringValue = ((String) value).toLowerCase(Locale.ROOT); 545 return (!"false".equals(stringValue) && !"0".equals(stringValue)); 546 } else { 547 return def; 548 } 549 } 550 getAsBoolean(@onNull Bundle extras, @NonNull String key, boolean def)551 public static boolean getAsBoolean(@NonNull Bundle extras, 552 @NonNull String key, boolean def) { 553 return parseBoolean(extras.get(key), def); 554 } 555 getAsBoolean(@onNull ContentValues values, @NonNull String key, boolean def)556 public static boolean getAsBoolean(@NonNull ContentValues values, 557 @NonNull String key, boolean def) { 558 return parseBoolean(values.get(key), def); 559 } 560 getAsLong(@onNull ContentValues values, @NonNull String key, long def)561 public static long getAsLong(@NonNull ContentValues values, 562 @NonNull String key, long def) { 563 final Long value = values.getAsLong(key); 564 return (value != null) ? value : def; 565 } 566 } 567