SQL ServerのEncryptByPassPhraseを使って暗号化

Kinya
2013-10-10 17:09 2451 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)
詳細な実装方法まで書いて頂きありがとうございます。
私も関数がないか探していましたがないのですね^^;
ご回答いただいた内容を参考にさせていただ来ます。
本当にありがとうございます。
SQL　特定のカラムが最大値のレコード取得
2013-11-15 2184 1
SQLServerのストアドで戻り値取得したい
2013-11-06 2860 28
プロバイダーで使用されるデータ型ではデータ値がオーバーフローするので、変換できませんでした
2013-10-31 2386 2
insertするときの文字数削除について
2013-10-30 2027 1
SQL Server 2005からSQL Server 2008へデータベース移行～
2013-10-24 3420 1
SQL SERVERのサービスポート1433について質問
2013-10-17 2460 4
レスポンスの良いクエリを教えてください。
2013-10-16 2027 1
SQL ServerのEncryptByPassPhraseを使って暗号化
2013-10-10 2451 2
データベース（SQL）初心者の学生です。良いSQLをご教示ください。
2013-10-02 2074 1
SQL Sever 2008のバックアップについて
2013-09-24 2396 2