SQLiteQueryBuilder.java revision 50b1f8d3fc1379339119933e8f567547efb89aa5
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 17package android.database.sqlite; 18 19import android.database.Cursor; 20import android.database.DatabaseUtils; 21import android.provider.BaseColumns; 22import android.text.TextUtils; 23import android.util.Log; 24 25import java.util.Iterator; 26import java.util.Map; 27import java.util.Map.Entry; 28import java.util.Set; 29import java.util.regex.Pattern; 30 31/** 32 * This is a convience class that helps build SQL queries to be sent to 33 * {@link SQLiteDatabase} objects. 34 */ 35public class SQLiteQueryBuilder 36{ 37 private static final String TAG = "SQLiteQueryBuilder"; 38 private static final Pattern sLimitPattern = 39 Pattern.compile("\\s*\\d+\\s*(,\\s*\\d+\\s*)?"); 40 41 private Map<String, String> mProjectionMap = null; 42 private String mTables = ""; 43 private StringBuilder mWhereClause = null; // lazily created 44 private boolean mDistinct; 45 private SQLiteDatabase.CursorFactory mFactory; 46 private boolean mStrict; 47 48 public SQLiteQueryBuilder() { 49 mDistinct = false; 50 mFactory = null; 51 } 52 53 /** 54 * Mark the query as DISTINCT. 55 * 56 * @param distinct if true the query is DISTINCT, otherwise it isn't 57 */ 58 public void setDistinct(boolean distinct) { 59 mDistinct = distinct; 60 } 61 62 /** 63 * Returns the list of tables being queried 64 * 65 * @return the list of tables being queried 66 */ 67 public String getTables() { 68 return mTables; 69 } 70 71 /** 72 * Sets the list of tables to query. Multiple tables can be specified to perform a join. 73 * For example: 74 * setTables("foo, bar") 75 * setTables("foo LEFT OUTER JOIN bar ON (foo.id = bar.foo_id)") 76 * 77 * @param inTables the list of tables to query on 78 */ 79 public void setTables(String inTables) { 80 mTables = inTables; 81 } 82 83 /** 84 * Append a chunk to the WHERE clause of the query. All chunks appended are surrounded 85 * by parenthesis and ANDed with the selection passed to {@link #query}. The final 86 * WHERE clause looks like: 87 * 88 * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) 89 * 90 * @param inWhere the chunk of text to append to the WHERE clause. 91 */ 92 public void appendWhere(CharSequence inWhere) { 93 if (mWhereClause == null) { 94 mWhereClause = new StringBuilder(inWhere.length() + 16); 95 } 96 if (mWhereClause.length() == 0) { 97 mWhereClause.append('('); 98 } 99 mWhereClause.append(inWhere); 100 } 101 102 /** 103 * Append a chunk to the WHERE clause of the query. All chunks appended are surrounded 104 * by parenthesis and ANDed with the selection passed to {@link #query}. The final 105 * WHERE clause looks like: 106 * 107 * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) 108 * 109 * @param inWhere the chunk of text to append to the WHERE clause. it will be escaped 110 * to avoid SQL injection attacks 111 */ 112 public void appendWhereEscapeString(String inWhere) { 113 if (mWhereClause == null) { 114 mWhereClause = new StringBuilder(inWhere.length() + 16); 115 } 116 if (mWhereClause.length() == 0) { 117 mWhereClause.append('('); 118 } 119 DatabaseUtils.appendEscapedSQLString(mWhereClause, inWhere); 120 } 121 122 /** 123 * Sets the projection map for the query. The projection map maps 124 * from column names that the caller passes into query to database 125 * column names. This is useful for renaming columns as well as 126 * disambiguating column names when doing joins. For example you 127 * could map "name" to "people.name". If a projection map is set 128 * it must contain all column names the user may request, even if 129 * the key and value are the same. 130 * 131 * @param columnMap maps from the user column names to the database column names 132 */ 133 public void setProjectionMap(Map<String, String> columnMap) { 134 mProjectionMap = columnMap; 135 } 136 137 /** 138 * Sets the cursor factory to be used for the query. You can use 139 * one factory for all queries on a database but it is normally 140 * easier to specify the factory when doing this query. @param 141 * factory the factor to use 142 */ 143 public void setCursorFactory(SQLiteDatabase.CursorFactory factory) { 144 mFactory = factory; 145 } 146 147 /** 148 * Need to keep this to not break the build until ContactsProvider2 has been changed to 149 * use the new API 150 * TODO: Remove this 151 * @hide 152 */ 153 public void setStrictProjectionMap(boolean flag) { 154 } 155 156 /** 157 * When set, the selection is verified against malicious arguments. 158 * When using this class to create a statement using 159 * {@link #buildQueryString(boolean, String, String[], String, String, String, String, String)}, 160 * non-numeric limits will raise an exception. If a projection map is specified, fields 161 * not in that map will be ignored. 162 * If this class is used to execute the statement directly using 163 * {@link #query(SQLiteDatabase, String[], String, String[], String, String, String)} 164 * or 165 * {@link #query(SQLiteDatabase, String[], String, String[], String, String, String, String)}, 166 * additionally also parenthesis escaping selection are caught. 167 * 168 * To summarize: To get maximum protection against malicious third party apps (for example 169 * content provider consumers), make sure to do the following: 170 * <ul> 171 * <li>Set this value to true</li> 172 * <li>Use a projection map</li> 173 * <li>Use one of the query overloads instead of getting the statement as a sql string</li> 174 * </ul> 175 * By default, this value is false. 176 */ 177 public void setStrict(boolean flag) { 178 mStrict = flag; 179 } 180 181 /** 182 * Build an SQL query string from the given clauses. 183 * 184 * @param distinct true if you want each row to be unique, false otherwise. 185 * @param tables The table names to compile the query against. 186 * @param columns A list of which columns to return. Passing null will 187 * return all columns, which is discouraged to prevent reading 188 * data from storage that isn't going to be used. 189 * @param where A filter declaring which rows to return, formatted as an SQL 190 * WHERE clause (excluding the WHERE itself). Passing null will 191 * return all rows for the given URL. 192 * @param groupBy A filter declaring how to group rows, formatted as an SQL 193 * GROUP BY clause (excluding the GROUP BY itself). Passing null 194 * will cause the rows to not be grouped. 195 * @param having A filter declare which row groups to include in the cursor, 196 * if row grouping is being used, formatted as an SQL HAVING 197 * clause (excluding the HAVING itself). Passing null will cause 198 * all row groups to be included, and is required when row 199 * grouping is not being used. 200 * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause 201 * (excluding the ORDER BY itself). Passing null will use the 202 * default sort order, which may be unordered. 203 * @param limit Limits the number of rows returned by the query, 204 * formatted as LIMIT clause. Passing null denotes no LIMIT clause. 205 * @return the SQL query string 206 */ 207 public static String buildQueryString( 208 boolean distinct, String tables, String[] columns, String where, 209 String groupBy, String having, String orderBy, String limit) { 210 if (TextUtils.isEmpty(groupBy) && !TextUtils.isEmpty(having)) { 211 throw new IllegalArgumentException( 212 "HAVING clauses are only permitted when using a groupBy clause"); 213 } 214 if (!TextUtils.isEmpty(limit) && !sLimitPattern.matcher(limit).matches()) { 215 throw new IllegalArgumentException("invalid LIMIT clauses:" + limit); 216 } 217 218 StringBuilder query = new StringBuilder(120); 219 220 query.append("SELECT "); 221 if (distinct) { 222 query.append("DISTINCT "); 223 } 224 if (columns != null && columns.length != 0) { 225 appendColumns(query, columns); 226 } else { 227 query.append("* "); 228 } 229 query.append("FROM "); 230 query.append(tables); 231 appendClause(query, " WHERE ", where); 232 appendClause(query, " GROUP BY ", groupBy); 233 appendClause(query, " HAVING ", having); 234 appendClause(query, " ORDER BY ", orderBy); 235 appendClause(query, " LIMIT ", limit); 236 237 return query.toString(); 238 } 239 240 private static void appendClause(StringBuilder s, String name, String clause) { 241 if (!TextUtils.isEmpty(clause)) { 242 s.append(name); 243 s.append(clause); 244 } 245 } 246 247 /** 248 * Add the names that are non-null in columns to s, separating 249 * them with commas. 250 */ 251 public static void appendColumns(StringBuilder s, String[] columns) { 252 int n = columns.length; 253 254 for (int i = 0; i < n; i++) { 255 String column = columns[i]; 256 257 if (column != null) { 258 if (i > 0) { 259 s.append(", "); 260 } 261 s.append(column); 262 } 263 } 264 s.append(' '); 265 } 266 267 /** 268 * Perform a query by combining all current settings and the 269 * information passed into this method. 270 * 271 * @param db the database to query on 272 * @param projectionIn A list of which columns to return. Passing 273 * null will return all columns, which is discouraged to prevent 274 * reading data from storage that isn't going to be used. 275 * @param selection A filter declaring which rows to return, 276 * formatted as an SQL WHERE clause (excluding the WHERE 277 * itself). Passing null will return all rows for the given URL. 278 * @param selectionArgs You may include ?s in selection, which 279 * will be replaced by the values from selectionArgs, in order 280 * that they appear in the selection. The values will be bound 281 * as Strings. 282 * @param groupBy A filter declaring how to group rows, formatted 283 * as an SQL GROUP BY clause (excluding the GROUP BY 284 * itself). Passing null will cause the rows to not be grouped. 285 * @param having A filter declare which row groups to include in 286 * the cursor, if row grouping is being used, formatted as an 287 * SQL HAVING clause (excluding the HAVING itself). Passing 288 * null will cause all row groups to be included, and is 289 * required when row grouping is not being used. 290 * @param sortOrder How to order the rows, formatted as an SQL 291 * ORDER BY clause (excluding the ORDER BY itself). Passing null 292 * will use the default sort order, which may be unordered. 293 * @return a cursor over the result set 294 * @see android.content.ContentResolver#query(android.net.Uri, String[], 295 * String, String[], String) 296 */ 297 public Cursor query(SQLiteDatabase db, String[] projectionIn, 298 String selection, String[] selectionArgs, String groupBy, 299 String having, String sortOrder) { 300 return query(db, projectionIn, selection, selectionArgs, groupBy, having, sortOrder, 301 null /* limit */); 302 } 303 304 /** 305 * Perform a query by combining all current settings and the 306 * information passed into this method. 307 * 308 * @param db the database to query on 309 * @param projectionIn A list of which columns to return. Passing 310 * null will return all columns, which is discouraged to prevent 311 * reading data from storage that isn't going to be used. 312 * @param selection A filter declaring which rows to return, 313 * formatted as an SQL WHERE clause (excluding the WHERE 314 * itself). Passing null will return all rows for the given URL. 315 * @param selectionArgs You may include ?s in selection, which 316 * will be replaced by the values from selectionArgs, in order 317 * that they appear in the selection. The values will be bound 318 * as Strings. 319 * @param groupBy A filter declaring how to group rows, formatted 320 * as an SQL GROUP BY clause (excluding the GROUP BY 321 * itself). Passing null will cause the rows to not be grouped. 322 * @param having A filter declare which row groups to include in 323 * the cursor, if row grouping is being used, formatted as an 324 * SQL HAVING clause (excluding the HAVING itself). Passing 325 * null will cause all row groups to be included, and is 326 * required when row grouping is not being used. 327 * @param sortOrder How to order the rows, formatted as an SQL 328 * ORDER BY clause (excluding the ORDER BY itself). Passing null 329 * will use the default sort order, which may be unordered. 330 * @param limit Limits the number of rows returned by the query, 331 * formatted as LIMIT clause. Passing null denotes no LIMIT clause. 332 * @return a cursor over the result set 333 * @see android.content.ContentResolver#query(android.net.Uri, String[], 334 * String, String[], String) 335 */ 336 public Cursor query(SQLiteDatabase db, String[] projectionIn, 337 String selection, String[] selectionArgs, String groupBy, 338 String having, String sortOrder, String limit) { 339 if (mTables == null) { 340 return null; 341 } 342 343 if (mStrict && selection != null && selection.length() > 0) { 344 // Validate the user-supplied selection to detect syntactic anomalies 345 // in the selection string that could indicate a SQL injection attempt. 346 // The idea is to ensure that the selection clause is a valid SQL expression 347 // by compiling it twice: once wrapped in parentheses and once as 348 // originally specified. An attacker cannot create an expression that 349 // would escape the SQL expression while maintaining balanced parentheses 350 // in both the wrapped and original forms. 351 String sqlForValidation = buildQuery(projectionIn, "(" + selection + ")", groupBy, 352 having, sortOrder, limit); 353 validateSql(db, sqlForValidation); // will throw if query is invalid 354 } 355 356 String sql = buildQuery( 357 projectionIn, selection, groupBy, having, 358 sortOrder, limit); 359 360 if (Log.isLoggable(TAG, Log.DEBUG)) { 361 Log.d(TAG, "Performing query: " + sql); 362 } 363 return db.rawQueryWithFactory( 364 mFactory, sql, selectionArgs, 365 SQLiteDatabase.findEditTable(mTables)); // will throw if query is invalid 366 } 367 368 /** 369 * Verifies that a SQL statement is valid by compiling it. 370 * If the SQL statement is not valid, this method will throw a {@link SQLiteException}. 371 */ 372 private void validateSql(SQLiteDatabase db, String sql) { 373 db.lock(sql); 374 try { 375 new SQLiteCompiledSql(db, sql).releaseSqlStatement(); 376 } finally { 377 db.unlock(); 378 } 379 } 380 381 /** 382 * Construct a SELECT statement suitable for use in a group of 383 * SELECT statements that will be joined through UNION operators 384 * in buildUnionQuery. 385 * 386 * @param projectionIn A list of which columns to return. Passing 387 * null will return all columns, which is discouraged to 388 * prevent reading data from storage that isn't going to be 389 * used. 390 * @param selection A filter declaring which rows to return, 391 * formatted as an SQL WHERE clause (excluding the WHERE 392 * itself). Passing null will return all rows for the given 393 * URL. 394 * @param groupBy A filter declaring how to group rows, formatted 395 * as an SQL GROUP BY clause (excluding the GROUP BY itself). 396 * Passing null will cause the rows to not be grouped. 397 * @param having A filter declare which row groups to include in 398 * the cursor, if row grouping is being used, formatted as an 399 * SQL HAVING clause (excluding the HAVING itself). Passing 400 * null will cause all row groups to be included, and is 401 * required when row grouping is not being used. 402 * @param sortOrder How to order the rows, formatted as an SQL 403 * ORDER BY clause (excluding the ORDER BY itself). Passing null 404 * will use the default sort order, which may be unordered. 405 * @param limit Limits the number of rows returned by the query, 406 * formatted as LIMIT clause. Passing null denotes no LIMIT clause. 407 * @return the resulting SQL SELECT statement 408 */ 409 public String buildQuery( 410 String[] projectionIn, String selection, String groupBy, 411 String having, String sortOrder, String limit) { 412 String[] projection = computeProjection(projectionIn); 413 414 StringBuilder where = new StringBuilder(); 415 boolean hasBaseWhereClause = mWhereClause != null && mWhereClause.length() > 0; 416 417 if (hasBaseWhereClause) { 418 where.append(mWhereClause.toString()); 419 where.append(')'); 420 } 421 422 // Tack on the user's selection, if present. 423 if (selection != null && selection.length() > 0) { 424 if (hasBaseWhereClause) { 425 where.append(" AND "); 426 } 427 428 where.append('('); 429 where.append(selection); 430 where.append(')'); 431 } 432 433 return buildQueryString( 434 mDistinct, mTables, projection, where.toString(), 435 groupBy, having, sortOrder, limit); 436 } 437 438 /** 439 * @deprecated This method's signature is misleading since no SQL parameter 440 * substitution is carried out. The selection arguments parameter does not get 441 * used at all. To avoid confusion, call 442 * {@link #buildQuery(String[], String, String, String, String, String)} instead. 443 */ 444 @Deprecated 445 public String buildQuery( 446 String[] projectionIn, String selection, String[] selectionArgs, 447 String groupBy, String having, String sortOrder, String limit) { 448 return buildQuery(projectionIn, selection, groupBy, having, sortOrder, limit); 449 } 450 451 /** 452 * Construct a SELECT statement suitable for use in a group of 453 * SELECT statements that will be joined through UNION operators 454 * in buildUnionQuery. 455 * 456 * @param typeDiscriminatorColumn the name of the result column 457 * whose cells will contain the name of the table from which 458 * each row was drawn. 459 * @param unionColumns the names of the columns to appear in the 460 * result. This may include columns that do not appear in the 461 * table this SELECT is querying (i.e. mTables), but that do 462 * appear in one of the other tables in the UNION query that we 463 * are constructing. 464 * @param columnsPresentInTable a Set of the names of the columns 465 * that appear in this table (i.e. in the table whose name is 466 * mTables). Since columns in unionColumns include columns that 467 * appear only in other tables, we use this array to distinguish 468 * which ones actually are present. Other columns will have 469 * NULL values for results from this subquery. 470 * @param computedColumnsOffset all columns in unionColumns before 471 * this index are included under the assumption that they're 472 * computed and therefore won't appear in columnsPresentInTable, 473 * e.g. "date * 1000 as normalized_date" 474 * @param typeDiscriminatorValue the value used for the 475 * type-discriminator column in this subquery 476 * @param selection A filter declaring which rows to return, 477 * formatted as an SQL WHERE clause (excluding the WHERE 478 * itself). Passing null will return all rows for the given 479 * URL. 480 * @param groupBy A filter declaring how to group rows, formatted 481 * as an SQL GROUP BY clause (excluding the GROUP BY itself). 482 * Passing null will cause the rows to not be grouped. 483 * @param having A filter declare which row groups to include in 484 * the cursor, if row grouping is being used, formatted as an 485 * SQL HAVING clause (excluding the HAVING itself). Passing 486 * null will cause all row groups to be included, and is 487 * required when row grouping is not being used. 488 * @return the resulting SQL SELECT statement 489 */ 490 public String buildUnionSubQuery( 491 String typeDiscriminatorColumn, 492 String[] unionColumns, 493 Set<String> columnsPresentInTable, 494 int computedColumnsOffset, 495 String typeDiscriminatorValue, 496 String selection, 497 String groupBy, 498 String having) { 499 int unionColumnsCount = unionColumns.length; 500 String[] projectionIn = new String[unionColumnsCount]; 501 502 for (int i = 0; i < unionColumnsCount; i++) { 503 String unionColumn = unionColumns[i]; 504 505 if (unionColumn.equals(typeDiscriminatorColumn)) { 506 projectionIn[i] = "'" + typeDiscriminatorValue + "' AS " 507 + typeDiscriminatorColumn; 508 } else if (i <= computedColumnsOffset 509 || columnsPresentInTable.contains(unionColumn)) { 510 projectionIn[i] = unionColumn; 511 } else { 512 projectionIn[i] = "NULL AS " + unionColumn; 513 } 514 } 515 return buildQuery( 516 projectionIn, selection, groupBy, having, 517 null /* sortOrder */, 518 null /* limit */); 519 } 520 521 /** 522 * @deprecated This method's signature is misleading since no SQL parameter 523 * substitution is carried out. The selection arguments parameter does not get 524 * used at all. To avoid confusion, call 525 * {@link #buildUnionSubQuery} 526 * instead. 527 */ 528 @Deprecated 529 public String buildUnionSubQuery( 530 String typeDiscriminatorColumn, 531 String[] unionColumns, 532 Set<String> columnsPresentInTable, 533 int computedColumnsOffset, 534 String typeDiscriminatorValue, 535 String selection, 536 String[] selectionArgs, 537 String groupBy, 538 String having) { 539 return buildUnionSubQuery( 540 typeDiscriminatorColumn, unionColumns, columnsPresentInTable, 541 computedColumnsOffset, typeDiscriminatorValue, selection, 542 groupBy, having); 543 } 544 545 /** 546 * Given a set of subqueries, all of which are SELECT statements, 547 * construct a query that returns the union of what those 548 * subqueries return. 549 * @param subQueries an array of SQL SELECT statements, all of 550 * which must have the same columns as the same positions in 551 * their results 552 * @param sortOrder How to order the rows, formatted as an SQL 553 * ORDER BY clause (excluding the ORDER BY itself). Passing 554 * null will use the default sort order, which may be unordered. 555 * @param limit The limit clause, which applies to the entire union result set 556 * 557 * @return the resulting SQL SELECT statement 558 */ 559 public String buildUnionQuery(String[] subQueries, String sortOrder, String limit) { 560 StringBuilder query = new StringBuilder(128); 561 int subQueryCount = subQueries.length; 562 String unionOperator = mDistinct ? " UNION " : " UNION ALL "; 563 564 for (int i = 0; i < subQueryCount; i++) { 565 if (i > 0) { 566 query.append(unionOperator); 567 } 568 query.append(subQueries[i]); 569 } 570 appendClause(query, " ORDER BY ", sortOrder); 571 appendClause(query, " LIMIT ", limit); 572 return query.toString(); 573 } 574 575 private String[] computeProjection(String[] projectionIn) { 576 if (projectionIn != null && projectionIn.length > 0) { 577 if (mProjectionMap != null) { 578 String[] projection = new String[projectionIn.length]; 579 int length = projectionIn.length; 580 581 for (int i = 0; i < length; i++) { 582 String userColumn = projectionIn[i]; 583 String column = mProjectionMap.get(userColumn); 584 585 if (column != null) { 586 projection[i] = column; 587 continue; 588 } 589 590 if (!mStrict && 591 ( userColumn.contains(" AS ") || userColumn.contains(" as "))) { 592 /* A column alias already exist */ 593 projection[i] = userColumn; 594 continue; 595 } 596 597 throw new IllegalArgumentException("Invalid column " 598 + projectionIn[i]); 599 } 600 return projection; 601 } else { 602 return projectionIn; 603 } 604 } else if (mProjectionMap != null) { 605 // Return all columns in projection map. 606 Set<Entry<String, String>> entrySet = mProjectionMap.entrySet(); 607 String[] projection = new String[entrySet.size()]; 608 Iterator<Entry<String, String>> entryIter = entrySet.iterator(); 609 int i = 0; 610 611 while (entryIter.hasNext()) { 612 Entry<String, String> entry = entryIter.next(); 613 614 // Don't include the _count column when people ask for no projection. 615 if (entry.getKey().equals(BaseColumns._COUNT)) { 616 continue; 617 } 618 projection[i++] = entry.getValue(); 619 } 620 return projection; 621 } 622 return null; 623 } 624} 625