Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
===== 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
       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;
/
</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
             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;
/
</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