Chain builder

Appearance

Case-insensitive Search ​

Problem: a search box — ?q=ali should match "Alice", "ALI", "Salim" — implemented portably across Postgres, MySQL, and SQLite, without letting the user's input smuggle LIKE wildcards into your pattern.

Two distinct concerns, often conflated:

  1. Case-insensitivity — solved by where_ilike, which the builder lowers per dialect.
  2. Wildcard hygiene — % and _ inside user input are pattern metacharacters, and the builder deliberately does not escape them. That part is yours.

where_ilike: one chain, three dialects ​

where_ilike is the portable case-insensitive match. On Postgres it compiles to the native ILIKE operator; MySQL and SQLite have no ILIKE, so the builder lowers it to LOWER(col) LIKE LOWER(?) — same semantics, same chain:

rust
use chain_builder::{MySql, Postgres, QueryBuilder};

let (sql, _) = QueryBuilder::<Postgres>::table("users")
    .select(["id", "name"])
    .where_ilike("name", "%ali%")
    .to_sql();
// SELECT "id", "name" FROM "users" WHERE "name" ILIKE $1

let (sql, _) = QueryBuilder::<MySql>::table("users")
    .select(["id", "name"])
    .where_ilike("name", "%ali%")
    .to_sql();
// SELECT `id`, `name` FROM `users` WHERE LOWER(`name`) LIKE LOWER(?)

The pattern is a bound value either way — no injection is possible through it. SQLite renders like MySQL but with double-quote identifiers.

The wildcard caveat ​

The builder binds your pattern verbatim. If you build it as format!("%{q}%") from raw input, then a user who types 100% matches 100<anything>, a lone % matches every row, and a_c matches abc. That is not SQL injection — the value is still safely bound — but it is a correctness and abuse problem: a filter that should narrow results can be forced wide open, and pathological patterns (%a%b%c%d%…) make the database scan hard.

Escape the three metacharacters before splicing input into a pattern — backslash first, then % and _ (order matters, or the backslashes produced for %/_ get double-escaped). The crate ships exactly this:

rust
use chain_builder::escape_like; // in-crate since 3.1.0

assert_eq!(escape_like("100%_a\\b"), "100\\%\\_a\\\\b");

Portable form: where_raw + ESCAPE ​

What \ means inside a LIKE pattern is itself dialect-defined: Postgres and MySQL treat backslash as the default escape character, but SQLite has no default escape character at all — \% there is two literal characters. The portable fix is an explicit ESCAPE clause, which the structured where_like/where_ilike do not model, so this is a legitimate where_raw job:

rust
use chain_builder::{escape_like, Postgres, QueryBuilder, Value};

let q = "50%"; // raw user input
let pattern = format!("%{}%", escape_like(q)); // "%50\%%"

// One bind ($1) precedes the raw fragment, so its placeholder is $2 —
// where_raw is verbatim and does NOT renumber (use ? on MySQL/SQLite).
let (sql, binds) = QueryBuilder::<Postgres>::table("users")
    .select(["id", "name"])
    .where_eq("status", "active")
    .where_raw(
        r#"LOWER("name") LIKE LOWER($2) ESCAPE '\'"#,
        vec![Value::Text(pattern)],
    )
    .to_sql();
// SELECT "id", "name" FROM "users" WHERE "status" = $1 AND LOWER("name") LIKE LOWER($2) ESCAPE '\'

On MySQL, write the escape literal as ESCAPE '\\' — inside a MySQL string literal a single backslash escapes the closing quote, so '\' is a syntax error. (SQLite accepts '\' as-is.)

Now 50% matches only names containing the literal text 50%. Mind the where_raw contract: the fragment is emitted verbatim — hand-write the correct $N (binds already accumulated + 1) on Postgres, ? elsewhere, and quote any identifiers in the fragment yourself. Never build the fragment from user input; only the bound pattern may carry it.

Simpler: the prefix-match pattern ​

If prefix search is enough (ali → "Alice", "Ali"), you can stay on the structured API. Escape the input, anchor it at the start, and skip the trailing-%-only worry:

rust
let q = "ali";
let (sql, _) = QueryBuilder::<Postgres>::table("users")
    .select(["id", "name"])
    .where_ilike("name", format!("{}%", escape_like(q)))
    .to_sql();
// SELECT "id", "name" FROM "users" WHERE "name" ILIKE $1

This is also the index-friendly shape: a leading wildcard (%ali%) defeats ordinary B-tree indexes, while ali% can use one (on Postgres, an index with text_pattern_ops / varchar_pattern_ops, or a functional index on LOWER(name) for the lowered form). Caveat: without an ESCAPE clause the escaped pattern relies on backslash being the default escape character — true on Postgres and MySQL, not on SQLite, where you need the where_raw … ESCAPE form above for literal matching.

Notes & caveats ​

  • Escaping is per-pattern, not global. Apply escape_like to the user's term only, then add your own % anchors — escaping after format!("%{q}%") would neutralize your anchors too.
  • where_like shares the caveat. Everything here applies to the case-sensitive where_like as well; see WHERE.
  • Unicode case-folding differs per backend (Postgres follows the database collation; MySQL depends on column collation — many _ci collations are case-insensitive for plain LIKE already; SQLite's built-in LOWER only folds ASCII). For ASCII search terms the lowered form behaves identically everywhere.
  • Heavy search workloads outgrow LIKE: consider Postgres pg_trgm or full-text search, MySQL FULLTEXT, SQLite FTS5 — all reachable via where_raw with bound values.
  • WHERE — where_like, where_ilike, and the where_raw placeholder contract
  • Security Model — why bound wildcards are an abuse problem, not an injection problem
  • HTTP Filters & Pagination — wiring a search term into a filter chain