In T-SQL, we can use the
HASHBYTES
function to encrypt to MD5 for data up to 8000 characters.
For longer data or binary data we can use the system function fn_repl_hash_binary
.Syntax
HashBytes('MD5', @hashThis)
Basic example:
SELECT HashBytes('MD5', 'Hello world!')
The above example will output:
0x86FB269D190D2C85F6E0468CECA42A20
As you can see, the result has 34 characters (prefixed with 0x) and is in upper case. In the next example, we will standardize the output.
Example #2 standardized output
SELECT LOWER(CONVERT(VARCHAR(32),HashBytes('MD5', 'Hello world!'),2))
Example #2 will output:
86fb269d190d2c85f6e0468ceca42a20
Example #3 with binary data
SELECT LOWER(CONVERT(VARCHAR(32),master.sys.fn_repl_hash_binary(CAST ('Hello world!' AS varbinary)),2));
Example #3 will output:
86fb269d190d2c85f6e0468ceca42a20
Example #4 with salt before string to be encrypted:
In some cases, it is worth using a salted MD5 hash for added security. This means that you add the string "salt" before or after the string to be encrypted.
DECLARE @salt VARCHAR(32) = N'i#8^*uu'
DECLARE @str2hash VARCHAR(100) = N'Hello world!'
SELECT LOWER(CONVERT(VARCHAR(32),HashBytes('MD5', CONCAT(@salt, @str2hash)),2))
Example #4 will output:
5e5eb686808a7c39710dccf1ce03a4e6