Explain plan для этого примера

SQL-инъекции

Отвлечемся от темы оптимизации.

Что такое SQL-injection?
Один из распространённых способов взлома сайтов и программ, работающих с базами данных, основанный на внедрении в запрос произвольного SQL-кода (спасибо Wikipedia).

А как внедрить то?
1. Предположим на сайте есть поля ввода, данные с этих полей передаются на сервер.
2. На сервере, они никак не обрабатываются, не проверяются, а вставляются в запрос как есть (такое бывает особенно в CMS’ках).
3. Запрос выполняет СУБД.
4. Результаты отправляются на сайт.

Запрос был вида:
select * from bank_account ba where account_id = id
“id” - пришло с сайта, это значение, типа 1234567.
Хакер или тулза по поиску уязвимостей: отправляет вместо “id”, например “id or 1=1”

Какой итоговый запрос получается?

select * from bank_account ba where account_id = id or 1=1

или cо значением:

select * from bank_account ba where account_id = 1234567 or 1=1

и что он вернет? правильно все банковские аккаунты.

Пример невероятно упрощенный. Главное чтобы поняли суть. Помимо “1 = 1”, отправить можно что угодно, хоть drop table.

В Oracle, такие кейсы можно прошляпить при использовании динамического SQL. Когда запрос конструируется на лету, и не используются переменные связывания (bind variables).
Подробно динамический SQL я разбираю в курсе “Oracle PL/SQL. Основы”, который уже почти готов 🎓

Так же возможен кейс, когда средний слой, неправильно работает с входными данными (пример с полями) и конструирует запрос "на лету", отправляя его в Oracle. Надеюсь, такой кейс нельзя встретить в промышленных системах 😊

Кажется, что ошибка глупая, ну как можно не проверить входные данные? И тем не менее, время от времени SQL-инъекции происходят.

Почему я упомянул CMS? В большинстве случаев, системы управления сайтом имеют открытый код. Никто вам не мешает, скачать исходники и посмотреть, правильно ли авторы CMS работают с БД.

Так же никто не отменял различные сканеры уязвимостей, которые долбят сайты/системы на предмет внедрения через поля форм. Подставляют в разные поля разные кусочки запросов типа 1 = 1, смотрят реакцию.

Тема достаточно интересная. Легкое гугление даст тонну статей по SQL-инъекциям, с разными примерами.

#security

Задача: Необходимо разрешить вставку значений в поле “vname”, состоящих только из латинских букв в верхнем регистре.
Остальные попытки с неподходящими значениями - отбивать с ошибкой.
PL/SQL, триггеры - использовать нельзя.

1. Таблица:

create table tab1(
  vname varchar2(100 char) not null
);

2. Языковой параметр сессии устанавливается в Russian.

alter session set nls_language = russian;

3. Команды для тестов:

insert into tab1 values ('ABC');-- ok
insert into tab1 values ('aBC');-- ошибка
insert into tab1 values ('abc');-- ошибка
insert into tab1 values ('АБВ');-- ошибка

В этот раз, было бы интересно получить решения от вас, друзья 😉
Если захотите поучаствовать, то крайний срок, до 19:00 четверга.

Самые интересные решения опубликую в пятницу с сохранением авторства 🏆

#задача

Как читать план запроса Oracle (последовательность выполнения шагов).

Друзья, всем привет!
По многочисленным просьбам, я таки запилил видос про то, как читать планы запросов🔮

Я не обещаю, что после просмотра урока вы сможете легко и просто читать планы и оптимизировать всё подряд. Однако, планы перестанут для вас быть магическим черным ящиком.

А еще, я экспериментирую с форматами видео, звуком и визуальной подачей материала. Если зайдет - не забудьте поставить палец вверх под видосом 👍

Приятного просмотра!

🎥 Смотреть видео - 8 мин

#видео #оптимизация

Задача: Необходимо разрешить вставку значений в поле “vname”, состоящих только из латинских букв в верхнем регистре.

Решение:
Задачка не так проста, как кажется на первый взгляд.

1. Необходимо ограничение (constraint), на столбец "vname", которое бы контролировало допустимость значений.
2. Что-то в этом ограничении нужно проверять.
На ум приходит регулярное выражение типа ^[A-Z]+$.
Бьюсь об заклад, что большинство мысленно, именно так решило эту задачу 🍅
Однако, Oracle не дремлет, и как всегда, подкинул баг/фичу 🐞

Регулярка не работает, при смене национальной языковой поддержки на nls_language = russian.
Смена nls_language, приводит к смене nls_sort.
Когда nls_sort не равен binary (по умолчанию), начинаются спецэффекты. Регулярка перестает работать.
Причем: в версиях 11g/12c все ок. В версия выше - начинаются проблемы.
С учетом этого, вариант с регуляркой ^[A-Z]+$ не подходит.
Привет тем, кто переходит на версии выше.

Ниже ограничения, которые успешно отрабатывают при любом nls_sort, с ссылкой на автора:

