AnyWhereInDB: A Database-Wide Needle-in-Haystack Search Tool

Abstract

AnyWhereInDB is a single-file PHP utility for locating an arbitrary string across a MySQL database. The original implementation searched every column with a generated LIKE '%needle%' predicate and rendered full rows. This rewrite preserves the drop-in operational model while replacing the legacy runtime with a schema-aware, Unicode-safe, prepared-statement search engine and an optional trigram inverted index. The goal is to make exploratory database archaeology practical: fast enough for repeated searches, safe enough to run on modern PHP, and precise enough to report the table, primary key, matching column, snippet, and algorithm used.

Background: The Legacy Baseline

The historical code in trunk/anywhereindb_older_version.php and the pre-rewrite trunk/anywhereindb.php followed a direct exhaustive strategy:

SELECT * FROM some_table
WHERE col_1 LIKE '%needle%'
   OR col_2 LIKE '%needle%'
   OR ...

That design had useful simplicity, but it also imposed several costs:

• mysql_* calls are removed from modern PHP, making the runtime fail on PHP 7+.
• SQL was built by string concatenation, exposing injection and escaping bugs.
• The connection never established utf8mb4, causing multilingual searches such as Persian text to degrade.
• The result model returned full rows rather than only matching columns.
• Leading-wildcard LIKE cannot use ordinary B-tree indexes and must scan candidate data.
• Browser rendering assumed non-null text values and could fail on NULL.

This rewrite treats the old implementation as the control case: correct in intent, but dominated by unnecessary scan width, missing Unicode guarantees, and obsolete database APIs.

Method

1. Schema-Aware Candidate Selection

The search engine queries INFORMATION_SCHEMA to enumerate base tables and searchable column types. It excludes the tool's own sidecar index tables and skips binary/blob columns. Numeric and date-like columns are included by casting them to CHAR, which keeps the utility useful during unknown-schema investigations.

2. Prepared Streaming Scan

The fallback path is still exhaustive, because the tool's promise is "search anywhere." The implementation now uses PDO, utf8mb4, identifier quoting, prepared values, LIKE ... ESCAPE, and bounded result collection. Every candidate match is verified in PHP before display. Results are column-level records:

{
  "table": "users",
  "column": "email",
  "primary_key": { "id": 42 },
  "snippet": "...needle...",
  "algorithm": "prepared_streaming_scan"
}

This directly fixes the old "return only the matching column" problem while preserving enough primary-key context to inspect the source row.

3. Unicode-Aware Matching and Snippets

The verifier uses mb_* functions and lowercases text with UTF-8 semantics. This makes case-insensitive verification and snippet extraction work for non-ASCII scripts instead of depending on browser or connection defaults.

4. Trigram Inverted Index

For repeated arbitrary substring search, the app can build two sidecar tables:

• _anywhereindb_documents: one logical document per (table, primary key, column).
• _anywhereindb_trigrams: a many-to-many map from Unicode trigrams to document ids.

For a query of length at least three, the search planner decomposes the query into trigrams, intersects posting lists in SQL, and verifies survivors with the Unicode matcher. This shifts repeated search from "scan every candidate column" to "retrieve documents containing all query trigrams, then verify." It is a practical approximation of the inverted-index architecture used by search engines while remaining deployable as a single PHP file.

5. Multi-Pattern Verification

The verifier treats the full query and whitespace-separated tokens as candidate needles. The design is inspired by the Aho-Corasick dictionary-matching model: normalize once, search several terms against the same cell, and produce a compact snippet. The current implementation uses PHP's Unicode string primitives for maintainability; the boundary is isolated so a full trie automaton can replace it when many thousands of simultaneous needles are required.

Complexity

Let N be the total searchable text size, G be the number of distinct trigrams in a query, and P(g) be the posting list size for trigram g.

• Legacy scan: O(N) per query, with SQL parsing and transfer overhead from SELECT *.
• Prepared streaming scan: still O(N) worst case, but with narrower rows, typed metadata, bounded results, prepared values, and PHP-side verification.
• Trigram search after indexing: approximately O(sum(P(g)) + V), where V is the number of candidate documents verified after intersection. The index build is O(N) and is amortized across future searches.

The sidecar index is not free: it stores a copy of searchable cell text plus trigram postings. That trade-off is explicit and user-triggered through the UI.

Usage

1. Place trunk/anywhereindb.php in a PHP-capable web directory.
2. Open it in a browser.
3. Connect to a MySQL database.
4. Search immediately with the prepared streaming scanner.
5. Optionally build the trigram index for faster repeated substring searches.

Delete the file after use if it is reachable from a public network. This is an administrative database inspection tool.

Resolved Legacy Issues

The rewrite addresses the migrated issues as follows:

• Persian/Unicode results: uses utf8mb4 at the PDO connection and Unicode-aware PHP matching.
• Null result rendering: all result values are checked before matching and escaped before display.
• First-run notices: request/session access is guarded.
• json_encode fatal error: modern PHP is required and JSON output is centralized.
• SQL errors from generated queries: PDO exceptions are caught and returned as structured errors.
• CSS inline rendering: results use table cells and mark, avoiding the old <pre> display problem.
• Matching columns only: result records identify the exact table, primary key, column, and snippet.

The historical SQL Server attachment is not implemented in this rewrite; the scope remains MySQL/MariaDB via PDO MySQL.

References

1. Alfred V. Aho and Margaret J. Corasick. "Efficient string matching: an aid to bibliographic search." Communications of the ACM, 18(6), 333-340, 1975. DOI: https://doi.org/10.1145/360825.360855.
2. Burton H. Bloom. "Space/time trade-offs in hash coding with allowable errors." Communications of the ACM, 13(7), 422-426, 1970. DOI: https://doi.org/10.1145/362686.362692.
3. MySQL Documentation. "The ngram Full-Text Parser." https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html.
4. MySQL Documentation. "Full-Text Search Functions." https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html.