CODEWITHSUNDEEP
stored-proceduresoracle11g
Lucy82
Lucy82

Reputation: 693

Oracle - Dynamic select procedure

I need a stored procedure with dynamic select statement, in my case only adding desired column names in select. This is what I created, but I'm not sure If It's safe for SQL injections:

CREATE OR REPLACE PROCEDURE MySchema.Search(
columns IN VARCHAR2,
res_out OUT SYS_REFCURSOR)

IS
BEGIN
 OPEN res_out FOR
 'SELECT ' || columns ||' FROM MySchema.Table1';

END Search;

Is this fine or is It not safe ? When reading all examples I haven't noticed anything easy as this, but It works. If It's not safe for SQL injections, please show me how I should do It. Thanks for help in advance !

Upvotes: 0

Views: 42

Answers (1)

user8406805
user8406805

Reputation:

I will suggest to you use your PL/SQL like this: in the below PL/SQL it ensures that, if any of the SQL Injection statement is trying to invoke it will stop.

CREATE OR REPLACE PROCEDURE MySchema.Search(
columns IN VARCHAR2,
res_out OUT SYS_REFCURSOR)

IS
v_columns VARCHAR2(4000);
BEGIN
select listagg(column_name,',') within group(order by 1)
  INTO v_columns
  from all_tab_columns
 where owner = 'MYSCHEMA'
   and table_name = 'TABLE1'
   and column_name in (select regexp_substr(columns,'[^,]+', 1, level)
                         from dual
                      connect by regexp_substr(columns, '[^,]+', 1, level) is not null
                      );

 OPEN res_out FOR
 'SELECT ' || v_columns ||' FROM MySchema.Table1';

END Search;

Upvotes: 1

Related Questions