SQL ServerのEncryptByPassPhraseを使って暗号化

Kinya
2013-10-10 17:09 2201 2
SQL Server 2008 R2 でお聞きしたいことがあります。
SQL ServerのEncryptByPassPhraseを使って暗号化して暗号化されたバイナリデータを文字列に変換したいのですが何かいい方法はないでしょうか?
ConvertかけたりCastで変換したりなど試したのですが空白が返されてしまい文字列へ変換できませんでした。

暗号化されたバイナリデータ 0x01000000DDE~ を文字列として扱いたいです。

流れとしては
①暗号化するストアドを作成し暗号化したいデータを引数として渡す
②暗号化したバイナリデータ(0x01000000DDE~)を文字列に変換して返す
③別の処理にて復号化するストアドに文字列(0x01000000DDE~)を引数として渡す
④文字列(0x01000000DDE~)をバイナリデータとして変換し復号化する

何かいい方法があればご教授ください、よろしくお願い致します。

コメント

2013年 10月 11日 (14:21)
こんにちは。
イノ(inno)です。

文字列をバイナリに変換する関数(dbo.fn_hexstrtovarbin)がSql Serverにはないので新しく関数を作成する必要があります。
参考:http://www.innoya.com/Board/ViewBlog.aspx?menuID=4&idx=202

もしくは下記のsqlで関数を作成することができます。

CREATE FUNCTION dbo.fn_hexstrtovarbin
(
@Str varchar(1000)
)
RETURNS varbinary(8000)
AS
BEGIN

DECLARE @RtnVal varbinary(8000)
DECLARE @i int

SET @RtnVal = 0x
SET @i = 2

WHILE (@i <= LEN(@Str)/2)
BEGIN

SET @RtnVal = @RtnVal +
CONVERT(binary(1),
CONVERT(tinyint,
CASE LOWER(SUBSTRING(@Str, @i*2-1, 1))
WHEN '0' THEN 0x00
WHEN '1' THEN 0x10
WHEN '2' THEN 0x20
WHEN '3' THEN 0x30
WHEN '4' THEN 0x40
WHEN '5' THEN 0x50
WHEN '6' THEN 0x60
WHEN '7' THEN 0x70
WHEN '8' THEN 0x80
WHEN '9' THEN 0x90
WHEN 'a' THEN 0xa0
WHEN 'b' THEN 0xb0
WHEN 'c' THEN 0xc0
WHEN 'd' THEN 0xd0
WHEN 'e' THEN 0xe0
WHEN 'f' THEN 0xf0
END
) |
CONVERT(tinyint,
CASE LOWER(SUBSTRING(@Str, @i*2, 1))
WHEN '0' THEN 0x00
WHEN '1' THEN 0x01
WHEN '2' THEN 0x02
WHEN '3' THEN 0x03
WHEN '4' THEN 0x04
WHEN '5' THEN 0x05
WHEN '6' THEN 0x06
WHEN '7' THEN 0x07
WHEN '8' THEN 0x08
WHEN '9' THEN 0x09
WHEN 'a' THEN 0x0a
WHEN 'b' THEN 0x0b
WHEN 'c' THEN 0x0c
WHEN 'd' THEN 0x0d
WHEN 'e' THEN 0x0e
WHEN 'f' THEN 0x0f
END
)
)

SET @i = @i + 1
END

RETURN @RtnVal
END
GO

作成後、下記のSQLを実行してみてください。
下記のSQLでこの質問に回答になると思います。
下記のSQLを実行するには上記のfn_hexstrtovarbin関数を作成する必要があります。

DECLARE @Str nvarchar(100)
DECLARE @EncryptKey varchar(20)
DECLARE @StrResultEncrypt varbinary(8000)
DECLARE @VarbinaryToStr varchar(1000)
DECLARE @StrResultDecrypt nvarchar(100)

SET @Str = N'test'
SELECT N'文字列', @Str

SET @EncryptKey = 'privatekey' --暗号化キーを設定。

SET @StrResultEncrypt = ENCRYPTBYPASSPHRASE(@EncryptKey, @Str)
SET @VarbinaryToStr = STUFF(UPPER(master.dbo.fn_varbintohexstr(@StrResultEncrypt)), 2,1,'x')
SELECT N'暗号化したデータ', @VarbinaryToStr AS [varchar_Data]

SET @StrResultEncrypt = dbo.fn_hexstrtovarbin(@VarbinaryToStr)
SET @StrResultDecrypt = DECRYPTBYPASSPHRASE(@EncryptKey, @StrResultEncrypt)
SELECT N'復号化したデータ', @StrResultDecrypt
Kinya 2013年 10月 11日 (15:41)
詳細な実装方法まで書いて頂きありがとうございます。
私も関数がないか探していましたがないのですね^^;
ご回答いただいた内容を参考にさせていただ来ます。
本当にありがとうございます。