Рекомендации по работе с динамическими командами (NDS) в PL/SQL

Рекомендации по работе с NDS командами в PL/SQLК настоящему моменту вы должны уже достаточно хорошо понимать, как «встроенный» динамический SQL работает в PL/SQL. В этом блоге я раскрою некоторые аспекты, которые следует учитывать при построении коммерческих приложений, использующих эту возможность PL/SQL.



 

Используйте права вызывающего для совместно используемых программ

Допустим, я создал несколько довольно полезных программ PL/SQL общего назначения, включая функции и процедуры для решения следующих задач:

  •  Выполнение произвольной команды DDL.
  •  Получение количества строк в произвольной таблице.
  •  Получение количества строк для каждой группировки по заданному столбцу.

Все эти средства весьма полезны, и я хочу, чтобы все участники моей группы разработки могли пользоваться ими. Итак, я компилирую их в схеме COMMON и предоставляю полно­мочия execute для этих программ категории public.

 Однако в этой политике кроется одна проблема. Сандра подключается к своей схеме SANDRA и выполняет следующую команду:

SQL> EXEC COMMON.exec_DDL ('create table temp (x date)');

При этом она непреднамеренно создает таблицу в схеме COMMON. Модель прав вызываю­щего означает, что вы определяете свои хранимые программы так, что они выполняются с привилегиями вызывающей, а не определяющей схемы (режим по умолчанию, начиная с Oracle8i, и единственно возможный вариант в предыдущих версиях).

К счастью, новая возможность достаточно проста в использовании. Ниже приведена версия моей процедуры exec_ddl, которая выполняет произвольную команду DDL, но всегда использует вызывающую схему:

PROCEDURE exec_DDL (ddl_string IN VARCHAR2)
   AUTHID CURRENT_USER IS
BEGIN
   EXECUTE IMMEDIATE ddl_string;
END;

Я рекомендую включать секцию AUTHID CURRENT_USER во все ваши динамические про­граммы SQL — и особенно в те, которые вы собираетесь распространять среди других разработчиков.

 

 

Прогнозирование и обработка динамических ошибок

Любое надежное приложение должно прогнозировать и обрабатывать ошибки. В ди­намическом SQL задачи выявления и исправления ошибок оказываются особенно непростыми.

Иногда самым сложным аспектом построения и выполнения динамических программ SQL оказывается построение правильной строки динамического SQL. Вероятно, вам придется объединить список столбцов со списком таблиц и добавить секцию WHERE, которая изменяется при каждом выполнении. Вы должны объединить все компоненты посредством конкатенации, правильно расставить запятые, не ошибиться с AND и OR и т. д. А что произойдет, если вы где-то ошибетесь?

База данных Oracle выдаст ошибку. Обычно эта ошибка сообщает, что именно не так со строкой SQL, но обычно эта информация оставляет желать лучшего. Рассмотрим следующий кошмарный сценарий: я строю самое сложное приложение PL/SQL в мире. В нем сплошь и рядом используется динамический SQL, но это нормально — я же про­фессионал в NDS. Я за считанные секунды ввожу execute immediate, open for и вообще все нужные команды. В обработке ошибок задействованы стандартные программы об­работки ошибок, которые я сам написал и которые выводят сообщение об ошибке при возникновении исключения.

Приходит время протестировать готовое приложение. Я запускаю тестовый сценарий, который выполняет большую часть моего кода. Начинаем тестирование:

SQL> @testall

И тут к моему жесточайшему разочарованию на экране появляются следующие со­общения:

 ORA-00942: table or view does not exist
 ORA-00904: invalid column name
 ORA-00921: unexpected end of SQL command
 ORA-00936: missing expression

И что мне с ними делать? К какой команде SQL относится каждое из этих сообщений? Мораль: при интенсивном использовании динамического SQL очень легко запутаться и потратить лишнее время на отладку кода — если только не принять необходимые меры при написании динамического SQL.

Мои рекомендации:

  •  Всегда включайте раздел обработки ошибок в код, вызывающий EXECUTE IMMEDIATE или OPEN FOR.
  •  В каждом обработчике сохраняйте и/или выводите сообщения и команды SQL при возникновении ошибок.
  •  Рассмотрите возможность добавления «трассировки» перед такими командами, чтобы вы могли легко наблюдать за динамическим SQL в процессе построения и выполнения.

Как эти рекомендации отражаются на вашем коде? Давайте применим их к процедуре exec_ddl, а затем пойдем по пути обобщения. Отправная точка выглядит так:

PROCEDURE exec_ddl (ddl_string IN VARCHAR2)
   AUTHID CURRENT_USER IS
BEGIN
   EXECUTE IMMEDIATE ddl_string;
END;

Добавим раздел обработки ошибок, который будет выводить информацию о возника­ющих проблемах:

