Search silently returns nothing when the query contains both an English "and"/"or"/"not"/"near" and an apostrophe

Summary

escapeQuery() treats the ordinary English words and, or, not and near as FTS5 operators, case-insensitively, and responds by handing the user's raw string to FTS5 unquoted. If that string also contains an apostrophe — which any possessive does — FTS5 raises fts5: syntax error near "'", isFts5SyntaxError() catches it, and the search returns [].

The result is a query that looks completely reasonable and finds nothing, with no error and no signal to the user. Typing a business's own full name into a site search is the case that hits it.

We found this on a directory of 9,676 listings. Searching for Tennessee Walking Horse Breeder's and Exhibitor's Association — the exact stored title of a published entry — returns 0 results. Dropping either the and or the apostrophes finds it immediately.

Affected version: 0.38.0. The code is unchanged on main.

Reproduction

Minimal, no EmDash needed. escapeQuery is transcribed verbatim from packages/core/src/search/query.ts:

import { DatabaseSync } from "node:sqlite";               // node --experimental-sqlite
const db = new DatabaseSync(":memory:");
db.exec(`CREATE VIRTUAL TABLE f USING fts5(title, content, tokenize='porter unicode61')`);
db.prepare(`INSERT INTO f VALUES(?,?)`).run("Tennessee Walking Horse Breeder's and Exhibitor's Association", "");

const FTS_OPERATORS = /\b(AND|OR|NOT|NEAR)\b/i;
function escapeQuery(q) {
  q = (q || "").trim();
  if (!q) return "";
  if (q.startsWith('"') && q.endsWith('"') && q.length >= 2) return `"${q.slice(1, -1).replace(/"/g, '""')}"`;
  const escaped = q.replace(/"/g, '""');
  if (FTS_OPERATORS.test(q)) return escaped;              // <-- raw passthrough
  const terms = escaped.split(/\s+/).filter((t) => t.length > 0);
  if (!terms.length) return "";
  return terms.map((t) => `"${t}"*`).join(" ");
}

for (const q of [
  "Tennessee Walking Horse Breeder's and Exhibitor's Association",
  "Tennessee Walking Horse Breeder's Exhibitor's Association",
  "Breeder's and Exhibitor",
  "O'Brien not here",
  "Breeders and Exhibitors",
]) {
  const m = escapeQuery(q);
  let out;
  try { out = db.prepare(`SELECT count(*) c FROM f WHERE f MATCH ?`).get(m).c; }
  catch (e) { out = "THROWS: " + e.message; }
  console.log(JSON.stringify(q), "->", JSON.stringify(m), out);
}

Only the two ingredients together fail. An apostrophe alone takes the quoted-term path and is fine; an operator word alone parses as barewords and is fine.

Reproduced end to end against a live D1 deployment through search(), and through the site's own search page.

Root cause

packages/core/src/search/query.ts:

const FTS_OPERATORS_PATTERN = /\b(AND|OR|NOT|NEAR)\b/i;
…
const escaped = query.replace(DOUBLE_QUOTE_PATTERN, '""');
if (FTS_OPERATORS_PATTERN.test(query)) return escaped;   // user is "writing FTS5 syntax"
const terms = escaped.split(WHITESPACE_SPLIT_PATTERN).filter((t) => t.length > 0);
return terms.map((t) => `"${t}"*`).join(" ");

Two decisions combine:

1. The operator test is case-insensitive. FTS5's own operators are uppercase-only — and is a bareword to FTS5, AND is the operator. Matching /i means every sentence containing the English word "and" is classified as a syntax query. That is a large fraction of natural search input: "boarding and training", "tack and feed", "Smith and Sons".
2. The escape hatch escapes only double quotes. Once on the raw path, nothing protects ', (, ), *, :, ^ or -, all of which are meaningful or illegal to FTS5 in a bareword.

The failure is silent because isFts5SyntaxError() correctly converts the resulting throw into []. That is the right behaviour for genuinely malformed syntax; it is the wrong outcome for a user who never intended to write syntax.

Impact

It is not limited to apostrophes. Anything that reaches the raw path and is not valid FTS5 fails the same way, for example Smith & Sons (Tack and Feed), or a hyphenated name plus an "and". And when the raw string is valid FTS5, the behaviour is different again but still surprising: prefix matching is silently dropped, so boarding and train no longer matches "training", while boarding train does.

There is no way for a site to opt out. escapeQuery is not configurable and the site search page passes user input straight through.

Possible directions

Offered as starting points.

• Make the operator test case-sensitive, /\b(AND|OR|NOT|NEAR)\b/. This alone fixes the reported case and matches FTS5's own rule: a user typing uppercase AND plausibly means the operator, a user typing "and" does not. It is a one-character change and the least surprising semantics.
• Escape the raw path properly rather than only its double quotes, so a query that opts into syntax still cannot produce a hard error from an apostrophe in a name.
• Fall back instead of returning empty. When isFts5SyntaxError fires, retry once with the fully quoted term construction before giving up. A search that quietly returns nothing is worse than one that ignores the user's operators.
• Either way, a regression test over a title containing both an apostrophe and "and" would pin it.

Happy to send a PR for the case-sensitivity change plus a test if that is the direction you would take.

Environment

• emdash 0.38.0, Cloudflare D1, listings collection with search enabled
• Also reproduced on stock SQLite FTS5 via node:sqlite