# Data masking

> Column-pattern and content-regex masking rules, the four strategies, and why results are masked before they reach disk.

- Documentation: Guides
- Updated: 2026-10-25
- Source: https://queryproxy.com/docs/data-masking/
- Language: en-US
- Author: Muhammet Şafak

---
Masking rules keep PII out of the result data a developer sees — and out of
QueryProxy's own result storage. Rules are managed by DBAs under **Masking**.

## Mask-on-write

Masking runs **inside the result stream**: as the worker cursors through a
result set, every row is masked *before* it is written to the result file. The
unmasked version of a result never exists on disk, so stored result data can
never be viewed or downloaded unmasked later. There is no "unmask" toggle by
design.

## Error messages

A failed execution has text too, and a database driver error can echo the values
that broke it (for example PostgreSQL's `invalid input syntax for type integer:
"alice@example.com"`, MySQL's `Duplicate entry 'bob@example.com'`, or the SQL
itself). So the error message goes through masking before it is stored or shown,
the same **mask-on-write** principle as result data:

1. **Redaction.** The connection's own password, username, host and database
   name are replaced with `[redacted]`.
2. **Regex rules.** Every enabled content rule of the connection masks what it
   matches, in place, with its own strategy. A connection with no rules gets the
   redaction only.
3. **Column rules, fail-closed.** If the message names a column that a column
   rule covers, every literal value in it (quoted values, `Duplicate entry '…'`,
   `Key (…)=(…)`) is masked with the strongest strategy among the matching
   column rules (`full`, then `hmac`, then `hash`, then `partial`). If the quotes in such a
   message do not balance (a stray apostrophe, as in MySQL's `doesn't exist`,
   would shift the pairing), the whole message becomes `*****`. PostgreSQL's
   `Failing row contains (…)` does not name a column, so when the connection has
   any rule the whole row is masked.
4. **Fail-closed otherwise.** If a regex cannot be evaluated, the whole message
   becomes `*****`. If no connection is left to take the rules from, or masking
   itself fails, the stored text is the fixed `Job failed; details in
   application log.`

The result is what the request detail, the requester's notification (first 120
characters) and the audit log show. The **raw message is written to the
application log only.** The queue job's own failure handler (`failed()`) uses
the same path, so a failure outside the normal execution path no longer stores
raw text, and the requester is notified and the failure audited exactly once.

> **What this does not cover.** A bare, unquoted value in a message (for example
> `value 42`) is caught only by a regex rule, not by a column rule. The error
> carries the SQL as written: string literals are quoted, numbers are not
> (`WHERE national_id = 12345678901`). Keep content rules for sensitive formats
> enabled. A connection **test** reports its errors through a separate path,
> outside this one.

### After upgrading

Messages stored by earlier versions stay unmasked until you mask them. Run,
once:

```bash
php artisan queryproxy:mask-error-messages --dry-run   # report only
php artisan queryproxy:mask-error-messages
```

The command re-masks the `error_message` of existing query requests and the copy
inside in-app "request failed" notifications, using each connection's current
rules. A request or connection that no longer exists gets the fixed message.
Timestamps and read state are kept, and the run is recorded as one
`error_messages.masked` audit entry. Running it again with the same rules
changes nothing; after a rule change it applies the current rules, and it can
only add masking, never remove it. New messages are masked with the rules in
force when they are written; changing a rule does not re-mask stored messages by
itself. A record whose connection rules cannot be read is skipped, left
unchanged and reported, and the command exits with an error.

> [!IMPORTANT]
> **The audit log is immutable, so the command cannot touch it.** Audit entries
> written by earlier versions (`request.execution_failed`,
> `execution.late_completion`) keep the unmasked message text, and an auditor
> can still read and export it. Only failures from the upgrade on are masked
> there.

## Rule types

| Match type | Matches on | Example pattern |
| :-- | :-- | :-- |
| **Column** | The column's name (case-insensitive wildcards; `\|` separates alternatives) | `*email*`, `*password*\|*secret*\|*token*\|*api_key*` |
| **Regex** | The cell's content (PCRE, with delimiters) | `/[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}/` |
| **Validator** | The cell's content, accepted only when a checksum holds | `tckn`, `vkn`, `iban_tr`, `luhn` |

Column rules win over content rules for the same value. Both kinds apply to
**every scalar value**, not only to strings: PDO hands back native integers for
`BIGINT` and `DECIMAL` columns, so a card number or a national id stored as a
number is matched by content rules exactly as a text one is. A value no rule
matches is returned untouched, with its original type.

A **validator** rule finds candidate values in the cell and masks only the ones
that pass the validator's checksum, so a random number is not masked just
because it has the right length. The validators are `tckn` (Turkish national
id), `vkn` (Turkish tax number), `iban_tr` (Turkish IBAN) and `luhn` (payment
card numbers). An unknown validator or a failing pattern masks the value (fail
closed). A digit run that is glued to a letter or an underscore
(`TC10000000146`) is not taken as a candidate by a content rule; a column rule
on the column name still catches it.

Each rule is either **team-wide** or pinned to a single connection, and can be
toggled on/off without deleting it.

## Strategies

