code to check the password at least one upper case letter - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: code to check the password at least one upper case letter

  1. #11
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Trina --

    Read my post again... if you use 'password' your are not applying regexp_instr() against the variable password but against the string 'password'.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  2. #12
    Join Date
    Oct 2000
    Posts
    144
    Hi Hrishy,
    I changed it, reran the function, tested it. It is still would not work.

    SQL> CREATE OR REPLACE FUNCTION trina_verify_function
    2 (username varchar2,
    3 password varchar2,
    4 old_password varchar2)
    5 RETURN boolean IS
    6 n boolean;
    7 m integer;
    8 differ integer;
    9 isdigit boolean;
    10 ischar boolean;
    11 ispunct boolean;
    12 isupper boolean;
    13 digitarray varchar2(20);
    14 punctarray varchar2(25);
    15 chararray varchar2(52);
    16 regular_expr varchar2(50);
    17
    18 BEGIN
    19 digitarray:= '0123456789';
    20 chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    21 punctarray:='!"#$%&()``*+,-/:;<=>?_';
    22 regular_expr:= '[A-Z]';
    23 if regexp_instr(password,regular_expr) > 0 THEN
    24 dbms_output.put_line('it has one upper case letter');
    25 end if;
    26
    27 -- if everything is fine retrun true.
    28 RETURN(true);
    29 END;
    30 /

    Function created.

    SQL> alter user trina identified by trina_7777777777;

    User altered.

    SQL> alter user trina identified by trina_B77777777;

    User altered.

  3. #13
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    regexp-instr() function works fine...

    Code:
    SQL> 
    SQL> select regexp_instr('trina_7777777777','[A-Z]') from dual;
    
    REGEXP_INSTR('TRINA_7777777777','[A-Z]')
    ---------------------------------------------------------------
                                                                  0
    
    SQL> 
    SQL> select regexp_instr('trina_B777777777','[A-Z]') from dual;
    
    REGEXP_INSTR('TRINA_B777777777','[A-Z]')
    ---------------------------------------------------------------
                                                                  7
    
    SQL>
    Why are you not raising application error anymore?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #14
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Show us the output of

    select trina_verify_function('TRINA','trina_55555555555','trina')
    from dual


    and

    select trina_verify_function('TRINA','trinA_55555555555','trina')
    from dual

  5. #15
    Join Date
    Oct 2000
    Posts
    144
    Hi Hrishy,
    Below are the output on my side.

    SQL> select trina_verify_function('TRINA','trina_5555555555', 'trina')
    2 from dual;
    select trina_verify_function('TRINA','trina_5555555555', 'trina')
    *
    ERROR at line 1:
    ORA-06552: PL/SQL: Statement ignored
    ORA-06553: PLS-382: expression is of wrong type


    SQL> select trina_verify_function('TRINA','trinA_5555555555', 'trina')
    2 from dual;
    select trina_verify_function('TRINA','trinA_5555555555', 'trina')
    *
    ERROR at line 1:
    ORA-06552: PL/SQL: Statement ignored
    ORA-06553: PLS-382: expression is of wrong type

  6. #16
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Your function will always return true as you have the return in the wrong place.

    Try this

    Code:
    CREATE OR REPLACE FUNCTION trina_verify_function
     (username varchar2,
     password varchar2,
     old_password varchar2)
     RETURN boolean IS
     n boolean;
     m integer;
     differ integer;
     isdigit boolean;
     ischar boolean;
     ispunct boolean;
     isupper boolean;
     digitarray varchar2(20);
     punctarray varchar2(25);
     chararray varchar2(52);
     regular_expr varchar2(50);
    
     BEGIN
     digitarray:= '0123456789';
     chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
     punctarray:='!"#$%&()``*+,-/:;<=>?_';
     regular_expr:= '[A-Z]';
     if regexp_instr(password,regular_expr) > 0 THEN
     --dbms_output.put_line('it has one upper case letter');
     RETURN TRUE ;
     end if;
      -- if everything is not fine retrun False
     RETURN FALSE;
     END;
    /
    Now for the unit tests

    Code:
    begin
    if trina_verify_function('TRINA','trina_55555555555','trina') then
      dbms_output.put_line('True');
    else
      dbms_output.put_line('False');
    end if ;
    end ;
    
    /
    
    begin
    if trina_verify_function('TRINA','trinA_5555555555', 'trina')
     then
      dbms_output.put_line('True');
    else
      dbms_output.put_line('False');
    end if ;
    end ;
    
    /

  7. #17
    Join Date
    Oct 2000
    Posts
    144
    Hi Hrishy,
    I applied your code and tried to retest. I did the select and it gave me error. Then, I tried to test out changing the password with and without upper case, it will give me the error.

    SQL> CREATE OR REPLACE FUNCTION trina_verify_function
    2 (username varchar2,
    3 password varchar2,
    4 old_password varchar2)
    5 RETURN boolean IS
    6 n boolean;
    7 m integer;
    8 differ integer;
    9 isdigit boolean;
    10 ischar boolean;
    11 ispunct boolean;
    12 isupper boolean;
    13 digitarray varchar2(20);
    14 punctarray varchar2(25);
    15 chararray varchar2(52);
    16 regular_expr varchar2(50);
    17
    18 BEGIN
    19 digitarray:= '0123456789';
    20 chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    21 punctarray:='!"#$%&()``*+,-/:;<=>?_';
    22 regular_expr:= '[A-Z]';
    23 if regexp_instr('password','regular_expr') > 0 THEN
    24 -- raise_application_error(-20006, 'Password must contain at least one upper case letter.');
    25 return true;
    26 end if;
    27 -- if everything is fine return false.
    28 RETURN (false);
    29 END;
    30 /

    Function created.

    SQL> show errors
    No errors.
    SQL> begin
    2 if trina_verify_function('TRINA','trina_5555555555','trina') then
    3 dbms_output.put_line('True');
    4 else
    5 dbms_output.put_line('False');
    6 end if;
    7 end;
    8 /

    PL/SQL procedure successfully completed.

    SQL> begin
    2 if trina_verify_function('TRINA','trinA_5555555555','trina') then
    3 dbms_output.put_line('True');
    4 else
    5 dbms_output.put_line('False');
    6 end if;
    7 end;
    8 /

    PL/SQL procedure successfully completed.


    SQL> select trina_verify_function('TRINA','trinA_5555555555','trina') from dual;
    select trina_verify_function('TRINA','trinA_5555555555','trina') from dual
    *
    ERROR at line 1:
    ORA-06552: PL/SQL: Statement ignored
    ORA-06553: PLS-382: expression is of wrong type


    SQL> alter user trina identified by trina_33333333333333;
    alter user trina identified by trina_33333333333333
    *
    ERROR at line 1:
    ORA-28003: password verification for the specified password failed
    ORA-28003: password verification for the specified password failed


    SQL> alter user trina identified by trina_333B333333;
    alter user trina identified by trina_333B333333
    *
    ERROR at line 1:
    ORA-28003: password verification for the specified password failed
    ORA-28003: password verification for the specified password failed

  8. #18
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    The way to test the function is

    Code:
    set serveroutput on
    
    begin
    if trina_verify_function('TRINA','trina_55555555555','trina') then
      dbms_output.put_line('True');
    else
      dbms_output.put_line('False');
    end if ;
    end ;
    
    /
    
    begin
    if trina_verify_function('TRINA','trinA_5555555555', 'trina')
     then
      dbms_output.put_line('True');
    else
      dbms_output.put_line('False');
    end if ;
    end ;
    
    /
    and not by running

    select trina_verify_function('TRINA','trinA_5555555555', 'trina')
    from dual

    23 if regexp_instr('password','regular_expr') > 0 THEN
    is wrong it shoud read instead

    if regexp_instr(password,regular_expr) > 0 THEN
    Last edited by hrishy; 10-20-2009 at 10:39 AM.

  9. #19
    Join Date
    Oct 2000
    Posts
    144
    Hi Hrishy,
    Either way, it is still shwoing false.



    SQL> show serveroutput
    serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
    SQL> begin
    2 if trina_verify_function('TRINA','trina_5555555555','trina') then
    3 dbms_output.put_line('True');
    4 else
    5 dbms_output.put_line('False');
    6 end if;
    7 end;
    8 /
    False

    PL/SQL procedure successfully completed.

    SQL> begin
    2 if trina_verify_function('TRINA','trinA_5555555555','trina') then
    3 dbms_output.put_line('True');
    4 else
    5 dbms_output.put_line('False');
    6 end if;
    7 end;
    8 /
    False

    PL/SQL procedure successfully completed.

  10. #20
    Join Date
    Oct 2000
    Posts
    144
    Hi Hrishy,
    I modified the code to revmove the quote out and tested it again. It worked. However, my question is how do you test when I change the password. It does not error out the second one.

    SQL> alter user trina identified by trinA_5555555555;

    User altered.

    SQL> alter user trina identified by trina_7777788888;

    User altered.





    ====
    23 if regexp_instr(password,regular_expr) > 0 THEN


    ======

    SQL> Rem utlpwdmg.sql
    SQL>
    SQL> CREATE OR REPLACE FUNCTION trina_verify_function
    2 (username varchar2,
    3 password varchar2,
    4 old_password varchar2)
    5 RETURN boolean IS
    6 n boolean;
    7 m integer;
    8 differ integer;
    9 isdigit boolean;
    10 ischar boolean;
    11 ispunct boolean;
    12 isupper boolean;
    13 digitarray varchar2(20);
    14 punctarray varchar2(25);
    15 chararray varchar2(52);
    16 regular_expr varchar2(50);
    17
    18 BEGIN
    19 digitarray:= '0123456789';
    20 chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    21 punctarray:='!"#$%&()``*+,-/:;<=>?_';
    22 regular_expr:= '[A-Z]';
    23 if regexp_instr(password,regular_expr) > 0 THEN
    24 -- raise_application_error(-20006, 'Password must contain at least one upper case letter.');
    25 return true;
    26 end if;
    27 -- if everything is fine return false.
    28 RETURN (false);
    29 END;
    30 /

    Function created.


    SQL> l
    1 begin
    2 if trina_verify_function('TRINA','trina_5555555555','trina') then
    3 dbms_output.put_line('True');
    4 else
    5 dbms_output.put_line('False');
    6 end if;
    7* end;
    SQL> /
    False

    PL/SQL procedure successfully completed.

    SQL> begin
    2 if trina_verify_function('TRINA','trinA_5555555555','trina') then
    3 dbms_output.put_line('True');
    4 else
    5 dbms_output.put_line('False');
    6 end if;
    7 end;
    8 /
    True

    PL/SQL procedure successfully completed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width