Encrypting / Decrypting a passed value in SQL Server

Question

I have the following scenario ...

A vendor sends a customer an email. At the bottom of the email is a link back to a web page that I am responsible for i.e. hosting. We need to pass the users email address through the link to the web page but we want it to be encrypted. We need to then be able to DECRYPT it on our end when we receive the request so that we can extract the email from it.

I would like for the vendor to be able to encrypt it via SQL Server (that's where the link is generated) and we would like to also decrypt it using SQL Server.

I have no, none, zero experience with encryption/decryption and need to devise a solution within the next 6 hours ... !!!

I see information on encrypting entire columns but I need to be able to encrypt/decrypt just a single string.

Any thoughts? Better approach?

Thanks, G

DavidG · Answer 1 · 2014-05-28T14:50:21.547

2

I wouldn't use encryption for this task. Anything you encrypt can potentially be decrypted by someone. A better solution is to generate a random code and assign it against the email address in your database. Then do a lookup based on that code. The code could be as simple as using a GUID.

In C#: Guid.NewGuid()

In SQL Server: NEWID()

edited May 28 '14 at 14:50

answered May 28 '14 at 14:39

DavidG

113,891
12
217
223

This was an original thought, but unfortunately the vendor would be the one creating the GUID but there is no way for us to get associated with the record in our database. Additionally there is no guarantee that the email even exists in our database at this point. We would need the ability to extract the email from the encrypted/hash in order to add it. – Gary O. Stenstrom May 28 '14 at 16:22
The vendor has to get a code from you though, why can't you create the email/code match at that point? – DavidG May 28 '14 at 16:34

score 1 · Accepted Answer · edited Feb 02 '16 at 12:08

1

You can use EncryptByPassPhrase and DecryptByPassPhrase in SQL Server:

select EncryptByPassPhrase('key', 'abc' );

select convert(varchar(100),
   DecryptByPassPhrase('key', 0x0100000001E5B67F919CCC4B8EA10E97FC50764BF6B30EC4347C4E54));

edited Feb 02 '16 at 12:08

BaCaRoZzo

7,502
6
51
82

answered May 28 '14 at 14:45

Dhaval

2,801
20
39

This worked for me! I am not sure if it's the MOST secure but it's lightyears ahead of where we are! – Gary O. Stenstrom May 28 '14 at 16:53

Tanner · Answer 3 · 2014-05-28T15:03:20.503

I would create a new column in your user email table that is of type UNIQUEIDENTIFIER and set it's default value to NEWID() so that it creates a new GUID (like: 400c4d0d-cd5a-42fd-8660-15250e27c247) when the record is created. This is on the assumption that per email, you create a new record in this table.

SQL to alter your source table:

ALTER TABLE [YOUR_TABLE] ADD
    UniqueEmailId uniqueidentifier NOT NULL 
    CONSTRAINT DF_YOUR_TABLE_UniqueEmailId DEFAULT newid()

You can then use the value created in your hyperlink, like so:

www.yoursite.com/emailid=400c4d0d-cd5a-42fd-8660-15250e27c247

Then once it's clicked, you simply look up the user/email related to that unique identifier and serve your page.

This would circumvent the requirement of passing sensitive information that you have to encrypt/decrypt in hyperlinks.

Encrypting / Decrypting a passed value in SQL Server

3 Answers3