Keep Literal Text Separate from Android FTS Syntax

Direct answer

Binding a user string to `MATCH ?` protects the SQL statement structure from being assembled with that string. It does not turn the right-hand value into plain text. SQLite passes the bound string to the full-text engine, where it is parsed as an FTS query expression. A value containing quotes, a prefix marker, a column selector, `NEAR`, or Boolean operators can therefore change what the search means or produce a syntax error, even though it is not SQL injection. Keep those two boundaries separate in design reviews and incident reports: SQL parameter binding addresses SQL syntax; a literal-search policy addresses the FTS mini-language and the user intent. Room version matters. Room 2 exposes `@Fts3`/`@Fts4` in `androidx.room`; Room 3.0.0 added `androidx.room3.Fts5` in `androidx.room3:room3-common`. Compilation is not runtime proof: the Room3 reference names `BundledSQLiteDriver` as supporting FTS5, while `AndroidSQLiteDriver` depends on device SQLite. Verify generated schema and the actual driver/database. Grammar differs: FTS3/4 standard/enhanced set operations, FTS4 leading-token `^`, and FTS5 `NEAR(...)` versus FTS3/4 `NEAR/N`. Choose a narrow default product mode. A literal mode should search user-entered words or an explicitly quoted phrase as content, not as operators, columns, prefixes, or arbitrary query syntax. The correct encoder depends on the exact FTS module and tokenizer. Use a maintained library or a small parser-aware builder whose quoting behavior is tested against the target runtime; do not promise that one regex or SQL-quote replacement safely encodes every FTS grammar. If the application cannot create that boundary confidently, expose only a restricted set of supported characters or a simpler exact-ID lookup, and label advanced FTS syntax as a separate opt-in mode rather than silently interpreting ordinary text as commands. Retain bound parameters for the FTS expression and every other user value. Never concatenate search text into SQL identifiers, column names, table names, or a DAO statement. Identifiers and schema shape should come from fixed application code. Binding remains necessary even if a query is produced by a verified FTS encoder because the two protections solve different problems. Limit input length, token count and result count; debounce repeated typing; handle empty, punctuation-only, malformed and over-complex expressions without crashing. A parameter can be safely bound at the SQL layer and still be an expensive or semantically broader FTS expression, so test resource behavior too. For a literal phrase mode, preserve a versioned fixture corpus and assert the exact document IDs returned by the shipped DAO. Include ordinary words, whitespace, apostrophes, embedded double quotes, parentheses, asterisk, colon, dash, uppercase operator words, combining marks, CJK, emoji, and punctuation-only input. Test both text that resembles syntax and syntax that is intentionally enabled. Assert that a phrase-like literal does not become a column filter or Boolean expansion, and separately verify what tokenizer normalization means for accents, case, and word boundaries. The result can be empty because tokenization produces no terms; do not convert that into a broad fallback query. If the product needs expert syntax such as prefixes, phrases, proximity, or boolean composition, make that a deliberately separate mode with a documented grammar, explicit controls, and a bounded parser contract. SQLite FTS3/4 standard and enhanced query syntax differ, and FTS5 adds its own operators and tokenizer options. A column selector must refer only to fixed indexed fields, not names supplied by the user. Test malformed expressions, nested grouping where supported, negation-only terms, unusual operators, and the maximum allowed query size. Do not conflate advanced search with SQL injection: the concern here is that users may receive results they did not intend, trigger costly parsing, or bypass app-level search scoping unless those controls remain outside the FTS grammar. This guide is about the query boundary, not search quality or a product integration. An FTS match is a retrieval candidate; it does not establish that a generated answer is supported by the source record. A local database and local FTS table also do not by themselves settle backup, deletion, authorization, or privacy. Recheck the current authoritative row and access policy before displaying private history, and delete or rebuild index state through the app-owned data lifecycle. Cove source evidence and the existing AppSearch article do not prove Cove currently uses Room FTS.

