code to check the password at least one upper case letter

**PAVB** · 10-15-2009 02:27 PM

Trina --

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

**Trina** · 10-15-2009 03:38 PM

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.

**PAVB** · 10-15-2009 04:48 PM

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?

**hrishy** · 10-16-2009 03:22 AM

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

**Trina** · 10-19-2009 02:12 PM

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

**hrishy** · 10-20-2009 03:25 AM

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 ;

/

**Trina** · 10-20-2009 10:29 AM

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

**hrishy** · 10-20-2009 10:36 AM

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

**Trina** · 10-20-2009 10:53 AM

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.

**Trina** · 10-20-2009 02:22 PM

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.

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

Thread Tools

Display

Bookmarks

Bookmarks

Posting Permissions