Oracle SELECT … WHERE IN (:bind_variable_array)

As we all know, one of the best ways to prevent SQL injection attacks in PHP (or any server side language for that matter) is to use parameterized queries. But how do you parameterize a WHERE ... IN clause?

One way is to use a user-defined function. The below example first defines a user type called INLISTTYPE, which is based on the TABLE data type. INLISTTYPE will be used by the in_list function to output a table that will be subsequently consumed by the SQL IN clause with the help of Oracle’s TABLE() function. The in_list function takes in a single parameter and the value for the parameter (e.g. val1,val2,val3) is parsed, which is then then outputted as an INLISTTYPE data type. As the final step, in order to output this data as rows, the TABLE() function is used and thus will be able to be used by the IN clause.

/*

CREATE OR REPLACE TYPE INLISTTYPE as table
   of varchar2 (255);

CREATE OR REPLACE function in_list(p_string in varchar2) return INLISTTYPE
as
  l_string        long default p_string || ',';
  l_data          INLISTTYPE := INLISTTYPE();
  n               number;
begin
  loop
    exit when l_string is null;
    n := instr(l_string, ',');
    l_data.extend;
    l_data(l_data.count) := ltrim(rtrim(substr(l_string, 1, n - 1)));
    l_string := substr(l_string, n + 1);
  end loop;

  return l_data;
end;

*/

SELECT * FROM TABLE(in_list(:IDs)); // Test out the function.

SELECT
  TO_CHAR(E.CREATE_DATE, 'DD-MON-YY HH24:MI') CREATE_DATE
  , E.STATUS
  , E.EMAIL_ID
  , E.EMAIL_FROM
  , E.EMAIL_TO
  , E.EMAIL_DATE_RECEIVED
  , E.EMAIL_SUBJECT
FROM EMAILS E
  LEFT JOIN EMAIL_ATTACHMENTS EA ON E.EMAIL_ID = EA.EMAIL_ID
WHERE 1 = 1
  AND E.EMAIL_ID IN (SELECT * FROM TABLE(in_list(:IDs))); // Use it in your query, e.g. 10,20,30[,...]

See oracle.com