webinar_0807.txt

Confidential Data Code Walkthrough - Keep Private Data Private!



TSQL

-- Find columns specifying SSN, TIN, etc
SELECT TABLE_NAME
	, COLUMN_NAME
	, *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%SSN%'
	OR COLUMN_NAME LIKE '%Social%'
	OR COLUMN_NAME LIKE '%TIN%'
	OR COLUMN_NAME LIKE '%Tax%'
	OR COLUMN_NAME LIKE '%Identification%'
	

-- Searching through objects
SELECT OBJECT_NAME(id)
	, [text]
FROM sys.syscomments
WHERE [text] LIKE '%SSN%'
	OR [text] LIKE '%Social%'
	OR [text] LIKE '%TIN%'
	OR [text] LIKE '%Tax%'
	OR [text] LIKE '%Identification%' 
	
	
-- Simple RegEx examples
CREATE TABLE InitialExamples(
	ColumnOne VARCHAR(25),
	ColumnTwo VARCHAR(100)
)

DROP TABLE InitialExamples


INSERT INTO InitialExamples
VALUES ('0','The quick brown fox 0 jumped over the lazy dogs.')
	, ('00','The quick brown fox jumped over the lazy dogs.')
	, ('000','The quick brown fox 00 jumped over the lazy dogs.')
	, ('00.00','The quick brown fox 00.00 jumped over the lazy dogs.')
	, ('00.0','Th00.0 quick brown fox jumped over the lazy dogs.')
	, ('0','0The quick brown fox jumped over the lazy dogs.')


SELECT *
FROM InitialExamples
WHERE ColumnOne LIKE '[0-9]'


SELECT *
FROM InitialExamples
WHERE ColumnOne LIKE '[0-9][0-9]'


SELECT *
FROM InitialExamples
WHERE ColumnOne LIKE '%[0-9]%'


SELECT *
FROM InitialExamples
WHERE ColumnOne LIKE '[0-9]%'


SELECT *
FROM InitialExamples
WHERE ColumnOne LIKE '[0-9][0-9].%'


SELECT *
FROM InitialExamples
WHERE ColumnTwo LIKE '%[0-9][0-9].%'


SELECT *
FROM InitialExamples
WHERE ColumnTwo LIKE '[^a-z]%'


SELECT *
FROM InitialExamples
WHERE ColumnTwo LIKE '[a-z][a-z][0-9][0-9]%'

/* 
	
	The Following Code Is For Example Purposes ONLY
	See: http://www.socialsecurity.gov/employer/stateweb.htm and http://www.ssa.gov/history/ssn/geocard.html
	
*/


CREATE TABLE SSNTable(
	SSN VARCHAR(11),
	TextField VARCHAR(500)
)


INSERT INTO SSNTable
VALUES ('000-00-0000','The quick brown fox 000-00-0000 jumped over the lazy dogs.')
	, ('000-00-0001','000-00-0001 The quick brown fox jumped over the lazy dogs.')
	, ('000-00-0002',' The quick brown fox jumped over the lazy dogs. 000-00-0002')
	, ('000000003',' The quick brown fox jumped over the 000.00.0002 lazy dogs.')


SELECT *
FROM SSNTable WITH(NOLOCK)
WHERE SSN LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'


SELECT *
FROM SSNTable WITH(NOLOCK)
WHERE SSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'


SELECT *
FROM SSNTable WITH(NOLOCK)
WHERE TextField LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'


SELECT *
FROM SSNTable WITH(NOLOCK)
WHERE TextField LIKE '%[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%'


SELECT  *
FROM SSNTable WITH(NOLOCK)
WHERE TextField LIKE '%[0-9][0-9][0-9][^0-9][0-9][0-9][^0-9][0-9][0-9][0-9][0-9]%'


SELECT *
FROM SSNTable


-- Masking example:
SELECT SSN
	, 'XXX-XX-' + RIGHT(SSN,4) AS SSN
FROM SSNTable


DROP TABLE SSNTable

/*  Loop approach to finding columns storing SSNs  */
-- Loop table
CREATE TABLE PrivacySecurityCheck(
	TableName VARCHAR(250),
	ColumnName VARCHAR(250)
)

