Broken search and filtering in AI CRUD apps: how to fix it

What “broken search” looks like in a CRUD app

Broken search feels like the app is lying. You type a name you know exists and it doesn’t show up. Or you filter by status and suddenly get records that clearly don’t match.

The most common symptoms are straightforward:

• Missing results
• Duplicate rows
• Ordering that seems random

Duplicates often come from joins. One record turns into many rows because the query joins another table and never groups or dedupes. “Random” ordering usually happens when there’s no stable sort, so the database returns rows in whatever order is convenient.

These issues get worse as data grows. With 50 records, you might not notice that a filter is sometimes ignored. With 50,000 records, the same bug turns into timeouts, half-loaded pages, and users giving up because they can’t find anything.

Once search feels unreliable, people stop trusting the whole app. They assume the data is wrong, not the query. Support tickets rise, and teams start keeping their own spreadsheets because the system of record no longer feels safe.

A quick way to reproduce the problem is to create a few records that are easy to tell apart. For example: one customer named "Ann Lee" (active), one named "Anne Li" (inactive), and one named "Bob" (active). Then check:

• Search "Ann" and confirm which records appear
• Filter status = active and confirm "Anne Li" disappears
• Sort by created date twice and confirm the order stays the same

If any result surprises you, the search is broken, even if it only fails “sometimes.”

Why AI CRUD apps commonly get search and filters wrong

Most AI CRUD apps start as quick demos. Search and filters get bolted on late, then quietly become the part users touch on every page. That’s why broken search and filters are so common: they’re built under time pressure, with copy-pasted patterns that look fine until real data and real users show up.

A frequent root cause is an AI-generated query builder that mixes safe parameterization with string concatenation. It might bind values safely in one spot, then directly inject a sort column, operator, or raw WHERE fragment somewhere else. That creates confusing bugs (wrong results, missing rows) and real risk (SQL injection through “clever” filter inputs).

Another issue is letting the UI send anything: any field name, any operator, any value. It feels flexible, but the backend ends up guessing intent. One user searches status, another uses createdAt, and someone else tries contains on a numeric column. Even when nothing crashes, behavior becomes inconsistent and hard to test.

Joins make it worse. Searching across joined tables without a plan leads to duplicates, missing matches, and slow queries. A “Customers” page might join orders and notes, then apply the search term to both. Without clear rules for grouping and deduping, one customer with many orders can appear multiple times, and pagination becomes unreliable.

Performance often fails for the same reason: the database isn’t indexed for what people actually do. Teams index id and call it done, while production queries filter by tenant_id + status, sort by created_at, and search by email.

Safety first: stop unsafe dynamic filters

Broken search and filtering often starts as a “flexible” filter box: users can pass any field, any operator, and any value. If your app stitches those pieces into a SQL string, an attacker can smuggle extra SQL into the query. In plain terms, they’re not just filtering rows anymore, they’re changing what the database executes.

A common example is a filter like status=active that becomes WHERE status = 'active'. If someone submits active' OR 1=1 --, the query can turn into “return everything.” In worse cases, injected text can read sensitive tables or modify data, depending on permissions.

Escaping is not the same as parameterization. Escaping tries to make dangerous characters safe inside a string. Parameterization (prepared statements) keeps the SQL structure fixed and sends values separately, so the database treats them as data, not instructions.

The tricky part is that many “dynamic filter” problems aren’t about values. These inputs are especially risky because most SQL libraries can’t parameterize them:

• Field names (example: sortBy=price)
• Sort direction (asc/desc)
• Operators (=, LIKE, >, IN)
• Raw SQL snippets (where=..., order=...)
• Table or relationship names

For those, don’t escape and hope. Use allowlists: define exactly which fields can be filtered or sorted, which operators are allowed per field, and how each one maps to safe SQL.

Also limit damage with least-privilege database roles. Even if a bad query slips through, the account used by your app shouldn’t be able to drop tables or read admin-only data.

Create a clear filter contract (what’s allowed and what’s not)

Most broken filtering happens because the app accepts “anything” from the UI and tries to turn it into a database query. A filter contract fixes that by setting plain rules for what filters exist, what they mean, and how invalid input is handled.

Keep the contract small. Start with a short whitelist of filterable fields and the operators you’ll support for each one. For example: status can be equals but not contains. A createdAt date can be before and after, not free text.

