Search and Filter

Letting users narrow down the table without leaving the page

Concepts

• A GET form submits its fields as URL query parameters (e.g., ?site=north&mutated=true)
  • Filtered views are bookmarkable, shareable, and reachable via Back
  • POST forms do not have these properties and should not be used for searches
  • How does a browser's cache treat a GET request differently from a POST request, and what does that mean for a search that filters personal or sensitive data?
• Conditional SQL means adding a where clause only when a filter value is present
  • A common pattern is to build a list of clause fragments and bind values, then join the fragments with and
  • What problem does conditional SQL solve, and why can't you simply always include a where clause with an empty string?
• Parameterized queries remain essential even for filter values
  • They come from user input, so concatenating them into the SQL string is still a SQL injection vulnerability
  • If the site name filter uses a parameterized query but a sort-order field is concatenated directly into the SQL string, is the query still vulnerable to injection?
• Pre-filling a form input means setting its value attribute to the currently active filter
  • The user can see what search is in effect and edit it without starting over
  • How would a user know what filter is currently applied if the form inputs were not pre-filled when the page loads?
• An empty result set is not an error
  • Return an empty table with a message explaining no records matched, not a 404 or an error page
  • What HTTP status code should the server return when a search finds no matching rows, and why?

Query parameters for filtering

• Site name and mutation presence as optional query parameters
  • Add optional site and mutated query parameters to the snail listing route handler
• Litestar's parameter declaration and validation
  • Update the handler so Litestar validates site as an optional string and mutated as an optional boolean

Building a conditional query

• Adding where clauses only when a filter is present
  • Write a Python function that builds a SQL select query, adding where clauses for site and mutation only when those values are provided
• Assembling SQL strings safely with parameterized placeholders
  • Rewrite the query builder so all filter values are passed as parameterized placeholders rather than concatenated into the SQL string

A search form

• An HTML form that submits GET parameters
  • Write an htpy function that generates a search form with a site text input and a 'mutated' checkbox that submits as a GET request
• Why GET rather than POST for searches
  • Bookmarkability and browser history
  • Add a short paragraph below the search form telling users that their filtered view is shareable and showing them how to copy the current URL

Reflecting the active filters

• Pre-filling form inputs from the current query parameters
  • Update the search form function to accept current filter values and set them as the default values of the form inputs
• The user can see and modify their search without starting over
  • Pass the current query parameter values from the route handler to the form renderer so the inputs show the active filter

Edge cases

• Empty results, invalid parameter values, and SQL injection
  • Update the handler to return an empty table with a "No records found" message when the query returns no rows
• Why parameterized queries are non-negotiable and how to verify them
  • Write a test that passes a SQL injection string as the site filter and verifies the query returns no rows rather than all rows

Check for Understanding

Why should a search form use method="get" rather than method="post"?

GET requests encode their parameters in the URL, so the result page can be bookmarked, shared, and reached via browser history. POST requests send data in the request body and are not represented in the URL, so refreshing the page re-submits the form and the URL cannot be shared. For searches (which do not modify server state) GET is always the right choice.

If you build a SQL query by concatenating the user's search term directly into the string, what attack does that enable?

SQL injection. A user who enters ' or '1'='1 as the site name would turn the query into where site = '' OR '1'='1', which matches every row. A more destructive input could add drop table or extract sensitive data. Parameterized queries fix this by passing values separately, so the database treats them as literals rather than SQL.

How do you write a query that filters by site name only when a site name is provided, but returns all rows otherwise?

Build the where clause conditionally. One approach is:

clauses = []
params = []
if site:
    clauses.append("site = ?")
    params.append(site)
sql = "select * from snails"
if clauses:
    sql += " where " + " and ".join(clauses)

This produces a valid query with or without a filter, and always uses parameterized placeholders.

What does "pre-filling" form inputs mean, and how do you retrieve the current filter value from the request in Litestar?

Pre-filling means setting the value attribute of a form input to the currently active filter so the user sees what they searched for and can refine it. In Litestar, the handler receives optional query parameters as function arguments (e.g., site: str | None = None). Those values are passed to the htpy renderer, which sets value=site on the corresponding <input>.

Exercises

Add a date range filter

Add start_date and end_date query parameters that filter results to measurements taken within the specified range. Write out the SQL you need before asking the LLM to generate it. Test the edge cases: what happens when only one of the two dates is provided, or when start_date is later than end_date?

Combine filters

Make the site name filter and the mutation filter work together correctly so that both conditions must be met when both are provided. Write a description of the combined query first, then implement it. Verify with at least three test inputs: site only, mutation only, and both at once.

Show active filters

Display a summary above the table listing each currently active filter with a link that removes it (e.g., "Site: north-beach [x]"). Clicking the link should return the same page with only that filter removed. Think through the URL construction before writing any code.

Test the edge cases

Manually test what the search route does when the filter value is: (a) an empty string, (b) a string containing a single quote, (c) a string of 500 characters. Record what the server returns for each; which cases does the current implementation not handle correctly?