===== In Textfeldern mit PL/SQL nach Kreditkarten Nummer mit Hilfe des Lun10 suchen =====
In einer PCI konformen Umgebung dürfen keine Kreditkarten Nummer "versteckt" in Bemerkungsfeldern hinterlegt werden.
Um die Bemerkungsfelder zu testen, überprüft die folgende PL/SQL Prozedur „analyseCCText“ ob in dem Text eine Kreditkarten Nummer versteckt haben könnte.
Dazu werden alle Buchstaben aus dem Text entfernt und dann die entstandenen Zahlenfolgen Stelle für Stelle auf eine mögliche Kreditkarte untersucht.
Mit dem Luhn10 Algorithmus für Prüfung auf eine gültige Kreditkarten Sequenz wird die Zahlenfolge einer Kreditkarte auch dann erkannt, wenn der Mitarbeiter diese mit diversen "Tricks" möglichst unkenntlich gemacht hat.
==== PL/SQL Umsetzung über ein Package ====
Spezifikation:
create or replace package search_CC
as
type num_arry is table of number
index by binary_integer;
function showCreditCardType (p_number varchar2)
return varchar2;
/*
check if the number string can be check with the LuhnMod10 alogrithmus
1 Sucess - is a Luh10 Number
-1 Invalid
*/
function checkLuhnMod10 (p_number number)
return integer;
/*
Read textstring and search for valid creditcards
*/
function analyseCCText (p_text varchar2)
return varchar2;
end;
/
Body:
create or replace package body search_CC
as
G_DEBUG constant boolean := false;
G_CC_CARD_FOUND constant varchar2 (20) := 'CC';
g_charreturn constant varchar2 (2) := '
';
function getCCfromList (p_list OWA_UTIL.ident_arr
,p_start pls_integer default 1)
return varchar2
is
v_cc varchar2 (32000);
v_return varchar2 (32000);
v_result varchar2 (32000);
begin
if p_start < p_list.COUNT
then
--- Count of tockens
for i in p_start .. p_list.COUNT
loop
DBMS_OUTPUT.put_line (
'GPI Tools : getCCfromList => get tocken ('
|| TO_CHAR (i)
|| ') => '
|| p_list (i));
v_cc := v_cc || p_list (i);
if LENGTH (v_cc) > 13
then
DBMS_OUTPUT.put_line (
'GPI Tools : analyseCCText =>check String for CC ' || v_cc);
if G_DEBUG
then
v_return :=
showCreditCardType (v_cc) || ' for ' || v_cc || ' ';
else
v_result := showCreditCardType (v_cc);
if v_result = G_CC_CARD_FOUND
then
v_return := v_result || ' for ' || v_cc || ' ';
end if;
end if;
exit;
end if;
end loop;
v_return :=
v_return
|| ''
|| getCCfromList (p_list => p_list, p_start => p_start + 1);
else
return '';
end if;
return v_return;
end getCCfromList;
/*
Read textstring and search for valid creditcards
*/
function analyseCCText (p_text varchar2)
return varchar2
is
v_textstring varchar2 (32000) := p_text;
v_token OWA_UTIL.ident_arr;
v_tokennum OWA_UTIL.num_arr;
v_tokenlen integer;
v_count pls_integer := 1;
v_return varchar2 (32000) := 'NOT ANALYSED';
v_length integer := 0;
begin
-- Remove text from the string
v_textstring := REPLACE (v_textstring, g_charreturn, ' ');
v_textstring :=
TRANSLATE (UPPER (v_textstring)
,'ABCDEFGHIJKLMNOPQRSTUVWÄÜÖXYZÀÌ'
,'X');
v_textstring := REPLACE (v_textstring, 'X', '');
DBMS_OUTPUT.put_line (
'GPI Tools : analyseCCText => normalized string 1 ' || v_textstring);
-- remove spezieal signs
v_textstring :=
TRANSLATE (v_textstring
,'²³{[]}\~|!"§$%&/()=?`*''#+´ß@€^°<>„“…¿'
,'X');
v_textstring := REPLACE (v_textstring, 'X', '');
DBMS_OUTPUT.put_line (
'GPI Tools : analyseCCText => normalized string 2 ' || v_textstring);
-- use this to seperate numbers strings
v_textstring := REPLACE (v_textstring, '.', ',');
v_textstring := REPLACE (v_textstring, '-', ',');
v_textstring := REPLACE (v_textstring, ':', ',');
v_textstring := REPLACE (v_textstring, '_', ',');
v_textstring := REPLACE (v_textstring, ';', ',');
DBMS_OUTPUT.put_line (
'GPI Tools : analyseCCText => normalized string 3 ' || v_textstring);
v_textstring := RTRIM (LTRIM (v_textstring));
-- three times space
v_textstring := REPLACE (v_textstring, ' ', ' ');
-- two times space
v_textstring := REPLACE (v_textstring, ' ', ' ');
-- use , as sperator
v_textstring := REPLACE (v_textstring, ' ', ',');
v_textstring := REPLACE (v_textstring, ',,,,,', ',');
v_textstring := REPLACE (v_textstring, ',,,', ',');
v_textstring := REPLACE (v_textstring, ',,', ',');
v_textstring := RTRIM (LTRIM (v_textstring, ','), ',');
-- if only on number exists
v_textstring := v_textstring || ',';
DBMS_OUTPUT.put_line (
'GPI Tools : analyseCCText => normalized string 4 ' || v_textstring);
-- tokeniser all elements or the text, Sperator ' '
OWA_UTIL.comma_to_ident_arr (v_textstring
,v_token
,v_tokennum
,v_tokenlen);
-- generate a moving window to collect the strings together
v_return := getCCfromList (p_list => v_token, p_start => 1);
return v_return;
exception
when others
then
return v_textstring;
end analyseCCText;
/*
identify a number as a creditcard number
Returns the type of a creditcard number
check if the number is a valid (Luhn Number check)
Returns: The type of Creditcard or NONE if not valid
*/
function showCreditCardType (p_number varchar2)
return varchar2
is
v_cardnumber number;
v_cardstring varchar2 (200);
v_cardtype varchar2 (200) := 'UNKOWN';
v_return varchar2 (200) := 'UNKOWN';
v_lenght pls_integer;
v_d1 pls_integer;
v_d2 pls_integer;
v_d3 pls_integer;
v_d4 pls_integer;
begin
-- normalize number
v_cardstring := RTRIM (LTRIM (p_number));
v_cardstring :=
TRANSLATE (
UPPER (v_cardstring)
,'.-,;:_#*+@ABCDEFGHIJKLMNOPQRSTUVW!"§$%&/()=²³{[]}?ßÄÜÖ'
,'X');
v_cardstring := REPLACE (v_cardstring, 'X', '');
-- check on valid number
begin
v_cardnumber := TO_NUMBER (v_cardstring);
exception
when VALUE_ERROR
then
return 'VALUE ERROR';
end;
--check length
v_lenght := LENGTH (v_cardstring);
if v_lenght between 13 and 16
then
v_d1 := TO_NUMBER (SUBSTR (v_cardstring, 1, 1));
v_d2 := TO_NUMBER (SUBSTR (v_cardstring, 1, 2));
v_d3 := TO_NUMBER (SUBSTR (v_cardstring, 1, 3));
v_d4 := TO_NUMBER (SUBSTR (v_cardstring, 1, 4));
--- 13
if v_lenght = 13
then
if v_d1 in (4)
then
v_cardtype := 'VISA';
end if;
end if;
--14
if v_lenght = 14
then
if v_d2 in (36, 38)
then
v_cardtype := 'DINERS CLUB';
elsif v_d3 in (300, 301, 302, 303, 304, 305)
then
v_cardtype := 'DINERS CLUB';
end if;
end if;
--15
if v_lenght = 15
then
if v_d2 in (34, 37)
then
v_cardtype := 'AMEX';
elsif v_d4 in (2014, 2149)
then
v_cardtype := 'enROUTE';
elsif v_d4 in (2131, 1800)
then
v_cardtype := 'JBC';
end if;
end if;
--16
if v_lenght = 16
then
if v_d1 in (4)
then
v_cardtype := 'VISA';
elsif v_d1 in (3)
then
v_cardtype := 'JBC';
elsif v_d2 in (51, 52, 53, 54, 55)
then
v_cardtype := 'MASTERCARD';
elsif v_d4 in (6011)
then
v_cardtype := 'DISCOVER';
end if;
end if;
-- Check on Lun CC
DBMS_OUTPUT.put_line (
'GPI Tools : showCreditCardType => test num '
|| TO_CHAR (v_cardnumber)
|| ' and type '
|| v_cardtype);
if checkLuhnMod10 (v_cardnumber) = 0
then
if G_DEBUG
then
v_return := 'VALID CC NUM TYPE ' || v_cardtype;
else
if v_cardtype != 'UNKOWN'
then
v_return := G_CC_CARD_FOUND;
else
v_return := 'NO CC';
end if;
end if;
else
if G_DEBUG
then
v_return := 'CC NUM Error TYPE ' || v_cardtype;
else
v_return := '-';
end if;
end if;
else
v_cardtype := 'NO CC NUM';
end if;
return v_return;
end showCreditCardType;
/*
create arry from a number
*/
function getNumArray (p_number number)
return num_arry
is
v_numstring varchar2 (32) := TO_CHAR (p_number);
v_nums num_arry;
v_pos pls_integer;
begin
for i in 1 .. LENGTH (v_numstring)
loop
v_nums (i) := TO_NUMBER (SUBSTR (v_numstring, i, 1));
DBMS_OUTPUT.put_line (
'GPI Tools : getNumArray => get num ' || TO_CHAR (v_nums (i)));
end loop;
return v_nums;
end;
/*
check if the number string can be check with the LuhnMod10 alogrithmus
1 Sucess - is a Luh10 Number
-1 Invalid
*/
function checkLuhnMod10 (p_number number)
return integer
is
v_nums num_arry;
v_pos pls_integer := 1;
v_return integer := 0;
v_total num_arry;
begin
-- create number array from a number
v_nums := getNumArray (p_number);
for i in reverse 1 .. v_nums.COUNT
loop
if MOD (v_pos, 2) = 0
then
v_total (i) := (v_nums (i) * 2);
else
v_total (i) := (v_nums (i));
end if;
v_pos := v_pos + 1;
end loop;
for i in 1 .. v_total.COUNT
loop
if v_total (i) > 9
then
v_nums := getNumArray (v_total (i));
for x in 1 .. v_nums.COUNT
loop
v_return := v_return + v_nums (x);
end loop;
else
v_return := v_return + v_total (i);
end if;
end loop;
return MOD (v_return, 10);
end;
end;
/
==== Verwendung ====
-- Debug Messages ausgeben ( falls im Code freigeschaltet! )
sql>set serveroutput on
- Text analysieren
sql>select id, search_cc.analyseCCText(bemerkung) as result
from bemerkungstexte
where cdatum < sysdate -10
/
id | result
---------------------------
100 VALID CC NUM TYPE VISA 45*******
==== Quellen ====
* http://en.wikipedia.org/wiki/Luhn_algorithm