Validate types before building the query. Treat each filter as a typed input, not a string to paste into SQL: strings with max length, numbers with range limits, strict dates, enums that must match allowed values, booleans that are only true/false.

Add constraints so one request can’t overload your database: a maximum page size, a maximum number of filters per request, and a maximum search text length.

Finally, pick consistent rules for empty, null, and unknown values. If a filter value is empty, do you ignore it or reject it? If a field name is unknown, do you return a clear validation error? These decisions prevent “why did my results disappear?” bugs.

Step-by-step: refactor a query builder that returns wrong results

Broken search and filtering often starts with a query builder that tries to be “flexible” by gluing SQL strings together. It works for a demo, then returns weird results, breaks on quotes, or becomes a security risk.

A practical refactor path

First, write down what the UI truly needs, in plain language. For example: “Search customers by name or email,” “Filter by status,” “Sort by newest,” “Show 25 per page.” If you can’t describe it clearly, the code won’t stay clean.

Then refactor in small steps:

• Lock allowed inputs: which filters exist, which sort options exist, and which columns they map to.
• Build WHERE with parameters only (no string concatenation for values).
• Treat filter keys as untrusted: map UI keys like status to known columns like customers.status, and reject or ignore unknown keys.
• Make sorting stable: add a tie-breaker (example: created_at then id) so pagination doesn’t skip or repeat rows.
• Make pagination rules explicit: start with limit and offset, or use cursor paging later, but don’t mix styles.

If the UI sends sort=createdAt, don’t pass that into SQL. Translate it to a fixed, safe snippet like ORDER BY customers.created_at DESC, customers.id DESC.

Tests that catch the “it looked fine” bugs

A few focused tests prevent regressions:

