The Percent Sign in the Search Box
Four ways a search box lies, all of them silent, none of them visible in a screenshot: it searches the page instead of the list, a typed percent sign matches everything, an ungrouped condition quietly drops your filters, and the escaping that was supposed to prevent the second one does nothing at all on one of the two databases.
A search box that returns nothing and a search box that returns everything are the same bug wearing different clothes, and neither one tells you it happened.
The short version
Search is the single most-used control in any list-heavy system and the one most often written twice. Four distinct failures are described below and every one of them produces a plausible-looking result page rather than an error, which is why they survive review, survive QA and survive a demo. Three are logic errors that widen or narrow a query without saying so. The fourth is worse in kind: correct-looking escaping that does nothing at all on one of the two databases involved, and does nothing precisely on the one the tests run against. All four are handled in one shared implementation in this product, and the reason to write them down is that the next person to add a search box will otherwise write them again.
One: the box that searches the page
A long list is paged by the server: twenty rows arrive, then twenty more. A search box is added to the screen, and it filters what has arrived. It looks completely correct. Type three letters and rows disappear, which is what a search box does.
Then somebody searches for a supplier whose name begins with W, on a list where W is on page seven, and the screen says nothing matched. Not "not on this page" — nothing. The person concludes the supplier is not in the system and creates a second one.
This is the one that creates duplicate records
The other three failures in this article produce a wrong list. This one produces a confident, specific, false statement — "that record does not exist" — to somebody who is about to act on it. It is the most damaging of the four for exactly that reason, and it is the least likely to be reported as a bug, because the user does not experience it as a bug. They experience it as a missing supplier.
The mobile app had six lists in this position: a chart of accounts, a general ledger, budgets, five settings lists, and a stock count sheet paging ten lines at a time. Ten lines. A count sheet with two hundred lines on it, and a search box that could see five per cent of them.
The fix cannot be made on the device. If the server does not accept a search term, the device cannot invent one — it can only filter what it was sent. So the correct fix is always at the other end, and a client-side filter on a server-paged list should be read as a defect rather than a feature.
Two: the percent sign that matches everything
A contains-search is built by wrapping the term in wildcards. Underneath, the query says: match anything, then the term, then anything. The wildcard character in SQL is the percent sign, and the single-character wildcard is the underscore.
Which means the user can type them too.
What a typed wildcard does to the pattern
The underscore case is more common than the percent case and much harder to notice, because the result set is nearly right rather than obviously wrong. Reference codes, account codes and part numbers contain underscores routinely, and a search for one of them silently matches its neighbours with a hyphen, a space or a letter in the same position.
The fix is to escape the user's own wildcards before wrapping the term, so a typed percent sign means a percent sign. That much is standard. What is not standard is the next part.
Three: the escaping that does nothing
Escaping in SQL's LIKE has a detail most people never meet: the escape character is not fixed by the language. A statement can declare one. If it does not, the database decides — and the two databases here decide differently.
MySQL assumes a backslash. SQLite has no default escape character at all. So escaping with a backslash and not declaring it works in production and, under SQLite, means nothing: the escape sequence is read as a literal backslash followed by any character, the escaping silently does not apply, and the wildcard the user typed is still a wildcard.
The escaping worked in production and did nothing under test — which is the wrong way round for a bug to hide.
Declaring the escape character in the statement fixes it on both. But then the backslash itself cannot be the character used, for a reason that is almost comic: writing a backslash as a SQL string literal needs it doubled on MySQL, where string literals process backslash escapes, and single on SQLite, where they do not. One spelling cannot be correct on both.
The way out is to stop using the backslash. This product escapes with a hash, which needs no escaping as a literal on either database, so a single spelling is correct everywhere. It is a small decision and it is the difference between escaping that works and escaping that reads as though it does.
Four: the condition that drops your filters
This is the subtlest of the four and the one worth reading twice. A search across several columns is a set of alternatives: match the name, or the code, or the description. Those alternatives have to be grouped together, or they alternate against everything else already narrowing the query.
Ungrouped
- The query reads: this session AND status open, OR name matches, OR code matches.
- Any row whose name matches satisfies the whole condition on its own.
- The status filter is gone. The session scope is gone. Every other filter on the screen is gone.
- On a count sheet, that is the difference between searching one session's lines and returning matching lines from every session in the workspace.
- The screen still shows a filtered-looking list with a search term in the box, so nothing looks wrong.
Grouped
- The query reads: this session AND status open AND (name matches OR code matches).
- The alternatives are contained. Every other filter still applies.
- A search narrows the list rather than replacing the criteria that built it.
- One closure in the code, and it is the whole difference.
- This is what the shared implementation does, and why it is shared rather than copied.
What this is not
It is worth being precise, because a widened query sounds alarming. This does not reach across workspaces. Tenancy in this product is held by a global scope applied to the model itself, not by a filter on the query, so it is not one of the conditions that an ungrouped alternative can step around. The damage is contained to one workspace and consists of ignoring the filters a person can see on their own screen — which is a real bug and a different one to a data leak.
The same mistake has a second form one level deeper, and it is harder to see. Searching a related record — a customer's name from an order list — is done with a subquery rather than a join, so the row count is not multiplied and the pager keeps counting correctly. Inside that subquery the condition must be added with and, not or, because there it sits beside the constraint linking child to parent. An or in that position matches every parent row the moment any child matches, which returns the entire list. Same shape, one nesting level in, and the code looks nearly identical.
What travels out of this
Four failures, one shared implementation, eighteen call sites. The general lesson is not about any of the four specifically. It is that all four produce a page of results, and a page of results is the thing everybody checks and nobody doubts.
Testing a search box in ninety seconds
On any system, ours included. Each of these takes one attempt.
Search for something on page seven
Make them prove it: Find a record you know is deep in the list and search for it from the first page. Nothing found means the box is filtering the page.
Type a single percent sign
Make them prove it: The list should show no matches, or match records literally containing one. A full list means wildcards are unescaped.
Search a code containing an underscore
Make them prove it: Look at whether records with a hyphen or a space in the same position come back too.
Apply a filter, then search
Make them prove it: Set a status or a date range first, then search. Watch whether rows outside the filter reappear.
Search by a related record
Make them prove it: From an order list, search a customer name. A result set the size of the whole list is the nested version of the same bug.
Where search could go from here
The four failures above are handled. What sits beyond them is the difference between a search box that is correct and one that is genuinely good at finding things.
Tolerant matching
Matching a name typed with a different spelling, a transposed pair of letters or a missing accent. Contains-matching is exact about characters and people are not.
Case folding beyond ASCII
Both databases fold ASCII case, so ordinary English search is already case-insensitive. Names carrying accents or non-Latin script are the ones that would benefit from a collation chosen deliberately rather than inherited.
Ranked results
An exact match on a code ahead of a partial match buried in a description. Contains-matching returns a set; ranking turns it into an answer.
We publish scope, not dates.
Scope search behaviourThree questions worth asking about any list
Does search run on the server or in the browser?
A good answer sounds like
On the server, on every paged list.
What ours actually is
On the server, through one shared implementation used at eighteen call sites. Ask it of every list rather than of the product — the answer often differs between two screens in the same system.
What happens if I type a percent sign?
A good answer sounds like
It is matched literally.
What ours actually is
Matched literally, with the escape character declared in the statement so it behaves the same on both databases.
Does searching keep my filters?
A good answer sounds like
Yes, search narrows rather than replaces.
What ours actually is
Yes — the alternatives are grouped, so every other filter on the query still applies.
What AWRA OpsHub does today
- One shared server-side search implementation, used at eighteen call sites, rather than a hand-written condition per controller.
- Wildcards typed by the user escaped before the term is wrapped, so a percent sign and an underscore are matched literally.
- The escape character declared in the statement and chosen so that one spelling is correct on both MySQL and SQLite, rather than left to a driver default that exists on one of them.
- Column alternatives grouped in their own closure, so a search narrows a filtered list instead of replacing the filters that built it.
- Related-record search done as a subquery rather than a join, keeping the paginator count correct, with the inner condition combined so that one matching child does not return every parent.
- Case-insensitive matching for ASCII text on both databases, which covers ordinary English search without a lowering call on either side.
- A blank or whitespace-only term treated as no search at all, so a call site can apply the filter unconditionally.
More we can add to your workspace
- Tolerant matching for near-misses — a transposed pair of letters, a dropped accent, a common misspelling of a supplier name.
- Ranked results, putting an exact match on a code above a partial match inside a description rather than returning an unordered set.
- A collation chosen deliberately for non-ASCII text, so case folding is as reliable for accented and non-Latin names as it already is for English.
- Search across record types from one box, so a reference can be pasted in without first choosing which list it belongs to.
- A result count shown before the page loads, so a search that matched four hundred rows announces itself as too broad rather than presenting its first twenty as an answer.
Where we point you to a specialist
- We would decline to make a search box quietly widen its own criteria to avoid an empty result. Returning approximate matches without saying they are approximate turns a clear answer into an ambiguous one, and the correct response to nothing found is to say so and offer to relax the search.
- Where a list is scoped by permission rather than by filter, we hold that a search must respect the scope even when it makes the box less useful. A search that finds a record the person cannot open is a disclosure, and we treat it as one.
Ranked results and a match count are the two that change the daily experience most for the least work, and neither disturbs the correctness described above. If your lists are long enough that finding is the bottleneck, they are the pair to scope.
Try the ninety-second test on whatever you use today
Four attempts, on any system in your organisation, and you will know something about it that no demo would have shown you. If one of them fails on something you rely on, that is worth a conversation before it invents a duplicate supplier.
Talk through your listsFrequently asked questions
Why not just filter in the browser? It feels faster.
It is faster, and it is correct only when the browser already holds the whole list. On a list the server pages, the browser holds one page, so filtering there can only ever search that page. The failure is not slowness but a false answer: the screen reports that nothing matched when the record is simply on a page that has not been fetched.
Is a typed percent sign a security problem?
No, and it is worth separating the two ideas because they get conflated. Parameter binding stops a search term from changing the structure of a query, and that protection is unaffected by any of this. A wildcard inside a bound parameter is still just a value; it simply happens to be a value that means "match anything" to the LIKE operator. The consequence is a wrong result set rather than an injected statement.
Does the ungrouped-condition bug let one workspace see another workspace data?
No. Separation between workspaces is enforced by a global scope on the model rather than by a filter added to the query, so it is not one of the conditions an ungrouped alternative can step around. What gets widened is the set of filters visible on the screen — a status, a date range, a session — which is a real defect contained within one workspace.
How did the escaping problem survive a test suite?
Because the test suite runs on SQLite and production runs on MySQL, and the bug exists only on SQLite. Escaping with a backslash works on MySQL, which assumes that character, and does nothing on SQLite, which assumes none. So the tests exercised the broken path and passed, while the deployed path was fine. It is a good argument for declaring the escape character explicitly rather than relying on any driver default.
Is search case-sensitive?
Not for ordinary English text. Both databases fold ASCII case for this operator, so searching for a name in lower case finds it in title case. The qualification matters for text outside that range — accented characters and non-Latin scripts depend on the collation in use, which is why a deliberately chosen collation appears on the list of work we can add rather than being claimed as done.
Why a subquery rather than a join for searching a related record?
A join can return the same parent row several times, once per matching child, which inflates the list and makes the pager count wrong — so page two starts in the wrong place and the total is overstated. A subquery asks whether any matching child exists and returns each parent once, which keeps both the list and the count honest.
