T-SQL Problem Solving highlights data encryption and decryption Complete Set

The following code has passed the test on the sample database of SQLServer2008

Question 1: How to encrypt and decrypt data to prevent users from stealing confidential data?
Some sensitive data, such as passwords and card numbers, cannot be stored using normal values. Otherwise, security risks may occur. In the past, front-end applications were used for encryption and decryption. Databases can only be encrypted and cannot be decrypted.

Data encryption and decryption at the database level is provided starting from 2005. The implementation methods are as follows:

1. Use CONVERT to change the encoding method:
Use this function to convert text or data to VARBINARY. However, this method does not provide the ability to protect data. It only prevents sensitive data from being directly viewed during data browsing.

2. Using symmetric keys:
Use EncryptByKey to encrypt data. Use the DecryptByKey function for decryption. This method is suitable for large data volumes. Because the process of symmetric keys is less resource-friendly.

3. Use asymmetric keys:
Use EncryptByAsymKey to encrypt data. Use the DecryptByAsymKey function for decryption. It is used to encrypt and decrypt data at a higher security level. Because many resources are consumed.

4. Use the credential:
Use EncryptByCert for encryption and DecryptByCert function for decryption. It is similar to an asymmetric key.

5. Password phrase:
Use EncryptBypassPhrase to encrypt and use the DecryptByPassPhrase function to decrypt the data. You can use meaningful phrases or other data rows as key words for encryption and decryption, which is suitable for general data encryption and decryption.

Case:
1. Convert mode:Copy codeThe Code is as follows: a) USE tempdb
B) GO
C) create table test
D )(
E) userID int identity (1, 1 ),
F) userName VARCHAR (10 ),
G) userSalary FLOAT,
H) cyberalary NVARCHAR (MAX)
I ));
J)
K) INSERT INTO TEST
L) (userName, userSalary)
M) VALUES ('taici', 1234 ),
N) ('hailong', 3214 ),
O) ('meiyuan', 1111)
P) -- alter table test
Q) -- ADD userNewSalary VARBINARY (512)
R) -- use the Conversion Function to convert data to varbinary and change the encoding method.
S) SELECT *,
T) CONVERT (VARBINARY (512), userSalary)
U) FROM test
V) -- convert data to int to restore the original encoding method.
W) SELECT *,
X) CONVERT (INT, userSalary)
Y) FROM test

2. symmetric key:Copy codeThe Code is as follows: a) -- create a symmetric key
B) USE AdventureWorks
C) GO
D) CREATE Your Ric KEY SymKey123
E) with algorithm = TRIPLE_DES encryption by password = 'P @ ssw0rd'
F) GO
G) -- Note: When enabled, you must first OPEN your Ric KEY and password, otherwise the generated data will be null. It must be used with the Key_GUID function.
H) -- enable symmetric keys
I) OPEN encryption Ric KEY SymKey123 DECRYPTION BY PASSWORD = 'P @ ssw0rd ';
J) -- encrypt data
K) SELECT *, ENCRYPTBYKEY (KEY_GUID ('symkey123'), CONVERT (VARCHAR (max), AddressLine1 ))
L) FROM Person. Address
M)
N) -- check the encrypted length and use the datalength () function
O) select datalength (ENCRYPTBYKEY (KEY_GUID ('symkey123'), CONVERT (VARCHAR (MAX), AddressLine1 )))
P) FROM Person. Address
Q) GO
R) -- Update encrypted data to another column
S) UPDATE Person. Address
T) SET AddressLine2 = ENCRYPTBYKEY (KEY_GUID ('symkey123'), CONVERT (VARCHAR (max), AddressLine1 ))
U) -- decryption: the decryption process also requires the OPEN encryption Ric KEY, and the DECRYPTBYKEY and CONVERT functions must be used.
V) OPEN encryption Ric KEY SymKey123 decryption by password = 'P @ ssw0rd ';
W)
X) SELECT AddressID, CONVERT (VARCHAR (MAX), CONVERT (VARCHAR (MAX), DECRYPTBYKEY (AddressLine2 )))
Y) FROM Person. Address

