Encrypt query string in SQL possible?

Question

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.

There is no such thing as a SQL database -- SQL is the Structured Query Language - a language used by many database systems, but it's not a database product... so are you saying SQL and you really mean SQL Server (the Microsoft RDBMS product) by that? — marc_s
– marc_s, Commented Jun 13, 2012 at 5:27

McGarnagle · Accepted Answer · 2013-10-08 23:14:11Z

3

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 .

edited Oct 8, 2013 at 23:14

answered Jun 13, 2012 at 5:04

McGarnagle

103k31 gold badges235 silver badges264 bronze badges

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

1 Comment

loveprogramming Over a year ago

It is a good starting point for me to do some research into this. Thanks.

loveprogramming · Accepted Answer · 2012-06-25 21:44:24Z

2

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.

answered Jun 25, 2012 at 21:44

loveprogramming

6282 gold badges6 silver badges22 bronze badges

1 Comment

xelco52 Over a year ago

If dbaseman provided the answer that allowed you to solve this problem, it would be good form to "accept" his answer by clicking the checkmark on the left side of his post.

Collectives™ on Stack Overflow

Encrypt query string in SQL possible?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related