SQL Server Encryption/Decryption with C#

Question

I am working on a project and the next thing I want to learn is about adding sensitive data, and encrypting/decrypting it.

I Have done the basics on this already with the below code (Using SQL SERVER):

--STEP 1, CREATE DATABASE MASTER KEY
USE DatabaseTest
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='securepassword'
GO

--STEP 2, CREATE A CERTIFICATE
CREATE CERTIFICATE creditcardcert WITH SUBJECT='creditcardcert'
GO

--STEP 3, CREATE SYMMETRIC KEY
CREATE SYMMETRIC KEY CCreditCard_KEY WITH ALGORITHM = triple_des ENCRYPTION BY CERTIFICATE creditcardcert
GO

--STEP 4, CREATE CCEncrypted ENCRYPTED COLUMN
ALTER TABLE Person ADD CCEncrypted varbinary(128)
GO

--STEP 5, ENCRYPT DATA
OPEN SYMMETRIC KEY CCreditCard_KEY 
DECRYPTION BY CERTIFICATE creditcardcert

UPDATE Person SET CCEncrypted = ENCRYPTBYKEY(KEY_GUID('CCreditCard_KEY'), CreditCardNumber)

CLOSE SYMMETRIC KEY CCreditCard_KEY
GO

Now I know I can use this to encrypt the data, and I have a query to decrypt it also with SQL Server, however I need to do this at C# level when the data is being added.

For example I have a C# Windows Form which can accept a Firstname, Surname, CredCardNumber). This data when added is saved into an SQL Server table, my question now is how can I encrypt this data as it is saved, and if I want to view it, can I decrypt it at the push of a button (for now)?

Thanks for any help

--IN ADDITION im a student doing my Final Year Project so using services like that would lower my marks for the programming considerably (although good for the research side) Essentially I want to be able to store the certain data in the SQL Server table as encrypted, so unless I write a decrypt statement i cannot see it in SQL Server. But as the application requires a password anyway and will have permission levels, then is there an alternate solution so I can still see/add/edit this data not encrypted in my application? if you get me?

and an example of adding the data in C#:

System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection("SomeConnectionString")

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandText = "Insert Into Person(Firstname, Surname, CCNumber) Values (@Firstname, @Surname, @CCNumber)";
cmd.Connection = cn;

cmd.Parameters.AddWithValue("@Firstname", txtFirstname.Text);
cmd.Parameters.AddWithValue("@Surname", txtSurname.Text);
cmd.Parameters.AddWithValue("@CCNumber", txtCCNumber.Text); //ENCRYPT ME
try
{
   cn.Open();
   cmd.ExecuteNonQuery();
   lblStatus.Text = "Item Inserted";
}
catch(Exception ex)
{
   lblStatus.Text = ex.ToString();
}
finally
{
   cn.Close(); 
}

Answer 1

I took a different approach as this was becoming to complicated encoding and decoding all of the time and it would have slowed my system down. Instead I have gone for a JavaScript approach on the front end, and with the use of CryptoJS

https://code.google.com/p/crypto-js/

I have managed to encrypt my data using this code snippet below taken from the above link:

DES, Triple DES DES is a previously dominant algorithm for encryption, and was published as an official Federal Information Processing Standard (FIPS). DES is now considered to be insecure due to the small key size.

default.html code

<script src="http://crypto-js.googlecode.com/svn/tags/3.1.2/build/rollups/tripledes.js"></script>

screen code

var encrypted = CryptoJS.DES.encrypt("Message", "Secret Passphrase");
var decrypted = CryptoJS.DES.decrypt(encrypted, "Secret Passphrase");

Triple DES applies DES three times to each block to increase the key size. The algorithm is believed to be secure in this form.

default.htm file

<script src="http://crypto-js.googlecode.com/svn/tags/3.1.2/build/rollups/tripledes.js"></script>

screen created code

var encrypted = CryptoJS.TripleDES.encrypt("Message", "Secret Passphrase");

var decrypted = CryptoJS.TripleDES.decrypt(encrypted, "Secret Passphrase");

I have managed to save the data as encrypted, and then on the view, edit and browse screens, decrypt it either on a user permissions base, or it can be done on for all data in the application. the code is therefore safe (as can be) in the database and without the encryption key ("Secret Passphrase") in the example above, it cannot be decrypted