Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

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.

share|improve this question
So an SQL column for which it is forbidden to SELECT from? –  Dracs Jul 17 '13 at 4:44
@Dracs, essentially, yes. –  David Jul 17 '13 at 4:45
I'd guess that you can achieve a similar effect with a stored procedure for password verification –  CodesInChaos Jul 17 '13 at 7:53
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
Given your exemple, what woudl happen if, for instance, I wanted to grab all CC number from your table ? (for instance). I can do that by using a WHERE clause that uses the LIKE operator instead of the equality, then loop through all numbers at each position and get a complete dump of your table (SELECT * FROM table WHERE ssn LIKE '1%',SELECT * FROM table WHERE ssn LIKE '2%'...SELECT * FROM table WHERE ssn LIKE '_1%', etc.) –  Stephane Jul 17 '13 at 13:41
No, you can't use LIKE to grab multiple values, because it's still returning a mask. Only a one-to-one correlation between query and masked data will allow you to determine what the actual data is. If you query SSN like '0%' it'll return XXX-XX-0123 (and others), from which you can only conclude 0XX-XX-0123 (and so on for all the values returned). But if you query SSN = '012-34-5678' and get XXX-XX-5678, then you can conclude that XXX-XX is 012-34. –  gowenfawr Jul 17 '13 at 14:06
I'm sorry, I still do not understand it. If it allows LIKE, then you can use it to query all records that have a specific digit ate a specific position, even if it only returns you a masked value. That means that you can run multiple queries (actually (10*n), n being the number of positions you want to explore) and use the result to deduce the full data of each field. The only way I can see around that is if you also limit the size of the result set to one single row (and even then, you can probably still use that attack to unmask one specific record). Or disallow the "LIKE" operator. –  Stephane Jul 17 '13 at 14:20
Okay, if the question is "Can you use LIKE to improve the efficiency of the enumeration", then the answer is yes. You could query SSN like '012-34-%' and get a list of all the SSN numbers in that grouping (it returns the last 4, you know the first 5). Then you can use SSN = 012-34-5678 queries to pull the name for each of the returned last 4 (5678 in this example). But you can't get a dump of the numbers themselves, which is what I understood your first question to be, because that's exactly what the masking protects against. –  gowenfawr Jul 17 '13 at 14:34
Ah, but basically, it means that the function you describe doesn't work in the same way as the OP suggested: you can still access the content of the masked row, it only takes several queries (and not that many). Thank you for the explanation, though: I was really curious how this worked. –  Stephane Jul 17 '13 at 14:44
show 1 more comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.