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.

How do I create a persistent, hack resistant, url safe, token in sql server when the defining data exists across two columns in a view? — aboy021
– aboy021, Commented Jan 13, 2010 at 21:22

aboy021 · Accepted Answer · 2010-01-15 01:46:27Z

1

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.

answered Jan 15, 2010 at 1:46

aboy021

2,3052 gold badges26 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Philip Kelley · Accepted Answer · 2010-01-13 04:22:38Z

0

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?

answered Jan 13, 2010 at 4:22

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

1 Comment

aboy021 Over a year ago

A random number is a good idea, as long as it's big enough to avoid collisions, but since the data is in a view rather than a table it doesn't actually exist as such. In order to use a random number I would need to have a real table based on the view data. Since the view contains a cross join this would lead to a bit of a data explosion.

Collectives™ on Stack Overflow

Querystring encryption in sql server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related