SET NOCOUNT ON;
USE AdventureWorks;
GO

-- Create the database master key
IF NOT EXISTS (
	SELECT 1
	FROM sys.symmetric_keys
	WHERE name = '##MS_DatabaseMasterKey##'
) BEGIN
	PRINT 'Create master key';
	CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'thuyuy73r_!r-kuwru4huy&Yar=-athE';
END;
GO

-- Create the database certificate
IF NOT EXISTS (
	SELECT 1
	FROM sys.certificates
	WHERE name = N'TestCert1'
) BEGIN
	PRINT 'Create certificate';
	CREATE CERTIFICATE TestCert1 WITH SUBJECT = N'TestCert1';
END;
GO

-- Create the symmetric key
IF NOT EXISTS (
	SELECT 1
	FROM sys.symmetric_keys
	WHERE name = 'TestKey1'
) BEGIN
	PRINT 'Create key';
	CREATE SYMMETRIC KEY TestKey1
	WITH ALGORITHM = AES_256
	ENCRYPTION BY CERTIFICATE TestCert1;
END;
GO

OPEN SYMMETRIC KEY TestKey1 DECRYPTION BY CERTIFICATE TestCert1;
GO

-- Create and populate "salt" table
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'SecretSalt' AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'BASE TABLE') BEGIN
	CREATE TABLE dbo.SecretSalt (
		SaltID		tinyint			NOT NULL,
		Salt		varbinary(100)	NOT NULL

		CONSTRAINT SecretSalt_PK PRIMARY KEY CLUSTERED (SaltID)
	);

	INSERT INTO dbo.SecretSalt (
		SaltID,
		Salt
	)
	SELECT
		1,
		EncryptByKey(Key_GUID('TestKey1'), 'd=s_4ZeG3me4E#U4ru6ag&@ru@A2Afr7');
END;
GO

-- Create table Hash1
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Hash1' AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'BASE TABLE') BEGIN
	DROP TABLE dbo.Hash1;
END;
GO

CREATE TABLE dbo.Hash1 (
	RecordID			int,
	EncryptResult		varbinary(116),		-- Encrypted
	HashResult			varbinary(20)		-- Hashed

	CONSTRAINT Hash1_PK PRIMARY KEY CLUSTERED (RecordID)
);
GO

CREATE NONCLUSTERED INDEX Hash1_HashResult_IDX ON dbo.Hash1 (
	HashResult
);
GO

-- Insert and test values
DECLARE @string1		varchar(74);
DECLARE @max			int;
DECLARE @ct				int;

SELECT @max	= 10;

SELECT @ct = 0;
WHILE @ct < @max BEGIN
	SELECT @ct = @ct + 1;

	-- Unencrypt salt; use that string to prepend the value being stored
	SELECT @string1 = CONVERT(varchar(63), DecryptByKey(Salt)) + CONVERT(varchar(11), @ct)
	FROM dbo.SecretSalt
	WHERE SaltID = 1;

	INSERT INTO dbo.Hash1 (
		RecordID,
		EncryptResult,
		HashResult
	)
	VALUES (
		@ct,
		EncryptByKey(Key_GUID (N'TestKey1'), CONVERT(varchar(11), @ct)),
		HashBytes('SHA1', @string1)
	);
END;
GO

SELECT * FROM dbo.Hash1;

-- =====================================================
-- Test look-up
DECLARE @lookUp		int;

SELECT @lookUp = 5;					-- Pick some record to search for

SELECT @lookUp AS 'Searching for';

SELECT
	h.RecordID,
	CONVERT(int, CONVERT(varchar(11), DecryptByKey(h.EncryptResult))) AS 'Decrypted Value'
FROM dbo.Hash1				AS h
CROSS JOIN dbo.SecretSalt	AS s
WHERE s.SaltID		= 1
  AND h.HashResult	= HashBytes (
						'SHA1',
						CONVERT(varchar(63), DecryptByKey(s.Salt)) + CONVERT(varchar(11), @lookUp)
					)	-- This uses the index to find the record
  AND CONVERT(int, CONVERT(varchar(11), DecryptByKey(h.EncryptResult)))	= @lookUp;
