Encrypt Sensitive Data in SQL Server 2005

SQL Server 2005 provides easier functions for encrypting and decrypting user sensitive information such as credit card numbers or bank account details, so as to deter any hacking attempts.

Data encryption in SQL Server 2005 can be done either by using password mechanism or by making use of keys and certificates. These methods are as follows:

1.Encryption by PassPhrase
This is a simple method in which we use the SQL method EncryptByPassPhrase('password','original_value') with our insert,update,select queries.

For an example suppose we have a table named user_info with the column named credit_card_no (varchar) in which we have to stored the encrypted credit card no then the query would be as follows:

Update user_info set credit_card_no=EncryptByPassPhrase('password',@credit_card_no);

where password is the key used to generate the encrypted value.
The same key is used to decrypt the credit_card_no as follows:

Select DecryptByPassPhrase('password',credit_card_no) as decrypted_no from user_info.

Note:The password has to be protected and remembered by the programmer. Hence can be vulnerable.


2.Encryption by Keys

The limitation of encryption by passphrase method is that we have to supply the password each time the data has to be accessed. But if we encrypt our symmetric key with a certificate then we won't have to pass the passphrase each time. To create a key or its certificate, we must first create or open the master key for the database.
The following command creates a master key:

create master key encryption by password='password';

Now we can create a certificate and then a symmetric key that is attached to that certificate. The following SQL script creates the certificate 'DemoCert' and a key 'DemoKey' associated with that certificate:

create certificate DemoCert with subject='Demo Certificate;

create symmetric key DemoKey with algorithm=AES_256 encryption by certificate DemoCert;

Now that we possess a key we can do encryption using the EncryptByKey() method and considering the previous table user_info as follows:

open symmetric key DemoKey decryption by certificate DemoCert;
update user_info set credit_card_no=EncryptByKey(Key_GUID('DemoKey'),@credit_card_no);

Similarly we can decrypt it as follows:

open symmetric key DemoKey decryption by certificate DemoCert;
select cast(DecryptByKey(credit_card_no) as varchar(16)) as decrypted_no from user_info;

This is a lenghty method but is very secure as we do not have to pass the password for the process of encryption/decryption.


So this is one advance features of SQL Server 2005 that not many of us use. But it can surely come in handy when dealing with large user database that requires some security features!!

Directory, Files Listing using GridView

Web Hosting Control Panel Type Directory Listing!!
Have you ever seen a Control Panel provided for a web hosting account? Notice the way they use, to show all the files and folders inside your sites folders? Well just in case you were wondering how to do the same using ASP.NET 2.0 then let me tell you that i have achieved the same using quiet a simple technique that i found Googling around!

The following piece of code demonstrated how by using the System.IO namespace and the ASP.NET 2.0 GridView, we can achieve the task of folder/files listing very easily........

ASPX Page:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" />

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="true" />


Code Behind:

protected void page_load()
{
ListFolder();
}

protected void ListFolder()
{
string basepath="~/myfolder";
DirectoryInfo dirInfo = new DirectoryInfo(Server.MapPath(basepath));

//For All Files
FileInfo[] fileInfo = dirInfo.GetFiles("*.*",SearchOptions.AllDirectories);

//For All Subdirectories
DirectoryInfo[] subDirInfo = dirInfo.GetDirectories("*.*",AllDirectories);

GridView1.DataSource = fileInfo;
GridView1.DataBind();

GridView2.DataSource = subDirInfo;
GridView2.DataBind();
}

As you can see this method takes very little code and you can even customize it to provide delete functionality. Furthermore by combining the files and folders dataset we can achieve a complete directory listing.

Free Shopping Cart Solutions

Free Shopping Carts - OsCommerce, Cre Loaded, Magento....
Its been days since i have return my last post. I was kept really busy by my employer. To be honest i was also a bit lazy in thinking of something new for my blog.
None of the less i spent a lot of my recent time working on free Shopping Cart Applications.

