Masking at the SQL Boundary: A Technical Deep Dive
When a model writes its own SQL, masking cannot be a filter on the results. It has to be a rewrite of the query.
This is the companion to How to Safely Work With Business Data in Claude. It uses the same example: a customers table where email is masked as a token, and full_name and phone are hidden.
Why masking the results fails
The obvious approach is to run the query and blank out sensitive columns by name in the result. The model gets around it without trying:
SELECT email AS contact FROM customers; -- a new name SELECT lower(email) FROM customers; -- a function SELECT * FROM customers; -- never named at all SELECT count(*) FROM customers WHERE email LIKE 'dana%'; -- only a number comes back
The first three return the real value under a name the filter does not know. The last returns no email at all, yet answers a question about one, and repeating it a letter at a time spells the address out.
Rewrite before the database sees it
Instead, parse each statement and rewrite it before it runs:
- Resolve every column to the table it really comes from, through aliases, joins, subqueries, CTEs, unions and
*. - Replace each protected column with its mask wherever it appears: in the output, in
WHERE,JOIN,GROUP BY,ORDER BYand inside functions. - Refuse anything the rewrite cannot follow, rather than guess.
The filter above becomes, in effect:
SELECT count(*) FROM customers WHERE token(email) LIKE 'dana%'; -- always 0: tokens start h_
The column now is its mask everywhere in the statement, so every trick above acts on the mask and learns nothing.
How a token should be made
- Keyed. Use a keyed hash such as HMAC-SHA256 with a secret the model never sees. A plain hash of an email can be checked by hashing a guess; a keyed one cannot.
- Computed in the database. The real value should never leave it. PostgreSQL and MySQL both have SHA-256 built in, which is enough to build HMAC with nothing installed.
- Stable. The same value gives the same token, so
count(DISTINCT email), joins andGROUP BYkeep working. That is also why a token is a pseudonym, not anonymity: everything with the same token is the same person. - Only for identifiers. On a column with few values (a plan, a zip code, a birth year), a token tells as much as the value. Hide those instead.
Hidden, and not there at all
A hidden column comes back as [redacted], and acts as NULL anywhere else in the statement, so a filter on it matches nothing. The model still knows the column exists.
For secrets, take the column away entirely: leave it out of the schema the statement is resolved against. SELECT * then returns every other column, and a statement that names it is refused with the column named, so the model can rephrase. Whole tables of secrets should simply not be listed for the model at all.
Writes that use what the model was shown
Because tokens are stable, a write can find rows by the token the model saw, in that call or a later one:
UPDATE customers SET plan = 'pro' WHERE email = 'h_3f9a21c07be4d518'; -- the rows behind that token
A few writes should be refused:
- Copying a protected value somewhere else:
SET notes = emailwould land it in a column nobody masked. The same goes forINSERT ... SELECTandRETURNING email. - Finding rows by a hidden column. There is no token to match, so the only possible match is the real value. Use the row’s id.
- Anything touching a column that is not there for the model at all.
What to refuse outright
- Whole-row references such as
row_to_json(c)orc::text, which carry every column at once. - Functions that run SQL from a string, like
query_to_xml: the rewrite cannot see inside the string. - Tables the connection does not list, including system catalogs such as
pg_stats, which can hold sampled column values. - Views nobody has reviewed. A view can show a protected column under a different name, and its own column would not be masked.
Numbers only, done carefully
Some people only need totals. “Aggregates only” access lets the model write its own SQL but only returns statistics over groups. It needs three rules to mean anything:
- True aggregates only.
count,sum,avg. Notminormax, which return one row’s value, and notsum(CASE WHEN id = 42 THEN salary END), which is one value dressed as a total. - A minimum group size, refusing the whole answer if any group falls below it, so
GROUP BY teamon a team of one is refused. - Count people, not rows. Grouping invoices by
customer_idmakes one group per customer, and a customer with fourteen invoices is a group of fourteen rows. A threshold that counts rows lets that through. Count the distinct people behind each group instead.
Even then, two large groups that differ by one person can be subtracted, and quasi-identifiers (country, plan, signup week) can narrow a group to one. Thresholds stop accidents and casual curiosity. A record of every question is what catches the rest.
Back to the main post: How to Safely Work With Business Data in Claude.
Know a team moving fast with Claude? Send them this.