К настоящему моменту вы должны уже достаточно хорошо понимать, как «встроенный» динамический 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.