[SQL Server] データ暗号化 / パスワード暗号化
HASHBYTES / MD2, MD4, MD5, SHA, SHA1, SHA2_256(SHA256), SHA2_512(SHA512)
一般的に使われるMD5, SHA1などのアルゴリズムでパスワードやデータを暗号化する時に使います。
「HASHBYTES」で使用可能なアルゴリズムがSQL SERVER バージョンによって異なります。
SQL SERVER 2005, SQL SERVER 2008, SQL SERVER 2008 R2の場合、
使用可能なアルゴリズム : MD2, MD4, MD5, SHA, SHA1
SQL SERVER 2012の場合、
使用可能なアルゴリズム : MD2, MD4, MD5, SHA, SHA1, SHA2_256(SHA256), SHA2_512(SHA512)
では、各アルゴリズムごとに実行してみましょう。
MD2
SQL SERVER 2005以上で使えます。
MD2は128Bitで16Byteを使いますので、
このデータを保存する際にはデータ型をvarbinary(16)に指定してください。
DECLARE @HashStr nvarchar(4000)
SELECT @HashStr = 'innoya'
SELECT HASHBYTES('MD2', @HashStr)
実行結果
MD4
SQL SERVER 2005以上で使えます。
MD4は128Bitで16Byteを使いますので、
このデータを保存する際にはデータ型をvarbinary(16)に指定してください。
DECLARE @HashStr nvarchar(4000)
SELECT @HashStr = 'innoya'
SELECT HASHBYTES('MD4', @HashStr)
実行結果
MD5
SQL SERVER 2005以上で使えます。
MD5は128Bitで16Byteを使いますので、
このデータを保存する際にはデータ型をvarbinary(16)に指定してください。
DECLARE @HashStr nvarchar(4000)
SELECT @HashStr = 'innoya'
SELECT HASHBYTES('MD5', @HashStr)
実行結果
SHA
SQL SERVER 2005以上で使えます。
SHAは160Bitで20Byteを使いますので、
このデータを保存する際にはデータ型をvarbinary(20)に指定してください。
DECLARE @HashStr nvarchar(4000)
SELECT @HashStr = 'innoya'
SELECT HASHBYTES('SHA', @HashStr)
実行結果
SHA1
SQL SERVER 2005以上で使えます。
SHA1は160Bitで20Byteを使いますので、
このデータを保存する際にはデータ型をvarbinary(20)に指定してください。
DECLARE @HashStr nvarchar(4000)
SELECT @HashStr = 'innoya'
SELECT HASHBYTES('SHA1', @HashStr)
実行結果
SHA2_256 (SHA256)
SQL SERVER 2012以上で使えます。
SQL SERVER 2012下位バージョンで使いたい場合は「fnEnCryptSHA」を使うとできます。
SHA2_256 (SHA256)は256Bitで32Byteを使いますので、
このデータを保存する際にはデータ型をvarbinary(32)に指定してください。
DECLARE @HashStr nvarchar(4000)
SELECT @HashStr = 'innoya'
SELECT HASHBYTES('SHA2_256', @HashStr)
実行結果 - SQL Server 2012以上で実行した場合。
実行結果 - SQL Server 2012下位バージョンでで実行した場合。
SQL Server 2012下位バージョンで実行するとエラーではなく実行は正常にでき「null」を返します。
SHA2_512 (SHA512)
SQL SERVER 2012以上で使えます。
SQL SERVER 2012下位バージョンで使いたい場合は「fnEnCryptSHA」を使うとできます。
SHA2_512 (SHA512)は512Bitで64Byteを使いますので、
このデータを保存する際にはデータ型をvarbinary(64)に指定してください。
DECLARE @HashStr nvarchar(4000)
SELECT @HashStr = 'innoya'
SELECT HASHBYTES('SHA2_512', @HashStr)
実行結果 - SQL Server 2012以上で実行した場合。
実行結果 - SQL Server 2012下位バージョンでで実行した場合。
SQL Server 2012下位バージョンで実行するとエラーではなく実行は正常にでき「null」を返します。
「HASHBYTES」の場合、暗号化されたデータは復号化できませんのでご注意ください。
では、復号化でできなかったらどうやってデータを比較するの???
下記の使用例を見てください。
パスワードを保存する時はMD5で暗号化して保存しています。
-- #### ユーザーデータテーブルを作成する。
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)すべて同じですので注意してください。。