MD5 and sha1 in the SQL Server database

MD5 and sha1 are one-way encryption Algorithm It is often used for password verification and other scenarios requiring encryption operations. In general, developers can write related functions themselves or use their own functions through Delphi or PHP, then, the encrypted results are stored in the database as needed.

However, in some cases, MD5 and sha1 hash values may need to be calculated on the database side, for example, within a stored procedure or a custom function. MySQL, an open-source database, provides built-in functions like this. The following statements can display the MD5 and sha1 values of the string "12345", and the returned results are of the string type:

Select MD5 ('000000 ′);
Select sha1 ('000000 ′);

However, SQL server does not directly provide such functions. In SQL Server 2000, undocumented data can be used if encryption is required.Pwdencrypt ()AndPwdcompare ()Function to encrypt data and compare results. The encryption method is Microsoft's own algorithm. With the upgrade of the SQL Server version, the encryption result of the function may be different.

However, in SQL Server 2005, Microsoft provides a functionHashbytes ()It can be used to calculate the MD5 and sha1 values of a string. The following statements obtain the MD5 and sha1 values of the string "12345" respectively:

Select hashbytes ('md5', '123 ′);
Select hashbytes ('sha1', '20140901 ′);

The returned result of the hashbytes () function is of the varbinary type, which is binary data starting with 0x in hexadecimal format. However, we usually need string-type data, most people may think of converting varbinary to varchar using cast or convert functions, but the converted result will be garbled, correct Conversion of varbinary Variable Length binary data to hexadecimal strings should use system built-in functionsSYS. fn_varbintohexstr (), As shown below:

Select Sys. fn_varbintohexstr (hashbytes ('md5', '200 ′))

The sys. fn_varbintohexstr () function is only valid in SQL Server 2005. in SQL Server 2000, the same function is implemented and the stored procedure is extended using the system:Master.. xp_varbintohexstr.