亂馬客

https://rainmakerho.github.io/

2012-10-11

[.NET]為資料做加解密處理-整合篇

[.NET]為資料做加解密處理-整合篇

前言

常常有人看了 [SQL]為資料做加解密處理 透過SQL SERVER的加解密處理方式後，會問說，那AP要如何整合起來呢? 以下筆者會一步步介紹如何整合進系統。

 

資料準備

使用 [SQL]為資料做加解密處理 的範例(資料表Employee)。


--1.產生對稱金鑰並設定密碼為 rainmaker
CREATE SYMMETRIC KEY DB_KEY1 WITH ALGORITHM = TRIPLE_DES 
    ENCRYPTION BY PASSWORD = 'rainmaker'
GO
 
--2.建立測試的Employee TABLE
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Employee]') AND type IN (N'U')) 
BEGIN 
   DROP TABLE [Employee] 
END 
GO 
CREATE TABLE [Employee]( 
   [EmployeeID] [int] NOT NULL PRIMARY KEY, 
   [FirstName] VARCHAR(250) NOT NULL, 
   [LastName] VARCHAR(250) NOT NULL, 
   [BirthDay] DATE,
   [Salary] MONEY,
   [EN_BirthDay] VARBINARY(8000), --放BirthDay加密的資料
   [EN_Salary] VARBINARY(8000)  --放Salary加密的資料
)  
GO 
 
--3.建立Trigger，存入資料時，將資料加密放到EN_開頭的欄位之中
CREATE TRIGGER TR_Employee
ON [Employee]
FOR INSERT, UPDATE
AS 
BEGIN
    --取得金鑰名稱
    DECLARE @KeyGUID AS UNIQUEIDENTIFIER
    SELECT @KeyGUID = KEY_GUID('DB_KEY1')
    
    --將資料放到加密欄位之中，並更新原本的欄位為其他值
    UPDATE [Employee]
        SET 
            [EN_BirthDay]= ENCRYPTBYKEY(@KeyGUID, CAST(i.BirthDay AS varbinary) )
            , BirthDay = '1970/10/10'
            , [EN_Salary]= ENCRYPTBYKEY(@KeyGUID, CAST(i.Salary AS varbinary) )
            , Salary = 0
            FROM inserted i 
                JOIN [Employee] ON(i.EmployeeID=[Employee].EmployeeID)
END;
 
--4.建立將資料解密的View
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[vwEN_Employee]'))
DROP VIEW [vwEN_Employee]
GO


CREATE VIEW [vwEN_Employee]
AS
SELECT [EmployeeID], [FirstName], [LastName]
, [BirthDay] = CAST(DECRYPTBYKEY([EN_BirthDay]) AS DATE) --將資料解密轉成DATE
, [Salary] = CAST(DECRYPTBYKEY([EN_Salary]) AS MONEY) --將資料解密轉成DECIMAL
FROM [Employee];
 
--5.開始測試
--5.1.為對稱金鑰解密，讓它能夠使用。
OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker';
 
--5.2.新增員工資料
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [BirthDay], [Salary]) 
VALUES (1, N'Eric', N'Lin', '1965/4/3', 47000 );
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [BirthDay], [Salary]) 
VALUES (2, N'Lily', N'Kuo', '1970/10/10', 51000 );
 
--5.3.查詢員工的資料
SELECT * FROM Employee;

--5.4.透過解密的View來查詢員工資料
SELECT * FROM vwEN_Employee;

--5.5.關閉對稱金鑰
CLOSE SYMMETRIC KEY DB_KEY1;

 

實作

1.建立一個Windows Form應用程式，在Form上面拉了一些控制項，說明如下，

image

bindingSource1 設定控制項的Binding
dataGridView1 顯示Employee的資料
lblEmployeeId 顯示Employee某筆資料的EmployeeID
txtSalary 顯示Employee某筆資料的Salary
lblFirstName 顯示Employee某筆資料的FirstName
btnGetEmployee 取得vwEN_Employee的資料，但是沒有先OPEN SYMMETRIC KEY
btnGetEmployeeWithKey 先OPEN SYMMETRIC KEY，取得vwEN_Employee的資料，最後再CLOSE SYMMETRIC KEY
btnUpdEmpWithKey 先OPEN SYMMETRIC KEY，更新Employee的資料，最後再CLOSE SYMMETRIC KEY

 

2.程式的初始設定及控制項的Binding設定


private string ConnectionString
{
    get
    {
        return System.Configuration.ConfigurationManager.ConnectionStrings["DEMO"].ConnectionString;
    }
}

