Reputation: 5990
I am working on database encryption, i.e. cell based symmetric encryption in SQL server 2008 Express. But the problem is that the parameterized queries for insertion are not working. Help me please . . .
Edit:
I am using the following query as Example:
foreach (var list in from DataRow row in dataTable.Rows select new ArrayList
{
String.Format("@var1, {0}", row["Column1"]),
String.Format("@var2, {0}", row["Column2"]),
String.Format("@var3, {0}", row["Column3"])
})
{
var query = String.Format(@"OPEN SYMMETRIC KEY {0} DECRYPTION BY CERTIFICATE {1}
INSERT INTO TableA (Column1, Column2, Column3) VALUES (@ENCRYPTBYKEY(KEY_GUID('symKey'), '{2}'), ENCRYPTBYKEY(KEY_GUID('symKey'), '{3}'), ENCRYPTBYKEY(KEY_GUID('symKey'), '{4}'))", symKey, symCer, "@var1", "@var2", "@var3");
con.Execute.ExecuteParameterizedQuery(query, list);
}
public string ExecuteParameterizedQuery(string query, ArrayList parametersList)
{
errorFlag = Connect(un, pasw, 3);
if ((String.CompareOrdinal(errorFlag, "Open") == 0))
{
var myTran = myConnection.BeginTransaction();
cmd = new SqlCommand(query, myConnection) { Transaction = myTran };
for (var i = 0; i < parametersList.Count; i++)
{
var split = parametersList[i].ToString().Split(',');
cmd.Parameters.AddWithValue(split[0], split[1]);
}
try
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
myTran.Commit();
errorFlag = string.Empty;
}
catch (Exception e)
{
errorFlag = e.Message;
}
finally
{
myConnection.Close();
myConnection.Dispose();
}
return errorFlag;
}
myConnection.Close();
myConnection.Dispose();
return errorFlag;
}
EDIT 2:
CREATE PROCEDURE ng_encryptString
(
@PlaneText VARCHAR(500), @SipherText VARBINARY(2000) OUT
)
AS
BEGIN
OPEN SYMMETRIC KEY symKey DECRYPTION BY CERTIFICATE SymCert
SELECT ENCRYPTBYKEY(KEY_GUID('SymKey'), @PlaneText)
END
Upvotes: 0
Views: 2389
Reputation: 13486
Here what I would suggest is,
CREATE a procedure with
INSERT INTO TableA (Column1, Column2, Column3) VALUES(@val1,@val2,@val3)
Now create a encryptThestring(string val)
function in C# and it should return you the encrypted values of the given normal input value.
Finally call the procedure in C# and pass the parameter as
encryptThestring(string val1)
encryptThestring(string val2)
encryptThestring(string val3)
This is the C#.net function
public string EncryptString(string val)
{
SqlConnection sqlconn = new SqlConnection("conn_string");
sqlconn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlconn;
cmd.CommandText = "ng_encryptString"; // This is the sproc which will encrypt the string
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param1 = cmd.Parameters.Add("inpuStr", SqlDbType.VarChar, 500);
param1.Direction = ParameterDirection.Input;
SqlParameter param3 = cmd.Parameters.Add("@encryptedStr", SqlDbType.VarChar, 2000);
param3.Direction = ParameterDirection.Output;
param1.Value = val;
cmd.ExecuteNonQuery();
sqlconn.Close();
return (string)param3.Value;
}
sproc ng_encryptString
CREATE Procedure [dbo].[ng_encryptString]
@string varchar(255),
@encryptedStr varbinary(2000) OUTPUT
As
Begin
Declare @res varbinary(2000)
IF NOT EXISTS(select * from sys.symmetric_keys where name='##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'yourpassword'
IF NOT EXISTS(select * from sys.certificates where name='EncryptTestCert')
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'yoursubject'
IF NOT EXISTS(select * from sys.symmetric_keys where name='TestTableKey')
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT @encryptedStr=ENCRYPTBYKEY(KEY_GUID('TestTableKey'),@string)
end
Upvotes: 1