CODEWITHSUNDEEP
oracle-databaseplsql
DKCroat
DKCroat

Reputation: 355

What I am doing wrong in this procedure

I have created procedure to check and validate username and password, even when I provide correct password I will receive always exception error. I tried different thing inside the procedure but results would be the same. 

create or replace 
PROCEDURE member_ck_sp
  (p_uname IN VARCHAR2,
   p_pass IN VARCHAR2,
   p_name OUT VARCHAR2,
   p_cookie OUT VARCHAR2)
IS
   CURSOR CUR_CHECK IS
   SELECT USERNAME, PASSWORD,FIRSTNAME||''||LASTNAME, COOKIE
   FROM bb_shopper;
   lv_check_txt VARCHAR2(100);
BEGIN
   FOR rec_check IN cur_check LOOP
   IF p_uname = rec_check.username 
      AND p_pass = rec_check.PASSWORD THEN
   lv_check_txt := 'Pass';
   ELSE lv_check_txt := 'Fail';
   END IF;
   END LOOP;

 IF lv_check_txt = 'Pass' THEN
    SELECT FIRSTNAME||''||LASTNAME, COOKIE
    INTO   p_name, p_cookie
    FROM   bb_shopper
    WHERE USERNAME = P_UNAME
    AND   password = p_pass;
    dbms_output.put_line(p_name||' '|| p_cookie);
 ELSE raise no_data_found;
 END IF;
--dbms_output.put_line(p_name||' '|| p_cookie);
EXCEPTION
  WHEN no_data_found THEN
  dbms_output.put_line('Please reneter credentials');
END;

And block to check code:

DECLARE
    lv_username_txt bb_shopper.username%TYPE := 'rat55';
    lv_password_txt bb_shopper.PASSWORD%TYPE := 'kile';
    lv_name_txt VARCHAR2(200);
    lv_cookie_txt bb_shopper.cookie%TYPE;
BEGIN
    member_ck_sp(lv_username_txt,lv_password_txt,lv_name_txt,lv_cookie_txt);
    --DBMS_OUTPUT.PUT_LINE('User name is '||lv_name_txt||' and      
    cookie '||lv_cookie_txt);
END;

Upvotes: 1

Views: 102

Answers (3)

Connor McDonald
Connor McDonald

Reputation: 11581

I'll take a different tack on this one. I see one potential error that overrides anything regarding the syntax and functionality, and that is:

I really really REALLY hope you are not planning on storing cleartext passwords in a database table.

Do not ever do this....ever. Please tell us that this routine already has the password salted/hashed before making it to this routine and table. Otherwise, this is the first thing you should looking at fixing before anything else.

Upvotes: 1

APC
APC

Reputation: 146219

Your problem is the opening LOOP reads all the records in bb_shopper. One of those records presumably matches the entered credentials. However, unless the last record read is the matching one, you will exit the loop with lv_check_txt = 'Fail'. And that's why you always fail the test in the subsequent IF and get no_data_found.

The solution seems quite simple: ditch the loop and just validate the passed parameters.

create or replace 
PROCEDURE member_ck_sp
  (p_uname IN VARCHAR2,
   p_pass IN VARCHAR2,
   p_name OUT VARCHAR2,
   p_cookie OUT VARCHAR2)
IS
BEGIN
    SELECT FIRSTNAME||''||LASTNAME, COOKIE
    INTO   p_name, p_cookie
    FROM   bb_shopper
    WHERE USERNAME = P_UNAME
    AND   password = p_pass;

    --dbms_output.put_line(p_name||' '|| p_cookie);
EXCEPTION
  WHEN no_data_found THEN
     raise_application_error(-20000, 'Please re-enter credentials');
END;

Upvotes: 2

chabzjo
chabzjo

Reputation: 626

I haven't looked at PL\SQL in a long time. However, my first suggestion would be to look at your test data:

SELECT * FROM bb_shopper where username = 'rat55';

A few things to keep in mind:

  • The last line in the block to check code was probably meant to be commented out. It contains a quotation mark left open and a close bracket without an opening bracket. That can't help.

Upvotes: 1

Related Questions