Hiding passwords

Here is a worked example of my Obfuscation method.

-- obfuscate_example3.sql


-- read the table

	SELECT *
	FROM [CMI_ProDB_Live].[dbo].[RecordPasswords]
	WHERE ExternalSystem = 'WEB'
--	AND code = 'U4101259' -- marks test account


-- encrypt the PW 

	UPDATE [CMI_ProDB_Live].[dbo].[RecordPasswords]
	SET [Password] = CONVERT(nvarchar(255), CONVERT(varbinary(255), [Password]), 1) 
	WHERE ExternalSystem = 'WEB'
	AND [Password] NOT like '0x[0-9]%' Collate Latin1_General_CS_AI -- not already encrypted
	AND LEN([password]) > 7
	AND [password] NOT LIKE 'Â%' -- exclude pws that start with 'Â' 601543



-- decrypt on the fly

	SELECT CONVERT(nvarchar(255), CONVERT(varbinary(255), [Password], 1)) [Password]
	FROM [CMI_ProDB_Live].[dbo].[RecordPasswords]
	WHERE LEFT([Password], 3) like '0x[0-9]' Collate Latin1_General_CS_AI -- is encrypted
	AND LEN([password]) > 7
	AND code = 'U4101259' -- marks test account (Password2018)


-- decrypt PW permenantly

	UPDATE [CMI_ProDB_Live].[dbo].[RecordPasswords_16may2018]
	SET [Password] = CONVERT(nvarchar(255), CONVERT(varbinary(255), [Password], 1)) 
	WHERE LEFT([Password], 2) = '0x' Collate Latin1_General_CS_AI -- is encrypted
	AND LEN([password]) > 7

--

-- check there are no differences

	SELECT aa.password, bb.password, *
	FROM [CMI_ProDB_Live].[dbo].[RecordPasswords_16may2018] aa
	JOIN  [CMI_ProDB_Live].[dbo].[RecordPasswords_16may2018B] bb
	ON aa.contactcounter = bb.contactcounter
	AND aa.code = bb.code
	AND aa.externalsystem = bb.externalsystem
	WHERE aa.externalsystem = 'Web'
	and aa.password  bb.password -- 601543 rows
    


-- manually recover pws

	SELECT 'update [CMI_ProDB_Live].[dbo].[RecordPasswords] SET [Password] = ''' + bb.[password] + ''' WHERE code = ''' + aa.code + ''''
	FROM [CMI_ProDB_Live].[dbo].[RecordPasswords] aa
	JOIN [CMI_ProDB_Live].[dbo].[RecordPasswords_15may2018_b] bb
	ON aa.code = bb.code
	WHERE aa.ExternalSystem = 'WEB'
	AND bb.ExternalSystem = 'WEB'
	AND aa.[password]  bb.[password]
	AND aa.[password] LIKE '0x%'
--	AND bb.password LIKE 'Â%'
	ORDER BY aa.[password] DESC

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s