2 * Copyright (C) 2006 The Android Open Source Project
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
8 * http://www.apache.org/licenses/LICENSE-2.0
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.
17 package android.database.sqlite;
19 import android.database.Cursor;
20 import android.database.DatabaseUtils;
21 import android.os.CancellationSignal;
22 import android.os.OperationCanceledException;
23 import android.provider.BaseColumns;
24 import android.text.TextUtils;
25 import android.util.Log;
27 import java.util.Iterator;
29 import java.util.Map.Entry;
31 import java.util.regex.Pattern;
34 * This is a convience class that helps build SQL queries to be sent to
35 * {@link SQLiteDatabase} objects.
37 public class SQLiteQueryBuilder
39 private static final String TAG = "SQLiteQueryBuilder";
40 private static final Pattern sLimitPattern =
41 Pattern.compile("\\s*\\d+\\s*(,\\s*\\d+\\s*)?");
43 private Map<String, String> mProjectionMap = null;
44 private String mTables = "";
45 private StringBuilder mWhereClause = null; // lazily created
46 private boolean mDistinct;
47 private SQLiteDatabase.CursorFactory mFactory;
48 private boolean mStrict;
50 public SQLiteQueryBuilder() {
56 * Mark the query as DISTINCT.
58 * @param distinct if true the query is DISTINCT, otherwise it isn't
60 public void setDistinct(boolean distinct) {
65 * Returns the list of tables being queried
67 * @return the list of tables being queried
69 public String getTables() {
74 * Sets the list of tables to query. Multiple tables can be specified to perform a join.
76 * setTables("foo, bar")
77 * setTables("foo LEFT OUTER JOIN bar ON (foo.id = bar.foo_id)")
79 * @param inTables the list of tables to query on
81 public void setTables(String inTables) {
86 * Append a chunk to the WHERE clause of the query. All chunks appended are surrounded
87 * by parenthesis and ANDed with the selection passed to {@link #query}. The final
88 * WHERE clause looks like:
90 * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>)
92 * @param inWhere the chunk of text to append to the WHERE clause.
94 public void appendWhere(CharSequence inWhere) {
95 if (mWhereClause == null) {
96 mWhereClause = new StringBuilder(inWhere.length() + 16);
98 if (mWhereClause.length() == 0) {
99 mWhereClause.append('(');
101 mWhereClause.append(inWhere);
105 * Append a chunk to the WHERE clause of the query. All chunks appended are surrounded
106 * by parenthesis and ANDed with the selection passed to {@link #query}. The final
107 * WHERE clause looks like:
109 * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>)
111 * @param inWhere the chunk of text to append to the WHERE clause. it will be escaped
112 * to avoid SQL injection attacks
114 public void appendWhereEscapeString(String inWhere) {
115 if (mWhereClause == null) {
116 mWhereClause = new StringBuilder(inWhere.length() + 16);
118 if (mWhereClause.length() == 0) {
119 mWhereClause.append('(');
121 DatabaseUtils.appendEscapedSQLString(mWhereClause, inWhere);
125 * Sets the projection map for the query. The projection map maps
126 * from column names that the caller passes into query to database
127 * column names. This is useful for renaming columns as well as
128 * disambiguating column names when doing joins. For example you
129 * could map "name" to "people.name". If a projection map is set
130 * it must contain all column names the user may request, even if
131 * the key and value are the same.
133 * @param columnMap maps from the user column names to the database column names
135 public void setProjectionMap(Map<String, String> columnMap) {
136 mProjectionMap = columnMap;
140 * Sets the cursor factory to be used for the query. You can use
141 * one factory for all queries on a database but it is normally
142 * easier to specify the factory when doing this query.
144 * @param factory the factory to use.
146 public void setCursorFactory(SQLiteDatabase.CursorFactory factory) {
151 * When set, the selection is verified against malicious arguments.
152 * When using this class to create a statement using
153 * {@link #buildQueryString(boolean, String, String[], String, String, String, String, String)},
154 * non-numeric limits will raise an exception. If a projection map is specified, fields
155 * not in that map will be ignored.
156 * If this class is used to execute the statement directly using
157 * {@link #query(SQLiteDatabase, String[], String, String[], String, String, String)}
159 * {@link #query(SQLiteDatabase, String[], String, String[], String, String, String, String)},
160 * additionally also parenthesis escaping selection are caught.
162 * To summarize: To get maximum protection against malicious third party apps (for example
163 * content provider consumers), make sure to do the following:
165 * <li>Set this value to true</li>
166 * <li>Use a projection map</li>
167 * <li>Use one of the query overloads instead of getting the statement as a sql string</li>
169 * By default, this value is false.
171 public void setStrict(boolean flag) {
176 * Build an SQL query string from the given clauses.
178 * @param distinct true if you want each row to be unique, false otherwise.
179 * @param tables The table names to compile the query against.
180 * @param columns A list of which columns to return. Passing null will
181 * return all columns, which is discouraged to prevent reading
182 * data from storage that isn't going to be used.
183 * @param where A filter declaring which rows to return, formatted as an SQL
184 * WHERE clause (excluding the WHERE itself). Passing null will
185 * return all rows for the given URL.
186 * @param groupBy A filter declaring how to group rows, formatted as an SQL
187 * GROUP BY clause (excluding the GROUP BY itself). Passing null
188 * will cause the rows to not be grouped.
189 * @param having A filter declare which row groups to include in the cursor,
190 * if row grouping is being used, formatted as an SQL HAVING
191 * clause (excluding the HAVING itself). Passing null will cause
192 * all row groups to be included, and is required when row
193 * grouping is not being used.
194 * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause
195 * (excluding the ORDER BY itself). Passing null will use the
196 * default sort order, which may be unordered.
197 * @param limit Limits the number of rows returned by the query,
198 * formatted as LIMIT clause. Passing null denotes no LIMIT clause.
199 * @return the SQL query string
201 public static String buildQueryString(
202 boolean distinct, String tables, String[] columns, String where,
203 String groupBy, String having, String orderBy, String limit) {
204 if (TextUtils.isEmpty(groupBy) && !TextUtils.isEmpty(having)) {
205 throw new IllegalArgumentException(
206 "HAVING clauses are only permitted when using a groupBy clause");
208 if (!TextUtils.isEmpty(limit) && !sLimitPattern.matcher(limit).matches()) {
209 throw new IllegalArgumentException("invalid LIMIT clauses:" + limit);
212 StringBuilder query = new StringBuilder(120);
214 query.append("SELECT ");
216 query.append("DISTINCT ");
218 if (columns != null && columns.length != 0) {
219 appendColumns(query, columns);
223 query.append("FROM ");
224 query.append(tables);
225 appendClause(query, " WHERE ", where);
226 appendClause(query, " GROUP BY ", groupBy);
227 appendClause(query, " HAVING ", having);
228 appendClause(query, " ORDER BY ", orderBy);
229 appendClause(query, " LIMIT ", limit);
231 return query.toString();
234 private static void appendClause(StringBuilder s, String name, String clause) {
235 if (!TextUtils.isEmpty(clause)) {
242 * Add the names that are non-null in columns to s, separating
245 public static void appendColumns(StringBuilder s, String[] columns) {
246 int n = columns.length;
248 for (int i = 0; i < n; i++) {
249 String column = columns[i];
251 if (column != null) {
262 * Perform a query by combining all current settings and the
263 * information passed into this method.
265 * @param db the database to query on
266 * @param projectionIn A list of which columns to return. Passing
267 * null will return all columns, which is discouraged to prevent
268 * reading data from storage that isn't going to be used.
269 * @param selection A filter declaring which rows to return,
270 * formatted as an SQL WHERE clause (excluding the WHERE
271 * itself). Passing null will return all rows for the given URL.
272 * @param selectionArgs You may include ?s in selection, which
273 * will be replaced by the values from selectionArgs, in order
274 * that they appear in the selection. The values will be bound
276 * @param groupBy A filter declaring how to group rows, formatted
277 * as an SQL GROUP BY clause (excluding the GROUP BY
278 * itself). Passing null will cause the rows to not be grouped.
279 * @param having A filter declare which row groups to include in
280 * the cursor, if row grouping is being used, formatted as an
281 * SQL HAVING clause (excluding the HAVING itself). Passing
282 * null will cause all row groups to be included, and is
283 * required when row grouping is not being used.
284 * @param sortOrder How to order the rows, formatted as an SQL
285 * ORDER BY clause (excluding the ORDER BY itself). Passing null
286 * will use the default sort order, which may be unordered.
287 * @return a cursor over the result set
288 * @see android.content.ContentResolver#query(android.net.Uri, String[],
289 * String, String[], String)
291 public Cursor query(SQLiteDatabase db, String[] projectionIn,
292 String selection, String[] selectionArgs, String groupBy,
293 String having, String sortOrder) {
294 return query(db, projectionIn, selection, selectionArgs, groupBy, having, sortOrder,
295 null /* limit */, null /* cancellationSignal */);
299 * Perform a query by combining all current settings and the
300 * information passed into this method.
302 * @param db the database to query on
303 * @param projectionIn A list of which columns to return. Passing
304 * null will return all columns, which is discouraged to prevent
305 * reading data from storage that isn't going to be used.
306 * @param selection A filter declaring which rows to return,
307 * formatted as an SQL WHERE clause (excluding the WHERE
308 * itself). Passing null will return all rows for the given URL.
309 * @param selectionArgs You may include ?s in selection, which
310 * will be replaced by the values from selectionArgs, in order
311 * that they appear in the selection. The values will be bound
313 * @param groupBy A filter declaring how to group rows, formatted
314 * as an SQL GROUP BY clause (excluding the GROUP BY
315 * itself). Passing null will cause the rows to not be grouped.
316 * @param having A filter declare which row groups to include in
317 * the cursor, if row grouping is being used, formatted as an
318 * SQL HAVING clause (excluding the HAVING itself). Passing
319 * null will cause all row groups to be included, and is
320 * required when row grouping is not being used.
321 * @param sortOrder How to order the rows, formatted as an SQL
322 * ORDER BY clause (excluding the ORDER BY itself). Passing null
323 * will use the default sort order, which may be unordered.
324 * @param limit Limits the number of rows returned by the query,
325 * formatted as LIMIT clause. Passing null denotes no LIMIT clause.
326 * @return a cursor over the result set
327 * @see android.content.ContentResolver#query(android.net.Uri, String[],
328 * String, String[], String)
330 public Cursor query(SQLiteDatabase db, String[] projectionIn,
331 String selection, String[] selectionArgs, String groupBy,
332 String having, String sortOrder, String limit) {
333 return query(db, projectionIn, selection, selectionArgs,
334 groupBy, having, sortOrder, limit, null);
338 * Perform a query by combining all current settings and the
339 * information passed into this method.
341 * @param db the database to query on
342 * @param projectionIn A list of which columns to return. Passing
343 * null will return all columns, which is discouraged to prevent
344 * reading data from storage that isn't going to be used.
345 * @param selection A filter declaring which rows to return,
346 * formatted as an SQL WHERE clause (excluding the WHERE
347 * itself). Passing null will return all rows for the given URL.
348 * @param selectionArgs You may include ?s in selection, which
349 * will be replaced by the values from selectionArgs, in order
350 * that they appear in the selection. The values will be bound
352 * @param groupBy A filter declaring how to group rows, formatted
353 * as an SQL GROUP BY clause (excluding the GROUP BY
354 * itself). Passing null will cause the rows to not be grouped.
355 * @param having A filter declare which row groups to include in
356 * the cursor, if row grouping is being used, formatted as an
357 * SQL HAVING clause (excluding the HAVING itself). Passing
358 * null will cause all row groups to be included, and is
359 * required when row grouping is not being used.
360 * @param sortOrder How to order the rows, formatted as an SQL
361 * ORDER BY clause (excluding the ORDER BY itself). Passing null
362 * will use the default sort order, which may be unordered.
363 * @param limit Limits the number of rows returned by the query,
364 * formatted as LIMIT clause. Passing null denotes no LIMIT clause.
365 * @param cancellationSignal A signal to cancel the operation in progress, or null if none.
366 * If the operation is canceled, then {@link OperationCanceledException} will be thrown
367 * when the query is executed.
368 * @return a cursor over the result set
369 * @see android.content.ContentResolver#query(android.net.Uri, String[],
370 * String, String[], String)
372 public Cursor query(SQLiteDatabase db, String[] projectionIn,
373 String selection, String[] selectionArgs, String groupBy,
374 String having, String sortOrder, String limit, CancellationSignal cancellationSignal) {
375 if (mTables == null) {
379 if (mStrict && selection != null && selection.length() > 0) {
380 // Validate the user-supplied selection to detect syntactic anomalies
381 // in the selection string that could indicate a SQL injection attempt.
382 // The idea is to ensure that the selection clause is a valid SQL expression
383 // by compiling it twice: once wrapped in parentheses and once as
384 // originally specified. An attacker cannot create an expression that
385 // would escape the SQL expression while maintaining balanced parentheses
386 // in both the wrapped and original forms.
387 String sqlForValidation = buildQuery(projectionIn, "(" + selection + ")", groupBy,
388 having, sortOrder, limit);
389 db.validateSql(sqlForValidation, cancellationSignal); // will throw if query is invalid
392 String sql = buildQuery(
393 projectionIn, selection, groupBy, having,
396 if (Log.isLoggable(TAG, Log.DEBUG)) {
397 Log.d(TAG, "Performing query: " + sql);
399 return db.rawQueryWithFactory(
400 mFactory, sql, selectionArgs,
401 SQLiteDatabase.findEditTable(mTables),
402 cancellationSignal); // will throw if query is invalid
406 * Construct a SELECT statement suitable for use in a group of
407 * SELECT statements that will be joined through UNION operators
408 * in buildUnionQuery.
410 * @param projectionIn A list of which columns to return. Passing
411 * null will return all columns, which is discouraged to
412 * prevent reading data from storage that isn't going to be
414 * @param selection A filter declaring which rows to return,
415 * formatted as an SQL WHERE clause (excluding the WHERE
416 * itself). Passing null will return all rows for the given
418 * @param groupBy A filter declaring how to group rows, formatted
419 * as an SQL GROUP BY clause (excluding the GROUP BY itself).
420 * Passing null will cause the rows to not be grouped.
421 * @param having A filter declare which row groups to include in
422 * the cursor, if row grouping is being used, formatted as an
423 * SQL HAVING clause (excluding the HAVING itself). Passing
424 * null will cause all row groups to be included, and is
425 * required when row grouping is not being used.
426 * @param sortOrder How to order the rows, formatted as an SQL
427 * ORDER BY clause (excluding the ORDER BY itself). Passing null
428 * will use the default sort order, which may be unordered.
429 * @param limit Limits the number of rows returned by the query,
430 * formatted as LIMIT clause. Passing null denotes no LIMIT clause.
431 * @return the resulting SQL SELECT statement
433 public String buildQuery(
434 String[] projectionIn, String selection, String groupBy,
435 String having, String sortOrder, String limit) {
436 String[] projection = computeProjection(projectionIn);
438 StringBuilder where = new StringBuilder();
439 boolean hasBaseWhereClause = mWhereClause != null && mWhereClause.length() > 0;
441 if (hasBaseWhereClause) {
442 where.append(mWhereClause.toString());
446 // Tack on the user's selection, if present.
447 if (selection != null && selection.length() > 0) {
448 if (hasBaseWhereClause) {
449 where.append(" AND ");
453 where.append(selection);
457 return buildQueryString(
458 mDistinct, mTables, projection, where.toString(),
459 groupBy, having, sortOrder, limit);
463 * @deprecated This method's signature is misleading since no SQL parameter
464 * substitution is carried out. The selection arguments parameter does not get
465 * used at all. To avoid confusion, call
466 * {@link #buildQuery(String[], String, String, String, String, String)} instead.
469 public String buildQuery(
470 String[] projectionIn, String selection, String[] selectionArgs,
471 String groupBy, String having, String sortOrder, String limit) {
472 return buildQuery(projectionIn, selection, groupBy, having, sortOrder, limit);
476 * Construct a SELECT statement suitable for use in a group of
477 * SELECT statements that will be joined through UNION operators
478 * in buildUnionQuery.
480 * @param typeDiscriminatorColumn the name of the result column
481 * whose cells will contain the name of the table from which
482 * each row was drawn.
483 * @param unionColumns the names of the columns to appear in the
484 * result. This may include columns that do not appear in the
485 * table this SELECT is querying (i.e. mTables), but that do
486 * appear in one of the other tables in the UNION query that we
488 * @param columnsPresentInTable a Set of the names of the columns
489 * that appear in this table (i.e. in the table whose name is
490 * mTables). Since columns in unionColumns include columns that
491 * appear only in other tables, we use this array to distinguish
492 * which ones actually are present. Other columns will have
493 * NULL values for results from this subquery.
494 * @param computedColumnsOffset all columns in unionColumns before
495 * this index are included under the assumption that they're
496 * computed and therefore won't appear in columnsPresentInTable,
497 * e.g. "date * 1000 as normalized_date"
498 * @param typeDiscriminatorValue the value used for the
499 * type-discriminator column in this subquery
500 * @param selection A filter declaring which rows to return,
501 * formatted as an SQL WHERE clause (excluding the WHERE
502 * itself). Passing null will return all rows for the given
504 * @param groupBy A filter declaring how to group rows, formatted
505 * as an SQL GROUP BY clause (excluding the GROUP BY itself).
506 * Passing null will cause the rows to not be grouped.
507 * @param having A filter declare which row groups to include in
508 * the cursor, if row grouping is being used, formatted as an
509 * SQL HAVING clause (excluding the HAVING itself). Passing
510 * null will cause all row groups to be included, and is
511 * required when row grouping is not being used.
512 * @return the resulting SQL SELECT statement
514 public String buildUnionSubQuery(
515 String typeDiscriminatorColumn,
516 String[] unionColumns,
517 Set<String> columnsPresentInTable,
518 int computedColumnsOffset,
519 String typeDiscriminatorValue,
523 int unionColumnsCount = unionColumns.length;
524 String[] projectionIn = new String[unionColumnsCount];
526 for (int i = 0; i < unionColumnsCount; i++) {
527 String unionColumn = unionColumns[i];
529 if (unionColumn.equals(typeDiscriminatorColumn)) {
530 projectionIn[i] = "'" + typeDiscriminatorValue + "' AS "
531 + typeDiscriminatorColumn;
532 } else if (i <= computedColumnsOffset
533 || columnsPresentInTable.contains(unionColumn)) {
534 projectionIn[i] = unionColumn;
536 projectionIn[i] = "NULL AS " + unionColumn;
540 projectionIn, selection, groupBy, having,
541 null /* sortOrder */,
546 * @deprecated This method's signature is misleading since no SQL parameter
547 * substitution is carried out. The selection arguments parameter does not get
548 * used at all. To avoid confusion, call
549 * {@link #buildUnionSubQuery}
553 public String buildUnionSubQuery(
554 String typeDiscriminatorColumn,
555 String[] unionColumns,
556 Set<String> columnsPresentInTable,
557 int computedColumnsOffset,
558 String typeDiscriminatorValue,
560 String[] selectionArgs,
563 return buildUnionSubQuery(
564 typeDiscriminatorColumn, unionColumns, columnsPresentInTable,
565 computedColumnsOffset, typeDiscriminatorValue, selection,
570 * Given a set of subqueries, all of which are SELECT statements,
571 * construct a query that returns the union of what those
573 * @param subQueries an array of SQL SELECT statements, all of
574 * which must have the same columns as the same positions in
576 * @param sortOrder How to order the rows, formatted as an SQL
577 * ORDER BY clause (excluding the ORDER BY itself). Passing
578 * null will use the default sort order, which may be unordered.
579 * @param limit The limit clause, which applies to the entire union result set
581 * @return the resulting SQL SELECT statement
583 public String buildUnionQuery(String[] subQueries, String sortOrder, String limit) {
584 StringBuilder query = new StringBuilder(128);
585 int subQueryCount = subQueries.length;
586 String unionOperator = mDistinct ? " UNION " : " UNION ALL ";
588 for (int i = 0; i < subQueryCount; i++) {
590 query.append(unionOperator);
592 query.append(subQueries[i]);
594 appendClause(query, " ORDER BY ", sortOrder);
595 appendClause(query, " LIMIT ", limit);
596 return query.toString();
599 private String[] computeProjection(String[] projectionIn) {
600 if (projectionIn != null && projectionIn.length > 0) {
601 if (mProjectionMap != null) {
602 String[] projection = new String[projectionIn.length];
603 int length = projectionIn.length;
605 for (int i = 0; i < length; i++) {
606 String userColumn = projectionIn[i];
607 String column = mProjectionMap.get(userColumn);
609 if (column != null) {
610 projection[i] = column;
615 ( userColumn.contains(" AS ") || userColumn.contains(" as "))) {
616 /* A column alias already exist */
617 projection[i] = userColumn;
621 throw new IllegalArgumentException("Invalid column "
628 } else if (mProjectionMap != null) {
629 // Return all columns in projection map.
630 Set<Entry<String, String>> entrySet = mProjectionMap.entrySet();
631 String[] projection = new String[entrySet.size()];
632 Iterator<Entry<String, String>> entryIter = entrySet.iterator();
635 while (entryIter.hasNext()) {
636 Entry<String, String> entry = entryIter.next();
638 // Don't include the _count column when people ask for no projection.
639 if (entry.getKey().equals(BaseColumns._COUNT)) {
642 projection[i++] = entry.getValue();