#眉標=SQL Server、MD5、SHA1
#副標=Microsoft SQL Server欄位字串加密應用
#大標=簡單為資訊系統加入安全機制
#作者=文／圖	林柏甫
#

===<反灰>=============
--測試 Hashbytes 函式是否有作用
Select hashbytes('MD5','12345') as MD5,
	hashbytes('SHA1','12345') as SHA1;
==<end>==============




===<反灰>=============
-- 建立測試 HashBytes 加密函式用之資料表
CREATE TABLE [dbo].[HashTest]( 
[INDEX_NO] [int] IDENTITY(1,1) NOT NULL, 
[PassWD] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL, 
[HashedPW] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL, 
CONSTRAINT [PK_HashTest] PRIMARY KEY CLUSTERED  
( 
[INDEX_NO] ASC 
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] 
) ON [PRIMARY]
==<end>==============



===<反灰>=============
-- 將PassWD加密後的值更新至 hashedPW
Update hashtest set hashedPW = HashBytes('SHA1', PassWD)
==<end>==============



===<反灰>=============
-- 以sys.fn_VarBinToHexStr()轉換成文字
update hashtest set hashedPW = sys.fn_VarBinToHexStr(HashBytes('SHA1', PassWD));
==<end>==============


===程式1	<BOX>=============
-- 建立 MD5() 自訂加密函式
CREATE FUNCTION MD5 (@_Str varchar(50))
RETURNS varchar(50)
AS
BEGIN
　　DECLARE	@HashedStr	varchar(50)
	SET		@HashedStr = sys.fn_VarBinToHexStr(HashBytes('MD5', @_Str))
	RETURN @HashedStr
END
GO
==<end>==============



===程式2	<BOX>=============
-- 建立 SHA1() 自訂加密函式
CREATE FUNCTION SHA1 (@_Str varchar(50))
RETURNS varchar(50)
AS

BEGIN
	DECLARE	@HashedStr	varchar(50)
	SET		@HashedStr = sys.fn_VarBinToHexStr(HashBytes('SHA1', @_Str))
	RETURN @HashedStr
END
GO
==<end>==============