| Strategy | Result | Example |
| :-- | :-- | :-- |
| `full` | The whole value replaced | `*****` |
| `partial` | First and last character kept, at most 8 stars between | `a***@***.com`, `5********2` |
| `hmac` | Keyed pseudonym, stable inside the team | `hmac:3f1a9c0d7b2e4a65` |
| `hash` | Deterministic SHA-256 prefix (deprecated) | `sha256:9f86d081884c` |

`partial` keeps emails readable (first letter + TLD); values of three
characters or fewer become `***`. `hmac` lets analysts join and count on a
column without seeing values: it is the first 16 hex characters of an
HMAC-SHA256 of the value, computed with a secret key that each team has (stored
encrypted, created with the team, never shown). Without the key the pseudonym
cannot be recomputed, which is the difference from `hash`: an unkeyed hash of a
phone number or national id can be reversed by trying every possible value.
`hash` stays selectable but is marked **deprecated**; prefer `hmac`. If a team
has no key, `hmac` falls back to `*****`.

`hmac` is **pseudonymisation inside the team, not anonymisation.** Values with
few possibilities (national ids, phone numbers) can still be recovered by
someone who can push candidate values through the same rule, for example in the
masking playground or in an approved query that returns candidates. Pseudonyms
are not comparable between teams.

### Rotating the masking key

```bash
php artisan queryproxy:masking:rotate-key <team id or slug>
```

The command asks for confirmation (use `--force` in scripts), writes a new key and
records `masking_key.rotated` in the audit log; the key itself is never logged
or printed. After a rotation, `hmac:` values in old and new results no longer
match each other. The strongest strategy wins when several rules match one
value: `full`, then `hmac`, then `hash`, then `partial`.

## Default rule set

One click (**Add default rules**) installs sensible defaults: email, phone and
card columns (`partial`), secret/token/password/api_key columns (`full`), plus
content regexes for:

- email addresses (`partial`),
- credit card numbers (`partial`) through a **Luhn validator** rule, so
  13-digit epoch timestamps, snowflake ids and order numbers that fail the
  checksum are left alone,
- vendor API keys and tokens with a fixed prefix — `sk-`, `xox…`, `ghp_` and
  friends, `glpat-`, `AKIA`/`ASIA`, `AIza` (`full`),
- JSON Web Tokens (`full`),
- PEM private key blocks (`full`),
- high-entropy secrets: 32 characters or more mixing lower case, upper case and
  digits (`full`).

The content rules for secrets exist because a column rule only ever sees the
**output** column name, which the query author chooses: `SELECT api_key AS k`
slipped past even the strictest `*api_key*` pattern. Matching on the value
catches the aliased secret anyway.

Defaults are idempotent — adding them twice creates nothing new. A team that
already has the older card-number regex keeps it; the Luhn rule is added next to
it (masking twice is harmless), and you can delete the old rule.

### Turkey rules

**Add TR rules** (DBA, on the Masking page; audited as
`masking_rule.tr_rules_added`) adds a pack for Turkish data:

- column rules: `*tckn*|*tc_kimlik*|*national_id*`, `*iban*` and `*vkn*|*tax_no*`
  (`hmac`), and `*plate*` (`partial`);
- validator rules: `tckn` and `iban_tr` (`hmac`);
- regex rules for Turkish mobile numbers, E.164 numbers and licence plates.

The `vkn` content validator comes **disabled**: about one in ten random ten-digit
numbers passes the tax-number checksum, so it would mask epoch values and ids.
The `*vkn*` column rule is on. Enable the validator only if you accept that.
Known gaps: a ten-digit number starting with 5 and written without separators can
match the mobile-number pattern, and an eleven-digit run inside an `hmac:` token
can occasionally be masked again.

> **New teams start with the default rules; existing teams do not get them
> automatically.** Any team created from v0.2.1 on — under **Teams & Users**,
> by the demo seed, or any other path — has the default rules installed the
> moment it is created. Teams that existed before the upgrade are **not**
> backfilled, and no team picks up newly added defaults on upgrade: open
> **Masking** for each such team and run **Add default rules** once. It is safe
> to run on a team that already has rules — only missing defaults are added,
> nothing existing is changed or removed. That also means an older, broader card
> pattern a team installed earlier stays in its rule list; disable it if you no
> longer want it.
>
> A team with no **enabled** masking rule shows a warning banner on **Query
> Studio** and **Masking**. With no rules at all it reads *"This team has no
> masking rules — query results are returned unmasked."* and, on the Masking
> page, links straight to **Add default rules**. When rules exist but every one
> is disabled it reads *"This team has no enabled masking rules — query results
> are returned unmasked."* and asks you to enable them. On Query Studio the
> banner tells the user to ask a DBA. The check is team-wide: a team whose only
> enabled rule is bound to one connection shows no banner, yet queries on its
> other connections come back unmasked — keep at least one team-wide rule
> enabled. The defaults are installed all-or-nothing, so a
> failure while creating a team never leaves it with a partial rule set.

## Testing rules

The **playground** at the bottom of the Masking page previews your enabled
rules: enter a column name and a sample value, see exactly what a result would
show. The preview applies all of the team's enabled rules; it is not filtered by
connection. Invalid regex patterns are rejected at save time, and patterns that risk
catastrophic backtracking (ReDoS) are rejected too — a broken or runaway pattern
can never silently match nothing in production.

Regex masking also **fails closed**: if a pattern cannot be evaluated against a
value at runtime, that value is **masked, not emitted raw** — masking never
falls open on the pathological data it most needs to protect.