PROCEDURE exec_ddl (ddl_string IN VARCHAR2)
   AUTHID CURRENT_USER IS
BEGIN
   EXECUTE IMMEDIATE ddl_string;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'Dynamic SQL Failure: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
      DBMS_OUTPUT.PUT_LINE (
         ' on statement: "' || ddl_string || '"');
      RAISE;
END;

При попытке использования этой версии для создания таблицы с недопустимым син­таксисом происходит следующее:

SQL> EXEC execddl ('create table x')
Dynamic SQL Failure: ORA-00906: missing left parenthesis
 on statement: "create table x"

Конечно, в окончательной версии можно применить более совершенные средства, чем встроенный пакет DBMS_OUTPUT.

Если при использовании DBMS_SQL попытка разбора завершается неудачей и курсор не будет явно закрыт в разделе ошибок, он так и останется открытым, что может привести к возможным ошибкам ORA-01000: maximum open cursors exceeded. В NDS это не случится; курсорные переменные, объявленные в локальной области действия автоматически закрываются при завершении блока (с освобождением занимаемой ими памяти).

А теперь взглянем на происходящее в перспективе: если задуматься, процедура exec_ddl не привязана к командам DDL. Она может использоваться для выполнения произволь­ной строки SQL, не требующей использования секций USING или INTO. С этой точки зрения можно создать одну программу, которая может использоваться вместо прямого вызова EXECUTE IMMEDIATE; со встроенной обработкой ошибок. Я включаю такую про­цедуру в пакет ndsutil.

Я мог бы даже создать аналогичную программу для OPEN FOR — снова только для ситуаций, не требующих секции USING. Поскольку OPEN FOR задает значение курсора, вероятно, реа­лизацию стоит оформить в виде функции, возвращающей тип слабой ссылки REF CURSOR.

В итоге получается пакетная реализация следующего вида:

PACKAGE ndsutil IS
   FUNCTION openFor (sql_string IN VARCHAR2) RETURN SYS_REFCURSOR;
END;

Пакет содержит полную реализацию функции; ее тело практически не отличается от приведенной ранее процедуры exec_dll.

 

 

Параметры вместо конкатенации

В большинстве случаев можно использовать два разных механизма вставки программных значений в строку SQL: передачу параметров и конкатенацию. В следующей таблице при­ведены примеры применения обоих подходов к построению динамической команды UPDATE.

 

Параметры Конкатенация
EXECUTE IMMEDIATE 'UPDATE ' || tab || 'SET sal = :new_sal' USING v_sal; EXECUTE IMMEDIATE 'UPDATE ' || tab || 'SET sal = '|| v_sal;

 

Первый способ подразумевает использование формальных параметров и секции USING; конкатенация упрощает этот процесс до прямого включения значений в строку SQL. Когда следует применять каждый из этих вариантов? Я рекомендую применять пере­дачу параметров там, где это возможно (см. следующий раздел). Для этого есть четыре причины:

  •  Передача параметров обычно работает быстрее. При передаче значения в пара­метре строка SQL не содержит значения — только имя формального параметра. Следовательно, вы можете связать разные значения с одной командой SQL без ее изменения. Так как команда SQL при этом не изменяется, повышается вероятность того, что ваше приложение воспользуется заранее разобранными курсорами, кэши­рованными в области SGA базы данных Oracle.

Примечание 1. Я говорю о «повышении вероятности», потому что в мире оптимиза­ции Oracle есть очень мало абсолютных истин. Например, один из возможных не­достатков передачи параметров заключается в том, что оптимизатор, основанный на определении затрат, располагает меньшей информацией и с меньшей вероятностью построит лучший план исполнения команды SQL.

Примечание 2. Если вам понадобится несколько раз выполнить одну команду SQL с разными параметрами, подумайте об использовании DBMS_SQL — возможно, вам удастся полностью избежать разбора команды, что невозможно с NDS. (См. «Ми­нимальный разбор динамических курсоров».)

  •  Передача параметров упрощает написание и сопровождение кода. При передаче параметров не нужно беспокоиться о преобразовании типа данных; все делается ав­томатически ядром NDS. Собственно, передача параметров сводит преобразования к минимуму, потому что она работает со встроенными типами данных. При использо­вании конкатенации часто приходится писать очень сложные строковые выражения с длинными сериями одиночных кавычек, вызовами функций TO_DATE и TO_CHAR и т. д.
  •  Передача параметров помогает избежать неявных преобразований. С конкатена­цией может оказаться, что вы случайно поручите базе данных выполнение неявных преобразований. В некоторых обстоятельствах база данных может выполнять не те преобразования, которые вам нужны; это может помешать использованию индексов.
  •  Передача параметров сводит к минимуму опасность внедрения кода. Одна из самых серьезных опасностей динамического SQL заключается в том, что вы пишете очень обобщенный код, который должен использоваться определенным образом, однако в зависимости от данных, передаваемых пользователем, полученная дина­мическая команда будет выполнять совершенно другие операции. Иначе говоря, пользователь может «внедрить» в команду SQL нежелательные операции. Пример приведен в следующем разделе.

