code/cl8y-dex-terraclassic

Fork 0

Security: ILIKE pair search accepts unescaped wildcards, single-char query matches all pairs [SEC-I04] (F02) #459

New issue

Closed

opened 2026-06-30 18:06:04 +00:00 by totdking · 9 comments

totdking commented

2026-06-30 18:06:04 +00:00

(Migrated from gitlab.com)

No description provided.

totdking commented

2026-06-30 18:09:04 +00:00

(Migrated from gitlab.com)

Summary

The GET /api/v1/pairs?q= search handler passes the query string into an ILIKE pattern without escaping % and _ wildcard metacharacters. A request with ?q=% produces a leading-wildcard ILIKE '%' clause that matches every row in the pairs table. Because leading-wildcard ILIKE cannot use B-tree indexes, each such request forces a sequential scan. This bypasses the intent of the 128-character truncation guard and creates a search-amplification vector: a burst of single-character or wildcard queries can saturate Postgres CPU ahead of the 30-second statement timeout circuit breaker.

What Was Checked

indexer/src/db/queries/pairs.rs lines 95-213 (push_pair_list_filters and push_pair_relevance_score): user query string embedded into ILIKE patterns via push_bind(format!("%{}%", q)) without escaping % or _ first.
indexer/src/api/pairs.rs lines 173-178: q parameter truncated to 128 chars only. No metacharacter stripping or minimum length check.
Rate limit (10 RPS on LCD-heavy routes) applies globally but the pairs list endpoint is not classified as LCD-heavy and uses the 60 RPS global governor only.

Expected (per checklist)

The q parameter metacharacters % and _ are escaped before being embedded in the ILIKE pattern (e.g., replace % with \% and _ with \_), or a minimum query length of 2+ characters is enforced so single-character and wildcard-only queries are rejected.

Actual

?q=% sends ILIKE '%' to Postgres, matching every pair. No metacharacter escaping or minimum length check is present.

Suggested Fix

