CODEWITHSUNDEEP
sqlsql-server
Aidil Scaft
Aidil Scaft

Reputation: 39

Encrypt password was shows Chinese language

I want to change a password, in a stored procedure from NVARCHAR into its MD5 hash, but after executing it the password hash is shown as Chinese characters. Why?

I am using this code EncryptByPassPhrase('MD5', @Password) but it still shows the result as Chinese characters

BEGIN

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),@Password);


Insert Into tblUserLogin (ICNumber,Email,[Password],CreateBy,CreateDate,LastUpdateBy,LastUpdateDate)
Values (@ICNumber,@Email,HASHBYTES('SHA1',@HashThis),@FirstName,GETDATE(),@FirstName,GETDATE())

END

The Expected Result should like this:

0x01000000E6A0E10AF0144E38670D9B8E92E6E22787F9CD27B467E253

But the Actual Result is:

詫鸺쇬⒍맣㮶뎨뗮兴サ

Upvotes: 4

Views: 3746

Answers (1)

Rob
Rob

Reputation: 45779

This is because you're storing the result of calling the HASHBYTES function into an NVARCHAR field when you should store it in a VARBINARY.

Here's a worked example that shows this:

CREATE TABLE UserHash
(
    [Password_NVARCHAR] NVARCHAR(200),
    [Password_VARBINARY] VARBINARY(20)
)

DECLARE @Password NVARCHAR(20) = 'Abcdefghijklmnop'
DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),@Password);

INSERT
INTO    [UserHash]
        (
            Password_NVARCHAR,
            Password_VARBINARY
        )
SELECT  HASHBYTES('SHA1',@HashThis),
        HASHBYTES('SHA1',@HashThis)

SELECT  *
FROM    [UserHash]

Here's the result of the last statement in that code: The results as shown in SQL Server Management Studio

As you can see, the NVARCHAR version shows something you're not expecting which is because the result of calling HASHBYTES is being interpreted as text, rather than as a series of bytes.

If you look at the length of the text in the Password_NVARCHAR field (e.g. by executing SELECT LEN(Password_NVARCHAR) FROM [UserHash]) you'll see this returned as 10. As NVARCHAR stores text using 2 bytes per character, your 20 byte hash is being displayed as a 10 character string.

NOTE: In the script above I've sized Password_VARBINARY to fit an SHA1 result, as per the linked documentation for HASHBYTES you should size the column according to the hashing algorithm being used:

The output conforms to the algorithm standard: 128 bits (16 bytes) for MD2, MD4, and MD5; 160 bits (20 bytes) for SHA and SHA1; 256 bits (32 bytes) for SHA2_256, and 512 bits (64 bytes) for SHA2_512

ASIDE: Don't store a password as a hash, use a Salted Hash.

Upvotes: 3

Related Questions