• Names with apostrophes (O'Connor)
• Emojis and non-Latin names
• Mixed case (alex vs Alex)
• Empty search with filters applied
• Unknown filter keys (ignored or rejected, consistently)

Choose the right search behavior (and keep it predictable)

A lot of broken search and filtering is really unclear rules. If users don’t know what the search box means, every result feels wrong, even when the SQL is doing what you told it to do.

Pick one default search mode and stick to it across the app. Mixing exact match on one screen and “contains” on another is a common way AI CRUD apps confuse people.

Exact match is best for IDs and emails. Prefix match is good for names and codes and can be fast with the right index. Contains match is helpful, but easy to make slow on large tables. Fuzzy matching is useful when typos are expected, but you need to say so.

If you use contains search, be careful with patterns like LIKE '%term%' on big tables. That leading wildcard often forces a full table scan.

Whatever you choose, normalize input the same way every time: trim spaces, fold case when it doesn’t change meaning, and decide how to treat punctuation. Searching " Acme, Inc " should behave like "acme inc", but searching "C++" shouldn’t silently turn into "c".

Make it fast: add the right indexes for your real queries

If users say search is slow, start by finding the few queries that hurt the most. Don’t guess. Pull the top offenders from database logs, API traces, or even a simple timestamped log around the search endpoint.

Indexes work best when they match real patterns: the columns in your WHERE clause, plus how you sort. If your UI filters by status and date and sorts by newest, indexing only status won’t help much.

Avoid indexing everything “just in case.” Every index adds cost: slower writes, heavier migrations, and more things to maintain. Add a small number of targeted indexes, then re-check performance with realistic data size.

Pagination and sorting that don’t break under load

Pagination bugs show up as “page 2 repeats items from page 1” or “some rows vanish.” The root cause is usually unstable sorting. If you sort only by created_at, many rows share the same timestamp, so the database is free to return ties in any order. When new rows are inserted between requests, the order shifts and items get skipped or repeated.

Use a stable sort with a tie-breaker, such as ORDER BY created_at DESC, id DESC. The id makes every row’s position unique, so “next page” stays predictable.

Offset pagination (LIMIT 50 OFFSET 5000) is simple, but it gets slower as the offset grows. For large tables, cursor (keyset) pagination is often a better choice: instead of “page 101,” you ask for “the next 50 rows after this last seen (created_at, id).”

Total counts can quietly become your slowest query. A filtered COUNT(*) over a big table can do a lot of work, and doing it on every request hurts. Common alternatives are showing counts only when needed, caching common counts, or returning hasNextPage using LIMIT pageSize + 1 instead of counting everything.

How to debug slow search and filters quickly

When broken search and filtering shows up as “it works, but it’s slow,” treat it like a measurement problem first. Guessing leads to random index changes and new bugs.

Start by capturing the actual SQL for only the slow requests. Keep it scoped to a request ID or a short time window, and avoid logging raw user input if it can contain emails, tokens, or other sensitive data. Logging the shape of the filters (which fields were used) is often enough.

Then run EXPLAIN (or your database’s equivalent) on the exact SQL that was executed. You’re looking for whether the database is using an index or scanning a whole table and sorting large result sets.

Common performance killers:

• N+1 queries (one query for rows, then one per row for related data)
• Unbounded joins (joining large tables without a selective filter)
• Missing LIMIT (or pagination that still sorts the whole table)
• Filters that block index use (functions on columns, leading wildcards like %term)
• Sorting by a non-indexed column

If you can’t reproduce the slowdown on your machine, build a minimal dataset that still shows it. If the slowdown disappears, the issue is often data distribution, not just code.

Common mistakes to avoid when fixing search

Escaping user input is good, but it doesn’t make the whole query safe. A very common bug in AI CRUD apps is escaping values while still letting the client send raw field names like ?sort=users.email or ?filter[field]=status. If someone can control column names, operators, or SQL fragments, they can still break your query.

Letting the client choose any sort column is another trap. It causes errors (sorting by a column you didn’t select), data leaks (sorting by an internal field), and performance issues (sorting by an unindexed column on a big table). Keep sorting to a small allowlist you actually support.

Filtering on computed fields also bites teams. Filtering by full_name when it’s built from first_name + last_name, or “days since last login” calculated in code, tends to get slow or inconsistent. If you must filter on it, consider storing it, indexing it, or caching it.

Be careful not to fix speed by changing results (or fix results by making it slow). Switching from LEFT JOIN to INNER JOIN can speed things up but silently drop records. Adding DISTINCT to hide duplicates can mask a join bug and make pagination and counts confusing.

Quick checklist before you ship the fix

Test the boring cases. Most bugs hide in edge inputs, unexpected combinations, and performance under real data.

Correctness checks: quotes, percent signs, underscores, emojis, very long text, multiple spaces, and empty search combined with filters. A good result is predictable, even when the input is messy.

Safety checks: the backend accepts only a small set of fields and operators, and every value is passed as a parameter (placeholders), never pasted into SQL.

Performance checks: measure slow queries before and after your changes using the same dataset and the same inputs. Document the indexes you rely on so future changes don’t accidentally undo the work.

Stability checks: sorting is deterministic (with a tie-breaker like id), and pagination doesn’t skip or repeat items when new rows arrive.

Example: fixing a broken “Customers” search in an AI CRUD app

A common case shows up on a “Customers” page: filter by status (active, paused), plan (Free, Pro), a signup date range, and a quick name search.

The symptoms look random. “Active + Pro” returns customers who are not Pro, name search misses obvious matches, and the list order changes on every refresh. Under load, the page gets slow enough to time out.

What usually went wrong:

• A join to plans or subscriptions multiplies rows, so one customer appears many times and counts are wrong.
• Sorting is built from raw input (unsafe and unstable).
• The database scans too much because there’s no index that matches the real filter + sort pattern.

A clean fix starts by making filters boring and strict: only allow known fields, validate types, and build queries from a small contract (status is one of X, plan is one of Y, dates are real dates, name is a plain string).

Then make results predictable: apply filters first, translate sort keys through an allowlist, and add a stable tie-breaker (for example, created_at then id) so pagination doesn’t reshuffle items.

Finally, add targeted indexes that match how people actually search. For this screen, that often means one composite index that covers the most common filter + sort combo, plus a separate approach for name search.

If you inherited an AI-generated codebase (Lovable, Bolt, v0, Cursor, Replit) with tangled query builders and unsafe dynamic filters, FixMyMess (fixmymess.ai) can start with a free code audit to pinpoint the wrong joins, risky SQL, and the specific queries to refactor first. Many projects can be repaired and prepared for deployment within 48-72 hours once the filter contract is locked down.