Generowanie losowych odpowiedników dla danych wrażliwych dla RODO . Część 3

W poprzedniej części  przygotowaliśmy szkic  procedury składowanej implementującej prosty mechanizm wykrywania danych osobowych.

To czego zabrakło, to wykorzystanie funkcji tabelarycznych  do wykrywania tych danych, które posiadają sumę kontrolną i same wyrażenia regularne mogą nie wystarczyć.

Zajmijmy się   identyfikatorami PESEL, NIP, REGON. Zainteresowanych budową  takich identyfikatorów odsyłam do powszechnie dostępnych źródeł (m.in WIkipedia).

Poniżej przedstawiono kod trzech prostych funkcji tabelarycznych napisanych w Transact SQL.

PESEL

CREATE FUNCTION [dbo].[fnCheckPESEL] (@value VARCHAR(100))
RETURNS TABLE
AS
RETURN

  WITH CheckBase as
  (
  SELECT  
		RTRIM(LTRIM(@value))

  AS RAWPESEL,

  LEFT(@value,10) +  
			TRY_CONVERT(VARCHAR(1), ((
			TRY_CONVERT(INT,SUBSTRING(@value,1,1))*9 +
			TRY_CONVERT(INT,SUBSTRING(@value,2,1))*7 +
			TRY_CONVERT(INT,SUBSTRING(@value,3,1))*3 +
			TRY_CONVERT(INT,SUBSTRING(@value,4,1))*1 +
			TRY_CONVERT(INT,SUBSTRING(@value,5,1))*9 +
			TRY_CONVERT(INT,SUBSTRING(@value,6,1))*7 +
			TRY_CONVERT(INT,SUBSTRING(@value,7,1))*3 + 
			TRY_CONVERT(INT,SUBSTRING(@value,8,1))*1 +
			TRY_CONVERT(INT,SUBSTRING(@value,9,1))*9 +
			TRY_CONVERT(INT,SUBSTRING(@value,10,1))*7)% 10)) 
			as PESEL
  )

select RAWPESEL, PESEL,
  isValid = case when  RAWPESEL = PESEL 
			and RAWPESEL like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'  
				then 
					1 
				else 
					0 
			end
	
 from  CheckBase

NIP

CREATE FUNCTION [dbo].[fnCheckNIP] (@value VARCHAR(100))
RETURNS TABLE
AS
RETURN

  WITH CheckBase as
  (
  SELECT  
		RTRIM(LTRIM(@value))

  AS RAWNIP,

			LEFT(@value,9) + 
					 TRY_CONVERT(VARCHAR(1), ((
					 TRY_CONVERT(INT,SUBSTRING(@value,1,1))*6
				   + TRY_CONVERT(INT,SUBSTRING(@value,2,1))*5
				   + TRY_CONVERT(INT,SUBSTRING(@value,3,1))*7
				   + TRY_CONVERT(INT,SUBSTRING(@value,4,1))*2
				   + TRY_CONVERT(INT,SUBSTRING(@value,5,1))*3
				   + TRY_CONVERT(INT,SUBSTRING(@value,6,1))*4
				   + TRY_CONVERT(INT,SUBSTRING(@value,7,1))*5
				   + TRY_CONVERT(INT,SUBSTRING(@value,8,1))*6
				   + TRY_CONVERT(INT,SUBSTRING(@value,9,1))*7)% 11)) 
						
						as NIP
  )

select RAWNIP, NIP,
  isValid = case when  RAWNIP = NIP 
			and RAWNIP like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'  
			then 
				1 
			else 
				0 
			end
	
 from  CheckBase

REGON

ALTER FUNCTION [dbo].[fnCheckREGON] (@value VARCHAR(100))
RETURNS TABLE
AS
RETURN

  WITH CheckBase as
  (
  SELECT  
		RTRIM(LTRIM(@value))

  AS RAWREGON,

			LEFT(@value,8) +  
				REPLACE(
					TRY_CONVERT(VARCHAR(1), 
					(
						(
						TRY_CONVERT(INT,SUBSTRING(@value,1,1))*8
						+TRY_CONVERT(INT,SUBSTRING(@value,2,1))*9
						+TRY_CONVERT(INT,SUBSTRING(@value,3,1))*2
						+TRY_CONVERT(INT,SUBSTRING(@value,4,1))*3
						+TRY_CONVERT(INT,SUBSTRING(@value,5,1))*4
						+TRY_CONVERT(INT,SUBSTRING(@value,6,1))*5
						+TRY_CONVERT(INT,SUBSTRING(@value,7,1))*6
						+TRY_CONVERT(INT,SUBSTRING(@value,8,1))*7
						) % 11)),'*','0')  
						
						as REGON
  )

select RAWREGON, REGON,
  isValid = case when  RAWREGON = REGON 
			and RAWREGON like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'  
			then 
				1 
			else 
				0 
			end
	
 from  CheckBase

Przykłady wywołania

 select * from  [dbo].[fnCheckPESEL] ('00000000001')
 select * from  [dbo].[fnCheckREGON] ('000000001')
 select * from  [dbo].[fnCheckNIP]	('0000000001')

Wynik działania

RAWPESEL PESEL isValid
00000000001 00000000000 0

RAWREGON REGON isValid
000000001 000000000 0

RAWNIP NIP isValid
0000000001 0000000000 0

Każda z tych funkcji jest zbudowana na podstawie tego samego schematu.
Na poczatku pobieramy na wejściu danę do weryfikacji, następnie pobieramy cześć danych bez sumy kontrolnej i wyliczany na jej podstawie brakującą sumę kontrolną. Jeśli tak spreparowany identyfikator jest tożsami z wejściowym i dodatkowo składa się z samych cyfr, możemy uznać, ze mamy do czynienia z poprawnymi danymi. W ten sposón wyznaczana jest kolumna isValid.

Jak można je zastosować ?

select 'PESEL detected' as Status,
fn.isValid,d.DataValue,d.idruletype,d.RuleName,fn.*,
t.* from DaneOsoboweDane d 
join DaneOsoboweTabele t
on (t.lp=d.lp)
cross apply [dbo].[fnCheckPESEL] (d.DataValue) as fn 
where fn.isValid=1

 select 'REGON detected' as Status,
fn.isValid,d.DataValue,d.idruletype,d.RuleName,fn.*,
t.* from DaneOsoboweDane d 
join DaneOsoboweTabele t
on (t.lp=d.lp)
cross apply [dbo].[fnCheckREGON] (d.DataValue) as fn 
where fn.isValid=1


select 'NIP detected' as Status,
fn.isValid,d.DataValue,d.idruletype,d.RuleName,fn.*,
t.* from DaneOsoboweDane d 
join DaneOsoboweTabele t
on (t.lp=d.lp)
cross apply [dbo].[fnCheckNIP] (d.DataValue) as fn 
where fn.isValid=1

Przydatne strony:

http://walidator.testerzy.pl/

http://zylla.wipos.p.lodz.pl/ut/nip-rego.html