Vulnerabilities of Oracle NVL and decode functions

In Oracle, even if the condition is not met, the NVL function also performs a conditional non-conforming option, and for the Decode function, if there is a custom function in it, decode will not execute the non-conforming function, but if Decode has sequence.nextval, Regardless of whether the conditions are met, sequence will increase.

1 --Create a function to print "The FUNCTIONSS is executed" when it is invoked2 CREATE OR REPLACE FUNCTIONF_print_str3 RETURN  Number4   as5 BEGIN6 7Dbms_output. Put_Line ('The funcionts is executed');8   RETURN 1;9 Ten ENDF_PRINT_STR;

NVL:

For example, even if the NVL first parameter is not empty, the value of the first parameter-11 is returned, but the function f_print_str is still executed, printing "The function is eceucted"

1Sql> SetServeroutput on2Sql> SelectNvl' One', F_PRINT_STR) fromdual;3  4NVL (' One', F_print_str)5 ----------------------------------------6  One7  8The funcionts isexecuted9  TenSql> 

DECODE:

The Decode function does not perform an option that does not meet the criteria, the following example does not print "The functions is exeucted"

1Sql> SetServeroutput on2Sql> SelectDecode'Ten','Ten','is same', F_PRINT_STR) fromdual;3  4DECODE ('Ten','Ten','Issame', F_PR5 ----------------------------------------6  issame7  8Sql> 

If the Decode function contains Sequence.nextval, the sequence value will change regardless of whether the condition is met.

1Sql> CREATESEQUENCE seq_test MINVALUE1MAXVALUE $START with 1INCREMENT by 1nocycle NOCACHE;2  3 Sequence created4  5Sql> SelectSeq_test.nextval fromdual;6  7 Nextval8 ----------9          1Ten   OneSql> SelectDecode'Ten','Ten','is same', Seq_test.nextval) fromdual;--sequence conditions are not established here, but are also executed A   -DECODE ('Ten','Ten','Issame', Seq_ - ---------------------------------------- the  issame -   -Sql> SelectSeq_test.currval fromdual; -   + Currval - ---------- +          2 A   atSql> 

To solve the above problem, you can create a function to return the value of Sequence.nextval, and if the condition is not true, the value of sequence will not change.

1 --creates a function that returns the value of the Sequence.nextval2 CREATE OR REPLACE FUNCTIONF_get_seqRETURN  Number  as3V_seq_num Number;4 BEGIN5   SELECTSeq_test. Nextval intoV_seq_num fromDUAL;6Dbms_output. Put_Line ('The funcionts is executed');7   RETURNV_seq_num;8 9 ENDF_get_seq;

For example, the F_GET_SEQ condition is not valid, the function F_get_seq will not execute, the corresponding Sequence:seq_test value will not change

1Sql> SetServeroutput on2Sql> SelectSeq_test.currval fromdual;3  4 Currval5 ----------6          37  8Sql> SelectDecode'Ten','Ten','is same', F_GET_SEQ) fromdual;9  TenDECODE ('Ten','Ten','Issame', F_ge One ---------------------------------------- A  issame -   -Sql> SelectSeq_test.currval fromdual; the   - Currval - ---------- -          3 +   -Sql> 

Vulnerabilities of Oracle NVL and decode functions