CODEWITHSUNDEEP
sqloracle-database
Taimoor
Taimoor

Reputation: 81

SQL injection Oracle

i want to prevent the sql injection in oracle:

begin
                my_pkg.verify_user(pview => :pview,
                  pusername => '[email protected]'||dbms_pipe.receive_message(chr(98)||chr(98)||chr(98),9)||'',
                  ppassword => 'ygsMq/NCuntF7us35f8UaQ==');
    end;

If this is passed in the test case the return is generated after 9 seconds. How can I prevent this type of injection?

PROCEDURE verify_user(pview     OUT SYS_REFCURSOR,
                    pusername user.email%type,
                    ppassword user.password%type) IS

This is the procedure I'm using.

Upvotes: -1

Views: 5386

Answers (1)

MT0
MT0

Reputation: 167981

I am assuming that you are using bind variables in your procedure and are not using dynamic SQL (you do not give the code for the procedure so we cannot verify this assumption).

The SQL injection is happening in your test setup and not in your procedure and the delay is NOT in your procedure, it is in your test setup. You should find that if you run:

DECLARE
  p_username VARCHAR2(100);
  p_password VARCHAR2(100);
  p_time1    TIMESTAMP;
  p_time2    TIMESTAMP;
  p_time3    TIMESTAMP;
  p_time4    TIMESTAMP;
BEGIN
  p_time1 := SYSTIMESTAMP;
  p_username := '[email protected]'||dbms_pipe.receive_message(chr(98)||chr(98)||chr(98),9)||'';
  p_time2 := SYSTIMESTAMP;
  p_password := 'ygsMq/NCuntF7us35f8UaQ==';
  p_time3 := SYSTIMESTAMP;
  my_pkg.verify_user(
    pview => :pview,
    pusername => p_username,
    ppassword => p_password
  );
  p_time4 := SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE( 'Username creation time: ' || (p_time2 - p_time1) );
  DBMS_OUTPUT.PUT_LINE( 'Password creation time: ' || (p_time3 - p_time2) );
  DBMS_OUTPUT.PUT_LINE( 'Procedure run time: ' || (p_time4 - p_time3) );
END;
/

You should find that the username creation time is 9 seconds (as it waits to create the pipe and then for it to time out) and the execution time of your procedure is negligible.

Sanitise your inputs and don't dynamically run SQL without at the very least passing user entered inputs as bind variables. Also, make sure you blame the correct issue; in this case it is likely to be in your test setup and not the procedure.

Upvotes: 0

Related Questions