In WHERE name LIKE '%'||:term||'%', what breaks when the user's term contains % or _?

## The failure mode A search box builds a contains-pattern by wrapping the user's text in wildcards: ```sql SELECT id, name FROM customers WHERE name LIKE '%' || :term || '%'; ``` The value arrives as data, not as SQL text. But the *pattern language* is evaluated after the value is in place, so any `%` or `_` inside the term is now pattern syntax. Two concrete outcomes: - Term `50%` → pattern `%50%%`. The extra `%` is a wildcard, so the filter degrades to "contains 50" and returns `'50 units'` alongside `'50% off'`. A superset — plausible enough that nobody notices. - Term `_` → pattern `%_%`. Every value with at least one character matches. The search box appears to be broken in the other direction: it returns the whole table. This is a **semantics** bug, not a statement-construction one. The value is bound; nothing about the statement's structure is under the user's control. The predicate simply means something other than what the user asked for. ## The fix, step by step 1. **Choose an escape character** that cannot occur in the searched data, or that you are willing to escape too. Avoid backslash: in MySQL, backslash is also processed inside string literals by default, so you end up reasoning about two layers of escaping. `!` or `#` is a cleaner choice. 2. **Escape the term** before it is concatenated, in this order: first replace the escape character with a doubled copy of itself, then prefix every `%` and every `_` with it. Doing the wildcards first is the classic bug — you then escape the escape characters you just inserted and corrupt the pattern. 3. **Declare the escape character in the SQL** with an `ESCAPE` clause, so you do not depend on an engine's default (standard SQL defines none, and engine defaults differ). 4. **Add the wildcards last**, around the already-escaped term — the anchoring `%` characters are yours and must stay wildcards. ```sql -- :escaped_term already has ! % and _ neutralised, e.g. '50!%' SELECT id, name FROM customers WHERE name LIKE '%' || :escaped_term || '%' ESCAPE '!'; ``` A compact way to express the escaping inside SQL, if you must, is a nested `REPLACE` over the term — escape character first, then `%`, then `_` — but doing it in one shared application helper is easier to test and easier to keep consistent. ## Anchoring is a product decision, not a default While you are there, ask whether the search should really be `'%term%'`. "Starts with" (`term || '%'`) matches how people search identifiers and names, and returns a much tighter result set; "contains" is right for prose. Screens across an application often disagree on this by accident, which users experience as the search being unpredictable. Leading wildcards additionally have access-path consequences, but that is an indexing question rather than a question about what the predicate means. ## Empty and whitespace terms An empty term produces `'%%'`, which matches every non-NULL value — usually correct as "no filter", but decide it deliberately rather than discovering it. Trim the term, and consider short-circuiting the predicate entirely when the term is empty rather than sending a pattern that scans everything. Values that are NULL never match either `LIKE` or `NOT LIKE`, since the predicate is UNKNOWN for a NULL operand; if a NULL name should be treated as "no match", that is already the behaviour, and if it should appear, you need an explicit `IS NULL` branch. ## Testing it The cheap regression test is a term of `_` and a term of `%`: both must return only rows literally containing those characters, not the whole table. Adding those two cases to the test suite catches every future refactor that drops the escaping — which is the realistic risk, because the un-escaped version looks perfectly fine in review. ## What an interviewer is listening for They want you to separate two ideas that are often conflated: how a value is transported into the statement, and how the pattern language interprets that value once it is there. A senior answer names the over-matching outcome concretely, gets the escaping order right, insists on an explicit `ESCAPE` clause rather than an engine default, and mentions centralising the logic so twelve search screens do not each invent it.