SQL Server：データの暗号化・複合化についての諸々メモ

SQL Serverでのデータ暗号化・複合化について検証した内容のまとめ。

０、前提

対象のSQL Serverのバージョンが最新ではない
DB単位での認証とか対称キーとかはなるべく使わずに、アプリ単位で暗号化・複合化したい
開発ではEntity Frameworkを使う予定。でもSQL直接実行もOK
暗号化対象は英語、日本語のほか、色んな国の言語が対象になる

１、検証用データベースの作成(localDB)

f:id:papamau:20171206172234p:plain

２、検証用テーブルの作成

f:id:papamau:20171206172350p:plain

USE [TESTDB01]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](500) NULL,
    [NameEnc] [varbinary](2000) NULL
) ON [PRIMARY]

GO

※暗号化したデータのdatatypeはvarbinary。

３、色んな言語でデータを作成

f:id:papamau:20171206172549p:plain

INSERT文には必ずNをつけてnvarcharとして登録する。

INSERT INTO [dbo].[Table_1]([Name]) VALUES (N'Name');

※datatypeをnvarcharで登録しないとCharとして登録され、"???"のように文字化けになる。

４、暗号化する

UPDATE [dbo].[Table_1] SET [NameEnc] = ENCRYPTBYPASSPHRASE('ENCRYPT',Name);

f:id:papamau:20171206173015p:plain

※Nameのdatatypeがnvarcharなので、ENCRYPTBYPASSPHRASEにはnvarcharのまま暗号化される。

文字列を直接入力して暗号化したい場合はN'...'のように設定する

UPDATE [dbo].[Table_1] SET [NameEnc] = ENCRYPTBYPASSPHRASE('ENCRYPT',N'暗号化したい内容');

５、複合化する

SELECT [ID], [Name], [NameEnc]
  , CAST(DECRYPTBYPASSPHRASE('ENCRYPT',NameEnc) AS nvarchar(2000)) AS NameDec
FROM [TESTDB01].[dbo].[Table_1]

f:id:papamau:20171206173245p:plain

※複合化した後、nvarcharに変換する処理を入れる。

以上

補足：MySQLの場合

暗号化

CAST(HEX(AES_ENCRYPT('暗号化するデータ', '暗号化key')) AS CHAR)

複合化

CAST(AES_DECRYPT(UNHEX('複合化するデータ'), '暗号化Key') AS CHAR)

IT業界のすみっこ暮らし

ふと気がついたときの記録