Querystring encryption in sql server

Question

I need to create a token to appear on a querystring that can be passed to a web page and decoded in order to find a record in a database view.

The token should not be vulnerable to brute force incrementing value type attacks.

View records in the database are uniquely defined as a combination of two keys.

The generation of this token needs to happen in the database and on demand. The database is accessed directly by another system that generates emails utilising the data in the view.

I have tried generating a sha1 hash based on the two keys and then url safe base64 encoding the result, but since it is a one way operation the lookup at the web end is unacceptably slow.

I think symmetric key encryption would be suitable, as long as the encryption occurred in the database, and the decryption occurred in the website, before the lookup.

At this stage I'm leaning towards building a CLR function to populate a generated column in the view.

Answer 1

So you want the database to generate a "token", pass that value out to the application (web/internet), and later a user will return that token and the database will use it essentially as a lookup/primary key value? And you want the values generated to be such that a hacker [our friend Mallory] cannot (a) guess the token for a specific account and/or (b) guess any valid token for an account?

Why not just make it a random number? A 4 byte value gets you 4 billion values, 8 bytes gets you 4 petabytes of values (2^64), and it keeps going up. Admittedly you have the age-old issue of generating a truly random number (Maybe try these guys?), and you need to gauge the ratio of possible tokens to valid tokens against how long a brute force attack would require to guess them, but you'll get that with any kind of security.

My question is, why bother with hashes and encryption if the data you're passing out is just a lookup key?

Answer 2

I've ended up writing a CLR function that uses triple DES to encrypt a salted concatenated string containing the keys I need. This function is used for a computed column on the view.

I used this page as a basis for the triple DES code: http://www.dotnetspark.com/kb/1279-triple-des-encryption-and-decryption-using.aspx

It's performant and secure, though a bit more work than I think is necessary in most situations.