Как передать имя таблицы в динамический SQL запрос?

Question

Пытаюсь передать имя таблицы как параметр связывания в в динамический SQL запрос.

Такой запрос работает:

DECLARE 
    FUNCTION foo (pat VARCHAR2) RETURN NUMBER IS
        tabname VARCHAR2 (100) := 'mytab1';
        n NUMBER := -1;
        sqlcmd VARCHAR2 (100) := 'SELECT COUNT(*) FROM '||tabname||' WHERE name LIKE :1';
    BEGIN
        EXECUTE IMMEDIATE sqlcmd INTO n USING pat;
        RETURN n;
    END foo;
BEGIN
    dbms_output.put_line (foo ('bla%'));
END;
/

Но если попробовать задать tabname параметром связывания, также как с pat:

DECLARE 
    FUNCTION foo (pat VARCHAR2) RETURN NUMBER IS
        tabname VARCHAR2 (100) := 'mytab1';
        n NUMBER := -1;
        sqlcmd VARCHAR2 (100) := 'SELECT COUNT(*) FROM :1 WHERE name LIKE :2';
    BEGIN
        EXECUTE IMMEDIATE sqlcmd INTO n USING tabname, pat;
        RETURN n;
    END foo;
BEGIN
    dbms_output.put_line (foo ('bla%'));
END;
/

, то получим ошибку:

ORA-00903: invalid table name

Как можно задать имя таблицы параметром связывания?

_{Свободный перевод вопроса PL/SQL: How to pass a tablename to a dynamic SQL? от участника @chris01}

Answer 1

TL;DR Переменные (или параметры) связывания нельзя применять для идентефикаторов БД: таблиц, колонок, функций и др. обьектов БД. Или другими словами, для всего того, что не имеет конкретный тип данных.

То есть, первый запрос в вопросе один из путей задать имя идентефикатора, таблицы в данном случае, в динамическом запросе.

Если с переменной связывания во втором запросе была попытка предотвратить SQL инъекцию, то можно воспользоваться DBMS_ASSERT.SQL_OBJECT_NAME. Например, чтобы избежать такой простой инъекции:

create table mytab1 as 
    select 1 id, 'blabla' name from dual
/
create table notmetab as 
    select 1 rid, 'xxxxxxxx' from dual;
/
create or replace function foo (tabname varchar2, pat varchar2) return sys_refcursor is
    ret sys_refcursor;
    sqlcmd varchar2 (100) := 'select * from '||tabname||' where name like :1';
begin
    open ret for sqlcmd using pat;
    return ret;
end foo;
/

var rc refcursor
exec :rc := foo ('mytab1, notmetab', 'bla%');

        ID NAME          RID   MEMO            
---------- -------- ---------- ----------------
         1 blabla            1 xxxxxxxx

Надо запрос в функции заменить на такой:

sqlcmd varchar2 (100) := 
    'select * from '||sys.dbms_assert.sql_object_name (tabname)||' where name like :1';

Теперь инъекция завершится ошибкой, или запрос выполнится, если таблица задана верно:

exec :rc := foo ('mytab1, notmetab', 'bla%');
Error report -
ORA-44002: invalid object name

exec :rc := foo ('mytab1', 'bla%');

        ID NAME    
---------- --------
         1 blabla

БЛОГ НА HUSL

Как передать имя таблицы в динамический SQL запрос?

Ответы (1 шт):