-- Applications are sometimes built with string SQL, that connect to a database as 
-- shown in this simple java "Hello World" example. If a query as shown below is used, 
-- a malicious user could easily enter "1 or 1=1" as input for emp_id to get all data.
-- This is a simple SQL injection to get all data. 

SELECT emp_id, yob, secure_id  --more details can be in a realtime table/query 
FROM
     (SELECT 100 emp_id, '111xx2222' secure_id, 2016 yob FROM DUAL   UNION
      SELECT 101 emp_id, '222xx3333' secure_id, 2015 yob FROM DUAL   UNION
      SELECT 102 emp_id, '333xx4444' secure_id, 2011 yob FROM DUAL   UNION
      SELECT 103 emp_id, '121xx4114' secure_id, 2014 yob FROM DUAL
     )
WHERE  emp_id = &i_usr_id;



-- With a simple query change, this can be prevented as shown below

SELECT emp_id, yob, secure_id  --more details can be in a realtime table/query 
FROM
     (SELECT 100 emp_id, '111xx2222' secure_id, 2016 yob FROM DUAL   UNION
      SELECT 101 emp_id, '222xx3333' secure_id, 2015 yob FROM DUAL   UNION
      SELECT 102 emp_id, '333xx4444' secure_id, 2011 yob FROM DUAL   UNION
      SELECT 103 emp_id, '121xx4114' secure_id, 2014 yob FROM DUAL
     )
WHERE  emp_id IN (&i_usr_id);





-- Here is an example of simple procedure to get data and populate a UI data-grid 
-- or table (typically in web applications).  Since the input is a number, the 
-- SQL injection attempt will fail.

CREATE OR REPLACE PROCEDURE get_user_info 
(i_emp_id    IN  NUMBER,
 o_sys_rc    OUT SYS_REFCURSOR
) AS
    
BEGIN
   o_sys_rc := NULL;
   OPEN o_sys_rc FOR
    SELECT *  FROM
     (SELECT 100 emp_id, '111xx2222' secure_id, 2016 yob FROM DUAL   UNION
      SELECT 101 emp_id, '222xx3333' secure_id, 2015 yob FROM DUAL   UNION
      SELECT 102 emp_id, '333xx4444' secure_id, 2011 yob FROM DUAL   UNION
      SELECT 103 emp_id, '121xx4114' secure_id, 2014 yob FROM DUAL
     )
    WHERE  emp_id = i_emp_id;
END get_user_info;
/

VARIABLE rc REFCURSOR
exec get_user_info('''1'' or 1=1', :rc);


-- The procedure call will fail as shown below.  Some error handling can keep the 
-- hacker obfuscated and prevent SQL injection.