[SQL Server] MD5ハッシュ（暗号化）データを文字列に変換・交換・取得
fn_varbintohexstr / varbinary to varchar
文字をMD5に交換/ハッシュ/変更/暗号化するにはSQLSERVER 2005から使える「HashBytes」関数を使います。
MD5を文字列に変更するにはSQL SERVERにある「dbo.fn_varbintohexstr」関数を利用します。
MD5を文字列に変更すると言ってもハッシュされたものが元の文字列に戻るわけではありません。
MD5に交換/ハッシュ/変更/暗号化すると元のデータには復号化することはできません。
では、「test」文字を「hashBytes」でMD5にハッシュします。
ハッシュするとvarbinary(16)値を返します。
そのvarbinary(16)データを「fn_varbintohexstr」関数を使って文字列「VARCHAR」に交換・変更する方法を説明します。
DECLARE @Str varchar(100)
DECLARE @DataVarbinary varbinary(16)
SET @Str = 'test'
SET @DataVarbinary = HashBytes('MD5', @Str)
SELECT master.dbo.fn_varbintohexstr(@DataVarbinary) AS [Result], @DataVarbinary AS [VarbinaryData]
実行結果
上記の実行結果を見てください。
[Result] : MD5のバイナリデータを文字列に変更した結果
[VarbinaryData] : MD5でハッシュされた結果
[Result]は「fn_varbintohexstr」関数で文字列に交換・変更されvarchar型になっています。
[VarbinaryData]はMD5ハッシュの結果なので、varbinary型になっています。
[VarbinaryData]カラムのデータ、varbinary型のデータを見ると大文字になっていますが、
[Result]カラムのデータ、varchar型のデータは小文字になっています。
MD5ハッシュデータを文字列に変更・交換した場合も
結果を同じく表示したい時は下記のように「UPPER」関数を使った大文字に変更します。
DECLARE @Str varchar(100)
DECLARE @DataVarbinary varbinary(16)
SET @Str = 'test'
SET @DataVarbinary = HashBytes('MD5', @Str)
SELECT STUFF(UPPER(master.dbo.fn_varbintohexstr(@DataVarbinary)), 2,1,'x') AS [Result], @DataVarbinary AS [VarbinaryData]
実行結果
「UPPER」だけ使うと実行結果の「0X098F6BCD4621D373CADE4E832627B4F6」の中で前から2番目の「X」も大文字になりますので、「STUFF」関数を使ってあそこだけ小文字の「x」に変更します。
個人的には普通はMD5でハッシュされたデータをそのまま使ったりしますが、
パスワード以外にもシリアル番号を作成したりするときもMD5でハッシュして使っていますので、
MD5ハッシュデータを文字列に変更する時は先頭2文字「0x」は削除して使います。
あの2文字を削除するとMD5は固定された32文字を取得することができます。
DECLARE @Str varchar(100)
DECLARE @DataVarbinary varbinary(16)
SET @Str = 'test'
SET @DataVarbinary = HashBytes('MD5', @Str)
SELECT RIGHT(UPPER(master.dbo.fn_varbintohexstr(@DataVarbinary)), 32) AS [Result], @DataVarbinary AS [VarbinaryData]
実行結果
上記のSQLのように「fn_varbintohexstr」関数でもMD5を文字列に変更した後に「RIGHT」関数を利用して右から32文字を切り取ります。
上記のSQLを1行に変更すると下記のようになりますが、私が良く使うSQLになります。
SELECT RIGHT(UPPER(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'test'))), 32)
実行結果
パスワードを保存する時にMD5で暗号化・ハッシュして保存する使用例は下記のSQLをご参考ください。
-- #### ユーザーデータテーブルを作成する。
CREATE TABLE dbo.UserData
(
UserID varchar(20)
,UserPW varbinary(16)
)
-- #### ユーザーのデータを登録する。
INSERT INTO dbo.UserData (UserID, UserPW)
VALUES ('testid', HASHBYTES('MD5', 'testpw'))
-- #### パスワード一致するか確認。
DECLARE @StrID varchar(20)
DECLARE @StrPW varchar(20)
SET @StrID = 'testid'
SET @StrPW = 'testpw'
SELECT * FROM dbo.UserData
WHERE UserID = @StrID AND UserPW = HASHBYTES('MD5', @StrPW)
既に保存されている「UserPW」データは復号化ができませんので、
入力されるデータ「@StrPW」を暗号化して比較します。
データが出るとパスワードは一致。
データが出ないとパスワードは一致しないことになります。
ここで注意点！！！！
暗号化（ハッシュ）する文字のデータ型によって同じ文字列でも戻り値は変わります。
下記は同じ文字をvarcharとnvarcharに保存して暗号化（ハッシュ）するSQLです。
DECLARE @Str1 varchar(100), @Str2 nvarchar(100)
SET @Str1 = 'innoya'
SET @Str2 = 'innoya'
SELECT HASHBYTES('MD2', @Str1) AS Str_varchar, HASHBYTES('MD2', @Str2) AS Str_nvarchar
実行結果
これはMD2だけではなく、「HASHBYTES」関数を使用するMD2, MD4, MD5, SHA, SHA1, SHA2_256(SHA256), SHA2_512(SHA512)すべて同じですので注意してください。。
ちなみに、文字列をバイナリにする方法はSQL Serverから作成されている関数がないので、
「dbo.fn_hexstrtovarbin」という関数を作成して利用することができます。
文字列をバイナリにする方法（dbo.fn_hexstrtovarbin）については下記のリンクをクリックしてください。