CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
potitit
potitit

Reputation: 63

oracle apex query sql injection

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.

Upvotes: 1

Views: 1467

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

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;

Upvotes: 1

MT0
MT0

Reputation: 168740

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.

Upvotes: 1

Related Questions