Oracle SP Checkmarx SAST scan showing Parameter Tampering

Question

SP accepts key and returns flag status. In Checkmarx SAST scan key, It is saying parameter not validated before being used in query. Please help to understand what validation can be added so that it passes the vulnerability test.

Create procedure sp_name (key varchar2, v_flag out number) as
v_key varchar2(50);
v_sql clob;
Begin
If key is null then
v_flag:=-1;
Else
v_key:=sys.dbms_assert.enquote_literal(upper(trim(key)));
v_key:=replace(v_key,'''');
v_sql:=q'[select flag from tbl where key=:1]';
Execute immediate v_sql into v_flag using v_key;
End if;
Exception when no_data_found then
v_flag:=-1;
End;

I tried bind variable and dbms_assert but still it shows code is vulnerable

Answer 1

Stop using EXECUTE IMMEDIATE when you do not need to (and trying to make it overly complicated) and use a simple SELECT ... INTO ... statement with the PL/SQL variable as the bind variable (and give the PL/SQL variable a different name to the table's column).

The procedure can be simplified to:

CREATE PROCEDURE sp_name (
  v_key  IN  TBL.KEY%TYPE,
  v_flag OUT TBL.FLAG%TYPE
)
AS
BEGIN
  SELECT flag
  INTO   v_flag
  FROM   tbl
  WHERE  key=v_key;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_flag := -1;
END;
/

Then, if you have the sample data:

CREATE TABLE tbl (key, flag) AS
  SELECT 'A',  1 FROM DUAL UNION ALL
  SELECT 'B',  2 FROM DUAL UNION ALL
  SELECT 'C',  3 FROM DUAL UNION ALL
  SELECT NULL, 4 FROM DUAL;

Then:

DECLARE
  v_flag TBL.FLAG%TYPE;
BEGIN
  sp_name('A', v_flag);
  DBMS_OUTPUT.PUT_LINE('A: ' || v_flag);
  sp_name('B', v_flag);
  DBMS_OUTPUT.PUT_LINE('A: ' || v_flag);
  sp_name('C', v_flag);
  DBMS_OUTPUT.PUT_LINE('A: ' || v_flag);
  sp_name('D', v_flag);
  DBMS_OUTPUT.PUT_LINE('D: ' || v_flag);
  sp_name(NULL, v_flag);
  DBMS_OUTPUT.PUT_LINE('NULL: ' || v_flag);
END;
/

Outputs:

A: 1
B: 2
C: 3
D: -1
NULL: -1

Or, if you want it slightly more complicated to avoid the SELECT if the key is NULL then:

CREATE OR REPLACE PROCEDURE sp_name (
  v_key  IN  TBL.KEY%TYPE,
  v_flag OUT TBL.FLAG%TYPE
)
AS
BEGIN
  IF v_key IS NULL THEN
    v_flag := -1;
  ELSE
    SELECT flag
    INTO   v_flag
    FROM   tbl
    WHERE  key=v_key;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_flag := -1;
END;
/

fiddle