There are some really interesting points that i would like to share about these Apps.
First of all the very thing that came to my mind is that "If people are providing good free software on the internet then what will happen to us hard working Software Developers".

To back up my thoughts i would like to press the points that these free Shopping Cart solutions provide a great bit of functionality, something that would take a lot of time to build from the scratch. Also the communities are pretty active and provide further customization support.
A few of their cool features include.
1.CMS support
2.Paypal , Authorize.Net etc payment modules.
3.FedEx , UPS, UPSC etc shipping modules.
4.Multilingual Suppport.
5.Multi Store Support (Magento)
6. And off course great catalog support

The sad part for me was that the only major thing that i was doing is understanding the structure, design integration and bug solving!!

There were times when i was really scratching my brains trying to search solution to a simple problem and just couldn't find it. Yes and Magento was the one to be blamed!!!

But the fact that not may people are aware of these Free Applications (yes these people are the end clients), having knowledge of customizing them can really be very handy.

Here's a short rating of the apps based on my experience of handling them:
1.CRE Loaded - Great out of the box and easy to setup, tech support
2.Magento - Huge functionality provided but takes ages to understand and setup due to overly streched mechanism. Also functions really Slowly(and i mean really slowly compared to any PHP site)
3.Oscommerce - Base version of cre loaded. Needs lot of customization.
4.Joomla-Virtue Mart - Has the power of Joomla with it. Good for setting up small store.

Windows Media Service

Windows Media Services For Website
Well its been some time since i have updated my blog, i had my own reasons. Any way this is something that i was working on this month.

My main goal was to create a live audio/video broadcast over the internet and show it on my web page.

Well i searched high and low over the internet and the best possible solution that i found was the Windows Media Services!

Yes! Its very simple to setup and not much programing need. All you need is a web server(can even be your own system), the Windows Media Encoder software and a Web page with a media player that plays the audio-video stream.

You can loads of info on setting up the Windows Media Encoder on the internet.
Now the HTML required for setting up the Active X media player control on your web page is as follows:

<object ID="MediaPlayer" WIDTH="320" HEIGHT="270"
CLASSID="CLSID:22D6f312-B0F6-11D0-94AB-0080C74C7E95" STANDBY="Loading
Windows Media Player components..." TYPE="application/x-oleobject"
CODEBASE="http://activex.microsoft.com/activex/controls/mplayer/en/nsmp2inf.cab#Version=6,4,7,1112">

<param name="autoStart" value="True">
<param name="filename" value="http://myserver.mystream.com:1121">
<param NAME="ShowControls" VALUE="False">
<param NAME="ShowStatusBar" VALUE="False">
<embed TYPE="application/x-mplayer2" SRC="myserver.mystream.com:1121"
NAME="MediaPlayer" WIDTH="320" HEIGHT="270" autostart="1"
showcontrols="0">
</embed>
</object>

Please note that the address:" myserver.mystream.com:1121" is generated by the media encoder software based on the Web Server and the network port(1121) you select for broadcasting!

Discovering Twitter

Twitter as we all know is the latest buzz word talked about over the Internet. For those who don't know Twitter is a free social networking service that allows its users to send and read other users' updates (otherwise known as tweets). You can read a hell lot about it on Wikipedia.

Excited and eager to check out this new website i immediately created and account and started tweeting(Check Out here). Guess what Twitter even has big celebrities like Britney Spears and popular people like the new US President Barak Obama having their own Twitter account(i don't think they actually post there though) which were recently Hacked!!

Initially i found the idea of tweeting quiet original and unique. But as with many things it soon got boring. The fact that i had to get people to follow me in order to view their tweets and updates is just so repeating procedure. Even most of my friends didn't reply to the invitations that i sent. For many people are getting tired of Social Networking sites.

But to be honest i was quiet disappointed !

Eventually i have discovered a good use of Twitter and that's using it to promote your site/blog and following some famous, geeky, new etc etc people!
Lets see if twitter can be the next big thing on the WEB especially since it has competitions like MySpace, Facebook & Orkut.