catsの出来ることからこつこつと…

なくても出来るけど、あると便利なコンポーネント。

楽だしライセンスフリーで商用利用も可能なので、使っています。

 

URL

 

VBやVBAで正規表現を行うのは、下記の通り。

 

Function 正規表現CK(STRSRC As String, LNGPTN As Long) As Boolean

'LNGPTNの設定値
'1=英字大文字
'2=英字子文字
'3=数値
'4=ひらがな
'5=全角カタカナ
'6=半角カタカナ
'7=特殊文字
'8=SQLServerの引数で良くない文字(SQL インジェクション簡易対策)

'パターンの文字があった場合にTrueを返す

On Error GoTo ERR_SKP:
   
Dim OBJREG As Object
Dim STRPTN As String
Dim RET As Boolean

Set OBJREG = CreateObject("VBScript.RegExp")

OBJREG.IGNORECASE = False

Select Case LNGPTN
    Case 1
        STRPTN = "[A-Z]"
    Case 2
        STRPTN = "[a-z]"
    Case 3
        STRPTN = "[0-9]"
    Case 4
        STRPTN = "[ぁ-ん]"
    Case 5
        STRPTN = "[ァ-ン]"
    Case 6
        STRPTN = "[ｧ-ﾝ]"
    Case 7
        'アスキーコードの32～47、58～64、91～96、123～126
        STRPTN = "( |!|""|#|\$|%|&|'|\(|\)|\*|\+|,|-|\.|/|:|;|<|=|>|\?|@|\[|\\|\]|\^|_|`|\{|\}|~|\|)"
    Case 8
        OBJREG.IGNORECASE = True
        STRPTN = "(;|'|--|/\*|\*/|xp_|SELECT|UPDATE|INSERT|DELETE|TRUNCATE|ALTER|EXECUTE|EXEC|DROP|CREATE)"
End Select

OBJREG.Pattern = STRPTN
RET = OBJREG.Test(STRSRC)

If RET = True Then
    ' マッチした場合の処理
Else
    ' マッチしなかった場合の処理
End If

' 戻り値をセットします。
正規表現CK = RET
   
Set OBJREG = Nothing
Exit Function
   
ERR_SKP:
If OBJREG Is Nothing Then
Else
    Set OBJREG = Nothing
End If

End Function
 

テーブルデータを暗号化する方法は下記の通り。

マスターキーは省略します。

 

--証明書発行
CREATE CERTIFICATE [SYOMEI]
   ENCRYPTION BY PASSWORD = 'password' --証明書パスワード
   WITH SUBJECT = 'SYO',
   START_DATE = '10/01/2007', --証明書有効開始日
   EXPIRY_DATE = '10/31/2009'; --証明書終了日
GO

--対称キー発行（#付にすると一時対称キー）
CREATE SYMMETRIC KEY DESTEST --DESTESTの部分が対称キーの名前
WITH ALGORITHM=TRIPLE_DES ENCRYPTION --暗号化アルゴリズム選択
BY CERTIFICATE [SYOMEI] --証明書指定
GO

--対称キーを開く（#付にすると一時対称キー）
OPEN SYMMETRIC KEY DESTEST DECRYPTION BY
CERTIFICATE [SYOMEI] WITH PASSWORD = 'password'
GO

--対称キーのGUID取得
DECLARE @KEYGUID UNIQUEIDENTIFIER
SET @KEYGUID = key_GUID('DESTEST')
SELECT @KEYGUID

 

--暗号化してデータ保存
INSERT [T1](A) VALUES( EncryptByKey(@KEYGUID,'ABCDE') )

--暗号化したまま表示
SELECT * FROM [T1]
--復号化して表示
SELECT CONVERT(varchar, DecryptByKey(A)) AS 'A' FROM [T1]

 

--暗号化一覧
select * from sys.symmetric_keys;

--対称キー削除
DROP SYMMETRIC KEY DESTEST

 

--証明書削除
DROP CERTIFICATE [SYOMEI]
 

当たり前だが、Passwordは忘れない様に！

ストアドとかのコードを暗号化するのは、下記の通り。

ストアドプロシージャ
 CREATE PROCEDURE 名前 WITH ENCRYPTION

ユーザー定義関数
 CREATE FUNCTION 名前 (引数...) WITH ENCRYPTION

トリガ
 CREATE TRIGGER 名前 ON { TABLE | VIEW } WITH ENCRYPTION

ビュー
 CREATE VIEW 名前 WITH ENCRYPTION
 

暗号化する前に、必ずバックアップを取っておく事！！！！

ただし、解読ツールもあるのでご注意を！