In push_pair_list_filters, escape the query string before embedding: replace % with \% and _ with \_ (and ensure the ILIKE call includes ESCAPE '\'). Alternatively enforce a minimum query length of 2 characters in the handler and reject with 400 if shorter. Additionally consider adding a pg_trgm GIN index on symbol, name, and contract_address columns to make the leading-wildcard case index-backed even when metacharacters are present.

Verification Checklist

push_pair_list_filters escapes % and _ in the query string before embedding in ILIKE
OR minimum query length of 2 characters enforced with 400 rejection in the handler
Test added: ?q=% returns an empty list or 400, not all pairs
Test added: ?q=_ does not match all single-character-symbol assets
pg_trgm GIN index considered and decision documented if deferred

Cc: @PlasticDigits

### Summary The `GET /api/v1/pairs?q=` search handler passes the query string into an ILIKE pattern without escaping `%` and `_` wildcard metacharacters. A request with `?q=%` produces a leading-wildcard `ILIKE '%'` clause that matches every row in the pairs table. Because leading-wildcard ILIKE cannot use B-tree indexes, each such request forces a sequential scan. This bypasses the intent of the 128-character truncation guard and creates a search-amplification vector: a burst of single-character or wildcard queries can saturate Postgres CPU ahead of the 30-second statement timeout circuit breaker. --- ### What Was Checked - `indexer/src/db/queries/pairs.rs` lines 95-213 (`push_pair_list_filters` and `push_pair_relevance_score`): user query string embedded into ILIKE patterns via `push_bind(format!("%{}%", q))` without escaping `%` or `_` first. - `indexer/src/api/pairs.rs` lines 173-178: `q` parameter truncated to 128 chars only. No metacharacter stripping or minimum length check. - Rate limit (10 RPS on LCD-heavy routes) applies globally but the pairs list endpoint is not classified as LCD-heavy and uses the 60 RPS global governor only. --- ### Expected (per checklist) The `q` parameter metacharacters `%` and `_` are escaped before being embedded in the ILIKE pattern (e.g., replace `%` with `\%` and `_` with `\_`), or a minimum query length of 2+ characters is enforced so single-character and wildcard-only queries are rejected. --- ### Actual `?q=%` sends `ILIKE '%'` to Postgres, matching every pair. No metacharacter escaping or minimum length check is present. --- ### Suggested Fix In `push_pair_list_filters`, escape the query string before embedding: replace `%` with `\%` and `_` with `\_` (and ensure the ILIKE call includes `ESCAPE '\'`). Alternatively enforce a minimum query length of 2 characters in the handler and reject with 400 if shorter. Additionally consider adding a pg_trgm GIN index on `symbol`, `name`, and `contract_address` columns to make the leading-wildcard case index-backed even when metacharacters are present. --- ### Verification Checklist - [ ] `push_pair_list_filters` escapes `%` and `_` in the query string before embedding in ILIKE - [ ] OR minimum query length of 2 characters enforced with 400 rejection in the handler - [ ] Test added: `?q=%` returns an empty list or 400, not all pairs - [ ] Test added: `?q=_` does not match all single-character-symbol assets - [ ] pg_trgm GIN index considered and decision documented if deferred Cc: @PlasticDigits

totdking commented

2026-06-30 18:09:59 +00:00

(Migrated from gitlab.com)

mentioned in issue #453

totdking commented

2026-06-30 18:37:55 +00:00

(Migrated from gitlab.com)

mentioned in issue #381

Brouie commented

2026-06-30 19:06:12 +00:00

(Migrated from gitlab.com)

mentioned in merge request !984

Brouie commented

2026-06-30 19:08:40 +00:00

(Migrated from gitlab.com)

Fixed. The pair search built ILIKE patterns with format!("%{}%", q) and never escaped metacharacters, so ?q=% -> ILIKE '%%%' matched every pair (and forced a seq scan), ?q=_ matched any single char.

Added escape_like_pattern() — escapes \, %, _ (backslash first to avoid double-escaping). Postgres' default LIKE/ILIKE escape char is backslash, so the bound pattern needs no explicit ESCAPE clause. Applied at every pattern site: the relevance score, the list filters, and the split pair-symbol sub-tokens. I went with escaping rather than a min-length reject so single-char symbol search still works.

Tests: 4 unit tests on the escaper, plus a Postgres integration test (search_wildcard_query_does_not_match_all_pairs) — ran it against the live test DB: ?q=% and ?q=_ both return empty, ?q=LUNC still returns the seeded pair. Full api_pairs suite 16/0 (incl relevance ordering, so normal search is intact).

MR !984, branch qa/459-ilike-escape, commit ef0c850d. Needs review/merge @PlasticDigits — leaving open for verification.

Fixed. The pair search built ILIKE patterns with `format!("%{}%", q)` and never escaped metacharacters, so `?q=%` -> `ILIKE '%%%'` matched every pair (and forced a seq scan), `?q=_` matched any single char. Added `escape_like_pattern()` — escapes `\`, `%`, `_` (backslash first to avoid double-escaping). Postgres' default LIKE/ILIKE escape char is backslash, so the bound pattern needs no explicit `ESCAPE` clause. Applied at every pattern site: the relevance score, the list filters, and the split pair-symbol sub-tokens. I went with escaping rather than a min-length reject so single-char symbol search still works. Tests: 4 unit tests on the escaper, plus a Postgres integration test (`search_wildcard_query_does_not_match_all_pairs`) — ran it against the live test DB: `?q=%` and `?q=_` both return empty, `?q=LUNC` still returns the seeded pair. Full api_pairs suite 16/0 (incl relevance ordering, so normal search is intact). MR !984, branch `qa/459-ilike-escape`, commit `ef0c850d`. Needs review/merge @PlasticDigits — leaving open for verification.

PlasticDigits commented

2026-06-30 22:00:29 +00:00

(Migrated from gitlab.com)

mentioned in commit 16cdb4336b

mentioned in commit 16cdb4336b481962713c68df68cefdc73f2f81e8

PlasticDigits commented

2026-06-30 22:21:06 +00:00

(Migrated from gitlab.com)

Verification — GitLab #459 (SEC-I04 F02)

Verified on main at merge 16cdb433 (MR !984, ef0c850d).

Checklist

Item	Result	Evidence
`push_pair_list_filters` escapes `%` and `_` before ILIKE	PASS	`escape_like_pattern()` in `indexer/src/db/queries/pairs.rs`; applied in `push_pair_list_filters`, `push_pair_relevance_score`, and split pair-symbol sub-tokens
OR min query length 2 → 400	N/A	Escaping chosen (keeps single-char symbol search); primary fix path satisfied
`?q=%` returns empty list or 400, not all pairs	PASS	`search_wildcard_query_does_not_match_all_pairs` in `indexer/tests/api_pairs.rs`
`?q=_` does not match all single-char-symbol assets	PASS	Same integration test
pg_trgm GIN index considered; decision documented if deferred	PASS (deferred)	Escaping closes the search-amplification vector (doc comment on `escape_like_pattern`). `pg_trgm` GIN on searchable columns would help legitimate substring-scan performance but is optional follow-up — not required for SEC-I04 F02 closure

How verified

make setup-indexer-postgres
cd indexer && cargo test --lib escape_like_tests -- --nocapture          # 4/4 pass
cd indexer && cargo test --test api_pairs search_wildcard_query_does_not_match_all_pairs -- --test-threads=1  # pass
cd indexer && cargo test --test api_pairs -- --test-threads=1              # 16/16 pass

Code review: escape_like_pattern escapes \, %, _ (backslash first); patterns bound via push_bind with Postgres default backslash escape (no explicit ESCAPE clause). Handler still truncates q to 128 chars only — wildcard abuse neutralized by escaping, not min-length reject.

Live curl against a running indexer API was not run (requires full deploy FACTORY_ADDRESS + seeded pairs); integration test with seeded Postgres covers ?q=% / ?q=_ / ?q=LUNC behavior.

Follow-ups

Optional: add pg_trgm GIN indexes on pair-search columns if substring ILIKE scan latency becomes an ops concern at scale (rate limits + 30s timeout already bound abuse).

## Verification — GitLab #459 (SEC-I04 F02) Verified on `main` at merge `16cdb433` (MR !984, `ef0c850d`). ### Checklist | Item | Result | Evidence | |------|--------|----------| | `push_pair_list_filters` escapes `%` and `_` before ILIKE | **PASS** | `escape_like_pattern()` in `indexer/src/db/queries/pairs.rs`; applied in `push_pair_list_filters`, `push_pair_relevance_score`, and split pair-symbol sub-tokens | | OR min query length 2 → 400 | **N/A** | Escaping chosen (keeps single-char symbol search); primary fix path satisfied | | `?q=%` returns empty list or 400, not all pairs | **PASS** | `search_wildcard_query_does_not_match_all_pairs` in `indexer/tests/api_pairs.rs` | | `?q=_` does not match all single-char-symbol assets | **PASS** | Same integration test | | pg_trgm GIN index considered; decision documented if deferred | **PASS (deferred)** | Escaping closes the search-amplification vector (doc comment on `escape_like_pattern`). `pg_trgm` GIN on searchable columns would help legitimate substring-scan performance but is optional follow-up — not required for SEC-I04 F02 closure | ### How verified ```bash make setup-indexer-postgres cd indexer && cargo test --lib escape_like_tests -- --nocapture # 4/4 pass cd indexer && cargo test --test api_pairs search_wildcard_query_does_not_match_all_pairs -- --test-threads=1 # pass cd indexer && cargo test --test api_pairs -- --test-threads=1 # 16/16 pass ``` Code review: `escape_like_pattern` escapes `\`, `%`, `_` (backslash first); patterns bound via `push_bind` with Postgres default backslash escape (no explicit `ESCAPE` clause). Handler still truncates `q` to 128 chars only — wildcard abuse neutralized by escaping, not min-length reject. Live `curl` against a running indexer API was not run (requires full deploy `FACTORY_ADDRESS` + seeded pairs); integration test with seeded Postgres covers `?q=%` / `?q=_` / `?q=LUNC` behavior. ### Follow-ups - Optional: add `pg_trgm` GIN indexes on pair-search columns if substring ILIKE scan latency becomes an ops concern at scale (rate limits + 30s timeout already bound abuse).

PlasticDigits (Migrated from gitlab.com) closed this issue

2026-06-30 22:21:09 +00:00

Brouie commented

2026-07-01 11:30:47 +00:00

(Migrated from gitlab.com)

mentioned in issue #337