roowBlogSign in

Arvind Ram Anantharam

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:

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

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:

What to refuse outright

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:

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.