sql server 2008密码字段加密

I have a table with username and password fields. Now i dont want the password to be stored exactly as a string the user inputted. I want this field to be encrypted or converted into a GUID so no one including people working on SQL can see it. In case the user loses his password, he has to come up with a new one and it shall get updated in the table. Any ideas how i can achieve this?

我有一个包含用户名和密码字段的表。现在我不希望密码完全存储为用户输入的字符串。我希望将此字段加密或转换为GUID,因此没有人(包括处理SQL的人)可以看到它。如果用户丢失了密码,他必须拿出一个新密码,并在表格中更新。我有什么想法可以做到这一点?

3 个解决方案

#1

OWASP guidelines say to use a one-way hash for storing passwords.

OWASP指南说使用单向散列来存储密码。

This article shows how in ASP.NET: http://www.15seconds.com/issue/000217.htm

本文将介绍如何在ASP.NET中使用:http://www.15seconds.com/issue/000217.htm

(You didn't mention the technology you're using to connect to the server, so I took a guess on ASP.NET.)

(你没有提到你用来连接服务器的技术,所以我猜测了ASP.NET。)

#2

You can use hashbytes to do so. Like this: assuming password = admin

您可以使用hashbytes来执行此操作。像这样:假设密码= admin

DECLARE @dummy nvarchar(4000);
select @dummy = CONVERT(nvarchar(4000),'admin');
SELECT HashBytes('SHA1', @dummy);

#3

CREATE FUNCTION dbo.fnInitRc4
(
    @Pwd VARCHAR(256)
)
RETURNS @Box TABLE (i TINYINT, v TINYINT)
AS

BEGIN
    DECLARE @Key TABLE (i TINYINT, v TINYINT)

    DECLARE @Index SMALLINT,
        @PwdLen TINYINT

    SELECT  @Index = 0,
        @PwdLen = LEN(@Pwd)

    WHILE @Index <= 255
        BEGIN
            INSERT  @Key
                (
                    i,
                    v
                )
            VALUES  (
                    @Index,
                     ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1))
                )

            INSERT  @Box
                (
                    i,
                    v
                )
            VALUES  (
                    @Index,
                    @Index
                )

            SELECT  @Index = @Index + 1
        END


    DECLARE @t TINYINT,
        @b SMALLINT

    SELECT  @Index = 0,
        @b = 0

    WHILE @Index <= 255
        BEGIN
            SELECT      @b = (@b + b.v + k.v) % 256
            FROM        @Box AS b
            INNER JOIN  @Key AS k ON k.i = b.i
            WHERE       b.i = @Index

            SELECT  @t = v
            FROM    @Box
            WHERE   i = @Index

            UPDATE  b1
            SET b1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b)
            FROM    @Box b1
            WHERE   b1.i = @Index

            UPDATE  @Box
            SET v = @t
            WHERE   i = @b

            SELECT  @Index = @Index + 1
        END

    RETURN
END

ANd this function does the encrypt/decrypt part

此函数执行加密/解密部分

CREATE FUNCTION dbo.fnEncDecRc4
(
    @Pwd VARCHAR(256),
    @Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
    DECLARE @Box TABLE (i TINYINT, v TINYINT)

    INSERT  @Box
        (
            i,
            v
        )
    SELECT  i,
        v
    FROM    dbo.fnInitRc4(@Pwd)

    DECLARE @Index SMALLINT,
        @i SMALLINT,
        @j SMALLINT,
        @t TINYINT,
        @k SMALLINT,
            @CipherBy TINYINT,
            @Cipher VARCHAR(8000)

    SELECT  @Index = 1,
        @i = 0,
        @j = 0,
        @Cipher = ''

    WHILE @Index <= DATALENGTH(@Text)
        BEGIN
            SELECT  @i = (@i + 1) % 256

            SELECT  @j = (@j + b.v) % 256
            FROM    @Box b
            WHERE   b.i = @i

            SELECT  @t = v
            FROM    @Box
            WHERE   i = @i

            UPDATE  b
            SET b.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
            FROM    @Box b
            WHERE   b.i = @i

            UPDATE  @Box
            SET v = @t
            WHERE   i = @j

            SELECT  @k = v
            FROM    @Box
            WHERE   i = @i

            SELECT  @k = (@k + v) % 256
            FROM    @Box
            WHERE   i = @j

            SELECT  @k = v
            FROM    @Box
            WHERE   i = @k

            SELECT  @CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
                @Cipher = @Cipher + CHAR(@CipherBy)

            SELECT  @Index = @Index  +1
            END

    RETURN  @Cipher
END

This is implemented by Peter but it helps u................

这是彼得实施的,但它有助于你................

#1