Sanitizing user inputs that are part of dynamic PL/SQL command

Question

I have a table filled by users and its VARCHAR2 records contain a part of executable PL/SQL code "IN(user_input)". I wonder how I can sanitize these user inputs or maybe revrite it to be more efficient. All my ideas failed so far. For example:

• A Bind variable is not accepted in this case
• DBMS_ASSERT.enquote_literal always raise an exception etc.

Thank you very much for any help.

/* A "MY_PARAMETER" column is part of SQL: ...WHERE MY_DATA IN(MY_PARAMETER)... */
CREATE TABLE my_parameter_table (
       "ID" INTEGER NOT NULL ENABLE,
       "MY_PARAMETER" VARCHAR(255) NOT NULL ENABLE   
)

INSERT INTO my_parameter_table ("ID","MY_PARAMETER") VALUES (1,'6,7,8');
INSERT INTO my_parameter_table ("ID","MY_PARAMETER") VALUES (2,'''b'',''g'',''k''');
INSERT INTO my_parameter_table ("ID","MY_PARAMETER") VALUES (3,'SELECT dummy FROM dual'); -- return "X"

/* Tested table with data */
CREATE TABLE my_data_table (
       "ID" INTEGER NOT NULL ENABLE,
       "MY_DATA" VARCHAR(255) NOT NULL ENABLE,
       "MY_RESULT" VARCHAR(255) NOT NULL ENABLE   
);

INSERT INTO my_data_table ("ID","MY_DATA","MY_RESULT") VALUES (1,'a','NOT');
INSERT INTO my_data_table ("ID","MY_DATA","MY_RESULT") VALUES (2,'b','THIS'); --WILL PASS
INSERT INTO my_data_table ("ID","MY_DATA","MY_RESULT") VALUES (3,'c','NOT');
INSERT INTO my_data_table ("ID","MY_DATA","MY_RESULT") VALUES (4,'X','IS'); --WILL PASS
INSERT INTO my_data_table ("ID","MY_DATA","MY_RESULT") VALUES (5,'Y','NOT');
INSERT INTO my_data_table ("ID","MY_DATA","MY_RESULT") VALUES (6,'Z','CORRECT'); --WILL PASS

/* Result table where results are inserted */
CREATE TABLE my_result_table (
       "MESSAGE" VARCHAR(255) NOT NULL ENABLE   
);

/* ------------------------------------------- */

DECLARE
    where_condition VARCHAR2(1000) := '';
    v_query VARCHAR2(1000) := '';
    insert_or VARCHAR2(5) := '';

    CURSOR test_parameter_cur IS
        (
          SELECT * FROM my_parameter_table            
        );
    test_parameter_rec test_parameter_cur%ROWTYPE;

BEGIN

    /* Read all parameters and build an WHERE condition */
    OPEN test_parameter_cur;
    LOOP 
        FETCH test_parameter_cur INTO test_parameter_rec; 
        EXIT WHEN test_parameter_cur%NOTFOUND;

        /* Condition check can be any type. Varchar, number, date or some subselect */
        IF test_parameter_rec.ID = 1 THEN where_condition := where_condition || insert_or || 'd.ID IN('|| test_parameter_rec.MY_PARAMETER ||')';
        ELSE where_condition := where_condition || insert_or || 'd.MY_DATA IN('|| test_parameter_rec.MY_PARAMETER ||')';
        END IF;
        insert_or := ' OR '; -- after first run the "OR" operator is inserted in front of each where condition 

    END LOOP; 
    CLOSE test_parameter_cur; 

v_query := 'INSERT INTO my_result_table(MESSAGE) 
           (SELECT d.MY_RESULT FROM my_data_table d
           WHERE '|| where_condition ||')';

EXECUTE IMMEDIATE v_query;
COMMIT; 
END;

/* Now the my_result_table contains 3 records: THIS, IS, CORRECT */
SELECT * FROM my_result_table;

DROP TABLE my_parameter_table; 
DROP TABLE my_data_table;

Answer 1

Have a look here : https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

Works like a charme for ',' - separated list. I recommend to TRIM() the result to remove trailing and leading blanks from the results.

You can use the select-statement as a subselect in an in-clause.