Encrypting the query string containing users email to unsubscribe in a Stored Proc (SS 2008R2) ??

[George Lewycky]

We are trying to encrypt the users email in a stored proc in SQL Server 2008R2 using cdosysmail to allow them to unsubscribe using encrypted query string
to prevent hacking, etc.

And also in the stored procedure to DEcrypt the query string back to
validate and/or update the record to unsubscribe.


SET @unsubfooter =
'<br>' + ' <a href="http://www.yada.com/Unsubscribe.aspx?email=' +
cast(@vendemail as varchar) + '">' + 'Click here to Unsubscribe </a> ';

Any ideas or suggestions or experiences appreciated

Sincerely

George

[Erland Sommarskog]

Post by George Lewycky
We are trying to encrypt the users email in a stored proc in SQL Server
2008R2 using cdosysmail to allow them to unsubscribe using encrypted
query string to prevent hacking, etc.
And also in the stored procedure to DEcrypt the query string back to
validate and/or update the record to unsubscribe.
'<br>' + ' <a href="http://www.yada.com/Unsubscribe.aspx?email=' +

It might be that this is not my business domain, but I don't really
understand what you want to do.

Is the purpose to generate a unsubscribe link which is unique to
the recipent, but which does not disclose the identity or the
email address of the recipent? Why not just generate a GUID and put
that in the link and when you receive the unsubcribe request you
look the GUID to see which email it maps to? If you don't want to
repeat the GUID, you can generate a new each time, and have multiple
GUIDs to map to the same recipent.

[George Lewycky]

Post by Erland Sommarskog

Post by George Lewycky
We are trying to encrypt the users email in a stored proc in SQL Server
2008R2 using cdosysmail to allow them to unsubscribe using encrypted
query string to prevent hacking, etc.
And also in the stored procedure to DEcrypt the query string back to
validate and/or update the record to unsubscribe.
'<br>' + ' <a href="http://www.yada.com/Unsubscribe.aspx?email=' +

It might be that this is not my business domain, but I don't really
understand what you want to do.
Is the purpose to generate a unsubscribe link which is unique to
the recipent, but which does not disclose the identity or the
email address of the recipent? Why not just generate a GUID and put
that in the link and when you receive the unsubcribe request you
look the GUID to see which email it maps to? If you don't want to
repeat the GUID, you can generate a new each time, and have multiple
GUIDs to map to the same recipent.
--

The GUID idea was a consideration but we can generate several thousand email announcements sporadically, so assigning a GUID when their account is created and referencing that GUID might be what we do.
But my boss wanted the encryption.

I also thought each registered user have a GUID stored and use that GUID in the
stored procedure generating the hyperlink + query string in the email to pass to the asp page and ultimately the stored procedure

[Erland Sommarskog]

Post by George Lewycky
The GUID idea was a consideration but we can generate several thousand
email announcements sporadically, so assigning a GUID when their account
is created and referencing that GUID might be what we do.
But my boss wanted the encryption.

Why? I can only see added complexity with no benefit. Why would you send
out an encrypted string? To see if any user is smart enough to crack it?

It would be a different thing if it something you store that you don't want
intruders to see easily.