Впрочем, у передачи параметров есть и потенциальные недостатки. В версиях, пред­шествующих 11.1, она нарушает использование гистограммной статистики, потому что значения параметров присваиваются только после разбора команды. Оптимизатор, осно­ванный на затратах, может располагать меньшим объемом информации, а это помешает ему построить оптимальный план выполнения команд SQL. В версии 11.1 и выше эта статистика используется функцией адаптивного совместного использования курсоров. Вероятно, разработчик PL/SQL должен стремиться прежде всего к написанию «чи­стого», понятного и простого в сопровождении кода. При многочисленных операциях конкатенации появляются команды, которые выглядят примерно так:

EXECUTE IMMEDIATE
   'UPDATE employee SET salary = ' || val_in ||
   ' WHERE hire_date BETWEEN ' ||
      ' TO_DATE (''' || TO_CHAR (v_start) || ''')' ||
      ' AND ' ||
      ' TO_DATE (''' || TO_CHAR (v_end) || ''')';

С переходом на передачу параметров код становится более понятным:

EXECUTE IMMEDIATE
   'UPDATE employee SET salary = :val
      WHERE hire_date BETWEEN :lodate AND :hidate'
   USING v_sal, v_start, v_end;

И хотя в некоторых ситуациях конкатенация работает более эффективно, не беспокой­тесь об этом, пока вы или ваш администратор базы данных не определит, что проблемы возникли из-за конкретной команды динамического SQL. Иначе говоря, переходите с передачи параметров на конкатенацию только при выявлении «узких мест» в про­грамме — и это должно быть исключением, а не правилом.

 

 

Минимизация опасности внедрения кода

Многие веб-приложения предоставляют исключительно гибкие возможности для ко­нечного пользователя. Гибкость часто достигается применением динамических блоков PL/SQL и SQL. Рассмотрим пример очень типичной процедуры получения строк:

PROCEDURE get_rows (
   table_in IN VARCHAR2, where_in IN VARCHAR2
)
IS
BEGIN
   EXECUTE IMMEDIATE
      'DECLARE
      l_row ' || table_in || '%ROWTYPE;
   BEGIN
      SELECT * INTO l row
      FROM ' || table_in || ' WHERE ' || where_in || ';
   END;';
END get_rows;

Выглядит совершенно невинно, но в действительности этот код открывает зияющие бреши в защите приложения. Рассмотрим следующий блок:

BEGIN
   get_rows ('EMPLOYEE'
      ,'employee_id=7369;
         EXECUTE IMMEDIATE
            ''CREATE PROCEDURE backdoor (str VARCHAR2)
               AS BEGIN EXECUTE IMMEDIATE str; END;''' );
END;
/

После выполнения этого кода создается процедура, которая выполнит любую команду, переданную в динамической строке. Например, я могу использовать UTL_FILE для полу­чения содержимого любого файла в системе, а затем создать (и удалить) любую таблицу или объект — эта возможность будет ограничена только привилегиями, определенными для схемы владельца.

Внедрение кода, также называемое внедрением SQL, способно серьезно повредить безопасности любого приложения. Выполнение динамических блоков PL/SQL от­крывает больше всего возможностей для внедрения. Хотя внедрение — очень большая тема, которую невозможно полноценно рассмотреть в этой теме, я приведу несколько рекомендаций, которые снизят вероятность внедрения в ваших приложениях.

 

Жесткое ограничение привилегий для пользовательских схем

Лучший способ свести к минимуму риск внедрения — позаботиться о том, чтобы при­вилегии любой схемы, к которой подключается внешний пользователь, были жестко ограничены.

Запретите таким схемам создание объектов базы данных, удаление объектов базы данных или прямой доступ к таблицам. Запретите выполнение пакетов, которые взаимодейству­ют (или могут использоваться для взаимодействия) с операционной системой — таких, как utl_smtp, utl_file, utl_tcp (и сопутствующих пакетов), а также dbms_pipe.

Такая схема должна обладать привилегиями только для выполнения хранимых про­грамм, определенных в другой схеме. Такой код PL/SQL тщательно планируется таким образом, чтобы он мог выполнять только ограниченный набор операций. При определении программ в этих исполняемых схемах, использующих динамический SQL, обязательно определите подпрограммы с конструкцией AUTHID CURRENTJJSER. В этом случае все программы SQL будут выполняться с ограниченными привилегиями схемы текущего подключения.

 

используйте передачу параметров там, где это возможно

