Write & "WHERE" Clause Only?

Question

I've had an idea kicking around in my head for a while, and am wondering if there are problems with it or it just has no value. What if a Database server (such as Postgres) had a column that could only be used for INSERT or as part of a WHERE clause? I'm thinking of its use for storing password hashes, such that even with SQL injection, an attacker could not dump the hashes. (Yes, the hashes would still be in files on disk.) It seems to me this would be relatively easy to implement and another layer in a defense-in-depth strategy.

Answer 1

A similar, but potentially stronger concept is to remove access to the tables entirely and manage all data access through stored procedures, as @CodesInChaos alluded to in a comment.

If a user doesn't have access to run SQL queries directly against the table, then you have complete control over the data that can be returned to the application. The stored procedures in this model become an API for the database, essentially.

Answer 2

In DB2, column masks are used to achieve the effect you're describing, but they are vulnerable to enumeration attacks. They will cause a mask (e.g. you can define it as "CHAR('XXX-XX-') || SUBSTR(SSN,8,4)" instead of the full SSN) to be returned instead of the data for SELECT, but can still be used normally for WHERE. They can be configured on a per-user/group/role basis.

The enumeration attack is as follows: if I can 'WHERE' the 'ssn' field but not 'SELECT' it, I can still figure out who has what SSN:

SELECT name FROM table WHERE ssn = '000-00-0000';
SELECT name FROM table WHERE ssn = '000-00-0001';
SELECT name FROM table WHERE ssn = '000-00-0002';
...
SELECT name FROM table WHERE ssn = '999-99-9999';

And for some of those lucky guesses, the name will be returned, telling me which SSN they have.

Depending on the data involved, the "brute force" can be somewhat smart. For example, with credit card numbers, the full number may be protected but the BIN (first 6) and last 4 digits may be stored unencrypted alongside it. That would allow an attacker to enumerate 5 or 6 digits instead of 15 or 16, and the space can be lowered even further if the attacker calculates mod 10 before attempting potential numbers.

That's not a great example because field limits as we're discussing probably don't satisfy PCI DSS 3.4 requirements for protecting the PAN data. But it's an example, and can translate to other areas. For example, some subset of the population has an SSN allocated according to their birthplace. Some subset of that subset still lives in the same area they were born. Accordingly, the SSN prefix of that sub-subset may be guessable given their address, which may be stored unencrypted alongside their SSN, thus reducing the attack space.