Гарантируйте “Разумные” запросы только
В нашей организации у нас есть потребность позволить сотрудникам отфильтровать данные в нашем веб-приложении путем предоставления операторов Where. Это работало отлично в течение долгого времени, но мы иногда сталкиваемся с пользователями, обеспечивающими запросы, которые требуют полных сканирований таблицы на больших таблицах или неэффективных соединениях и т.д.
Некоторый клоун мог бы записать что-то как:
select * from big_table where
Name in (select name from some_table where name like '%search everything%')
or name in ('a', 'b', 'c')
or price < 20
or price > 40
or exists (select 1 from some_other_table where col1 + col2 + col3 = 4)
or exists (select 1 from table_a, table+b)
Очевидно, это не отличный способ запросить эти таблицы с вычисленными значениями, неиндексированными столбцами, большим количеством OR's и неограниченное соединение на table_a и table_b.
Но для пользователя, это может иметь общий смысл.
Таким образом, каков лучший способ, если таковые имеются, чтобы позволить внутренним пользователям предоставлять запрос к базе данных при обеспечении, что это не заблокирует дюжину таблиц и подвесит веб-сервер в течение 5 минут?
Я предполагаю, что это - программный путь в c#/sql-server для получения плана выполнения относительно запроса, прежде чем он будет работать. И если так, что факторы вносят в стоимость? Предполагаемый ввод-вывод стоится? Предполагаемый ЦП стоится? Каковы были бы разумные пределы, в которых можно сказать пользователю что отрицательный результат его запроса?
Править: Мы - компания по исследованию рынка. У нас есть тысячи обзоров, каждый с их собственными данными. У нас есть десятки исследователей, которые хотят нарезать те данные произвольными способами. У нас есть инструменты, чтобы позволить им создать "допустимые" фильтры с помощью GUI, но некоторые "продвинутые пользователи" хотят предоставить свои собственные запросы. Я понимаю, что это не общепринятая практика или лучшая практика, но как еще я могу позволить десяткам пользовательских таблиц запроса для строк, они хотят использовать произвольно сложные условия и постоянно меняющиеся условия?
11 ответов
] Вы можете попробовать использовать следующее:[
] [SET SHOWPLAN_ALL ON
GO
SET FMTONLY ON
GO
<<< Your SQL code here >>>
GO
SET FMTONLY OFF
GO
SET SHOWPLAN_ALL OFF
GO
] Тогда вы сможете разобрать то, что у вас есть. Что касается того, где провести черту на различных вещах, то это потребует некоторого опыта. Есть некоторые вещи, на которые стоит обратить внимание, но ничего не вырезано и не высушено. Часто это скорее искусство - рассматривать планы запросов, чем наука.[
] [] Как отмечали другие, я думаю, что ваша проблема уходит глубже, чем технологические последствия. Тот факт, что вы позволяете неквалифицированным людям получить доступ к вашей базе данных таким образом, является основной проблемой. Из прошлого опыта я часто вижу это в компаниях, где они слишком ленивы или неопытны, чтобы должным образом зафиксировать требования своего приложения. Я не говорю, что это обязательно так в вашей корпоративной среде, но это то, что я видел[
].]В дополнение к попыткам контролировать то, что пользователи вводят (что является проигрышной битвой, всегда будет новый сотрудник, который придет с невообразимым запросом), я бы заглянул в Resource Governor, смотрите []Управление рабочими нагрузками SQL сервера с помощью Resource Governor[]. Вы помещаете специальные запросы в отдельный пул и ограничиваете выделенные ресурсы. Таким образом, вы можете смягчить проблему, ограничив количество повреждений, которые плохой запрос может причинить [] другим [] задачам. [
] [] А также вы должны рассмотреть возможность предоставления доступа к данным другими способами, например, []Power Pivot[], и позволить пользователям массировать свои данные так сильно, как они хотят, в их собственном Excel. Бизнес-пользователям это нравится, и влияние на сервер transaciton processign минимально.[
].]Предпосылка Вашего вопроса гласит:[
] [][]В нашей организации мы должны позволить сотрудникам фильтровать дату в нашем веб-приложении, поставляя пункты ГДЕ.[][
] []Я нахожу эту предпосылку ошибочной на первый взгляд. Я не могу представить себе ситуацию, в которой я бы позволил пользователям делать это. В дополнение к проблемам, которые вы уже определили, вы открываете себя для атак SQL Injection.[
] []Я бы настоятельно рекомендовал пересмотреть ваши требования, чтобы понять, не можете ли вы построить более безопасный и сфокусированный способ предоставления пользователям возможности осуществлять поиск.[
] []Однако, если ваши пользователи действительно достаточно опытны (и им доверяют!), чтобы напрямую предоставлять условия WHERE, они должны быть осведомлены о том, что они могут и что они не могут предоставлять в качестве фильтра.[
].] Вместо того, чтобы позволить сотрудникам напрямую писать (добавлять к) запросы, а затем пытаться вычислить стоимость запроса перед его запуском, почему бы не создать некий Расширенный Поиск или функцию фильтрации, которая НЕ пишет SQL, который вы не можете контролировать?[
].]В очень больших корпорациях это обычная практика. Часто на этапе проектирования вы ограничиваете критерии или накладываете разумные ограничения на диапазоны данных, но как только предприятие получит приложение, будут поступать звонки от руководства бизнес-подразделения для снятия ограничений. В моем происхождении это проблема управления, а не инженерная проблема.[
] []Мы профилировали все критерии и нашли крупнейших нарушителей, как пользователей, так и то, какие типы запросов вызвали наибольшие проблемы и наложили ограничения на некоторые из запросов. Также в приложение были добавлены некоторые очень дорогие запросы, которые использовались на регулярной основе, и приложение кэшировало результаты и выполняло запросы при низкой загрузке. Мы также создавали канонизированные оптимизированные запросы для стандартных пользователей и предоставляли только определенным пользователям возможность искать что угодно. Всего пара идей[
].] Вы можете создать модель данных для своей базы данных и позволить пользователям использовать SQL Report Builder. Его графический интерфейс основан на GUI и не требует написания пунктов WHERE, поэтому должно быть ограничение на то, какой ущерб они могут нанести.[
] [] Или вы можете хранить копию db для запросов пользователей, обновлять db каждый час или около того, и отпускать их в город... :) [
]] По этой причине прямое разрешение SELECT почти никогда не предоставляется пользователям в подавляющем большинстве приложений.[
] []Гораздо лучшим подходом было бы проектирование вашего приложения на основе случаев использования, чтобы вы могли покрыть разумный процент требований с помощью специально разработанных фильтров/агрегации/раскладок. [
] []Существует бесчисленное множество способов сделать это, так что некоторый анализ вашей конкретной проблемной области, безусловно, потребуется вместе с исследованием жизнеспособных методов.[
] []Хотя прямой доступ к SQL является наиболее гибким для ваших пользователей, длительные запросы на выполнение, скорее всего, будут лишь началом вашей головной боли. SQL-инъекция - это большая проблема, независимо от того, является ли ее источник вредоносным или просто ошибочным[
].] Думаю, вы никогда не слышали об атаках SQL Injection? Что делать, если пользователь вводит команду DROP DATABASE после пункта WHERE?[
].Я работал в нескольких местах, где это тоже всплыло. То, что мы в итоге делали, это НЕ разрешали пользователям неограниченный доступ, а обещали, что ИТ-отделы сделают все возможное, чтобы предоставлять запросы, когда это необходимо. Проблема заключалась в том, что база данных довольно сложна, и даже если пользователи могли бы писать грамматически и синтаксически корректный SQL, они не обязательно понимают отношения между таблицами. Другими словами, даже если бы они могли написать свой собственный SQL, они бы получили неправильные ответы. Мы убедили пользователей, что риск принятия неверного решения, основанного на неправильном или неполном понимании 200 таблиц в БД, слишком велик. Лучше сразу же получить правильный ответ через день, чем неправильный.
Другая часть этого - что делает IT, когда пользователь A записывает запрос и получает 1 ответ, затем пользователь B записывает то, что он думает, что это тот же самый запрос и получает другой ответ? Разве это работа IT - находить различия? Исправить обе части SQL и т.д. Суть в том, что я бы не разрешил им доступ. Я бы загрузил систему предопределенными запросами, как упоминали другие, и попытался бы обучить mgmt, почему только так он будет работать в долгосрочной перспективе.
Если у вас так много данных, и вы хотите предоставить своим клиентам возможность анализировать и просматривать информацию так, как они хотят, я настоятельно рекомендую использовать OLAP технологии.
(Чад упомянул об этом в комментарии, но я думаю, что это заслуживает ответа.)
Может быть, вам стоит скопировать данные, которые нужно запросить по отдельности, в отдельную базу данных, чтобы изолировать любые проблемы от большинства пользователей.