IT業界のすみっこ暮らし

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

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


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

0、前提

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

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

f:id:papamau:20171206172234p:plain

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

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。

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

f:id:papamau:20171206172549p:plain

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

INSERT INTO [dbo].[Table_1]([Name]) VALUES (N'Name');
※datatypeをnvarcharで登録しないとCharとして登録され、"???"のように文字化けになる。

4、暗号化する

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'暗号化したい内容');

5、複合化する

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)


プライバシーポリシー