Support safe FTS5 operators (AND, OR, NOT) in text_search queries

[coreyt]

Problem

text_search() currently sanitizes all user input by quoting every whitespace-delimited token via sanitize_fts5_query() in crates/fathomdb-query/src/compile.rs:82-91:

fn sanitize_fts5_query(raw: &str) -> String {
    let tokens: Vec<String> = raw
        .split_whitespace()
        .map(|token| {
            let escaped = token.replace('"', "\"\"");
            format!("\"{escaped}\"")
        })
        .collect();
    tokens.join(" ")
}

This produces an implicit AND of quoted literals. The sanitization is intentional — it prevents FTS5 syntax injection (operators like AND, OR, NOT, NEAR, column filters col:, parentheses, and * wildcards are all neutralized inside quoted strings).

However, this means queries like "ship OR docs" become "ship" "OR" "docs" — an AND of three literal terms. Since the literal word OR rarely appears in indexed text, the query returns 0 results. Users have no way to express disjunction.

Proposal

Recognize a subset of FTS5 operators as safe and pass them through unquoted, while continuing to quote all other tokens. Candidate safe operators:

Operator	FTS5 syntax	Risk	Notes
AND	implicit (space)	None	Already the default behavior
OR	OR	Low	Broadens results, no injection vector
NOT	NOT	Low	Narrows results; could be surprising if first token
NEAR	NEAR / NEAR/N	Medium	Proximity search; the /N syntax adds parsing complexity
* (prefix)	token*	Medium	Triggers prefix scan; could be expensive on short prefixes
Column filter	col:	High	Exposes internal column names; should remain blocked
Parentheses	( )	Medium	Grouping; adds nesting complexity

A conservative first pass: support OR and NOT only. These are the highest-value operators with the lowest risk. NEAR, prefix wildcards, and parenthetical grouping can be considered later.

Suggested behavior

"ship OR docs"       → "ship" OR "docs"         (disjunction)
"ship NOT blocked"   → "ship" NOT "blocked"      (exclusion)
"ship docs"          → "ship" "docs"             (AND, unchanged)
"ship OR"            → "ship" "OR"               (trailing operator treated as literal)
"NOT ship"           → NOT "ship"                (leading NOT is valid FTS5)

Operators should be case-sensitive (OR recognized, or quoted as a literal) to match FTS5 convention and reduce false positives in natural language.

Context

Discovered while integrating memex with fathomdb's property FTS. Memex needs OR semantics for search queries but cannot do client-side term fanout as a workaround.

🤖 Generated with Claude Code

[coreyt]

I think the right framing here is trust layering, not a single sanitizer trying to solve every problem.

For fathomdb, there are really three concerns that the current preprocessing step is collapsing together:

1. Protect the database engine from dangerous syntax or pathological queries.
2. Protect application developers and agents from accidentally turning arbitrary text into control language.
3. Preserve useful search semantics that users and agents actually need.

That suggests a cleaner model: fathomdb should expose a constrained search language, not “raw FTS5 except sanitized awkwardly”.

In practice, that means:

• define a small supported operator set
• parse that language explicitly
• compile it to FTS5 safely
• reject or downgrade everything outside the supported subset

This is better than “quote everything except maybe some tokens”, because it gives us an actual contract.

A reasonable first contract for text_search() would be:

• plain text terms
• quoted phrases
• OR
• NOT
• implicit AND via whitespace
• optionally unary -term later, if we want web-style syntax

And explicitly not support yet:

• column filters like col:foo
• parentheses
• NEAR
• prefix *
• raw FTS5 passthrough

Why this fits the likely threat model here:

The main risk is probably not a human typing malicious OR into a prompt. The bigger risks are:

• copied text from web pages
• agent ingestion of hostile content
• model mistakes that forward text as executable query structure

So the right defense is not “remove all operators”. The right defense is:

• only treat syntax as syntax when it matches a tiny, intentional grammar
• otherwise treat it as literal text

Examples:

• ship OR docs -> operator recognized
• title:secret -> literal, since : syntax is unsupported
• (a OR b) -> literal/rejected, since grouping is unsupported
• foo* -> literal, since prefix syntax is unsupported

That sharply limits the attack surface from hostile source text while still preserving standard boolean search.

I think the implementation should be a two-stage pipeline:

1. Parse user/agent input into a tiny AST.

   • recognize only supported operators
   • require exact forms, e.g. uppercase OR / NOT
   • treat malformed syntax as literal text, not partial control language

2. Compile that AST into FTS5.

   • quote every literal term/phrase
   • emit only the operators we support
   • never pass through unknown tokens verbatim

So yes, there is still a preprocessor, but it becomes a real allowlist parser rather than a string sanitizer with special cases.

A few balancing rules seem useful:

1. Default to literals.
2. Operators must be unambiguous.
3. Unsupported syntax never passes through.
4. Expensive constructs should be gated separately from security concerns.
5. The SDK can help agents do the right thing, but fathomdb should still enforce the contract itself.

Given that, my recommendation would be:

• text_search(query) = safe structured subset, default API
• maybe later: text_search_raw_fts5(query) = explicit expert mode

And for the initial supported subset, I’d start with:

• phrase search
• OR
• NOT
• implicit AND

Then defer grouping, prefix, and NEAR, and continue to block column filters / raw passthrough.

Rule of thumb: if a feature is common in user-facing search boxes and low-risk, support it in the safe subset. If it exposes schema, parser complexity, or cost hazards, keep it out.