Search this blog

Thursday, June 11, 2009

Encrypt the values in SQL using HashBytes(T-SQL)

HashBytes is a T-SQL function, used to encrypt the input value by using the algorithm MD2, MD5, MD5, SHA or SHA1. This T-SQL function is available from SQL 2005.

HashBytes accepts two parameters, one is Name of the algorithm, and other is input value either direct value or @variable. Variable accepts, if belongs to varchar, nvarchar, or varbinary Datatypes

HashBytes ( '', { @input | 'input' } )

Refer this link, to know about these algorithms

http://www.rampant-books.com/t_super_sql_63_md4_sha_hash_algorithm.htm

Sample Query with HashBytes:

DECLARE @Value1 VARBINARY(150)

SET @Value1 = HashBytes('MD5', 'Value1')

SELECT @Value1 as HashValue


Output of this Query:

HashValue

0x7EE5A281B28D7CA1FAB06B43D0B7D8AC

5 comments:

  1. SQL Server provides encryption as a new feature to protect data against the attacks of hackers. And HashBytes T-SQL function from SQL 2005 is really useful tool for encrypting values by taking parameters.

    ReplyDelete
  2. Please help,

    I got the problem about hashing result. I have table as scripting

    create table tb_users (
    userid varchar(8), pwd varchar(8)
    );

    and the records like these,

    ('user1', 'password1'),
    ('user2', 'password2')

    I want to convert data in pwd field to MD5 encrypted, but after select userid, hashbytes('MD5', pwd) from tb_users;

    the results of encrypted password are difference from the traditional encrypted result, such as, MD5(password) in MySQL or PHP.

    I tried several hours of finding out, but still got the confuses.

    ReplyDelete
  3. OK, I've found that at the end of every record of pwd is contains invisible character.

    ReplyDelete
  4. I was unaware until recently that SQL Server 2005 has nicely built in support for hashing and it is called “HashBytes”. Truly the new features from SQL server efficiently protect data from unauthorized access.

    ReplyDelete