A saved-note search treats ordinary punctuation as query syntax

A user types `plan (draft)` while looking for a saved local summary. The Room DAO correctly binds the string to a `MATCH` placeholder, but the selected FTS engine parses its value as a query expression. The search returns an error or a surprising set of records, and the team initially labels it SQL injection.

Decision

Keep SQL binding, FTS parsing, query-mode policy, and authorization separate; make the default literal mode use a tested grammar-aware encoder for the exact shipped module.

Expected outcome

Ordinary search text is treated according to the chosen literal contract, while advanced operators remain explicit and unsupported syntax fails without broadening the result set.

Evidence to collect

The claim becomes reviewable only when the following evidence is attached to the same artifact and test run:

• Room/SQLite driver and FTS module identity
• Literal versus advanced-query fixtures
• Bound parameter and current-row authorization checks

Task quality needs a versioned corpus and acceptance rules. Fluent sample output is not an accuracy result.

Implementation workflow

1. Identify the Room API and runtime driver. Record Room major version, namespace/compiler artifact, generated FTS schema, SQLite driver and device matrix. Room2 androidx.room is FTS3/4; androidx.room3.Fts5 requires Room3.0.0+. Test runtime support on the actual driver/database, not just compilation.
2. Define literal and advanced modes separately. State whether ordinary user input means a term, phrase, or restricted text. Keep prefixes, column selectors, Boolean operators, and proximity syntax behind a separate, intentional advanced mode.
3. Use two layers of safe query construction. Bind the complete MATCH expression as a SQL argument, and use a module-specific tested encoder or parser to prevent literal text from becoming FTS operators. Keep table and column identifiers fixed in application code.
4. Exercise grammar and tokenizer fixtures. Test quotes, punctuation, operator-like words, prefixes, Unicode, combining marks, empty inputs, malformed grammar and excessive length against the actual DAO and target SQLite engine. Assert returned row identities, not only that the call did not throw.
5. Bound results and preserve source authorization. Cap query length and result count, handle errors without broad fallback, and re-read each hit from the current authorized source row before display. Search matches are candidates, not verified model answers.

Keep each transition observable. A failure should identify the stage, artifact, runtime, and recovery action without logging private user content.

Acceptance criteria

• The Room major version, API namespace, compiler artifact, generated FTS schema, SQLite driver and tokenizer are identified; FTS5 runtime support is proven against the actual driver/database.
• Every value remains SQL-bound, while a separate parser-aware policy controls the FTS expression language.
• Literal fixtures prove punctuation and operator-like text cannot silently broaden or redirect the intended search.
• Advanced syntax, result limits, errors, and current-source authorization have explicit outcomes without SQL-injection or ranking claims unsupported by evidence.

Decision table

Failure patterns to prevent

• Treating SQL binding as literal MATCH encoding
• Applying FTS5 escaping to FTS3/4
• Using query terms as database field names

Also prevent silent fallback, unpinned artifacts, missing cancellation, and conclusions that combine unlike configurations. Store unsuccessful runs alongside successful ones.

Minimum reproducibility record

Release checklist

• ☐ The primary query is answered without an unsupported number.
• ☐ Every artifact and runtime is pinned.
• ☐ The representative task and failure policy are explicit.
• ☐ Lifecycle, cancellation, cleanup, and fallback are tested.
• ☐ User-content and network boundaries are documented.
• ☐ Result wording applies only to the recorded configuration.
• ☐ The page links to raw method or evidence when results are added.

Sources and related evidence

• Android privacy and security
• Android Developers — Define data using Room entities
• Android Developers — Room 3 Fts5 API and driver availability
• SQLite — FTS3 and FTS4 Extensions
• SQLite — FTS5 Extension
• SQLite — SQL expressions and bound parameters

Chinese deployment and troubleshooting content is organized in the .奇连 AI 端侧专题.

Frequently asked questions