Select null from что это
Заметка про NULL
Основные положения
Для удобства сделаем процедуру, печатающую состояние булевого параметра:
и включим опцию печати сообщений на консоль:
Привычные операторы сравнения пасуют перед NULLом:
Сравнение с NULLом
Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:
DECODE идёт против системы:
Пример с составными индексами находится в параграфе про индексы.
Логические операции и NULL
В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ :
Отрицание неизвестности даёт неизвестность:
Операторы IN и NOT IN
Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу T с одним числовым столбцом A и четырьмя строками: 1, 2, 3 и NULL
Включим трассировку запроса (для этого надо обладать ролью PLUSTRACE ).
В листингах от трассировки оставлена только часть filter, чтобы показать, во что разворачиваются указанные в запросе условия.
Предварительные действия закончены, давайте теперь поработаем с операторами. Попробуем выбрать все записи, которые входят в набор (1, 2, NULL) :
Попробуем теперь с NOT IN :
Вообще ни одной записи! Давайте разберёмся, почему тройка не попала в результаты запроса. Посчитаем вручную фильтр, который применила СУБД, для случая A=3 :
Из-за особенностей трёхзначной логики NOT IN вообще не дружит с NULLами: как только NULL попал в условия отбора, данных не ждите.
NULL и пустая строка
Здесь Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. Тем более, говорить об этом они начали как минимум с седьмой версии СУБД (1992-1996 годы), а сейчас уже двенадцатая на подходе.
NULL и пустая строка эквивалентны:
непременный атрибут жёсткого спора:
Длина пустой строки не определена:
Сравнение с пустой строкой невозможно:
Критики подхода, предлагаемого Ораклом, говорят о том, что пустая строка не обязательно обозначает неизвестность. Например, менеджер по продажам заполняет карточку клиента. Он может указать его контактный телефон (555-123456), может указать, что он неизвестен (NULL), а может и указать, что контактный телефон отсутствует (пустая строка). С оракловым способом хранения пустых строк реализовать последний вариант будет проблемно. С точки зрения семантики довод правильный, но у меня на него всегда возникает вопрос, полного ответа на который я так и не получил: как менеджер введёт в поле «телефон» пустую строку и как он в дальнейшем отличит его от NULLа? Варианты, конечно, есть, но всё-таки…
Вообще-то, если говорить про PL/SQL, то где-то глубоко внутри его движка пустая строка и NULL различаются. Один из способов увидеть это связан с тем, что ассоциативные коллекции позволяют сохранить элемент с индексом » (пустая строка), но не позволяют сохранить элемент с индексом NULL:
Использовать такие финты ушами на практике не стоит. Во избежание проблем лучше усвоить правило из доки: пустая строка и NULL в оракле неразличимы.
Математика NULLа
Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.
Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:
С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.
NULL и агрегатные функции
Таблица с данными. Используется ниже много раз:
Пустые значения игнорируются агрегатами:
Набор данных только из NULLов:
Пустой набор данных:
NULL в OLAP
Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:
Проверяем дуализм NULLа в многомерном кубе:
Современные веб-приложения имеют сейчас довольно сложную структуру. Вместе с этим, для хранения информации стали активно использоваться базы данных на основе языка SQL. При обращении к какой-либо странице, веб-приложение формирует специальный SQL-запрос, который запрашивает в базе данных соответствующую запись. Результат запроса возвращается в веб-приложение, которое, в свою очередь, отображает его на странице браузера для пользователя.
Использовать подобную схему взаимодействия удобно, но вместе с этим, возрастает риск появления SQL-инъекций. Суть их работы заключается во внедрении собственного кода в SQL-запрос к базе данных с целью получить дополнительную информацию от нее.
Пожалуй, наиболее часто встречаются SQLi в SELECT запросах, так как данный тип запросов является самым распространенным, но, все же не ограничивается только им.
в строковом параметре;
в числовом параметре.
Для числового параметра характерна обработка числовых данных, например, в параметре ID:
http://example.com/index.php?id=1
Если параметр не будет иметь фильтрацию, а код выглядит примерно так:
то при эксплуатации SQL-инъекции веб-приложение направит некорректный запрос в базу данных, например:
и в ответе от веб-приложения пользователь получит ошибку синтаксиса. Но если этого не произойдет, то здесь 2 варианта:
SQL-инъекции здесь нет — фильтруются кавычки, или просто стоит преобразование в целочисленный тип int;
отключен вывод ошибок.
В строковом параметре ситуация аналогичная, только вместо числового параметра будет строка:
http://example.com/index.php?user=admin’
Статья носит информационный характер. Не нарушайте законодательство.
Основные методы эксплуатации SQL-injection
Union Based SQL-injection — применяется, если SQL-injection возникает в SELECT запросе. Благодаря данному методу можно объединить два SELECT запроса в один набор результатов. Особенность этого метода заключается в том, что он будет работать только в том случае, если количество столбцов, возвращаемых первым запросом, будет равно количеству столбцов, возвращаемых во втором запросе.
Для определения количества столбцов можно воспользоваться 3 методами:
добавление нового столбца при каждой итерации проверки, что не совсем удобно, так как столбцов может быть 20,30,50 и т.д.:
Почему используется значение NULL? Все просто, типы данных в каждом столбце должны быть совместимы между исходным и внедренным запросами. Поскольку NULL может быть преобразован во все часто используемые типы данных, его использование увеличивает вероятность успешного выполнения полезной нагрузки при правильном подсчете столбцов.
использование команды order by для определение количества столбцов. В этом случае нужно опираться на появление ошибки о несоответствии количества столбцов в запросе. Стоит начать с большого количества столбцов и уменьшать их вдвое при каждой итерации проверки. Если количество столбцов не будет соответствовать фактическому значению, то вернется ошибка
использование команды group by, который основывается на обратном методе проверки, в отличие от order by.
Boolean Based SQL-injection — метод эксплуатации слепых инъекций. Информация извлекается исходя из реакции на условные выражения. Инъекция называется слепой в тех случаях, если нет никакой видимой реакции от веб-приложения. Например, при подстановке кавычек в потенциально уязвимый параметр, ошибка, связанная с нарушением логики SQL-запроса, не появляется, а страница отображается без изменений. Но тут, как уже говорилось ранее, несколько вариантов: либо входные параметры фильтруются и уязвимости нет, либо на сервере отключен вывод ошибок на странице.
Вместе с запросом указывается некоторое условие. Условие может быть как истинным, так и ложным. Если оба условия выполняются одновременно, то, запрос отработает корректно, в противном случае запрос не будет выполнен.
Error Based SQL-injection — данный метод позволяет получить информацию из базы данных в тексте ошибки.
Для поиска такого рода инъекций можно воспользоваться следующими примерами:
В первом варианте 0X01 будет являться действительным числом, но в языке PHP, а не MySQL, что вызовет ошибку последнего. Во втором варианте число будет преобразовано в INF, что также будет являться действительным числом для PHP, но не для MySQL.
В этом примере добавляется задержка 20 секунд в виде команды sleep(20) для базы данных, которая создаст задержку перед ответом, что соответственно отразится и на времени ответа веб-приложения. Также для этих целей можно использовать и другие команды, например, benchmark или waitfor:
Пример, где по длительному ответу от базы данных можно определить, что первый символ значения user_password для пользователя с будет равняться «2» т.к. char(50) является цифрой 2:
Команда benchmark, в случае верного условия, выполнит функцию ENCODE 50000 раз, что вызовет задержку ответа, по которой и определяется наличие SQL-инъекции.
Как искать и эксплуатировать SQLi
1. Ручной поиск. Для начала во все параметры подставляем спецсимволы: кавычки, двойные кавычки и т.д. Если на каком-то варианте вернулась ошибка, то инъекция найдена и можно продолжать. Как правило, используется оператор union select, чтобы объединить сразу 2 запроса, а как мы уже знаем, этот метод требует соответствия в количестве столбцов. С помощью order by определяем количество столбцов. После этого можно формировать запрос для получения информации о самой базе данных. Допустим у нас столбцов будет 4, то команда будет выглядеть так:
В конце запроса обязательно ставится комментарий, например, в виде двойного дефиса. Это необходимо для того, чтобы лишняя информация не попала в передаваемый базе данных запрос, и не нарушала его структуру.
Отправив такой запрос, мы получим версию базы данных, имя пользователя базы данных, и порт, который использует БД (может быть полезно в случаях, когда его не удалось определить при сканировании веб-приложения).
Указав table_name и добавив from information_schema.tables мы получим список всех таблиц базы данных, включая системные. Это стало возможным благодаря добавлению таблицы information_schema в MySQL 5.0+ и по ее наличию также можно косвенно определить версию используемой БД. Получив список таблиц ищем среди них нужную и получаем из нее информацию. Например, у нас есть таблица users, где хранятся пользовательские данные — имя пользователя, хэш пароля и прочая информация.
В результате запроса в одном поле будет выведена информация о пользователях БД (логин) и их пароли в виде хеша, используя функцию concat.
2. Автоматизация. Безусловно, навык ручного поиска и эксплуатации SQL-инъекций очень важен и порой незаменим в тестировании на проникновение, однако и время тоже является крайне ценным ресурсом. Для его экономии, особенно при поиске и эксплуатации слепых инъекций, разработаны вспомогательные инструменты.
полная поддержка для таких систем управления базами данных, как: MySQL, Oracle, PostgreSQL, Microsoft SQL Server, Microsoft Access, IBM DB2, SQLite, Firebird, Sybase, SAP MaxDB и HSQLDB;
поддержка прямого подключения к базе данных без использования SQL инъекции, посредством введения учётных данных СУБД, IP адреса, порта и имени БД;
поддержка перечисления пользователей, хешей паролей, привилегий, ролей, БД, таблиц и колонок;
автоматическое распознавание форматов хешей паролей и предложение их взлома с использованием атаки по словарю;
поддержка выполнения произвольных команд на ОС сервера БД и получение их стандартного вывода при использовании MySQL, PostgreSQL или Microsoft SQL Server и многое другое.
Основные ключи, которые используются при работе с инструментом:
Инструмент позволяет найти и проэксплуатировать практически любую известную SQL-уязвимость, а за счет постоянных обновлений расширяется функционал, поэтому утилита обязательна к использованию. Ну а мы перейдем к менее популярному, но не менее эффективному инструменту.
JSQL injection
Инструмент для автоматизации поиска и эксплуатации SQL-уязвимостей. Написан на Java, поэтому является кроссплатформенным, нужно только установить нужную версию Java. Скачать jar-файл можно по ссылке.
В отличие от популярного SQLmap здесь есть графический интерфейс и инструмент в целом может выполнять большинство всех основных действий, необходимых для поиска и эксплуатации SQL-инъекций.
поддержка работы с 33 базами данных, например: Access, Altibase, Firebird, MemSQL, MySQL, Oracle, PostgreSQL, Presto, SQLite, SQL Server и т.д.;
поддержка различных видов инъекций: нормальные, error-based, stacked-based, blind и time-based;
список для введения нескольких целей;
чтение и запись файла с помощью инъекции;
создание и внедрение веб-шелла и SQL-шелла;
полный перебор паролей по хешу;
поиск страниц администратора;
кодирование и декодирование текста;
аутентификация с использованием Basic, Digest, NTLM и Kerberos;
прокси-соединение по HTTP, SOCKS4 и SOCKS5.
Судя по набору функций, инструмент в себе собрал не только средство поиска и эксплуатации SQLi, но и функционал Dirb и Hashcat. Лучше их использовать отдельно, но если такой возможности нет, то приходится использовать то, что есть, но об этом чуть позже.
Интерфейс
Интерфейс легкий, незамысловатый и интуитивно понятный. В главном меню указывается URL для проверки параметров в GET- или POST-запросах, cookie и прочих заголовках. После указания необходимых параметров автоматически начнется поиск уязвимостей. Если нашлись, то они будут проэксплуатированы, а во вкладке Database отобразится содержимое всей базы данных, где можно посмотреть информацию по любой из предоставленных таблиц.
Просмотр базы данных
Кстати, и SQLmap и JSQL injection поддерживают поиск уязвимостей в достаточно экзотических параметрах как, например, cookie. Но из-за того, что в SQLmap база пейлоадов намного больше, то и результата он, вероятнее, будет добиваться чаще. При тестировании инструментов на одном и том же приложении SQLmap смог подобрать нужный пейлоад командой:
а у JSQL injection с этим возникли небольшие сложности, вероятно, из-за не такой обширной базы пейлоадов.
Если удалось получить хэш пароля пользователя из БД, то в инструменте предусмотрен встроенный функционал перебора во вкладке Brute force. Это именно чистый bruteforce т.к. перебор по словарю недоступен и нужно указывать набор символов и их количество. Если сравнивать с SQLmap, то в последнем представлен перебор только по словарю, который может и не так эффективен, как полный перебор, но и не требует таких затрат по ресурсам компьютера и времени.
Перебор пароля по хешу
Есть возможность поиска панели администратора по списку. Разумеется, есть готовый список страниц для поиска, но его можно редактировать и дополнять.
Поиск панели администратора
Следующий набор функций позволяет читать файлы, загружать их, создавать и записывать web- и sql-шеллы на сервере. Все эти действия производятся на основе списка директорий, который необходимо либо прочитать, либо в который записать. Список можно редактировать. Но тут небольшая оговорка — чтобы этот функционал работал, у пользователя, от имени которого веб-приложение обращается к базе данных, должны быть соответствующие привилегии на чтение или запись, используя СУБД. То есть, должны быть привилегии на выполнение команд, например, load_file() или into outfile(). Но чаще всего подобные привилегии пользователю не предоставляются.
Чтение, запись и загрузка файлов
Вкладка Encode позволяет в реальном времени закодировать текст в base16, base32, base58, base64, Url-Encode, Hex,Html-Encode и т.д.
Последняя вкладка Scan позволяет установить список URL-адресов для автоматизации поиска SQL-инъекций. Предоставляемый изначально список можно удалить или отредактировать, добавляя собственные URL-адреса.
Используем WAF
И еще немного про взаимодействие инструментов с WAF. Изначально не заявлено, что оба инструмента позволяют обойти средство защиты, поэтому и тестирование было довольно коротким. Для тестирования использовался только Nemesida WAF Free и анализ запросов на основе сигнатур. Атаки обоих инструментов были успешно заблокированы и отображены в личном кабинете.
Фиксирование атак в личном кабинете
Вывод
Риск SQL-инъекций возникает всякий раз, когда программист создает динамический запрос к базе, содержащий введённые пользователем данные. Это значит, что способов предотвращения SQL-инъекций два:
не использовать динамические запросы к базе;
не использовать пользовательские данные.
Поскольку отказаться от этих двух условий довольно проблематично, то SQL-инъекции были и будут оставаться одними из самых распространенных и опасных уязвимостей веб-приложений. И вот небольшой список рекомендаций для защиты веб-приложений от них:
тщательная проверка данных от пользователя перед использованием их в запросе;
отключение вывода ошибок на сервере. От SQL-инъекций это не защитит, но затруднит ее поиск и эксплуатацию;
периодическое проведение аудита веб-приложения как вручную, так и с помощью инструментов автоматизации (SQLmap, jSQL Injection и т.д.). Также можно доверить этот процесс профессионалам с достаточным опытом и уровнем подготовки;
удаление неиспользуемого функционала. Та или иная функция, которая не была вовремя удалена может сыграть на руку злоумышленникам;
регулярная установка обновлений;
использование средств защиты веб-приложений, например WAF.
SQL-операторы: руководство с примерами запросов. Часть 2
Перевод второй части статьи «SQL Operators Tutorial – Bitwise, Comparison, Arithmetic, and Logical Operator Query Examples».
В первой части статьи мы рассмотрели такие темы:
В этой части мы рассмотрим:
Операторы для проверки существования (IN / NOT IN)
Если мы хотим проверить, есть ли определенное значение в списке значений, мы можем воспользоваться операторами IN или NOT IN :
Аналогично, для отрицания используется NOT IN :
Частичное совпадение — использование LIKE
Иногда нам нужно найти строки, основываясь на частичном совпадении.
Поиск пользователей, чей email оканчивается на gmail.com :
Строка %gmail.com означает «совпадение со всем, что кончается на gmail.com».
Если мы посмотрим на данные наших пользователей, мы заметим, что среди них только у двоих адрес электронной почты кончается на gmail.com :
Но в email Джейн указана заглавная «G». Предыдущий запрос не выберет эту запись, потому что мы ищем точное совпадение с gmail.com, а там «g» в нижнем регистре.
Чтобы поиск не зависел от регистра, нужно заменить LIKE на ILIKE :
Групповой символ % в начале строки означает, что вернуть нужно все, что заканчивается на «gmail.com». Это может быть и ob.jones+12345@gmail.com, и asdflkasdflkj@gmail.com — главное, чтобы в конце стояло «gmail.com».
Мы также можем использовать столько групповых символов, сколько нам нужно.
Работа с отсутствующими данными (NULL)
Давайте посмотрим, как быть со столбцами и строками, где нет данных.
Этот новый столбец будет TIMESTAMP (подобно datetime в других языках) и будет представлять дату и время, когда пользователь впервые заплатил нам за наше приложение. Может, мы хотим послать ему открытку и цветы в честь годовщины.
Чтобы изменить таблицу, не стирая ее и не лишаясь данных, можно использовать ALTER TABLE :
NULL это специальное значение в базах данных. Это отсутствие значения, и оно ведет себя не так, как можно было бы ожидать.
Чтобы это продемонстрировать, давайте посмотрим на простой SELECT :
Теперь давайте попробуем проделать то же самое с NULL :
Если мы запустим этот запрос еще раз, мы увидим в выводе ожидаемый нами NULL :
Использование IS NULL и IS NOT NULL
Эти значения ожидаемы: NULL IS NULL — истина, NULL IS NOT NULL — ложь.
Это все прекрасно и очень интересно, но как это применять на практике?
Что ж, для начала давайте заведем какие-то данные в нашем столбце first_paid_at :
В приведенной выше инструкции UPDATE мы задали значения для столбца first_paid_at у троих разных пользователей: пользователю с ID 1 — текущее время ( NOW() ), пользователю с ID 2 — текущее время минус месяц, а пользователю с ID 3 — текущее время минус год.
Во-первых, давайте найдем пользователей, которые нам уже платили, и пользователей, которые пока этого не делали:
Операторы сравнения при работе с датами и временем
Попробуем найти пользователей, которые совершили платеж на протяжении последней недели. Для этого мы можем взять текущее время ( NOW() ) и вычесть из него одну неделю при помощи ключевого слова INTERVAL :
Мы также можем использовать другой интервал, например, последние три месяца:
Теперь давайте найдем пользователей, которые совершали платеж в промежутке от одного до шести месяцев назад.
Проверка существования с использованием EXISTS / NOT EXISTS
Эти операторы фильтруют строки, проверяя существование или несуществование условия. Это условие обычно является запросом к другой таблице.
Это простая таблица. Она содержит только ID, поле для хранения текста поста ( body ) и ссылку на пользователя, который написал этот пост ( user_id ).
Давайте добавим в новую таблицу некоторые данные:
Согласно добавленным данными, у пользователя с ID 1 есть два поста, у пользователя с ID 2 — один пост, у пользователя с ID 3 — тоже один пост.
EXISTS принимает подзапрос. Если этот подзапрос возвращает что-либо (даже строку со значением NULL ), база данных включит эту строку в результат.
EXISTS проверяет лишь существование строки из подзапроса, ему не важно, что именно содержится в этой строке.
Вот пример выборки пользователей, имеющих посты:
Как и ождилалось, мы получили пользователей с ID 1, 2 и 3.
Аналогично, мы можем найти пользователей, у которых нет постов. Для этого нужно заменить EXISTS на NOT EXISTS :
Наконец, мы можем переписать наш запрос и использовать IN или NOT IN вместо EXISTS или NOT EXISTS :
Поразрядные операторы
Хотя на практике поразрядные операторы используются нечасто, для полноты картины давайте рассмотрим простой пример.
Если мы по какой-то причине хотим посмотреть возраст наших пользователей в бинарном виде и поиграться с перестановкой битов, мы можем использовать поразрядные операторы.
Чтобы осуществить поразрядную операцию, нам сначала нужно преобразовать значения в нашем столбце age из целых чисел в бинарный формат. В данном случае мы использовали ::bit(8) и получили восьмибитовые строки.
Практически все остальные поразрядные операторы используют тот же формат:
Поразрядный оператор «not» (
) немного отличается. Он применяется к одному термину, так же, как и обычный оператор NOT :
И, наконец, самый полезный из поразрядных операторов: конкатенация.
Этот оператор обычно используется для склейки вместе строк текста. Например, если мы хотим составить вычисленное «полное имя» для пользователей, мы можем воспользоваться конкатенацией:
Заключение
Итак, мы рассмотрели практически все операторы фильтрации, котоыре вам могут понадобиться в работе!
Есть еще несколько, о которых мы не упоминали, но они либо используются не слишком часто, либо используются точно так же, как те, что мы разобрали, так что у вас не должно возникнуть проблем с ними.
От редакции Techrocks: возможно, вам будет интересна еще одна статья того же автора: SQL JOIN: руководство по объединению таблиц.