How to build dynamic SQL with using

Question

I am trying to make dynamic sql like this 'select col1,col2 from '|| my_table ||' it works fine but I want to write like this sql_stmt:='select col1,col2 from :myTable'; execute immediate sql_stmt using my_table; but I have error I have same error when I want to do something like this tooo v_filter := my_proc(); sql_stmt:='select col1,col2 from my_table where :filter' execute immediate sql_stmt using v_filter; is it impossible to build dynamic sql like this with using ? If it is impossible what is another way to avoid sql injections ?

Answer 1

When you want to use table names in dynamic SQL, yes - you'll have to concatenate them. In order to avoid SQL injection, use DBMS_ASSERT.SQL_OBJECT_NAME.

Here's an example:

SQL> create or replace procedure p_test (par_table in varchar2) is
  2    l_table varchar2(30);
  3    l_str   varchar2(200);
  4    l_cnt   number;
  5  begin
  6    l_table := dbms_assert.sql_object_name(par_table);
  7
  8    l_str := 'select count(*) from ' || par_table;
  9    execute immediate (l_str) into l_cnt;
 10    dbms_output.put_line('Table contains ' || l_cnt || ' rows');
 11  end;
 12  /

Procedure created.

SQL>
SQL> exec p_test('dept');
Table contains 4 rows

PL/SQL procedure successfully completed.

SQL> exec p_test('delete from emp');
BEGIN p_test('delete from emp'); END;

*
ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316
ORA-06512: at "SCOTT.P_TEST", line 6
ORA-06512: at line 1


SQL>

[EDIT: WHERE clause]

This works:

SQL> create or replace procedure p_test (par_table in varchar2,
  2                                      par_filter in varchar2) is
  3    l_table varchar2(30);
  4    l_str   varchar2(200);
  5    l_cnt   number;
  6  begin
  7    l_table := dbms_assert.sql_object_name(par_table);
  8
  9    l_str := 'select count(*) from ' || par_table ||
 10             ' where deptno = :filter';
 11    execute immediate (l_str) into l_cnt using par_filter;
 12    dbms_output.put_line('Table contains ' || l_cnt || ' rows');
 13  end;
 14  /

Procedure created.

SQL> exec p_test('emp', '10');
Table contains 3 rows

PL/SQL procedure successfully completed.

SQL>

WHERE clause, modified so that it contains only the WHERE keyword, while the rest is to be used as a parameter:

SQL> create or replace procedure p_test (par_table in varchar2,
  2                                      par_filter in varchar2) is
  3    l_table varchar2(30);
  4    l_str   varchar2(200);
  5    l_cnt   number;
  6  begin
  7    l_table := dbms_assert.sql_object_name(par_table);
  8
  9    l_str := 'select count(*) from ' || par_table ||
 10             ' where :filter';
 11    execute immediate (l_str) into l_cnt using par_filter;
 12    dbms_output.put_line('Table contains ' || l_cnt || ' rows');
 13  end;
 14  /

Procedure created.

SQL> exec p_test('emp', 'deptno = 10');
BEGIN p_test('emp', 'deptno = 10'); END;

*
ERROR at line 1:
ORA-00920: invalid relational operator
ORA-06512: at "SCOTT.P_TEST", line 11
ORA-06512: at line 1


SQL>

It won't work; is that what you're asking?

Some more reading about dynamic SQL on Oracle, as well as here, on Stack overflow (How can I create a dynamic WHERE clause.