Строгое соблюдение правил использования параметров в сочетании со встроенными средствами анализа и автоматизированным отклонением потенциально опасных строк позволит свести к минимуму опасность внедрения кода.

Правда, передача параметров оборачивается некоторой потерей гибкости. Например, в процедуре get_rows мне пришлось заменить полностью динамическую секцию WHERE менее универсальной конструкцией, которая лучше соответствует ожидаемому пове­дению приложения. Пример слегка видоизмененной процедуры get_rows:

 

PROCEDURE get_rows (
   table_in IN VARCHAR2, value1_in in VARCHAR2, value2_in IN DATE
)
IS
   l_where VARCHAR2(32767);
BEGIN
   IF table_in = 'EMPLOYEES'
   THEN
      l_where := 'last_name = :name AND hire_date < :hdate';
   ELSIF table_in = 'DEPARTMENTS'
   THEN
      l_where := 'name LIKE :name AND incorporation_date = :hdate';
   ELSE
      RAISE_APPLICATION_ERROR (
         -20000, 'Invalid table name for get_rows: ' || table_in);
   END IF;
   EXECUTE IMMEDIATE
      'DECLARE l_row ' || table_in || '%ROWTYPE;
      BEGIN
         SELECT * INTO l_row
         FROM ' || table_in || ' WHERE ' || l_where || ';
      END;'
      USING value1_in, value2_in;
END get_rows;
/

В этой версии секция WHERE использует два параметра; у злоумышленника не будет воз­можности выполнить постороннюю операцию из-за конкатенации. Я также проверяю имя таблицы и убеждаюсь в том, что оно соответствует ожидаемому. Это помогает из­бежать вызовов функций в секции FROM (так называемые табличные функции), которые тоже могут привести к нежелательным последствиям.

 

 

Проверка динамического текста

У рекомендаций из предыдущих разделов есть один недостаток: они зависят от созна­тельности конкретного разработчика или администратора. Эти рекомендации станут неплохой отправной точкой, и все же их недостаточно. Вероятно, в программу стоит включить проверки, которые убедятся в том, что пользовательский текст не содержит «опасных» символов — например, точки с запятой.

Я написал программу SQL Guard, которая идет по другому пути: она анализирует строку, введенную пользователем, и определяет, создает ли она риск внедрения SQL. Затем программист может решить, стоит ли выполнять эту команду (и возможно, за­регистрировать подозрительный текст в журнале). Код и руководство пользователя SQL Guard находятся в файле sqlguard.zip.

В программе SQL Guard проверки, по которым определяется риск внедрения SQL, могут настраиваться пользователем. Другими словами, SQL Guard поставляется с набором предопределенных проверок; вы можете удалять и добавлять проверки в список для схем внедрения SQL, специфических для рабочей среды вашего приложения. Невозможно создать механизм, который будет со 100% вероятностью перехватывать любые возможные попытки внедрения SQL. Тем не менее если вы решите использовать SQL Guard, эта программа (на мой взгляд) поможет вам:

  •  донести до ваших разработчиков необходимость учитывать угрозу внедрения SQL;
  •  предотвратить наиболее распространенные атаки внедрения SQL;
  •  проанализировать кодовую базу для выявления возможных путей внедрения.

 

Использование DBMS_ASSERT для проверки входных данных

Пакет DBMS_ASSERT проверяет действительность вводимого пользователем имени объекта SQL (например, имени схемы или таблицы). Пакет DBMS_ASSERT впервые был документи­рован в Oracle Database 11g. С того времени он был адаптирован для следующих версий Oracle: 8.1, 9.2, 10.1 и 10.2. В некоторых случаях он доступен в составе обновлений (в том числе и критических). Возможно, перед началом использования пакета вам придется связаться со службой поддержки Oracle.

DBMS_ASSERT.SIMPLE_SQL_NAME получает строку, которая должна содержать имя объекта SQL. Если имя проходит проверку, то функция возвращает строку без изменения. Если же имя недействительно, Oracle инициирует исключение DBMS_ASSERT. INVALID_SQL_NAME. За более подробным изложением темы обращайтесь к статье «How to write SQL injection proof PL/SQL», размещенной в Oracle Technology Network.

 

 

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 1970 просмотров Rasen Fasenger Mon, 22 Oct 2018, 04:44:08
Назначение языка SQL и необход...
Назначение языка SQL и необход... 367 просмотров Ирина Светлова Mon, 28 Oct 2019, 05:40:06
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 3747 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 2946 просмотров Doctor Thu, 12 Jul 2018, 08:41:33

Войдите чтобы комментировать

OraCool аватар
OraCool ответил в теме #9475 30 сен 2019 16:38
Крутая статья, спасибо. столько тонкостей и приемов программирования. Прям бриллиант!)