3. asymmetric key:Copy codeThe Code is as follows: a) -- asymmetric keys use two different keys. Therefore, encryption requires no password verification, but encryption requires
B) USE AdventureWorks
C) GO
D) CREATE asypolicric KEY AsymKey123 with algorithm = RSA_2048 encryption by password = 'P @ ssw0rd ';
E) GO
F)
G) -- add new columns to store encrypted data
H) alter table Person. Address ADD AddressLine3 nvarchar (MAX)
I) GO
J) -- Encrypt
K) SELECT *, ENCRYPTBYASYMKEY (ASYMKEY_ID ('asymkey123 '), CONVERT (VARCHAR (MAX), AddressLine1 ))
L) FROM Person. Address
M) GO
N)
O) -- update data to a new column
P) UPDATE Person. Address
Q) SET AddressLine3 = ENCRYPTBYASYMKEY (ASYMKEY_ID ('asymkey123 '), CONVERT (VARCHAR (MAX), AddressLine1 ))
R)
S)
T) SELECT * -- addressline3
U) FROM Person. Address
V)
W) -- decryption: the password must be used for decryption. The type here must be the same as that during encryption. For example, varchar can be used for encryption, but nvarchar cannot be used for decryption.
X) select top 10 AddressID, CONVERT (VARCHAR (MAX), CONVERT (VARCHAR (MAX), DECRYPTBYASYMKEY (ASYMKEY_ID ('asymkey123 '), AddressLine3, N 'P @ ssw0rd') AS Decryptedata
Y) FROM Person. Address

4. Certificate encryption:Copy codeThe Code is as follows: a) -- certificate encryption: first create a certificate (certificate)
B) CREATE CERTIFICATE certKey123 -- CERTIFICATE Name
C) encryption by password = 'P @ ssw0rd '-- PASSWORD
D) with subject = 'address Certificate ', -- Certificate description
E) START_DATE = '2014/1/18', -- certificate validity period
F) EXPIRY_DATE = '2014/1/18'; -- certificate expiration date
G) GO
H) -- Use Certificate Encryption
I) SELECT *, ENCRYPTBYCERT (CERT_ID ('certkey123'), CONVERT (VARCHAR (MAX), AddressLine1) cyberAddress
J) FROM Person. Address
K)
L) -- add new columns to store encrypted data
M) alter table Person. Address ADD AddressLine4 Nvarchar (MAX)
N)
O) -- put encrypted data into new columns
P) UPDATE Person. Address
Q) SET AddressLine4 = ENCRYPTBYCERT (CERT_ID ('certkey123'), CONVERT (VARCHAR (MAX), AddressLine1 ))
R)
S) -- decrypt
T) SELECT AddressID, CONVERT (VARCHAR (MAX), CONVERT (VARCHAR (MAX), DECRYPTBYCERT (CERT_ID ('certkey123 '), AddressLine4, n'p @ ssw0rd') DecryAddress
U) FROM Person. Address

5. phrase encryption:Copy codeThe Code is as follows: a) -- phrase encryption: This process is relatively simple. You only need to use the EncryptByPassPhrase function. When you use phrase encryption, the referenced data cannot be changed; otherwise, decryption fails.
B) SELECT *, AddressLine5 = ENCRYPTBYPASSPHRASE ('P @ ssw0rd ', CONVERT (varbinary, AddressLine1), AddressID)
C) FROM Person. Address
D)
E) -- Add a new column to store data. Note that the ENCRYPTBYPASSPHRASE function returns the VARBINARY type.
F) alter table Person. Address ADD AddressLine5 VARBINARY (256)
G)
H) -- update the data. Use the P @ ssw0rd and AddressID data lines as the password phrase.
I)
J) UPDATE Person. Address
K) SET AddressLine5 = ENCRYPTBYPASSPHRASE ('P @ ssw0rd ', CONVERT (varbinary, AddressLine1), AddressID)
L)
M) SELECT * FROM Person. Address

Question 2: how can we protect the definition of database objects and avoid exposing sensitive information during transition?

The general protection measure is to use with encryption to encrypt the object when creating the object, so that the definition cannot be viewed. However, the problem is that maintenance becomes a problem, and this part of objects will be lost during Backup and restoration.

One solution is to save the Definition Statement in the [extended attributes] of the object, which can solve the problem above.

The following is an example:Copy codeThe Code is as follows: -- 1. Create an encrypted Stored Procedure
USE AdventureWorks
GO
Create proc test
WITH ENCRYPTION
AS
SELECT SUSER_SNAME (),
USER_NAME ()
GO
-- 2. Remove the above definition content and use phrase encryption with EncryptByPassPhrase function encryption. Then, use sys. sp_addextendedproperty to specify an extension name.
USE AdventureWorks
GO
DECLARE @ SQL VARCHAR (MAX)
SET @ SQL = 'create PROC Test WITH ENCRYPTION AS SELECT suer_sname (), user_name () GO'