GO

-- =====================================================
-- Test with more than one record with the same string
DELETE FROM dbo.Hash1;

DECLARE @i			int;
DECLARE @string1	varchar(74);
DECLARE @lookUp		int;

SELECT @i = 1;

SELECT @string1 = CONVERT(varchar(63), DecryptByKey(Salt)) + CONVERT(varchar(11), @i)
FROM dbo.SecretSalt
WHERE SaltID = 1;

INSERT INTO dbo.Hash1 (RecordID, EncryptResult, HashResult) VALUES (1, EncryptByKey(Key_GUID(N'TestKey1'), CONVERT(varchar(11), @i)), HashBytes('SHA1', @string1));
INSERT INTO dbo.Hash1 (RecordID, EncryptResult, HashResult) VALUES (2, EncryptByKey(Key_GUID(N'TestKey1'), CONVERT(varchar(11), @i)), HashBytes('SHA1', @string1));
INSERT INTO dbo.Hash1 (RecordID, EncryptResult, HashResult) VALUES (3, EncryptByKey(Key_GUID(N'TestKey1'), CONVERT(varchar(11), @i)), HashBytes('SHA1', @string1));

SELECT @i = 2;

SELECT @string1 = CONVERT(varchar(63), DecryptByKey(Salt)) + CONVERT(varchar(11), @i)
FROM dbo.SecretSalt
WHERE SaltID = 1;

INSERT INTO dbo.Hash1 (RecordID, EncryptResult, HashResult) VALUES (4, EncryptByKey(Key_GUID(N'TestKey1'), CONVERT(varchar(11), @i)), HashBytes('SHA1', @string1));
INSERT INTO dbo.Hash1 (RecordID, EncryptResult, HashResult) VALUES (5, EncryptByKey(Key_GUID(N'TestKey1'), CONVERT(varchar(11), @i)), HashBytes('SHA1', @string1));
INSERT INTO dbo.Hash1 (RecordID, EncryptResult, HashResult) VALUES (6, EncryptByKey(Key_GUID(N'TestKey1'), CONVERT(varchar(11), @i)), HashBytes('SHA1', @string1));

SELECT @i = 3;

SELECT @string1 = CONVERT(varchar(63), DecryptByKey(Salt)) + CONVERT(varchar(11), @i)
FROM dbo.SecretSalt
WHERE SaltID = 1;

INSERT INTO dbo.Hash1 (RecordID, EncryptResult, HashResult) VALUES (7, EncryptByKey(Key_GUID(N'TestKey1'), CONVERT(varchar(11), @i)), HashBytes('SHA1', @string1));
INSERT INTO dbo.Hash1 (RecordID, EncryptResult, HashResult) VALUES (8, EncryptByKey(Key_GUID(N'TestKey1'), CONVERT(varchar(11), @i)), HashBytes('SHA1', @string1));
INSERT INTO dbo.Hash1 (RecordID, EncryptResult, HashResult) VALUES (9, EncryptByKey(Key_GUID(N'TestKey1'), CONVERT(varchar(11), @i)), HashBytes('SHA1', @string1));

SELECT * FROM dbo.Hash1;

SELECT @lookUp = 2;					-- Pick some record to search for

-- Expect RecordIDs 4, 5, and 6
SELECT
	h.RecordID,
	CONVERT(int, CONVERT(varchar(11), DecryptByKey(h.EncryptResult))) AS 'Decrypted Value'
FROM dbo.Hash1				AS h
CROSS JOIN dbo.SecretSalt	AS s
WHERE s.SaltID		= 1
  AND h.HashResult	= HashBytes (
						'SHA1',
						CONVERT(varchar(63), DecryptByKey(s.Salt)) + CONVERT(varchar(11), @lookUp)
					)	-- This uses the index to find the record
  AND CONVERT(int, CONVERT(varchar(11), DecryptByKey(h.EncryptResult)))	= @lookUp;
GO