diff options
8 files changed, 797 insertions, 22 deletions
diff --git a/src/com/android/providers/contacts/ContactsDatabaseHelper.java b/src/com/android/providers/contacts/ContactsDatabaseHelper.java index 81e3f847..e827df33 100644 --- a/src/com/android/providers/contacts/ContactsDatabaseHelper.java +++ b/src/com/android/providers/contacts/ContactsDatabaseHelper.java @@ -16,6 +16,8 @@ package com.android.providers.contacts; +import com.android.providers.contacts.sqlite.DatabaseAnalyzer; +import com.android.providers.contacts.sqlite.SqlChecker; import com.android.providers.contacts.util.PropertyUtils; import com.google.android.collect.Sets; import com.google.common.annotations.VisibleForTesting; @@ -95,6 +97,7 @@ import libcore.icu.ICU; import java.security.MessageDigest; import java.security.NoSuchAlgorithmException; +import java.util.ArrayList; import java.util.Locale; import java.util.Set; import java.util.concurrent.ConcurrentHashMap; @@ -1002,6 +1005,8 @@ public class ContactsDatabaseHelper extends SQLiteOpenHelper { private CharArrayBuffer mCharArrayBuffer = new CharArrayBuffer(128); private NameSplitter mNameSplitter; + private volatile boolean mEnableSqlCheck = false; + public static synchronized ContactsDatabaseHelper getInstance(Context context) { if (sSingleton == null) { sSingleton = new ContactsDatabaseHelper(context, DATABASE_NAME, true); @@ -1013,7 +1018,7 @@ public class ContactsDatabaseHelper extends SQLiteOpenHelper { * Returns a new instance for unit tests. */ @NeededForTesting - static ContactsDatabaseHelper getNewInstanceForTest(Context context) { + public static ContactsDatabaseHelper getNewInstanceForTest(Context context) { return new ContactsDatabaseHelper(context, null, false); } @@ -6178,4 +6183,63 @@ public class ContactsDatabaseHelper extends SQLiteOpenHelper { metadataSyncInsert.bindLong(4, deleted); return metadataSyncInsert.executeInsert(); } + + public void setSqlCheckEnabled(boolean enabled) { + mEnableSqlCheck = enabled; + } + + private SqlChecker mCachedSqlChecker; + + private SqlChecker getSqlChecker() { + // No need for synchronization on mCachedSqlChecker, because worst-case we'll just + // initialize it twice. + if (mCachedSqlChecker != null) { + return mCachedSqlChecker; + } + final ArrayList<String> invalidTokens = new ArrayList<>(); + + // Disallow referring to tables and views. However, we exempt tables whose names are + // also used as column names of any tables. (Right now it's only 'data'.) + invalidTokens.addAll(DatabaseAnalyzer.findTableViewsAllowingColumns(getReadableDatabase())); + + // Disallow token "select" to disallow subqueries. + invalidTokens.add("select"); + + // TODO Add hidden columns if needed. + + mCachedSqlChecker = new SqlChecker(invalidTokens); + + return mCachedSqlChecker; + } + + /** + * Ensure (a piece of) SQL is valid and doesn't contain disallowed tokens. + */ + public void validateSql(String sqlPiece) { + if (mEnableSqlCheck) { + getSqlChecker().ensureNoInvalidTokens(sqlPiece); + } + } + + /** + * Ensure all keys in {@code values} are valid. (i.e. they're all single token.) + */ + public void validateContentValues(ContentValues values) { + if (mEnableSqlCheck) { + for (String key : values.keySet()) { + getSqlChecker().ensureSingleTokenOnly(key); + } + } + } + + /** + * Ensure all column names in {@code projection} are valid. (i.e. they're all single token.) + */ + public void validateProjection(String[] projection) { + if (mEnableSqlCheck && projection != null) { + for (String column : projection) { + getSqlChecker().ensureSingleTokenOnly(column); + } + } + } } diff --git a/src/com/android/providers/contacts/ContactsProvider2.java b/src/com/android/providers/contacts/ContactsProvider2.java index ac9108c2..c52cd584 100644 --- a/src/com/android/providers/contacts/ContactsProvider2.java +++ b/src/com/android/providers/contacts/ContactsProvider2.java @@ -1582,7 +1582,12 @@ public class ContactsProvider2 extends AbstractContactsProvider mMetadataSyncEnabled = android.provider.Settings.Global.getInt( getContext().getContentResolver(), Global.CONTACT_METADATA_SYNC_ENABLED, 0) == 1; + // STOPSHIP Move the constant to Settings. + final boolean enableSqlCheck = android.provider.Settings.Global.getInt( + getContext().getContentResolver(), "contact_sql_check_enabled", 1) == 1; + mContactsHelper = getDatabaseHelper(getContext()); + mContactsHelper.setSqlCheckEnabled(enableSqlCheck); mDbHelper.set(mContactsHelper); // Set up the DB helper for keeping transactions serialized. @@ -2240,6 +2245,8 @@ public class ContactsProvider2 extends AbstractContactsProvider public Uri insert(Uri uri, ContentValues values) { waitForAccess(mWriteAccessLatch); + mContactsHelper.validateContentValues(values); + if (mapsToProfileDbWithInsertedValues(uri, values)) { switchToProfileMode(); return mProfileProvider.insert(uri, values); @@ -2252,6 +2259,9 @@ public class ContactsProvider2 extends AbstractContactsProvider public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) { waitForAccess(mWriteAccessLatch); + mContactsHelper.validateContentValues(values); + mContactsHelper.validateSql(selection); + if (mapsToProfileDb(uri)) { switchToProfileMode(); return mProfileProvider.update(uri, values, selection, selectionArgs); @@ -2264,6 +2274,8 @@ public class ContactsProvider2 extends AbstractContactsProvider public int delete(Uri uri, String selection, String[] selectionArgs) { waitForAccess(mWriteAccessLatch); + mContactsHelper.validateSql(selection); + if (mapsToProfileDb(uri)) { switchToProfileMode(); return mProfileProvider.delete(uri, selection, selectionArgs); @@ -5495,6 +5507,11 @@ public class ContactsProvider2 extends AbstractContactsProvider " order=[" + sortOrder + "] CPID=" + Binder.getCallingPid() + " User=" + UserUtils.getCurrentUserHandle(getContext())); } + + mContactsHelper.validateProjection(projection); + mContactsHelper.validateSql(selection); + mContactsHelper.validateSql(sortOrder); + waitForAccess(mReadAccessLatch); if (!isDirectoryParamValid(uri)) { @@ -10138,4 +10155,9 @@ public class ContactsProvider2 extends AbstractContactsProvider } } } + + @VisibleForTesting + public ContactsDatabaseHelper getContactsDatabaseHelperForTest() { + return mContactsHelper; + } } diff --git a/src/com/android/providers/contacts/sqlite/DatabaseAnalyzer.java b/src/com/android/providers/contacts/sqlite/DatabaseAnalyzer.java new file mode 100644 index 00000000..63502214 --- /dev/null +++ b/src/com/android/providers/contacts/sqlite/DatabaseAnalyzer.java @@ -0,0 +1,99 @@ +/* + * Copyright (C) 2016 The Android Open Source Project + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License + */ + +package com.android.providers.contacts.sqlite; + +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; +import android.util.Log; + +import com.android.providers.contacts.AbstractContactsProvider; + +import java.util.ArrayList; +import java.util.List; + +/** + * Class to extract table/view/column names from databases. + */ +public class DatabaseAnalyzer { + private static final String TAG = "DatabaseAnalyzer"; + + private static final boolean VERBOSE_LOGGING = AbstractContactsProvider.VERBOSE_LOGGING; + + private DatabaseAnalyzer() { + } + + /** + * Find and return all table/view names in a db. + */ + private static List<String> findTablesAndViews(SQLiteDatabase db) { + final List<String> ret = new ArrayList<>(); + try (final Cursor c = db.rawQuery( + "SELECT name FROM sqlite_master WHERE type in (\"table\", \"view\")", null)) { + while (c.moveToNext()) { + ret.add(c.getString(0)); + } + } + return ret; + } + + /** + * Find all columns in a table/view. + */ + private static List<String> findColumns(SQLiteDatabase db, String table) { + final List<String> ret = new ArrayList<>(); + + // Open the table/view but requests 0 rows. + final Cursor c = db.rawQuery("SELECT * FROM " + table + " WHERE 0 LIMIT 0", null); + try { + // Collect the column names. + for (int i = 0; i < c.getColumnCount(); i++) { + ret.add(c.getColumnName(i)); + } + } finally { + c.close(); + } + return ret; + } + + /** + * Return all table/view names that clients shouldn't use in their queries -- basically the + * result contains all table/view names, except for the names that are column names of any + * tables. + */ + public static List<String> findTableViewsAllowingColumns(SQLiteDatabase db) { + final List<String> tables = findTablesAndViews(db); + if (VERBOSE_LOGGING) { + Log.d(TAG, "Tables and views:"); + } + final List<String> ret = new ArrayList<>(tables); // Start with the table/view list. + for (String name : tables) { + if (VERBOSE_LOGGING) { + Log.d(TAG, " " + name); + } + final List<String> columns = findColumns(db, name); + if (VERBOSE_LOGGING) { + Log.d(TAG, " Columns: " + columns); + } + for (String c : columns) { + if (ret.remove(c)) { + Log.d(TAG, "Removing [" + c + "] from disallow list"); + } + } + } + return ret; + } +} diff --git a/src/com/android/providers/contacts/sqlite/SqlChecker.java b/src/com/android/providers/contacts/sqlite/SqlChecker.java new file mode 100644 index 00000000..6404220f --- /dev/null +++ b/src/com/android/providers/contacts/sqlite/SqlChecker.java @@ -0,0 +1,256 @@ +/* + * Copyright (C) 2016 The Android Open Source Project + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License + */ +package com.android.providers.contacts.sqlite; + +import android.annotation.Nullable; +import android.util.ArraySet; +import android.util.Log; + +import com.android.providers.contacts.AbstractContactsProvider; + +import com.google.common.annotations.VisibleForTesting; + +import java.util.List; +import java.util.concurrent.atomic.AtomicBoolean; +import java.util.function.Consumer; + +/** + * Simple SQL validator to detect uses of hidden tables / columns as well as invalid SQLs. + */ +public class SqlChecker { + private static final String TAG = "SqlChecker"; + + private static final boolean VERBOSE_LOGGING = AbstractContactsProvider.VERBOSE_LOGGING; + + private final ArraySet<String> mInvalidTokens; + + /** + * Create a new instance with given invalid tokens. + */ + public SqlChecker(List<String> invalidTokens) { + mInvalidTokens = new ArraySet<>(invalidTokens.size()); + + for (int i = invalidTokens.size() - 1; i >= 0; i--) { + mInvalidTokens.add(invalidTokens.get(i).toLowerCase()); + } + if (VERBOSE_LOGGING) { + Log.d(TAG, "Intialized with invalid tokens: " + invalidTokens); + } + } + + private static boolean isAlpha(char ch) { + return ('a' <= ch && ch <= 'z') || ('A' <= ch && ch <= 'Z') || (ch == '_'); + } + + private static boolean isNum(char ch) { + return ('0' <= ch && ch <= '9'); + } + + private static boolean isAlNum(char ch) { + return isAlpha(ch) || isNum(ch); + } + + private static boolean isAnyOf(char ch, String set) { + return set.indexOf(ch) >= 0; + } + + /** + * Exception for invalid queries. + */ + @VisibleForTesting + public static final class InvalidSqlException extends IllegalArgumentException { + public InvalidSqlException(String s) { + super(s); + } + } + + private static InvalidSqlException genException(String message, String sql) { + throw new InvalidSqlException(message + " in '" + sql + "'"); + } + + private void throwIfContainsToken(String token, String sql) { + if (mInvalidTokens.contains(token.toLowerCase())) { + throw genException("Detected disallowed token: " + token, sql); + } + } + + /** + * Ensure {@code sql} is valid and doesn't contain invalid tokens. + */ + public void ensureNoInvalidTokens(@Nullable String sql) { + findTokens(sql, OPTION_NONE, token -> throwIfContainsToken(token, sql)); + } + + /** + * Ensure {@code sql} only contains a single, valid token. Use to validate column names + * in {@link android.content.ContentValues}. + */ + public void ensureSingleTokenOnly(@Nullable String sql) { + final AtomicBoolean tokenFound = new AtomicBoolean(); + + findTokens(sql, OPTION_TOKEN_ONLY, token -> { + if (tokenFound.get()) { + throw genException("Multiple tokens detected", sql); + } + tokenFound.set(true); + throwIfContainsToken(token, sql); + }); + if (!tokenFound.get()) { + throw genException("Token not found", sql); + } + } + + @VisibleForTesting + static final int OPTION_NONE = 0; + + @VisibleForTesting + static final int OPTION_TOKEN_ONLY = 1 << 0; + + private static char peek(String s, int index) { + return index < s.length() ? s.charAt(index) : '\0'; + } + + /** + * SQL Tokenizer specialized to extract tokens from SQL (snippets). + * + * Based on sqlite3GetToken() in tokenzie.c in SQLite. + * + * Source for v3.8.6 (which android uses): http://www.sqlite.org/src/artifact/ae45399d6252b4d7 + * (Latest source as of now: http://www.sqlite.org/src/artifact/78c8085bc7af1922) + * + * Also draft spec: http://www.sqlite.org/draft/tokenreq.html + */ + @VisibleForTesting + static void findTokens(@Nullable String sql, int options, Consumer<String> checker) { + if (sql == null) { + return; + } + int pos = 0; + final int len = sql.length(); + while (pos < len) { + final char ch = peek(sql, pos); + + // Regular token. + if (isAlpha(ch)) { + final int start = pos; + pos++; + while (isAlNum(peek(sql, pos))) { + pos++; + } + final int end = pos; + + final String token = sql.substring(start, end); + checker.accept(token); + + continue; + } + + // Handle quoted tokens + if (isAnyOf(ch, "'\"`")) { + final int quoteStart = pos; + pos++; + + for (;;) { + pos = sql.indexOf(ch, pos); + if (pos < 0) { + throw genException("Unterminated quote", sql); + } + if (peek(sql, pos + 1) != ch) { + break; + } + // Quoted quote char -- e.g. "abc""def" is a single string. + pos += 2; + } + final int quoteEnd = pos; + pos++; + + if (ch != '\'') { + // Extract the token + final String tokenUnquoted = sql.substring(quoteStart + 1, quoteEnd); + + final String token; + + // Unquote if needed. i.e. "aa""bb" -> aa"bb + if (tokenUnquoted.indexOf(ch) >= 0) { + token = tokenUnquoted.replaceAll( + String.valueOf(ch) + ch, String.valueOf(ch)); + } else { + token = tokenUnquoted; + } + checker.accept(token); + } else { + if ((options &= OPTION_TOKEN_ONLY) != 0) { + throw genException("Non-token detected", sql); + } + } + continue; + } + // Handle tokens enclosed in [...] + if (ch == '[') { + final int quoteStart = pos; + pos++; + + pos = sql.indexOf(']', pos); + if (pos < 0) { + throw genException("Unterminated quote", sql); + } + final int quoteEnd = pos; + pos++; + + final String token = sql.substring(quoteStart + 1, quoteEnd); + + checker.accept(token); + continue; + } + if ((options &= OPTION_TOKEN_ONLY) != 0) { + throw genException("Non-token detected", sql); + } + + // Detect comments. + if (ch == '-' && peek(sql, pos + 1) == '-') { + pos += 2; + pos = sql.indexOf('\n', pos); + if (pos < 0) { + // We disallow strings ending in an inline comment. + throw genException("Unterminated comment", sql); + } + pos++; + + continue; + } + if (ch == '/' && peek(sql, pos + 1) == '*') { + pos += 2; + pos = sql.indexOf("*/", pos); + if (pos < 0) { + throw genException("Unterminated comment", sql); + } + pos += 2; + + continue; + } + + // Semicolon is never allowed. + if (ch == ';') { + throw genException("Semicolon is not allowed", sql); + } + + // For this purpose, we can simply ignore other characters. + // (Note it doesn't handle the X'' literal properly and reports this X as a token, + // but that should be fine...) + pos++; + } + } +} diff --git a/tests/Android.mk b/tests/Android.mk index 35a6b395..d2f99861 100644 --- a/tests/Android.mk +++ b/tests/Android.mk @@ -4,7 +4,9 @@ include $(CLEAR_VARS) # We only want this apk build for tests. LOCAL_MODULE_TAGS := tests -LOCAL_STATIC_JAVA_LIBRARIES := mockito-target +LOCAL_STATIC_JAVA_LIBRARIES := \ + android-support-test \ + mockito-target-minus-junit4 LOCAL_JAVA_LIBRARIES := android.test.runner diff --git a/tests/src/com/android/providers/contacts/SqlInjectionDetectionTest.java b/tests/src/com/android/providers/contacts/SqlInjectionDetectionTest.java index e7b80a04..c7eb64cc 100644 --- a/tests/src/com/android/providers/contacts/SqlInjectionDetectionTest.java +++ b/tests/src/com/android/providers/contacts/SqlInjectionDetectionTest.java @@ -17,6 +17,7 @@ package com.android.providers.contacts; import static com.android.providers.contacts.EvenMoreAsserts.assertThrows; +import static com.android.providers.contacts.TestUtils.cv; import android.database.Cursor; import android.database.sqlite.SQLiteException; @@ -25,6 +26,7 @@ import android.net.Uri.Builder; import android.provider.ContactsContract; import android.provider.ContactsContract.CommonDataKinds.Phone; import android.provider.ContactsContract.Contacts; +import android.provider.ContactsContract.Data; import android.test.suitebuilder.annotation.MediumTest; import com.android.providers.contacts.testutil.RawContactUtil; @@ -42,36 +44,48 @@ import com.android.providers.contacts.testutil.RawContactUtil; public class SqlInjectionDetectionTest extends BaseContactsProvider2Test { private static final String[] PHONE_ID_PROJECTION = new String[] { Phone._ID }; - public void testPhoneQueryValid() { - long rawContactId = RawContactUtil.createRawContactWithName(mResolver, "Hot", "Tamale"); - insertPhoneNumber(rawContactId, "555-123-4567"); + @Override + protected void setUp() throws Exception { + super.setUp(); + + getContactsProvider().getContactsDatabaseHelperForTest().setSqlCheckEnabled(true); + } + public void testPhoneQueryValid() { assertQueryValid(Phone.CONTENT_URI, PHONE_ID_PROJECTION, Phone.NUMBER + "='555-123-4567'", null); } public void testPhoneQueryBadProjection() { - long rawContactId = RawContactUtil.createRawContactWithName(mResolver, "Hot", "Tamale"); - insertPhoneNumber(rawContactId, "555-123-4567"); - - assertQueryThrows(IllegalArgumentException.class, Phone.CONTENT_URI, + assertQueryThrows(Phone.CONTENT_URI, new String[] { "0 UNION SELECT _id FROM view_data--" }, null, null); + + // Invalid column names should be detected too. + assertQueryThrows(Phone.CONTENT_URI, new String[] { "a" }, null, null); + assertQueryThrows(Phone.CONTENT_URI, new String[] { " _id" }, null, null); + + // This is still invalid because we only allow exact column names in projections. + assertQueryThrows(Phone.CONTENT_URI, new String[] { "[_id]" }, null, null); } public void testPhoneQueryBadSelection() { - long rawContactId = RawContactUtil.createRawContactWithName(mResolver, "Hot", "Tamale"); - insertPhoneNumber(rawContactId, "555-123-4567"); - - assertQueryThrows(SQLiteException.class, Phone.CONTENT_URI, PHONE_ID_PROJECTION, + assertQueryThrows(Phone.CONTENT_URI, PHONE_ID_PROJECTION, "0=1) UNION SELECT _id FROM view_data--", null); + assertQueryThrows(Phone.CONTENT_URI, PHONE_ID_PROJECTION, ";delete from contacts", null); + assertQueryThrows(Phone.CONTENT_URI, PHONE_ID_PROJECTION, + "_id in default_directory", null); + assertQueryThrows(Phone.CONTENT_URI, PHONE_ID_PROJECTION, + "_id in (select _id from default_directory)", null); } public void testPhoneQueryBadSortOrder() { - long rawContactId = RawContactUtil.createRawContactWithName(mResolver, "Hot", "Tamale"); - insertPhoneNumber(rawContactId, "555-123-4567"); - - assertQueryThrows(SQLiteException.class, Phone.CONTENT_URI, + assertQueryThrows(Phone.CONTENT_URI, PHONE_ID_PROJECTION, null, "_id UNION SELECT _id FROM view_data--"); + assertQueryThrows(Phone.CONTENT_URI, PHONE_ID_PROJECTION, null, ";delete from contacts"); + assertQueryThrows(Phone.CONTENT_URI, PHONE_ID_PROJECTION, null, + "_id in default_directory"); + assertQueryThrows(Phone.CONTENT_URI, PHONE_ID_PROJECTION, + null, "exists (select _id from default_directory)"); } public void testPhoneQueryBadLimit() { @@ -100,14 +114,39 @@ public class SqlInjectionDetectionTest extends BaseContactsProvider2Test { c.close(); } - private <T extends Exception> void assertQueryThrows(Class<T> exception, final Uri uri, + private <T extends Exception> void assertQueryThrows(final Uri uri, final String[] projection, final String selection, final String sortOrder) { - assertThrows(exception, new Runnable() { - @Override - public void run() { + assertThrows(IllegalArgumentException.class, () -> { final Cursor c = mResolver.query(uri, projection, selection, null, sortOrder); c.close(); - } + }); + } + + public void testBadDelete() { + assertThrows(IllegalArgumentException.class, () -> { + mResolver.delete(Contacts.CONTENT_URI, ";delete from contacts;--", null); + }); + assertThrows(IllegalArgumentException.class, () -> { + mResolver.delete(Contacts.CONTENT_URI, "_id in default_directory", null); + }); + } + + public void testBadUpdate() { + assertThrows(IllegalArgumentException.class, () -> { + mResolver.update(Data.CONTENT_URI, cv(), ";delete from contacts;--", null); + }); + assertThrows(IllegalArgumentException.class, () -> { + mResolver.update(Data.CONTENT_URI, cv(), "_id in default_directory", null); + }); + assertThrows(IllegalArgumentException.class, () -> { + mResolver.update(Data.CONTENT_URI, cv("_id/**/", 1), null, null); + }); + mResolver.update(Data.CONTENT_URI, cv("[data1]", 1), null, null); // this is actually fine + } + + public void testBadInsert() { + assertThrows(IllegalArgumentException.class, () -> { + mResolver.insert(Data.CONTENT_URI, cv("_id/**/", 1)); }); } } diff --git a/tests/src/com/android/providers/contacts/sqlite/DatabaseAnalyzerTest.java b/tests/src/com/android/providers/contacts/sqlite/DatabaseAnalyzerTest.java new file mode 100644 index 00000000..0ef020b7 --- /dev/null +++ b/tests/src/com/android/providers/contacts/sqlite/DatabaseAnalyzerTest.java @@ -0,0 +1,46 @@ +/* + * Copyright (C) 2016 The Android Open Source Project + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License + */ + +package com.android.providers.contacts.sqlite; + +import android.test.AndroidTestCase; + +import com.android.providers.contacts.ContactsDatabaseHelper; + +import java.util.List; + +public class DatabaseAnalyzerTest extends AndroidTestCase { + public void testFindTableViewsAllowingColumns() { + final ContactsDatabaseHelper dbh = + ContactsDatabaseHelper.getNewInstanceForTest(getContext()); + try { + final List<String> list = DatabaseAnalyzer.findTableViewsAllowingColumns( + dbh.getReadableDatabase()); + + assertTrue(list.contains("contacts")); + assertTrue(list.contains("raw_contacts")); + assertTrue(list.contains("view_contacts")); + assertTrue(list.contains("view_raw_contacts")); + assertTrue(list.contains("view_data")); + + assertFalse(list.contains("data")); + assertFalse(list.contains("_id")); + + } finally { + dbh.close(); + } + } +}
\ No newline at end of file diff --git a/tests/src/com/android/providers/contacts/sqlite/SqlCheckerTest.java b/tests/src/com/android/providers/contacts/sqlite/SqlCheckerTest.java new file mode 100644 index 00000000..a0a8622c --- /dev/null +++ b/tests/src/com/android/providers/contacts/sqlite/SqlCheckerTest.java @@ -0,0 +1,247 @@ +/* + * Copyright (C) 2016 The Android Open Source Project + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License + */ +package com.android.providers.contacts.sqlite; + +import android.test.AndroidTestCase; +import android.test.MoreAsserts; + +import com.android.providers.contacts.sqlite.SqlChecker.InvalidSqlException; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.List; + +public class SqlCheckerTest extends AndroidTestCase { + private ArrayList<String> getTokens(String sql) { + final ArrayList<String> tokens = new ArrayList<>(); + + SqlChecker.findTokens(sql, SqlChecker.OPTION_NONE, token -> tokens.add(token)); + + return tokens; + } + + private void checkTokens(String sql, String spaceSeparatedExpectedTokens) { + final List<String> expected = spaceSeparatedExpectedTokens == null + ? new ArrayList<>() + : Arrays.asList(spaceSeparatedExpectedTokens.split(" +")); + + assertEquals(expected, getTokens(sql)); + } + + private void assertInvalidSql(String sql, String message) { + try { + getTokens(sql); + fail("Didn't throw InvalidSqlException"); + } catch (InvalidSqlException e) { + MoreAsserts.assertContainsRegex(message, e.getMessage()); + } + } + + public void testWhitespaces() { + checkTokens(" select \t\r\n a\n\n ", "select a"); + checkTokens("a b", "a b"); + } + + public void testComment() { + checkTokens("--\n", null); + checkTokens("a--\n", "a"); + checkTokens("a--abcdef\n", "a"); + checkTokens("a--abcdef\nx", "a x"); + checkTokens("a--\nx", "a x"); + assertInvalidSql("a--abcdef", "Unterminated comment"); + assertInvalidSql("a--abcdef\ndef--", "Unterminated comment"); + + checkTokens("/**/", null); + assertInvalidSql("/*", "Unterminated comment"); + assertInvalidSql("/*/", "Unterminated comment"); + assertInvalidSql("/*\n* /*a", "Unterminated comment"); + checkTokens("a/**/", "a"); + checkTokens("/**/b", "b"); + checkTokens("a/**/b", "a b"); + checkTokens("a/* -- \n* /* **/b", "a b"); + } + + public void testStrings() { + assertInvalidSql("'", "Unterminated quote"); + assertInvalidSql("a'", "Unterminated quote"); + assertInvalidSql("a'''", "Unterminated quote"); + assertInvalidSql("a''' ", "Unterminated quote"); + checkTokens("''", null); + checkTokens("''''", null); + checkTokens("a''''b", "a b"); + checkTokens("a' '' 'b", "a b"); + checkTokens("'abc'", null); + checkTokens("'abc\ndef'", null); + checkTokens("a'abc\ndef'", "a"); + checkTokens("'abc\ndef'b", "b"); + checkTokens("a'abc\ndef'b", "a b"); + checkTokens("a'''abc\nd''ef'''b", "a b"); + } + + public void testDoubleQuotes() { + assertInvalidSql("\"", "Unterminated quote"); + assertInvalidSql("a\"", "Unterminated quote"); + assertInvalidSql("a\"\"\"", "Unterminated quote"); + assertInvalidSql("a\"\"\" ", "Unterminated quote"); + checkTokens("\"\"", ""); + checkTokens("\"\"\"\"", "\""); + checkTokens("a\"\"\"\"b", "a \" b"); + checkTokens("a\"\t\"\"\t\"b", "a \t\"\t b"); + checkTokens("\"abc\"", "abc"); + checkTokens("\"abc\ndef\"", "abc\ndef"); + checkTokens("a\"abc\ndef\"", "a abc\ndef"); + checkTokens("\"abc\ndef\"b", "abc\ndef b"); + checkTokens("a\"abc\ndef\"b", "a abc\ndef b"); + checkTokens("a\"\"\"abc\nd\"\"ef\"\"\"b", "a \"abc\nd\"ef\" b"); + } + + public void testBackQuotes() { + assertInvalidSql("`", "Unterminated quote"); + assertInvalidSql("a`", "Unterminated quote"); + assertInvalidSql("a```", "Unterminated quote"); + assertInvalidSql("a``` ", "Unterminated quote"); + checkTokens("``", ""); + checkTokens("````", "`"); + checkTokens("a````b", "a ` b"); + checkTokens("a`\t``\t`b", "a \t`\t b"); + checkTokens("`abc`", "abc"); + checkTokens("`abc\ndef`", "abc\ndef"); + checkTokens("a`abc\ndef`", "a abc\ndef"); + checkTokens("`abc\ndef`b", "abc\ndef b"); + checkTokens("a`abc\ndef`b", "a abc\ndef b"); + checkTokens("a```abc\nd``ef```b", "a `abc\nd`ef` b"); + } + + public void testBrackets() { + assertInvalidSql("[", "Unterminated quote"); + assertInvalidSql("a[", "Unterminated quote"); + assertInvalidSql("a[ ", "Unterminated quote"); + assertInvalidSql("a[[ ", "Unterminated quote"); + checkTokens("[]", ""); + checkTokens("[[]", "["); + checkTokens("a[[]b", "a [ b"); + checkTokens("a[\t[\t]b", "a \t[\t b"); + checkTokens("[abc]", "abc"); + checkTokens("[abc\ndef]", "abc\ndef"); + checkTokens("a[abc\ndef]", "a abc\ndef"); + checkTokens("[abc\ndef]b", "abc\ndef b"); + checkTokens("a[abc\ndef]b", "a abc\ndef b"); + checkTokens("a[[abc\nd[ef[]b", "a [abc\nd[ef[ b"); + } + + public void testSemicolons() { + assertInvalidSql(";", "Semicolon is not allowed"); + assertInvalidSql(" ;", "Semicolon is not allowed"); + assertInvalidSql("; ", "Semicolon is not allowed"); + assertInvalidSql("-;-", "Semicolon is not allowed"); + checkTokens("--;\n", null); + checkTokens("/*;*/", null); + checkTokens("';'", null); + checkTokens("[;]", ";"); + checkTokens("`;`", ";"); + } + + public void testTokens() { + checkTokens("a,abc,a00b,_1,_123,abcdef", "a abc a00b _1 _123 abcdef"); + checkTokens("a--\nabc/**/a00b''_1'''ABC'''`_123`abc[d]\"e\"f", + "a abc a00b _1 _123 abc d e f"); + } + + private SqlChecker getChecker(String... tokens) { + return new SqlChecker(Arrays.asList(tokens)); + } + + private void checkEnsureNoInvalidTokens(boolean ok, String sql, String... tokens) { + if (ok) { + getChecker(tokens).ensureNoInvalidTokens(sql); + } else { + try { + getChecker(tokens).ensureNoInvalidTokens(sql); + fail("Should have thrown"); + } catch (InvalidSqlException e) { + // okay + } + } + } + + public void testEnsureNoInvalidTokens() { + checkEnsureNoInvalidTokens(true, "a b c", "Select"); + + checkEnsureNoInvalidTokens(false, "a b ;c", "Select"); + checkEnsureNoInvalidTokens(false, "a b seLeCt", "Select"); + + checkEnsureNoInvalidTokens(true, "a b select", "x"); + + checkEnsureNoInvalidTokens(false, "A b select", "x", "a"); + checkEnsureNoInvalidTokens(false, "A b select", "a", "x"); + + checkEnsureNoInvalidTokens(true, "a /*select*/ b c ", "select"); + checkEnsureNoInvalidTokens(true, "a 'select' b c ", "select"); + + checkEnsureNoInvalidTokens(true, "a b ';' c"); + checkEnsureNoInvalidTokens(true, "a b /*;*/ c"); + } + + private void checkEnsureSingleTokenOnly(boolean ok, String sql, String... tokens) { + if (ok) { + getChecker(tokens).ensureSingleTokenOnly(sql); + } else { + try { + getChecker(tokens).ensureSingleTokenOnly(sql); + fail("Should have thrown"); + } catch (InvalidSqlException e) { + // okay + } + } + } + + public void testEnsureSingleTokenOnly() { + checkEnsureSingleTokenOnly(true, "a", "select"); + checkEnsureSingleTokenOnly(true, "ab", "select"); + checkEnsureSingleTokenOnly(true, "selec", "select"); + checkEnsureSingleTokenOnly(true, "selectx", "select"); + + checkEnsureSingleTokenOnly(false, "select", "select"); + checkEnsureSingleTokenOnly(false, "select", "a", "select"); + checkEnsureSingleTokenOnly(false, "select", "select", "b"); + checkEnsureSingleTokenOnly(false, "select", "a", "select", "b"); + + + checkEnsureSingleTokenOnly(true, "`a`", "select"); + checkEnsureSingleTokenOnly(true, "[a]", "select"); + checkEnsureSingleTokenOnly(true, "\"a\"", "select"); + + checkEnsureSingleTokenOnly(false, "'a'", "select"); + + checkEnsureSingleTokenOnly(false, "b`a`", "select"); + checkEnsureSingleTokenOnly(false, "b[a]", "select"); + checkEnsureSingleTokenOnly(false, "b\"a\"", "select"); + checkEnsureSingleTokenOnly(false, "b'a'", "select"); + + checkEnsureSingleTokenOnly(false, "`a`c", "select"); + checkEnsureSingleTokenOnly(false, "[a]c", "select"); + checkEnsureSingleTokenOnly(false, "\"a\"c", "select"); + checkEnsureSingleTokenOnly(false, "'a'c", "select"); + + checkEnsureSingleTokenOnly(false, "", "select"); + checkEnsureSingleTokenOnly(false, "--", "select"); + checkEnsureSingleTokenOnly(false, "/**/", "select"); + checkEnsureSingleTokenOnly(false, " \n", "select"); + checkEnsureSingleTokenOnly(false, "a--", "select"); + checkEnsureSingleTokenOnly(false, "a/**/", "select"); + checkEnsureSingleTokenOnly(false, "a \n", "select"); + } +} |