SELECT ROW_NUMBER() OVER (ORDER BY TABLE_NAME) ID
	, TABLE_NAME TableName
	, COLUMN_NAME ColumnName
INTO ##Loop
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar','nvarchar','char')
	AND CHARACTER_MAXIMUM_LENGTH >= 11


DECLARE @b INT = 1, @m INT, @t NVARCHAR(250), @c NVARCHAR(250), @sql NVARCHAR(MAX)
SELECT @m = MAX(ID) FROM ##Loop

WHILE @b <= @m
BEGIN
	SELECT @t = TableName FROM ##Loop WHERE ID = @b
	SELECT @c = ColumnName FROM ##Loop WHERE ID = @b 

	SET @sql = 'IF EXISTS(
		SELECT 1
		FROM ' + QUOTENAME(@t) + ' WITH(NOLOCK)
		WHERE ' + QUOTENAME(@c) + ' LIKE ''%[0-9][0-9][0-9][^0-9][0-9][0-9][^0-9][0-9][0-9][0-9][0-9]%''
	)
	INSERT INTO PrivacySecurityCheck
	VALUES (''' + QUOTENAME(@t) + ''',''' + QUOTENAME(@c) + ''')'
	
	--PRINT @t, @c
	--PRINT @sql
	EXEC sp_executesql @sql
	
	SET @b = @b + 1
END


SELECT *
FROM PrivacySecurityCheck


DROP TABLE PrivacySecurityCheck
DROP TABLE ##Loop




PowerShell

"Let's see if we find the number 000-00-0000 in this string." -Match "\d{3}-\d{2}-\d{4}"
"Do we find the number 00-0000000 in this sentence?" -Match "\d{2}-\d{7}"
"Another example 12.12 offers some insight." -Match "\d.\d"
"Okay, now we're entering into Saxon Math territory with 1-1-1 as a possible match." -Match "\d-\d-\d"


"ValueOne,ValueTwo,ThreeIsALongerColumn,23.65,8065551212,000-00-0000" -Match "\d{3}-\d{2}-\d{4}"
"ValueOne,ValueTwo,ThreeIsALongerColumn,23.65,8065551212,000-00-0000" -Replace "\d{3}-\d{2}-(\d{4})","XXX-XX-`${1}"
"ValueOne,ValueTwo,ThreeIsALongerColumn,23.65,8065551212,000-00-0000" -Replace "(\d{3})-\d{2}-(\d{4})","`${1}-XX-`${2}"
"ValueOne,ValueTwo,ThreeIsALongerColumn,23.65,8065551212,00-0000000" -Replace "\d{2}-\d{3}(\d{4})","XX-XXX`${1}"


$string = "ValueOne,ValueTwo,ThreeIsALongerColumn,23.65,8065551212,000-00-0000" 
$string = $string -Replace "\d{3}-\d{2}-(\d{4})","XXX-XX-`${1}"
$string


## Example file
$file = "C:\files\ssn.txt"
$ext = $file.Substring($file.LastIndexOf("."))
$loc = $file.Substring(0,($file.LastIndexOf("\")+1))
$name = $file.Substring($file.LastIndexOf("\")+1).Replace($ext,"")

$valid = $loc + $name + "_clean" + $ext

New-Item $valid -ItemType file

$read = New-Object System.IO.StreamReader($file)
$cleaned = New-Object System.IO.StreamWriter($valid)

while (($line = $read.ReadLine()) -ne $null)
{
    $line = $line -replace "\d{3}-\d{2}-(\d{4})","XXX-XX-`${1}"
    $cleaned.WriteLine($line)
    $cleaned.Flush()
}

$read.Close()
$read.Dispose()
$cleaned.Close()
$cleaned.Dispose()


Function FindWord ($githubfolder, $pattern)
{
    $cnt = Get-ChildItem $githubfolder -Include @("*.sql", "*.cs", "*.xml", "*.ps1") -Recurse | Select-String -pattern $pattern
    if ($cnt.Count -gt 0)
    {
        $cnt
    }

}

FindWord -githubfolder "C:\Path\" -pattern "Social"