Data masking

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: "[email protected]", MySQL’s Duplicate entry '[email protected]', 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 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:

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

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.

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

Strategies

partial keeps emails readable (first letter + TLD); values of three characters or fewer become ***. hash lets analysts join/count on a column without seeing values.

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), narrowed to real issuer identification ranges so they no longer fire on 13-digit epoch timestamps, snowflake ids or order numbers,
• 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.

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.