当前位置：文江博客话题详情

T-SQL：加盐密码

发布于 2024-11-07 02:38:21 字数 174 浏览 0 评论 0原文

我正在寻找使用 T-SQL 存储过程对密码加盐的示例。当然还有验证用户的匹配过程。

创建过程 ChangePassword(@用户名 nVarChar(50)，@密码 nVarChar(50))

创建过程 ValidateUser(@用户名 nVarChar(50)，@密码 nVarChar(50))

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

找个人就嫁了吧 2024-11-14 02:38:21

首先，我要大胆地说，就安全性而言，对数据库中的密码进行哈希处理通常是一种不好的做法。您将无法免受流量嗅探器监视数据库流量的影响。防止这种情况的唯一方法是确保与数据库的连接是加密的，这通常意味着到数据库的所有其他流量都将被加密。可以解决这个问题，但更好的解决方案是让应用程序进行哈希处理。

正如 Sam Saffron 所说，您可以使用 Hashbytes 函数来获取 SHA1 哈希值。如果您想要更好的算法，您需要创建一个 CLR 过程。加盐涉及为每个用户存储一个加密随机值，然后将该值附加到密码并通过 Hashbytes 运行它：

Create Procedure ValidateUser
    @Username nvarchar(50)
    , @Password nvarchar(50)
As

Declare @PasswordSalt varbinary(256)

Set @PasswordSalt = ( Select PasswordSalt From Users Where Username = @Username )

If @PasswordSalt Is Null
        -- generate a salt? 

Declare @Hash varbinary(max)
Set @Hash = Hashbytes('SHA1', @PasswordSalt + Cast('|' As binary(1)) + Cast(@Password As varbinary(100))

If Exists(  Select 1
            From Users
            Where Username = @Username
                And PasswordHash = @Hash )
    -- user is valid

Else
    -- user is not valid

请记住，加盐应该是加密随机的，因此我不建议使用 NewId()。相反，我会使用 .NET 的 RNGCryptoServiceProvider 类之类的东西来生成它。

First, I'm going to go out on a limb here and say that hashing passwords in the database is in general a bad practice with respect to security. You would not be protected against traffic sniffers watching traffic to the database. The only way to protect against that is to ensure your connection to the database was encrypted which generally means all other traffic to the database is going to be encrypted. It's possible to work around this, but the better solution is to have the application(s) do the hashing.

As Sam Saffron stated, you can use the Hashbytes functions to get SHA1 hashing. If you want better algorithms you would need to create a CLR procedure. Salting would involve storing a cryptographically random value for each user, then appending that value to the password and running it through Hashbytes:

Create Procedure ValidateUser
    @Username nvarchar(50)
    , @Password nvarchar(50)
As

Declare @PasswordSalt varbinary(256)

Set @PasswordSalt = ( Select PasswordSalt From Users Where Username = @Username )

If @PasswordSalt Is Null
        -- generate a salt? 

Declare @Hash varbinary(max)
Set @Hash = Hashbytes('SHA1', @PasswordSalt + Cast('|' As binary(1)) + Cast(@Password As varbinary(100))

If Exists(  Select 1
            From Users
            Where Username = @Username
                And PasswordHash = @Hash )
    -- user is valid

Else
    -- user is not valid

Remember that the salt should be cryptographically random so I would not recommend using NewId(). Instead, I would generate that using something like .NET's RNGCryptoServiceProvider class.

回复收藏 0 原文