@v1talys

alter table tab1
  add constraint check_vname
  check (not regexp_substr(vname, '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]{'||length(vname)||'}') is null);

или

alter table tab1
  add constraint check_vname2
  check(trim(translate(vname, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')) is null);

——-

@alexeionin

alter table tab1 add constraint tab1$vname$chk check (trim(translate(vname,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','                          ')) is null)

или

alter table tab1 add constraint tab1$vname$chk check (regexp_count(vname, '[^[:upper:]]|[^A-Z]')=0);

——-

Игорь Дроздов

alter table tab1 add constraint lat_upper check(regexp_like(vname, '^[ABCDEFGHIJKLMNOPQRSTUVWXYZ]+$'));

—--
@SergeyElagin

alter table tab1 add constraint lat_upper check(regexp_like(vname collate binary_ci,'^[A-Z]+$','c'));

—--
Я не буду комментировать решения коллег, лишь скажу спасибо за старания 🤝

Если вам понравился такой формат задач, когда решения присылаете вы - ставьте 👍
В этом, кстати, есть замечательная особенность, на одну и ту же задачу можно посмотреть с совершенно разных сторон.
Как говорится, "каждую задачу можно решить разными способами"

#решениезадачи #regexp

Задача:
Опять задачка на регулярки, давно крутилась у меня в голове.
Итак, дан запрос:

select count(*)
  from dual
 where regexp_like('A_^bc\D','[A-z]+');

nls_sort = binary (подвоха в этом нет),

Возможные варианты: 0️⃣ или 1️⃣.
Попробуйте, не выполняя в СУБД этот запрос, выбрать правильный вариант 🔮

Как всегда, смотрите решение в четверг 🎓

#задача

Задача: какой будет результат выполнения запроса:

select count(*)
  from dual
 where regexp_like('A_^bc\D','[A-z]+');

nls_sort равен binary ❗️

Решение: будет 1️⃣

Возможно кто-то шокирован этим ответом. Ведь в строчке присутствуют спец символы типа “_^\”. А в паттерне мы не указывали спец символы. Как так то?

Указание диапазона “A-z” содержит в себе подводные камни.
Может помнит кто-то из курса университета про ASCII табличку? Assembler? )
В общем, между символом “Z” и “a” есть еще шесть спец символов, которые как раз таки попадают в диапазон A-z (см. скрин).

Соответственно, регулярка их пропускает.

Эта особенность проявляется не только в Oracle, но и в других языках, например в Java.
Будьте внимательны 👀

Надеюсь вам понравилась задачка 👍

#решениезадачи #regexp

Вакансия: Программист PL/SQL

Полное описание в вакансии.

Краткое:
- задачи OLTP-разработки (клики с сайтов/порталов), интеграционные решения точка-точка и средствами Informatica, работы с front-end на Oracle Application Express (APEX) и .NET, проектирование структур данных хранения, работа c legacy-решениями.
- ожидаемый уровень скилов Middle/Senior PL/SQL Developer .
- уровень з/п обсуждается индивидуально по итогам интервью.
- резюме (или краткое описание опыта разработки в свободной форме) просьба направлять на почту OFRuzaykina@rolf.ru Ольге Рузайкиной или в телегу https://t.me/Kapustin_Anton Капустину Антону.

#вакансия

Выбираем видео 🎥

Друзья, всем привет!
Пора выбирать тему для следующего видоса.

Для тех, кто вступил недавно. На нашем канале можно выбрать тему для обучающего видео, которое я сделаю.
А еще, вы можете предложить какую-то другую тему. Причем, тема может быть для любого уровня (Junior и выше).

Темы:
1. Отладка PL/SQL-кода.
2. Распараллеливаем выполнение задач через dbms_parallel_execute.
3. Практика. Логирование прикладных сообщений в БД (секционирование, типы сообщений и т.п.)
4. Оптимизация. Виды индексного доступа (unique scan, range scan и т.д,)
5. Полнотекстовый поиск средствами СУБД (почти как поиск в Яндексе =)
6. Что-нибудь другое - предлагайте в комментах или пишите в личку @denis_dbd. Не стесняйтесь 😉

Кстати, впервые решил сделать комментарии к посту. Посмотрим, как зайдёт 🤷🏻‍♂️

#видео

Если выбрали опцию "Что-нибудь другое", предложите ваш вариант в комментариях 😉

Пакет sys.standard

Довольно интересный системный пакет.
Содержит в себе определения типов, функций, именованных исключений и много чего другого. Можно сказать, все определение языка PL/SQL =)

Станем понятно, что за тип STRING или например, VARRAY. Чем они отличаются от VARCHAR2. Так же найдете забавные числовые типы, например SIGNTYPE.

Забыли какие есть именованные исключения типа NO_DATA_FOUND? Вам в этот пакет. Здесь они все перечислены и связаны с кодами ошибок (pragma exception init).

В некоторых версиях СУБД можно увидеть в тексте спецификации пакета забавные комменты, которые уже “не вырубить топором”.

Рекомендую заглянуть в него просто для расширения кругозора.

#пакеты

Друзья, всем привет!

В личку поступают вопросы от взволнованных подписчиков о снижении частоты постов.
Спешу успокоить, все в порядке 👌
Частота действительно слегка снизилась, но на то есть объективная причина.

Максимум усилий направлено на завершение курса по основам PL/SQL 🎓
Хочется сделать максимально удобный и качественный продукт.
Естественно, о нем я еще расскажу подробней чуть позже.

А пока, всем хороших выходных! 👍

B-tree индексы

Опрос показал, что многих интересует тема индексного доступа.
Прежде чем начать переходить к планам и обходам, необходимо затронуть тему физического устройства индексов.

Существует два основных типа физической организации (исключаем экзотику):
1. B-дерево (balanced, big).
2. Bitmap-карты.

Первый тип наиболее универсальный.
Второй для своих узких задач. Его мы рассматривать не будем, да простят меня любители DWH 😌

B-tree индекс
Используется в большинстве систем. Представляет собой сбалансированное дерево высотой не более четырех уровней (больше я не встречал).
1. В узлах ключи по которым происходит поиск нужного ветвления -> узла -> листа.
2. В листьях списки из значений + rowid. Rowid - самый быстрый способ получить доступ к строке таблицы.
3. Листья указывают друг на друга - типа двусвязного списка.
4. Null-значения не хранятся в индексе (значения всех столбцов дб is null).

#архитектура #индекс

Всем привет!

Коллега вчера поделился забавным куском кода с "фичей" Oracle 😊

Задача: Как думаете сколько записей будет в таблице test_tab1?

Думаете "больше чем ноль строк"? Нет =)
Выполните в СУБД посмотрите на результат.

create table test_tab1(a number);

begin
  for i in 1..2 loop
    insert into test_tab1 values (i);
  end loop
  commit;
  rollback;
end;
/

select * from test_tab1;

Отключаю коменты к этой задаче, чтобы не было спойлеров. Присылайте в личку объяснения, почему так происходит.
Опубликую ответ на этой недели.

#задача

Задача: Как думаете сколько записей будет в таблице test_tab1?
полную постановку смотрите в посте вторника.

Ответ:
Записей в таблице, действительно, не будет.
Все потому, что мы сталкиваемся с багом/фичей Oracle 🤷🏻‍♂️

Если внимательно посмотреть на код, то можно увидеть пропущенную ";" после loop.
Таким образом, "commit" выступает как метка цикла (label).
Сразу после цикла происходит откат вставленных строк (rollback).

Мы привыкли, что метка цикла (<< название >>) должна быть задана перед for.
Потому, код в примере выглядит немного диковато, зачем название метки в конце без указания её в начале 🤓
Еще и зарезервированное слово можно использовать...

Практически, все, кто написал мне, были правы. Спасибо, за решения 👍

Понравилась задачка?

#решениезадачи

Index Scan (Сканирование индексов)

Сканирование индекса - получение данных из индекса с использованием различного прохода по его блокам.
Данные из индекса могут использоваться как для получения искомых строк в таблице (по rowid), так и непосредственно в запросе, если значений в индексе будет достаточно, чтобы сформировать результаты запроса.

Как я уже отмечал в первом посте, речь пойдет только про B-tree индексы.

Виды сканирования (scan):
▫️ Unique (поиск по уникальному ключу)
▫️ Range (поиск по диапазону)
▫️ Skip (поиск в индексе с пропусками)
▫️ Full и Fast Full (полное сканирование индекса)
▫️ Index join (соединение результатов индексного поиска)

В следующих постах, буду раскрывать подробности.

#оптимизация #индекс

Оптимизация "на ровном месте"

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

Судя по всему, необходимо раскрыть это чуть более подробней. Проще всего на примере.

create table demo1(
id number,
nname varchar2(200 char)
);

-- вставим 10К записей
insert into demo1 select level, level from dual connect by level <= 10000;

-- создадим уникальный индекс
create unique index demo1_id_unq on demo1(id);

-- соберем статистику
call dbms_stats.gather_table_stats(ownname => user, tabname => 'demo1');

Запросы:
-- 1. будет задействован только индекс
select id from demo1 where id = 1000;

-- 2. будет задействован индекс + таблица (по rowid)
select id, nname from demo1 where id = 1000;

1-й запрос: искомое значение найдено в индексе (это ключ), другие поля для возврата результата не нужны.

2-й запрос: помимо поля id, которое можно получить из индекса, еще нужно поле nname, которое достать можно исключительно сходив в таблицу по rowid. В плане запроса шаг с table access by index rowid.

В видео про то как читать план запроса, я упоминал, старайтесь в запросе получать только те поля, которые действительно нужны. В некоторых случаях, это поможет снизить количество чтений данных с диска/памяти.

Планы запросов на скриншоте 🖼

#оптимизация #индекс