oracle apex query sql injection

Question

i have check my sql query which is vulnerable to sql injection. V_NAME is detected sql injection. how can I securing my query ?

this is my query :

FUNCTION  "GET_SEQUENCE" (P_BID VARCHAR2, P_PSC VARCHAR2) RETURN NUMBER AS
TYPE T_HASIL IS TABLE OF NUMBER;
V_HASIL T_HASIL;
V_NAME VARCHAR2(30);
V_SQL LONG;
BEGIN
SELECT KEYSEQ INTO V_NAME
FROM MST_SEQUENCE_DETAIL Tbl
WHERE BRANCHCODE=P_BID AND KEYCODE=P_PSC
AND YEAR = TO_CHAR(SYSDATE,'RRRR');

V_SQL := 'SELECT ' || V_NAME || '.NEXTVAL FROM DUAL';
EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO V_HASIL;

RETURN V_HASIL(1);
END;

thank u.

Just wondering, why are you using a PL/SQL array, doing a bulk collect, to get only 1 record? You could use a simple numeric value to get the sequence value, you don't need an array. — Jeffrey Kemp
– Jeffrey Kemp, Commented Apr 26, 2018 at 5:29
By the way, this is not a great way of getting sequence values - part of the point of using Oracle sequences is their excellent performance; using a function like this, presumably being called for each record before it is inserted, would perform very poorly. — Jeffrey Kemp
– Jeffrey Kemp, Commented Apr 26, 2018 at 5:31
You have marked MT0's answer as "correct", and while their answer does contain accurate and useful information, it does not fully answer your question as stated, which is "how to secure my query". I don't care about the points but I do care that this Q&A may help future developers. — Jeffrey Kemp
– Jeffrey Kemp, Commented May 3, 2018 at 1:44

MT0 · Accepted Answer · 2018-04-26 08:27:39Z

Your function is vulnerable to SQL injection.

Consider if someone performs this insert:

INSERT INTO MST_SEQUENCE_DETAIL (
  BRANCHCODE,
  KEYCODE,
  YEAR,
  KEYSEQ
) VALUES (
  1,
  1,
  TO_CHAR( SYSDATE, 'RRRR' ),
  '(SELECT psswd FROM usr),keyseq'
);

Then calling your function:

GET_SEQUENCE( 1, 1 );

Will set the query to:

V_SQL := 'SELECT (SELECT psswd FROM usr),keyseq.NEXTVAL FROM DUAL';

The next statement:

EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO V_HASIL;

Will fail but how it fails can tell you whether:

There is a usr table; if is there is not then you will get the exception (SQLFiddle):

ORA-00942: table or view does not exist ORA-06512: at "USER_4_C4D95A.GET_SEQUENCE", line 18

It has a column called psswd; if there is not then you will get the exception (SQLFiddle)

ORA-00904: "PSSWD": invalid identifier ORA-06512: at "USER_4_9B4C87.GET_SEQUENCE", line 18

Performing this repeatedly, you can start to map the structure of the database and look for other vulnerabilities that may allow greater exploits.

Jeffrey Kemp · Accepted Answer · 2018-05-03 01:45:52Z

1

V_SQL := 'SELECT ' || V_NAME || '.NEXTVAL FROM DUAL';
EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO V_HASIL;

The issue here is that V_NAME could in theory be any SQL code and thus an injection vulnerability. The way you protect this is to use dbms_assert.simple_sql_name, since you expect this variable to be a simple identifier:

V_SQL := 'SELECT ' || sys.dbms_assert.simple_sql_name(V_NAME) || '.NEXTVAL FROM DUAL';
EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO V_HASIL;

edited May 3, 2018 at 1:45

answered Apr 26, 2018 at 5:26

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

1 Comment

MT0 Over a year ago

The function is vulnerable to SQL Injection.

Collectives™ on Stack Overflow

oracle apex query sql injection

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related