在 SQL Server 中加密列中的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15093775/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Encrypt the data in a column in SQL Server
提问by Manish Rawat
Is there any algorithm exist in the sql server to encrypt the data. I have a password field which i need to encrypt but i have to encrypt the data once its being passed in the stored procedure. It means i cannot pass the encrypted data from my c# code.
sql server 中是否存在任何算法来加密数据。我有一个需要加密的密码字段，但是一旦数据在存储过程中传递，我就必须对其进行加密。这意味着我无法从我的 c# 代码中传递加密数据。
CREATE PROC saveData(@p1 VARCHAR(50),
@p2 VARCHAR(50))
AS
BEGIN
DECLARE @encryptedP1 VARCHAR(50)
SET @encryptedP1=dbo.fnEncrypt(@p1) --ENCRYPTING THE DATA WITH AN INBUILT FUNCTION
INSERT INTO table1
(column1,
column2)
VALUES (@encryptedP1,
@p2)
END
回答by Orlando Herrera
This is a very fast example. It works with Asymmetric Key. I hope this can help.
这是一个非常快的例子。它适用于非对称密钥。我希望这会有所帮助。
First off:Create your asymmetric Key using this code:
首先：使用以下代码创建您的非对称密钥：
USE [yourDB]
GO
CREATE ASYMMETRIC KEY ClaveAsym
WITH ALGORITHM = RSA_2048 ---->Take a look in this type of algorithms
ENCRYPTION BY PASSWORD = 'yourKey'
GO
Remember this, you must to declare a variable always that you want to decryptor encrypt
请记住这一点，您必须始终声明要解密或加密的变量
DECLARE @KEYID INT
SET @KEYID = AsymKey_ID('ClaveAsym')
Decrypting data
解密数据
SELECT columnA, CAST(DecryptByAsymKey(@keyid, columnUser, N'yourKey') AS VARCHAR(100)),
CONVERT(VARCHAR(MAX), DECRYPTBYASYMKEY(@keyId, columnPass, N'yourKey'))
FROM yourTable
Encrypting Data
加密数据
DECLARE @User VARCHAR(MAX), @pass VARCHAR(MAX)
SET @User = 'yourUser'
sET @pass = 'yourPass'
DECLARE @KEYID INT SET @KEYID = AsymKey_ID('ClaveAsym')
INSERT INTO yourTable( User, passw) VALUES EncryptByAsymKey (@keyid, @User ), EncryptByAsymKey (@keyid, @pass))
回答by Simon
As usual, good old Pinal Dave has some useful information on this. It's a little involved but if you mustdo it in SQL Server then rather than in client side code then it should do the job for you. You might want to keep an eye out for performance issues though as it's quite a CPU intensive process.
像往常一样，好老 Pinal Dave 有一些关于此的有用信息。这有点复杂，但如果您必须在 SQL Server 中执行此操作，而不是在客户端代码中执行，那么它应该为您完成这项工作。您可能需要注意性能问题，因为它是一个 CPU 密集型过程。
回答by idipous
I would suggest a salted sha1 or md5
我建议加盐 sha1 或 md5
CONVERT(VARCHAR(32), HashBytes('MD5', '[email protected]'), 2)
Although hashing is not technically encryption.
虽然散列在技术上不是加密。