private void SetControlBindings(DataTable employeeData)
{
    bindingSource1.DataSource = employeeData;
    dataGridView1.DataSource = bindingSource1;
    txtSalary.DataBindings.Clear();
    txtSalary.DataBindings.Add("Text", bindingSource1, "Salary");
    lblEmployeeId.DataBindings.Clear();
    lblEmployeeId.DataBindings.Add("Text", bindingSource1, "EmployeeID");
    lblFirstName.DataBindings.Clear();
    lblFirstName.DataBindings.Add("Text", bindingSource1, "FirstName");
}

 

3.直接從vwEN_Employee取得資料，呈現到畫面上，如下。


private void GetEmployeeDataWithoutKey()
{
    string queryString = @"SELECT [EmployeeID], [FirstName], [LastName], [BirthDay], [Salary] 
                            FROM vwEN_Employee";
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        SqlDataAdapter da = new SqlDataAdapter(queryString, conn);
        DataTable employeeData = new DataTable();
        da.Fill(employeeData);
        SetControlBindings(employeeData);
    }
    MessageBox.Show("GetEmployeeDataWithoutKey OK!");
}

image

從上圖可發現，如果直接從vwEN_Employee取得資料時，BirthDay及Salary欄位都是NULL。

 

4.加入OPEN/CLOSE SYMMETRIC KEY。

因為直接從vwEN_Employee取得資料時，會因為沒有OPEN SYMMETRIC KEY，而導致BirthDay及Salary欄位都是NULL。

所以在從vwEN_Employee取得資料前，先OPEN SYMMETRIC KEY，最後再CLOSE SYMMETRIC KEY，如下，


private void GetEmployeeDataWithKey()
{
    string queryString = @"SELECT [EmployeeID], [FirstName], [LastName], [BirthDay], [Salary] 
                            FROM vwEN_Employee";
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        try
        {
            conn.Open();
            //1.要先下 OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker';
            SqlCommand cmd = new SqlCommand(@"OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker'", conn);
            cmd.ExecuteNonQuery();
            //2.再取資料
            SqlDataAdapter da = new SqlDataAdapter(queryString, conn);
            DataTable employeeData = new DataTable();
            da.Fill(employeeData);
            SetControlBindings(employeeData);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            //3.最後，要把Key Close
            SqlCommand cmd = new SqlCommand(@"CLOSE SYMMETRIC KEY DB_KEY1", conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
    MessageBox.Show("GetEmployeeDataWithKey OK!");
}

image

 

5.新增或更新資料前，也要加入OPEN/CLOSE SYMMETRIC KEY，如下，


private void UpdateEmployeeDataWithKey()
{
    string queryString = @"UPDATE Employee SET Salary = @Salary Where EmployeeID = @EmployeeID";
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        try
        {
            conn.Open();
            //1.要先下 OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker';
            SqlCommand cmd = new SqlCommand(@"OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker'", conn);
            cmd.ExecuteNonQuery();
            //2.再將資料更新
            SqlCommand updCmd = new SqlCommand(queryString, conn);
            updCmd.Prepare();
            updCmd.Parameters.Clear();
            var empIdParam = updCmd.Parameters.Add("EmployeeID", SqlDbType.Int);
            empIdParam.Value = lblEmployeeId.Text;
            var salaryParam = updCmd.Parameters.Add("Salary", SqlDbType.Money);
            salaryParam.Value = txtSalary.Text;
            updCmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            //3.最後，要把Key Close
            SqlCommand cmd = new SqlCommand(@"CLOSE SYMMETRIC KEY DB_KEY1", conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
    MessageBox.Show("UpdateEmployeeDataWithKey OK!");
}

image

 

6.將OPEN/CLOSE SYMMETRIC KEY抽出成另外的Method。


private void OpenSymmetricKey(SqlConnection conn)
{
    //1.要先下 OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker';
    SqlCommand cmd = new SqlCommand(@"OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker'", conn);
    cmd.ExecuteNonQuery();
}

private void CloseSymmetricKey(SqlConnection conn)
{
    //3.最後，要把Key Close
    SqlCommand cmd = new SqlCommand(@"CLOSE SYMMETRIC KEY DB_KEY1", conn);
    cmd.ExecuteNonQuery();
}

 

結論

以上是透過Windows Form應用程式來示範如何應用SQL SERVER的加解密處理。

 

範例程式

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^

學習網站

pluralsight

wintellectnow

標籤雲

系列文章

最新留言