(#) Using STRING instead of TEXT !!! WARNING: Using STRING instead of TEXT This is a warning. Id : `SQLiteString` Summary : Using STRING instead of TEXT Severity : Warning Category : Correctness Platform : Any Vendor : Android Open Source Project Feedback : https://issuetracker.google.com/issues/new?component=192708 Since : 1.3.0 (July 2015) Affects : Kotlin and Java files Editing : This check runs on the fly in the IDE editor See : https://www.sqlite.org/datatype3.html Implementation : [Source Code](https://cs.android.com/android-studio/platform/tools/base/+/mirror-goog-studio-main:lint/libs/lint-checks/src/main/java/com/android/tools/lint/checks/SQLiteDetector.java) Tests : [Source Code](https://cs.android.com/android-studio/platform/tools/base/+/mirror-goog-studio-main:lint/libs/lint-tests/src/test/java/com/android/tools/lint/checks/SQLiteDetectorTest.java) In SQLite, any column can store any data type; the declared type for a column is more of a hint as to what the data should be cast to when stored. There are many ways to store a string. `TEXT`, `VARCHAR`, `CHARACTER` and `CLOB` are string types, **but `STRING` is not**. Columns defined as STRING are actually numeric. If you try to store a value in a numeric column, SQLite will try to cast it to a float or an integer before storing. If it can't, it will just store it as a string. This can lead to some subtle bugs. For example, when SQLite encounters a string like `1234567e1234`, it will parse it as a float, but the result will be out of range for floating point numbers, so `Inf` will be stored! Similarly, strings that look like integers will lose leading zeroes. To fix this, you can change your schema to use a `TEXT` type instead. (##) Example Here is an example of lint warnings produced by this check: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~text src/test/pkg/SQLiteTest.java:25:Warning: Using column type STRING; did you mean to use TEXT? (STRING is a numeric type and its value can be adjusted; for example, strings that look like integers can drop leading zeroes. See issue explanation for details.) [SQLiteString] db.execSQL("CREATE TABLE " + name + "(" + Tables.AppKeys.SCHEMA + ");"); // ERROR ----------------------------------------------------------------------- src/test/pkg/SQLiteTest.java:30:Warning: Using column type STRING; did you mean to use TEXT? (STRING is a numeric type and its value can be adjusted; for example, strings that look like integers can drop leading zeroes. See issue explanation for details.) [SQLiteString] db.execSQL(TracksColumns.CREATE_TABLE); // ERROR -------------------------------------- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Here are the relevant source files: `src/test/pkg/TracksColumns.java`: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~java linenumbers package test.pkg; import android.provider.BaseColumns; public interface TracksColumns extends BaseColumns { String TABLE_NAME = "tracks"; String NAME = "name"; String CATEGORY = "category"; String STARTTIME = "starttime"; String MAXGRADE = "maxgrade"; String MAPID = "mapid"; String TABLEID = "tableid"; String ICON = "icon"; String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + NAME + " STRING, " + CATEGORY + " STRING, " + STARTTIME + " INTEGER, " + MAXGRADE + " FLOAT, " + MAPID + " STRING, " + TABLEID + " STRING, " + ICON + " STRING" + ");"; } ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ `src/test/pkg/SQLiteTest.java`: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~java linenumbers package test.pkg; import android.database.sqlite.SQLiteDatabase; @SuppressWarnings({"unused", "SpellCheckingInspection"}) public class SQLiteTest { public interface Tables { interface AppKeys { String NAME = "appkeys"; interface Columns { String _ID = "_id"; String PKG_NAME = "packageName"; String PKG_SIG = "signatureDigest"; } String SCHEMA = Columns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + Columns.PKG_NAME + " STRING NOT NULL," + Columns.PKG_SIG + " STRING NOT NULL"; } } public void test(SQLiteDatabase db, String name) { db.execSQL("CREATE TABLE " + name + "(" + Tables.AppKeys.SCHEMA + ");"); // ERROR } public void onCreate(SQLiteDatabase db) { db.execSQL(TracksColumns.CREATE_TABLE); // ERROR } private void doCreate(SQLiteDatabase db) { // Not yet handled; we need to flow string concatenation across procedure calls createTable(db, Tables.AppKeys.NAME, Tables.AppKeys.SCHEMA); // ERROR } private void createTable(SQLiteDatabase db, String tableName, String schema) { db.execSQL("CREATE TABLE " + tableName + "(" + schema + ");"); } } ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ You can also visit the [source code](https://cs.android.com/android-studio/platform/tools/base/+/mirror-goog-studio-main:lint/libs/lint-tests/src/test/java/com/android/tools/lint/checks/SQLiteDetectorTest.java) for the unit tests for this check to see additional scenarios. The above example was automatically extracted from the first unit test found for this lint check, `SQLiteDetector.test`. To report a problem with this extracted sample, visit https://issuetracker.google.com/issues/new?component=192708. (##) Suppressing You can suppress false positives using one of the following mechanisms: * Using a suppression annotation like this on the enclosing element: ```kt // Kotlin @Suppress("SQLiteString") fun method() { execSQL(...) } ``` or ```java // Java @SuppressWarnings("SQLiteString") void method() { execSQL(...); } ``` * Using a suppression comment like this on the line above: ```kt //noinspection SQLiteString problematicStatement() ``` * Using a special `lint.xml` file in the source tree which turns off the check in that folder and any sub folder. A simple file might look like this: ```xml <?xml version="1.0" encoding="UTF-8"?> <lint> <issue id="SQLiteString" severity="ignore" /> </lint> ``` Instead of `ignore` you can also change the severity here, for example from `error` to `warning`. You can find additional documentation on how to filter issues by path, regular expression and so on [here](https://googlesamples.github.io/android-custom-lint-rules/usage/lintxml.md.html). * In Gradle projects, using the DSL syntax to configure lint. For example, you can use something like ```gradle lintOptions { disable 'SQLiteString' } ``` In Android projects this should be nested inside an `android { }` block. * For manual invocations of `lint`, using the `--ignore` flag: ``` $ lint --ignore SQLiteString ...` ``` * Last, but not least, using baselines, as discussed [here](https://googlesamples.github.io/android-custom-lint-rules/usage/baselines.md.html).