Missy Missy - 28 days ago 7
C# Question

Encryption with Parameters C# SQL Server Not Saving Properly

I am trying to insert encrypted data into an SQL table. I am trying to save two random numbers. Here is my code for generating the random numbers:

Random randomNumber = new Random();
int key1 = randomNumber.Next(10000000, 99999999);
Thread.Sleep(1000);
Random randomNumber2 = new Random(DateTime.Now.Second);
int key2 = randomNumber2.Next(10000000, 99999999);


Then I insert it into the table. This first set of code works but I can't use it because it doesn't work with my random numbers. It does however confirm that I have it mostly right:

SqlCommand cmd = new SqlCommand(@"OPEN SYMMETRIC KEY KeyCodesKey DECRYPTION BY CERTIFICATE KeyCodes;
insert into keyfile(encrypted_key1, encrypted_key2, startingkeydate) values (EncryptByKey(Key_GUID('KeyCodesKey'), '19630515'), EncryptByKey(Key_GUID('KeyCodesKey'), '19520921'), CURRENT_TIMESTAMP)", cx);
int success = cmd.ExecuteNonQuery();


This is the code I would prefer to use but I can't seem to get it to work:

SqlCommand cmd = new SqlCommand(@"OPEN SYMMETRIC KEY KeyCodesKey DECRYPTION BY CERTIFICATE KeyCodes;
insert into keyfile(encrypted_key1, encrypted_key2, startingkeydate) values (EncryptByKey(Key_GUID('KeyCodesKey'), @key1), EncryptByKey(Key_GUID('KeyCodesKey'), @key2), CURRENT_TIMESTAMP)", cx);
cx.Open();
cmd.Parameters.AddWithValue("@key1",key1.ToString());
cmd.Parameters.AddWithValue("@key2", key2.ToString());
int success = cmd.ExecuteNonQuery();


I am not getting any errors but when I look at the unencrypted data, I just get a single digit number. If I hard code the parameters, it does not work either. It only works if the actual numbers are in the SqlCommand.

Any help or advice would be greatly appreciated!

Answer Source

This is the code I ended up using:

    SqlCommand cmd = new SqlCommand(@"OPEN SYMMETRIC KEY KeyCodesKey DECRYPTION BY CERTIFICATE KeyCodes; 
    insert into keyfile(encrypted_key1, encrypted_key2, startingkeydate) values (EncryptByKey(Key_GUID('KeyCodesKey'),"+key1.ToString()+@"), EncryptByKey(Key_GUID('KeyCodesKey'), "+key2.ToString()+@"), CURRENT_TIMESTAMP)", cx);
    cx.Open(); 
    int success = cmd.ExecuteNonQuery();
    cx.Close()

Since this is a Windows Forms application and there is no possibility of injection issues, this is the best solution I could come up with that works.