-- 3. encrypt the content and convert it to the SQL _variant Data Type
DECLARE @ bsql SQL _VARIANT
SET @ bsql = (select convert (SQL _VARIANT, ENCRYPTBYPASSPHRASE ('P @ ssw0rd ',
CONVERT (VARCHAR (MAX), @ SQL )))
)

-- 4. Added to the extended attributes of the specified stored procedure:
EXEC sys. sp_addextendedproperty @ name = n' test defination', @ value = n' System. Byte [] ',
@ Level0type = n' SCHEMA ', @ level0name = n' dbo', @ level1type = n' PROCEDURE ',
@ Level1name = n' Test'
GO
EXEC sys. sp_addextendedproperty @ name = n' code content ',
@ Value = n' create proc Test with encryption as select suer_sname (), user_name () GO ',
@ Level0type = n' SCHEMA ', @ level0name = n' dbo', @ level1type = n' PROCEDURE ',
@ Level1name = n' Test'
GO

-- 5. Restore
DECLARE @ pwd VARCHAR (100) = 'P @ ssw0rd'
-- Password phrase

DECLARE @ proc VARCHAR (100) = 'test'
-- Stored procedure name

DECLARE @ exName NVARCHAR (100) = 'Code Content'
-- Extended attribute name

-- Query the original results
SELECT value
FROM sys. all_objects AS sp
Inner join sys. extended_properties as p on p. major_id = sp. object_id
And p. minor_id = 0
And p. class = 1
WHERE (P. name = @ exName)
AND (sp. type = n'p'
OR sp. type = n' rf'
OR sp. type = 'pc'
)
AND (sp. name = @ proc
AND SCHEMA_NAME (sp. schema_id) = N 'dbo'
)
)

Question 3: How can a specified user Truncate a data table?

When Truncate deletes a large table, it is much faster and more effective than the Delete statement. However, because it does not need to store logs and must be deleted from the entire table, data cannot be recovered. It also illustrates its danger.
However, to execute Truncate, you must have one of the high-Permission roles in the table owner, system administrator, db_owner, and db_ddladmin.
In this regard, you can use the execute as expression after 05 to switch the permission content:

1. Switch Logon: EXECUTE AS LOGIN

2. Switch USER: EXECUTE AS USER

3. Switch the execution permission: execute as owner/'user name', and use the high user permission to EXECUTE the job. This step can perform high-Permission operations under a low-Permission entity and avoid security vulnerabilities.

In addition, only execute as Caller can be executed across databases, while permission switching in other ways is only limited to this database.

Note: When performing the execute as user simulated USER switch, you must first obtain the authorization of the simulated USER.

You can use REVERT to restore the original identity before execution.

Question 4: How can I obtain frontend connection information, such as IP addresses and computer names?

For DBA work or some special applications, You need to obtain the system information of the front-end application. If the user table is used for storage, the cost is higher than that for Directly Reading the database system information. We recommend that you read the system table as follows:

The session information can be found in the master database during the session connection to the database. However, from 05 onwards, many DMV/DMF functions are available:

L Master. dbo. sysprocesses or master. sys. sysprocesses: Provide the SPID, computer name, and application name of the execution stage.

L Sys. dm_exec_sessions: records the basic information of each session, including the id, computer name, program name, and application name.

L Sys. dm_exec_connections: records the front-end information of each connection to the SQLServer instance, including the network location and connection time.

L select client_net_address 'client IP address', local_net_address 'SQL ServerIP address ',*

L from sys. dm_exec_connections

L where session_id = @ spid

We recommend that you replace the system table with DMV after 2005.

Question 5: How to Avoid SQL injection attacks?

SQL injection is a major risk for database applications, regardless of DBMS.

To avoid SQL injection, do the following:

1. Check the input data. The application does not trust the data entered by the user. It must be verified before entering the database. Special symbols such as % and -- must be excluded.

2. Avoid exposing the error message. We recommend that you convert it to a Windows event or an internal error message of the application.

3. Use parameterized queries or stored procedures

Note:
Dynamic SQL is the main cause of SQL Injection

Author: Huang zhaoji