prog:lun10_plsql_find_cc_in_text
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
prog:lun10_plsql_find_cc_in_text [2013/07/11 21:57] – [Verwendung] gpipperr | prog:lun10_plsql_find_cc_in_text [2013/07/11 21:57] (aktuell) – [Quellen] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ===== In Textfeldern mit PL/SQL nach Kreditkarten Nummer mit Hilfe des Lun10 suchen ===== | ||
+ | In einer PCI konformen Umgebung dürfen keine Kreditkarten Nummer " | ||
+ | |||
+ | Um die Bemerkungsfelder zu testen, überprüft die folgende PL/SQL Prozedur | ||
+ | |||
+ | 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 " | ||
+ | |||
+ | |||
+ | |||
+ | ==== PL/SQL Umsetzung über ein Package ==== | ||
+ | |||
+ | Spezifikation: | ||
+ | |||
+ | <code sql search_CC.pks> | ||
+ | create or replace package search_CC | ||
+ | as | ||
+ | type num_arry is table of number | ||
+ | index by binary_integer; | ||
+ | |||
+ | | ||
+ | return varchar2; | ||
+ | |||
+ | /* | ||
+ | check if the number string can be check with the LuhnMod10 alogrithmus | ||
+ | | ||
+ | -1 Invalid | ||
+ | */ | ||
+ | | ||
+ | return integer; | ||
+ | |||
+ | /* | ||
+ | Read textstring and search for valid creditcards | ||
+ | |||
+ | */ | ||
+ | | ||
+ | return varchar2; | ||
+ | end; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | Body: | ||
+ | <code sql search_CC.pkb> | ||
+ | create or replace package body search_CC | ||
+ | as | ||
+ | | ||
+ | | ||
+ | |||
+ | | ||
+ | '; | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | , | ||
+ | return varchar2 | ||
+ | is | ||
+ | v_cc | ||
+ | v_return | ||
+ | v_result | ||
+ | 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 | ||
+ | | ||
+ | 'GPI Tools : analyseCCText =>check String for CC ' || v_cc); | ||
+ | |||
+ | if G_DEBUG | ||
+ | then | ||
+ | v_return := | ||
+ | | ||
+ | else | ||
+ | v_result := showCreditCardType (v_cc); | ||
+ | |||
+ | if v_result = G_CC_CARD_FOUND | ||
+ | then | ||
+ | | ||
+ | end if; | ||
+ | end if; | ||
+ | |||
+ | exit; | ||
+ | end if; | ||
+ | end loop; | ||
+ | |||
+ | | ||
+ | | ||
+ | || '' | ||
+ | || getCCfromList (p_list => p_list, p_start => p_start + 1); | ||
+ | else | ||
+ | | ||
+ | end if; | ||
+ | |||
+ | return v_return; | ||
+ | end getCCfromList; | ||
+ | |||
+ | |||
+ | |||
+ | /* | ||
+ | Read textstring and search for valid creditcards | ||
+ | |||
+ | */ | ||
+ | |||
+ | | ||
+ | return varchar2 | ||
+ | is | ||
+ | v_textstring | ||
+ | |||
+ | v_token | ||
+ | v_tokennum | ||
+ | v_tokenlen | ||
+ | v_count | ||
+ | v_return | ||
+ | v_length | ||
+ | begin | ||
+ | -- Remove text from the string | ||
+ | v_textstring := REPLACE (v_textstring, | ||
+ | v_textstring := | ||
+ | | ||
+ | ,' | ||
+ | ,' | ||
+ | v_textstring := REPLACE (v_textstring, | ||
+ | DBMS_OUTPUT.put_line ( | ||
+ | ' | ||
+ | -- remove spezieal signs | ||
+ | v_textstring := | ||
+ | | ||
+ | ,' | ||
+ | ,' | ||
+ | v_textstring := REPLACE (v_textstring, | ||
+ | DBMS_OUTPUT.put_line ( | ||
+ | ' | ||
+ | |||
+ | -- 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 ( | ||
+ | ' | ||
+ | |||
+ | 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 ( | ||
+ | ' | ||
+ | |||
+ | -- tokeniser all elements or the text, Sperator ' ' | ||
+ | OWA_UTIL.comma_to_ident_arr (v_textstring | ||
+ | ,v_token | ||
+ | ,v_tokennum | ||
+ | , | ||
+ | |||
+ | -- generate a moving window to collect the strings together | ||
+ | v_return := getCCfromList (p_list => v_token, p_start => 1); | ||
+ | return v_return; | ||
+ | | ||
+ | when others | ||
+ | then | ||
+ | | ||
+ | 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 | ||
+ | */ | ||
+ | |||
+ | | ||
+ | return varchar2 | ||
+ | is | ||
+ | v_cardnumber | ||
+ | v_cardstring | ||
+ | v_cardtype | ||
+ | v_return | ||
+ | |||
+ | v_lenght | ||
+ | |||
+ | v_d1 | ||
+ | v_d2 | ||
+ | v_d3 | ||
+ | v_d4 | ||
+ | begin | ||
+ | -- normalize number | ||
+ | v_cardstring := RTRIM (LTRIM (p_number)); | ||
+ | v_cardstring := | ||
+ | | ||
+ | UPPER (v_cardstring) | ||
+ | ,' | ||
+ | ,' | ||
+ | v_cardstring := REPLACE (v_cardstring, | ||
+ | |||
+ | -- check on valid number | ||
+ | begin | ||
+ | | ||
+ | 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, | ||
+ | v_d2 := TO_NUMBER (SUBSTR (v_cardstring, | ||
+ | v_d3 := TO_NUMBER (SUBSTR (v_cardstring, | ||
+ | v_d4 := TO_NUMBER (SUBSTR (v_cardstring, | ||
+ | |||
+ | --- 13 | ||
+ | if v_lenght = 13 | ||
+ | then | ||
+ | if v_d1 in (4) | ||
+ | then | ||
+ | | ||
+ | end if; | ||
+ | end if; | ||
+ | |||
+ | --14 | ||
+ | if v_lenght = 14 | ||
+ | then | ||
+ | if v_d2 in (36, 38) | ||
+ | then | ||
+ | | ||
+ | elsif v_d3 in (300, 301, 302, 303, 304, 305) | ||
+ | then | ||
+ | | ||
+ | end if; | ||
+ | end if; | ||
+ | |||
+ | --15 | ||
+ | if v_lenght = 15 | ||
+ | then | ||
+ | if v_d2 in (34, 37) | ||
+ | then | ||
+ | | ||
+ | elsif v_d4 in (2014, 2149) | ||
+ | then | ||
+ | | ||
+ | elsif v_d4 in (2131, 1800) | ||
+ | then | ||
+ | | ||
+ | end if; | ||
+ | end if; | ||
+ | |||
+ | --16 | ||
+ | if v_lenght = 16 | ||
+ | then | ||
+ | if v_d1 in (4) | ||
+ | then | ||
+ | | ||
+ | elsif v_d1 in (3) | ||
+ | then | ||
+ | | ||
+ | elsif v_d2 in (51, 52, 53, 54, 55) | ||
+ | then | ||
+ | | ||
+ | elsif v_d4 in (6011) | ||
+ | then | ||
+ | | ||
+ | end if; | ||
+ | end if; | ||
+ | |||
+ | |||
+ | -- Check on Lun CC | ||
+ | | ||
+ | ' | ||
+ | || TO_CHAR (v_cardnumber) | ||
+ | || ' and type ' | ||
+ | || v_cardtype); | ||
+ | |||
+ | if checkLuhnMod10 (v_cardnumber) = 0 | ||
+ | then | ||
+ | if G_DEBUG | ||
+ | then | ||
+ | | ||
+ | else | ||
+ | if v_cardtype != ' | ||
+ | then | ||
+ | v_return := G_CC_CARD_FOUND; | ||
+ | else | ||
+ | v_return := 'NO CC'; | ||
+ | end if; | ||
+ | end if; | ||
+ | else | ||
+ | if G_DEBUG | ||
+ | then | ||
+ | | ||
+ | else | ||
+ | | ||
+ | end if; | ||
+ | end if; | ||
+ | else | ||
+ | | ||
+ | end if; | ||
+ | |||
+ | return v_return; | ||
+ | end showCreditCardType; | ||
+ | |||
+ | /* | ||
+ | create arry from a number | ||
+ | |||
+ | */ | ||
+ | |||
+ | | ||
+ | return num_arry | ||
+ | is | ||
+ | v_numstring | ||
+ | v_nums | ||
+ | v_pos | ||
+ | begin | ||
+ | for i in 1 .. LENGTH (v_numstring) | ||
+ | loop | ||
+ | | ||
+ | | ||
+ | '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 Invalid | ||
+ | */ | ||
+ | |||
+ | | ||
+ | return integer | ||
+ | is | ||
+ | v_nums | ||
+ | v_pos pls_integer := 1; | ||
+ | v_return | ||
+ | v_total | ||
+ | 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 | ||
+ | | ||
+ | end loop; | ||
+ | else | ||
+ | v_return := v_return + v_total (i); | ||
+ | end if; | ||
+ | end loop; | ||
+ | |||
+ | return MOD (v_return, 10); | ||
+ | end; | ||
+ | end; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | |||
+ | ==== Verwendung ==== | ||
+ | |||
+ | <code sql> | ||
+ | -- Debug Messages ausgeben ( falls im Code freigeschaltet! ) | ||
+ | sql>set serveroutput on | ||
+ | |||
+ | - Text analysieren | ||
+ | |||
+ | sql> | ||
+ | from bemerkungstexte | ||
+ | where cdatum < sysdate -10 | ||
+ | / | ||
+ | |||
+ | |||
+ | id | result | ||
+ | --------------------------- | ||
+ | 100 VALID CC NUM TYPE VISA 45******* | ||
+ | |||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ==== Quellen ==== | ||
+ | |||
+ | * http:// |
prog/lun10_plsql_find_cc_in_text.txt · Zuletzt geändert: 2013/07/11 21:57 von gpipperr