Encryption is used to ensure the security of sensitive data, such as user passwords and bank card passwords.
1. The simplest encryption method is to encrypt a single character or column and use HashBytes to return the input hash value. Syntax: HashBytes ('encryption type', 'encrypted text') returns a maximum value of varbinary (8000)
The following example assumes that the AdventureWorks sample database has been installed.
-- Create a sample table and fill in data
Set nocount on;
Select CreditCardID, CardNumber into dbo. t1 from Sales. CreditCard;
Go
-- Add a column to store the encrypted Hash value of the card number.
Alter table dbo. t1 add md2Number varbinary (20) null;
Alter table dbo. t1 add md4Number varbinary (20) null;
Alter table dbo. t1 add md5Number varbinary (20) null;
Alter table dbo. t1 add shaNumber varbinary (20) null;
Alter table dbo. t1 add sha1Number varbinary (20) null;
Go
-- Encryption
Update dbo. t1 set
Md2Number = HashBytes ('md2', CardNumber ),
Md4Number = HashBytes ('md4', CardNumber ),
Md5Number = HashBytes ('md5', CardNumber ),
ShaNumber = HashBytes ('sha', CardNumber ),
Sha1Number = HashBytes ('sha1', CardNumber );
Go
Select top (10) * from dbo. t1;
Point: the sha and sha1 encryption results are the same in the result. HashBytes encryption is irreversible, that is, it can only be modified, but cannot retrieve the pre-encryption value.
A varchar character. It is different from the Hash value returned by a nvarchar character (reason: it occupies different bytes), for example:
Select HashBytes ('md5', 'SQL Server 100') as md5varchar, HashBytes ('md5', n' SQL Server 100') as md5nvarchar; 2. the advantages of phrase encryption can be decrypted, and the security is higher than Hash encryption. If you forget the phrase, it will not be saved.
-- Fill data
Set nocount on;
Select CreditCardID, CardNumber into dbo. t2 from Sales. CreditCard;
Go
-- Add encrypted Columns
Alter table dbo. t2 add PhraseNumber varbinary (256) null;
Go
-- Encryption
Update dbo. t2 set PhraseNumber = EncryptByPassPhrase ('phrasetext ', CardNumber, 1, convert (varbinary (30), CreditCardID ));
Go
-- View the encrypted result
Select * from dbo. t2;
Go
-- Decryption
Select CreditCardID, CardNumber, PhraseNumber,
Convert (nvarchar, DecryptByPassphrase ('phrasetext ', PhraseNumber, 1, convert (varbinary (30), CreditCardID) as "Decrypted card number"
From dbo. t2;
Go 3: Certificate public key encryption advantages: the password is more complex and secure. Disadvantages: high overhead, and the certificate must be maintained.
-- Create a self-signed certificate. This example describes encryption. For more information about other certificate types, see related articles.
Create certificate cer001
Encryption by password = n '! @ # $ % ^ &*'
With subject = 'cer _ encryption ',
Start_date = '123 ',
Expiry_date = '20140901 ';
Go
-- Fill data
Set nocount on;
Select CreditCardID, CardNumber into dbo. t3 from Sales. CreditCard;
Go
-- Add encrypted Columns
Alter table dbo. t3 add CerEncryptionENumber varbinary (256) null;
Go
-- View unencrypted results
Select * from dbo. t3;
Go
-- Encryption
Update dbo. t3 set CerEncryptionENumber = EncryptByCert (Cert_ID ('cer001'), CardNumber );
Go
-- View the encrypted result
Select * from dbo. t3;
Go
-- View the decrypted result
Select top (10) CardNumber as 'original card No ',
Convert (nvarchar (30), DecryptByCert (Cert_Id ('cer001'), CerEncryptionENumber, n '! @ # $ % ^ & * ') As 'decrypted'
From dbo. t3;
Go 4 asymmetric key encryption, decryption disadvantage: high overhead, not used for processing large datasets
Create asypolicric key asym_key_001
With algorithm = RSA_2048
Encryption by password = n '! @ # $ % ^ &*';
Go
-- Fill data
Set nocount on;
Select CreditCardID, CardNumber into dbo. t4 from Sales. CreditCard;
Go
-- Add encrypted Columns
Alter table dbo. t4 add AsymKeyEncryptionENumber varbinary (256) null;
Go
-- View unencrypted results
Select * from dbo. t4;
Go
-- Encryption
Update dbo. t4 set AsymKeyEncryptionENumber = EncryptByAsymKey (AsymKey_ID ('asym _ key_001 '), CardNumber );
Go
-- View the encrypted result
Select * from dbo. t4;
Go
-- View decrypted data
Select CardNumber, convert (nvarchar (30), DecryptByAsymKey (AsymKey_Id ('asym _ key_001 '), AsymKeyEncryptionENumber, n '! @ # $ % ^ &*'
) As DecryptedData
From dbo. t4;
Go 5 symmetric key encryption: the decryption algorithm must be in the operating system. The encryption speed is fast and the decryption syntax is simple.
-- Use the DESX algorithm to create a symmetric key named sym_key_001, and then use the certificate cer001 to encrypt the new key.
Create policric key sym_key_001
With algorithm = DESX
Encryption by certificate cer001;
Go
-- Fill data
Set nocount on;
Select CreditCardID, CardNumber into dbo. t5 from Sales. CreditCard;
Go
-- Add encrypted Columns
Alter table dbo. t5 add SymKeyEncryptionENumber varbinary (256) null;
Go
-- View unencrypted results
Select * from dbo. t5;
Go
-- Encryption
Open encryption Ric key sym_key_001 decryption by certificate cer001 with password = n '! @ # $ % ^ &*';
Update dbo. t5 set SymKeyEncryptionENumber = EncryptByKey (Key_GUID ('sym _ key_001 '), CardNumber );
Go
-- View the encrypted result
Select * from dbo. t5;
Go
-- View decrypted data
Open encryption Ric key sym_key_001 decryption by certificate cer001 with password = n '! @ # $ % ^ &*';
Select CardNumber, SymKeyEncryptionENumber, convert (nvarchar (30), DecryptByKey (SymKeyEncryptionENumber) as DecryptedData
From dbo. t5;
Go