How to convert from Hashbytes function's SHA2_256 encryption datatype to varchar(256) in SQL Server 2012

user_8235678 0
Viewed 8379

I want to implement a password encryption system using SQL in SQL Server 2012.

The HASHBYTES method must have only nvarchar, varbinary datatype.

update members 
set passwd = cast(HASHBYTES('SHA2_256', 'asd123123') as varchar(256)),
    pwchangedat = GETDATE()
where userid = 'abcd1234'

The unexpected result of the Select query applied above update query:

?lZX?갅4磎?닽뙷?I?#?뚭?

I want to fix this varchar(256) data type string 'asd123123' applied SHA-256 encryption.

I want:

c82a6c7f5a58a915814334cda5a2ef88ab8cba922f49ec1623e2248ceabf7ddc

How can I get a solution?

Regards,

sql-server sql
2 Answers

I think this is what you are looking for...

select convert(varchar(256),HASHBYTES('SHA2_256','asd123123'),2)

See the Binary Styles Section (since Hashbytes returns varbinary)

Thus....

update members set
    passwd = convert(varchar(256),HASHBYTES('SHA2_256','asd123123'),2),
    pwchangedat = GETDATE()
    where userid = 'abcd1234'
user_61678550

If the datatype is Varchar then we get the Same Hash code for literal string as well as the SQL Variable where as if the SQL Variable is of NVARCHAR datatype then you get different hash values when you use Literal String and NVARCHAR Variable using the SHA2_256 Function. Run the below code to check.

    DECLARE @VarcharInput  VARCHAR(70)  = 'Test';
    DECLARE @NVarcharInput NVARCHAR(70) = 'Test';

    SELECT HASHBYTES('SHA2_256','Test')         AS NewHashValue;
    --0x532EAABD9574880DBF76B9B8CC00832C20A6EC113D682299550D7A6E0F345E25
    SELECT HASHBYTES('SHA2_256',@VarcharInput)  AS NewHashValue; 
    --0x532EAABD9574880DBF76B9B8CC00832C20A6EC113D682299550D7A6E0F345E25
    SELECT HASHBYTES('SHA2_256',@NVarcharInput) AS NewHashValue;
    --0xE6FA3CA87B1B641AB646D3B4933BBA8D0970763F030B6578A60ABDEAE7366247
user_69316330
Related