Oracle Developer👨🏻‍💻 on Telegram: “EXECUTE IMMEDIATE и конкатенация: страшная залеп…”

EXECUTE IMMEDIATE и конкатенация: страшная залепа 😱

Коллеги, всем привет! 👋
На связи Денис.

На днях смотрел рабочий код и увидел просто страшную залепу. Динамический SQL, а все параметры вклеены в текст запроса конкатенацией. Материал для Junior и Middle, но такое, к сожалению, пишут и сеньоры 🍅

Примерно так (упростил и переписал на HR-схему):

l_sql := 'select count(*) from employees where department_id = ' || p_dept_id ||
         ' and last_name = ''' || p_last_name || '''' ||
         ' and hire_date >= ''' || p_hire_from || '''';
execute immediate l_sql into l_cnt;

Работает? Работает. А теперь по пунктам, почему так нельзя.

1️⃣ Каждый вызов - новый запрос
Для Oracle ... = 101 и ... = 102 - это два разных текста, а значит, два разных SQL_ID и два hard parse. Прогнал цикл на 10 000 вызовов:

concat: 15.61 s   -- 10 000 разных SQL_ID
bind:   .15 s     -- 1 SQL_ID, 10 000 executions

В 100 раз медленнее, и это на пустой тестовой базе. На проде вы ещё и забиваете shared pool мусором: у меня из 10 000 курсоров в v$sql дожили 1319, остальные вытеснили друг друга. Заодно вытесняются и нормальные запросы соседей.

2️⃣ SQL-инъекция
Передаём фамилию с сюрпризом:

p_last_name := 'OConnell'' or ''1''=''1';

Конкатенация: вернула 107 строк, то есть всю таблицу. С bind-переменной - 0, потому что это просто такая странная фамилия 😄

3️⃣ Кавычки и O'Connell
Обычная фамилия с апострофом ломает запрос:

-- 23ai
ORA-03049: SQL keyword 'CONNELL' is not syntactically valid following '...= 80 and last_name = 'O''
-- 18c
ORA-00933: SQL command not properly ended

4️⃣ Даты через NLS
p_hire_from - это date, при конкатенации он неявно превращается в строку по NLS_DATE_FORMAT, а потом обратно. Поставил формат DD.MM.YY и передал 01.01.1999:

string: 01.01.99
concat: 0      -- 99 превратился в 2099 год
bind:   107

Поменяли настройки сессии - поменялся результат запроса. Хрен найдёшь потом 🤦🏻‍♂️

Как надо
Значения - только через bind-переменные и using:

execute immediate
  'select count(*)
     from employees
    where department_id = :dept_id
      and last_name     = :last_name
      and hire_date    >= :hire_from'
  into l_cnt
  using p_dept_id, p_last_name, p_hire_from;

Один SQL_ID на все вызовы, инъекции нет, кавычки и даты не ваша забота ✅

Дисклеймер
А теперь главное: в примере выше динамический SQL вообще не нужен. Где можно написать статический SQL - пишите статический:

select count(*)
  into l_cnt
  from employees
 where department_id = p_dept_id
   and last_name     = p_last_name
   and hire_date    >= p_hire_from;

PL/SQL-переменные в статическом SQL и так становятся bind-переменными. Плюс компилятор проверит, что таблица и колонки существуют, а зависимости будут видны в user_dependencies.

Динамика нужна, когда в рантайме меняется сама структура запроса: имя таблицы, набор колонок, условия. И даже тогда значения идут через bind, а имена объектов - через dbms_assert.

Прогнал всё на 23ai и 18c, поведение одинаковое, отличается только текст ошибки в п.3. Подробнее - в документации Oracle по динамическому SQL.

Итог
Конкатенация в execute immediate - это минус производительность, минус безопасность и плюс баги, которые зависят от настроек сессии. Сначала статический SQL, если никак - динамический с bind-переменными.

Если интересно, сниму небольшой видос про это - ставьте 👍🏻

А у вас на код-ревью такое ловят? Или проскакивает? Пишите в чатике 💬

С вами был Денис. Всем хорошего дня 🤝

#oracle #plsql #sql #oracledeveloper #performance #базы_данных #Denis_Kivilev

Канал Oracle Developer | Чатик 💬
Мини-курс Оптимизация: Быстрый старт 🚀

📱 YouTube 📱 ВКонтакте 📱 LinkedIn 📱Threads
RUTUBE