Procedure of updating_user throws error, when the parameter is null

Question

I have next function, that hashes user password before inserting data in table:

--/
CREATE OR REPLACE FUNCTION hash_password
(
    f_password IN CLOB
)
RETURN RAW
IS
    hash RAW(32);
BEGIN
    hash := dbms_crypto.hash(f_password, dbms_crypto.hash_sh256);
    RETURN hash;
END;
--/

And I have this procedure for updating user data:

--/
CREATE OR REPLACE PROCEDURE update_user
(
    p_id in USERS.id%TYPE,
    p_user_role in USERS.user_role%TYPE,
    p_email in USERS.email%TYPE,
    p_password in CLOB
)
IS
BEGIN
    UPDATE USERS
    SET user_role = (select nvl2(p_user_role, p_user_role, (select user_role from users where id = p_id)) from dual),
        email = (select nvl2(p_email, p_email, (select email from users where id = p_id)) from dual),
        password = (select nvl2(p_password, (select hash_password(p_password) from dual), (select password from users where id = p_id)) from dual)
    WHERE id = p_id;
    COMMIT;
END;
--/

Oracle throws me these kind of errors:

[Code: 1405, SQL State: 22002]  ORA-01405: fetched column value is NULL
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 159
ORA-06512: at "SYS.DBMS_CRYPTO", line 86
ORA-06512: at "SYSTEM.HASH_PASSWORD", line 9
ORA-06512: at "SYSTEM.UPDATE_USER", line 10
ORA-06512: at line 2
  [Script position: 17878 - 17883]

But WHY? I use nvl2 function, I thought that I predicted the situation when the parameter is null.

I have tried to create additional variable, that will contain old password. And if parameter is not null, then I change the value of this variable. But it did not help me :(

P.S. If you need the sctructure of my table, then here:

CREATE TABLE USERS
(
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_role NVARCHAR2(64) NOT NULL,
    email NVARCHAR2(256) NOT NULL UNIQUE,
    password RAW(32) NOT NULL,
    CONSTRAINT FK_USER_ROLE
        FOREIGN KEY (user_role)
        REFERENCES ROLES (role_name)
);

The error comes from within DBMS_CRYPTO, so it probably means that the f_password parameter being passed into dbms_crypto.hash is null, which could happen in your code if the update_user proc is called with a NULL p_password value. The NVL2 doesn't prevent the evaluation of the `hash_password(p_password) from dual` piece, and that may be where it's passing a NULL to the hash function. Oracle evaluates all the parameters to your NVL2 first, then passes their results to the NVL2 program. You can't short-circuit it (easily) in SQL to not call one of them. You should address NULL in hash_password. — Paul W, May 12 '23 at 01:01
And by the way, why use CLOB for password? That seams excessive... do you expect a password > 32767 bytes long? Because if not, I suggest you use varchar2 instead. — Paul W, May 12 '23 at 01:05
@PaulW, thx for explaining, I will try anything else. About clob password: dbms_crypto.hash need parameter as **clob or blob**. If you know any other method of hashing password, than it would be ok if you tell me about it. And by the way, is it good to hash password inside database? Maybe it will be better to hash on the backend side of application, and then store hash as varchar2? Am I wrong? — Дмитрий Воликов, May 12 '23 at 10:37
I posted my response as an answer below. Yes, hashing in the database is fine. — Paul W, May 12 '23 at 11:35

Paul W · Accepted Answer · 2023-05-12T11:39:26.270

NVL, NVL2, DECODE, etc... these functions do not short-circuit evaluate (in fact, almost nothing in SQL does... if you want to avoid making a call using branching logic it typically requires PL/SQL). Oracle will resolve all their parameters first in any order it wants and then pass them into the function program. So your hash_password will execute every time, whether or not you have passed in a NULL for the password. That NULL being fed into the hash algorithm is what is raising your exception.

Address NULL values in your hash function. Also, don't use CLOB for passwords... dbms_crypto.hash supports RAW as well, which you can convert to from a much more reasonable varchar2 datatype. I also suggest simplifying your update statement to avoid those unnecessary subqueries. You can refer to the existing values in the same query block and use them with NVL:

CREATE OR REPLACE FUNCTION hash_password(f_password IN varchar2)
  RETURN raw
AS
BEGIN
  IF f_password IS NULL
  THEN
    RETURN NULL;
  ELSE
    RETURN sys.dbms_crypto.hash(utl_raw.cast_to_raw(f_password), sys.dbms_crypto.hash_sh256);
  END IF;
END;
/

CREATE OR REPLACE PROCEDURE update_user
(
    p_id in USERS.id%TYPE,
    p_user_role in USERS.user_role%TYPE,
    p_email in USERS.email%TYPE,
    p_password in varchar2
)
IS
BEGIN
  UPDATE USERS
     SET user_role = NVL(p_user_role, user_role),
         email = NVL(p_email, email),
         password = NVL(hash_password(p_password),password)
   WHERE id = p_id;
  
  COMMIT;
END;

Thanks, buddy Your help is simply invaluable – Дмитрий Воликов May 12 '23 at 12:36 — Дмитрий Воликов, May 12 '23 at 12:36

Procedure of updating_user throws error, when the parameter is null

1 Answers1