CODEWITHSUNDEEP
sqlsql-serverclassencryption
loveprogramming
loveprogramming

Reputation: 578

Encrypt query string in SQL possible?

please give me some advice. I have to send email using SQL database mail. In my email, I have to create links that a user can click to go to a product page on my site. However, I need to encrypt the ID of the product and once the product page load, the querystring of the product ID will be decrypted.

How can I get the encryption algorithm which I created in web site class into SQL? I believe the decryption and encryption algorithm must be the same, so that my page can decrypt correctly when the user click on the link in their email. The encryption class I use is something similar to this link: http://www.codeproject.com/Articles/33350/Encrypting-Query-Strings

Thanks a lot for your help.

PS: I also posted this question on asp.net forum.

Upvotes: 2

Views: 1903

Answers (2)

McGarnagle
McGarnagle

Reputation: 102723

You can run CLR (ie .NET) code within SQL Server. There are several steps involved, but it starts with creating an "SQL Server" project within Visual Studio, writing the code, and deploying it to your SQL Server. You also have to enable CLR functions within SQL Server using sp_configure.

See here (MSDN) for detailed instructions: How to: Create and Run a CLR SQL Server User-Defined Function .

Upvotes: 3

loveprogramming
loveprogramming

Reputation: 578

After doing quite some research, I can confirm that CLR is the way to go. As suggested, I used user defined function CLR. First I need to create CLR project in visual studio. Here is a good starting point to understand how it works:

http://blog.sqlauthority.com/2008/10/19/sql-server-introduction-to-clr-simple-example-of-clr-stored-procedure/

Then instead of adding a CLR stored procedure I move to User defined function. Add an encryption class and called the class in the user defined function. Build and Deploy. That's it.

Here is another useful links:

http://channel9.msdn.com/Learn/Courses/SQL2008R2TrainingKit/CLR/CLRIntegrationLab/Exercise-1-Creating-User-Defined-Functions-Using-SQL-CLR

Thanks dbaseman for pointing that out.

Upvotes: 2

Related Questions