Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:lun10_plsql_find_cc_in_text

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
prog:lun10_plsql_find_cc_in_text [2013/07/11 21:57]
gpipperr [Verwendung]
prog:lun10_plsql_find_cc_in_text [2013/07/11 21:57] (aktuell)
gpipperr [Quellen]
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 "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:
 +
 +<code sql search_CC.pks>
 +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
 +        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;
 +/
 +</code>
 +
 +Body:
 +<code sql  search_CC.pkb>
 +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
 +              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;
 +/
 +</code>
 +
 +
 +==== Verwendung ====
 +
 +<code sql>
 +-- 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******* 
 +
 +
 +</code>
 +
 +
 +==== Quellen ====
 +
 +  * http://en.wikipedia.org/wiki/Luhn_algorithm 
"Autor: Gunther Pipperr"
prog/lun10_plsql_find_cc_in_text.txt · Zuletzt geändert: 2013/07/11 21:57 von gpipperr