asked
Encrypting query string containing users email to unsubscribe in a Stored Proc (SS 2008R2) ?
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 = '
sp_send_dbmail should be used in place of the sp_oa* methods for sending emails. What is the concern with using plain text for the email - that a hacker might randomly submit email addresses to the page and forcibly unsubscribe your users? Of the methods you have outlined, I would lean towards the GUID approach. If you choose to encrypt the email, you should reconsider and choose a 1 way hash with salt ([
http://www.troyhunt.com/2013/03/should-websites-be-required-to-publicly.html][1]). Use the hash just like you would have used the GUID (pre-hash the salted email addresses in the table then use those values to match against the hash that is being unsubscribed). Will your unsubscribe page be using https? [1]:
http://www.troyhunt.com/2013/03/should-websites-be-required-to-publicly.html
I spoke to my tech manager and also suggested using a guid assigned for the users email that altering in the query string shouldn't pose a risk as he and our end-users are concerned about. I see this being used on many websites. We are still looking into it. We need to have HTML formatted emails with an logo, HTML table, colors and hyperlinks which is why we need cdosysmail. I'll try sp_send_dmail which we use for alerts and notifications with the html string as you suggested, but my experience it wasn't as flexible. No we wont be using https for the unsubscribe. Thanks also for the HASH + SALT suggestion I will research this. Other options we are considering are using: 1. sending the user an email confirmation to click to become disabled the same way we are confirming them when subscribing. 2. On the same page users subscribes allow them to unsubscribe also. 3. Encrypting email or username and using that value in the query string Thanks again George
15 People are following this question.
