Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Гигантский поиск  [new]
hey
Guest
В системе есть функция поиска по более чем 400 полей. Т.е. пользователь может использовать любое из них

По соображениям безопасности, использовать динамический sql нельзя.
Если написать ХП вида

CREATE PROCEDURE [dbo].[SearchSP]
@arg1 VARCHAR(100),
@arg2 VARCHAR(100),
@arg3 VARCHAR(100)
AS
BEGIN

   SELECT * FROM MyTable
   WHERE field1 = ISNULL(@arg1, field1) AND 
   field2 = ISNULL(@arg2, field2) AND 
   field3 = ISNULL(@arg3, field3)
END
GO

то скорее всего производительность провалится ниже плинтуса из-за невозможности использовать индексы.

Что тут можно сделать ? Подумываю разбить ее на несколько десятков более мелких (делать проверку на null параметров и если они null то не вызывать соответствующую ХП) с последующим джойном результатов

Так вроде количество сканов можно сократить

Может есть еще способы ?
23 июн 09, 03:36    [7331157]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
аффтар
Guest
параметры передавать в виде XML и с ним и соединяться для поиска
23 июн 09, 07:35    [7331212]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
hey
Guest
аффтар
параметры передавать в виде XML и с ним и соединяться для поиска

не понял, с кем соединяться ?
23 июн 09, 07:43    [7331215]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
ветерочек
Member

Откуда: СПБ
Сообщений: 153
есть - автоматически сгенерить 400*399*398 процедур и нужную вызывать

а что это за соображения безопасности?

зы если в примере в полях может быть NULL то пример не верен.
23 июн 09, 08:19    [7331251]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
hey
Guest
ветерочек

а что это за соображения безопасности?

зы если в примере в полях может быть NULL то пример не верен.

что-бы sql injection не подсунули.
Пример правильный, NULL будет означать отсутствие аргумента, т.е. по нему никакой фильтрации производить не надо.

Я этот способ часто применяю для подобных задач, но вот в данном случае он не годится.
23 июн 09, 08:31    [7331266]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
ветерочек
Member

Откуда: СПБ
Сообщений: 153
hey
ветерочек

а что это за соображения безопасности?

зы если в примере в полях может быть NULL то пример не верен.

что-бы sql injection не подсунули.
Пример правильный, NULL будет означать отсутствие аргумента, т.е. по нему никакой фильтрации производить не надо.

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


SELECT * FROM MyTable
   WHERE field1 = ISNULL(@arg1, field1) 
в этом случае при @arg1 IS NULL в результат не попадут записи у которых field1 IS NULL.

хотя бы один пример sql injection пожалуйста.
23 июн 09, 08:42    [7331286]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Конечно динамик лучше всего будет. Но раз нельзя, то можно попробовать примерно так, сам не пробывал, но думаю может получиться:
...
if @f1 is not null
begin
select * into #t1 from myTable m where m.f1 = @f1
end

if @f2 is not null
begin
if object_id('tempdb..#t1') is not null begin
  select * into #t2 from #t1 m where m.f2 = @f2
  drop table #t1
  end else begin
  select * into #t1 from myTable m where m.f2 = @f2
  end 
end

if @f3 is not null
begin
if object_id('tempdb..#t1') is not null begin
  select * into #t2 from #t1 m where m.f3 = @f3
  drop table #t1
  end else
  if object_id('tempdb..#t2') is not null begin
    select * into #t1 from #t2 m where m.f3 = @f3
    drop table #t2
    end else begin
    select * into #t1 from myTable m where m.f3 = @f3
    end 
end

if @f4 is not null
begin
if object_id('tempdb..#t1') is not null begin
  select * into #t2 from #t1 m where m.f4 = @f4
  drop table #t1
  end else
  if object_id('tempdb..#t2') is not null begin
    select * into #t1 from #t2 m where m.f4 = @f4
    drop table #t2
    end else begin
    select * into #t1 from myTable m where m.f4 = @f4
    end 
end

if @f5 is not null
begin
if object_id('tempdb..#t1') is not null begin
  select * into #t2 from #t1 m where m.f5 = @f5
  drop table #t1
  end else
  if object_id('tempdb..#t2') is not null begin
    select * into #t1 from #t2 m where m.f5 = @f5
    drop table #t2
    end else begin
    select * into #t1 from myTable m where m.f5 = @f5
    end 
end
...
--И так далее, ну и в конце понятно
if object_id('tempdb..#t1') is not null select * from #t1
  else if object_id('tempdb..#t2') is not null select * from #t2
    esle select * from myTable

не тестил, если будет ругаятся на временные таблицы, то тогда надо будет их не дропать, а трункейтить. Создать 1 раз и вести отдельный флаг, для той, которая актуальная.
Две временные таблица для быстродействия. Вставлять из одной в другую быстрее, чем удалять лишние записи в одной.
23 июн 09, 08:44    [7331289]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Что бы индексы из myTable использовались, надо все таки переделать.

Insert into #t1(PK)
select PK from MyTable where f1 = @f1

--И далее 
Insert into #t2
select PK from MyTable M 
    inner join #t1 t on t.PK = m.PK
where M.f2 =@f2
23 июн 09, 08:58    [7331319]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
hey
Guest
ветерочек


SELECT * FROM MyTable
   WHERE field1 = ISNULL(@arg1, field1) 
в этом случае при @arg1 IS NULL в результат не попадут записи у которых field1 IS NULL.

хотя бы один пример sql injection пожалуйста.

ну так именно это у меня в начале и написано. Этот подход при большом числе параметров скорее всего свалит любой мощности сервер
23 июн 09, 09:09    [7331344]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
ветерочек
Member

Откуда: СПБ
Сообщений: 153
hey
ветерочек


SELECT * FROM MyTable
   WHERE field1 = ISNULL(@arg1, field1) 
в этом случае при @arg1 IS NULL в результат не попадут записи у которых field1 IS NULL.

хотя бы один пример sql injection пожалуйста.

ну так именно это у меня в начале и написано. Этот подход при большом числе параметров скорее всего свалит любой мощности сервер


я не решение предлагаю , а ошибку показываю....

а как насчет примера sql injection?
23 июн 09, 09:12    [7331349]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
hey
Guest
Deff
Конечно динамик лучше всего будет. Но раз нельзя, то можно попробовать примерно так, сам не пробывал, но думаю может получиться:


что-то мне кажется, что при 400 параметрах это все станет неуправляемым :) Там один только парсинг может хорошо нагрузить :)

Вообще у меня была идея последовательного фильтрования, когда с каждой новой порцией параметров результирующий набор записей сокращается, собственно если иметь порядка 20 процедур, и в каждой производить нечто-вроде

DELETE FROM #t a
JOIN 
(
SELECT * FROM MyTable WHERE field1 = @arg1
) b

где #t берется из внешней процедуры.
Но что-то тоже тяжеловестно получается
23 июн 09, 09:18    [7331361]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
hey
Guest
ветерочек
я не решение предлагаю , а ошибку показываю....
а как насчет примера sql injection?

а, невнимательно прочитал,
если field1 IS NULL то он мне в резалтсете и не нужен, так что там все нормально, это вообще распостраненный подход к таким вещам.
sql injection при динамическом sql делается очень просто - добавляется дополнительный код в виде параметра
23 июн 09, 09:23    [7331379]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
ветерочек
Member

Откуда: СПБ
Сообщений: 153
hey
ветерочек
я не решение предлагаю , а ошибку показываю....
а как насчет примера sql injection?

а, невнимательно прочитал,
если field1 IS NULL то он мне в резалтсете и не нужен, так что там все нормально, это вообще распостраненный подход к таким вещам.
sql injection при динамическом sql делается очень просто - добавляется дополнительный код в виде параметра


сферические sql injection в вакууме это конечно интересно

вот процедура, взламывай
create proc test_dinamic @name sysname = null,@id int= null
AS

DECLARE @SQL NVARCHAR (4000)
SET	@SQL	='SELECT * FROM sysobjects WHERE 1=1 '+
CASE WHEN  @name IS NULL THEN '' ELSE ' AND name=@name ' END +
CASE WHEN  @id IS NULL THEN '' ELSE ' AND id=@id ' END 
print @sql
EXEC SP_EXECUTESQL
	@Stmt  			= @SQL
	,@Params 		= N'@name sysname, @id int'
	,@name 			= @name
	,@id			= @id
GO
23 июн 09, 09:39    [7331427]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
ветерочек
Member

Откуда: СПБ
Сообщений: 153
hey

если field1 IS NULL то он мне в резалтсете и не нужен, так что там все нормально, это вообще распостраненный подход к таким вещам.


?

create table #testMyTable (field1 VARCHAR(100),field2 VARCHAR(100),field3 VARCHAR(100))
insert #testMyTable
values ('1',null,null),(null,'2',null),(null,null,'3'),('4','4','4')

go
CREATE PROCEDURE [dbo].[SearchSP]
@arg1 VARCHAR(100),
@arg2 VARCHAR(100),
@arg3 VARCHAR(100)
AS
BEGIN

   SELECT * FROM #testMyTable
   WHERE field1 = ISNULL(@arg1, field1) AND 
   field2 = ISNULL(@arg2, field2) AND 
   field3 = ISNULL(@arg3, field3)
END
GO
exec SearchSP @arg1='4',@arg2= null,@arg3 =null -- здесь правильно 
exec SearchSP @arg1='1',@arg2= null,@arg3 =null -- здесь должна вывестись строка или нет? 

23 июн 09, 09:44    [7331452]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
В первоначальном варианте сервер все равно будет использовать индексы, но только index scan.
Чтобы заставить его делать index seek, нужно извращаться вот таким образом:

CREATE PROCEDURE [dbo].[SearchSP]
@arg1 INT,
@arg2 DATETIME,
@arg3 VARCHAR(100)
AS
BEGIN
   SELECT * FROM MyTable
   WHERE field1 BETWEEN ISNULL(@arg1, -2147483648) AND ISNULL(@arg1, 2147483647) 
   field2 BETWEEN ISNULL(@arg2, '1990-01-01') AND ISNULL(@arg2, '9999-12-31')
   field3 LIKE ISNULL(@arg3, '%')
END

НО, это хорошо подходит для варианта когда 90% параметров все таки не равны NULL, а в вашем случае пользователи наверное будут вводить 2-3 параметра из 400, что поставит сервер раком, потому что оптимизатор индексы то выберет для поиска, а в процессе выполнения выяснится что селективности то по ним никакой.
Так что идеальный вариант для таких монстров - только динамичский запрос. У нас в компании тоже почему то политикой запрещено их использование, надо будет выяснить кто это придумал и по каким соображениям. Возможно "просто потому что так заведено". А с секьюрностью, так можно докатиться до того, что компьютер не секьюрный, там вирусы, черви и интернет, данные своруют. Давайте лучше на счётах считать и на бумажке все записывать :-)
23 июн 09, 10:54    [7331809]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
hey
Guest
ветерочек

вот процедура, взламывай


thanks, не знал, похоже это решает проблему, не знаю правда как наш лид отреагирует на то, что динамический запрос все-же имеет место быть, но раз injection становится невозможно подсунуть, то может и сойдет

Для Null полей кстати действительно необходимо доп. обработку вводить, не замечал этого раньше, у нас все поля not null
23 июн 09, 11:07    [7331876]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
hey
Guest
Mind

НО, это хорошо подходит для варианта когда 90% параметров все таки не равны NULL, а в вашем случае пользователи наверное будут вводить 2-3 параметра из 400, что поставит сервер раком, потому что оптимизатор индексы то выберет для поиска, а в процессе выполнения выяснится что селективности то по ним никакой.
Так что идеальный вариант для таких монстров - только динамичский запрос. У нас в компании тоже почему то политикой запрещено их использование, надо будет выяснить кто это придумал и по каким соображениям. Возможно "просто потому что так заведено". А с секьюрностью, так можно докатиться до того, что компьютер не секьюрный, там вирусы, черви и интернет, данные своруют. Давайте лучше на счётах считать и на бумажке все записывать :-)


у нас в основном проблема была в injection, если вышеназваный способ ее полностью устраняет, то возможно политика компании и подвинется, иначе все-же придется писать монстроподобные конструкции для замены
23 июн 09, 11:11    [7331896]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
hey,

Устраняет. В официальных MS инструкциях написано - пользуйте SP_EXECUTESQL с параметрами и да будет вам счастье великое.

Я подозреваю, что у нас тоже проблема в injection. Народ по глупости понапишет динамических запросов, потом кто-то хакнет все это дело и пишут в инструкциях - не используйте динамические запросы, это ЗЛО. А на самом деле надо писать - "не используйте динамические запросы неправильно"
23 июн 09, 11:29    [7332029]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
Crimean
Member

Откуда:
Сообщений: 13148
проблема инъекций решается парсом параметров, а не подстановкой их в запрос
а проблема прав "для динамики" в sql 2005 и старше решается with execute as опцией
так что имхо проблема весьма надумана :)
23 июн 09, 11:53    [7332186]     Ответить | Цитировать Сообщить модератору
 Re: Гигантский поиск  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Crimean
а проблема прав "для динамики" в sql 2005 и старше решается with execute as опцией


Давайте подробней.
Рассмотрим 2 варианата. Первый доступ юзерам на таблицы давать. Второй, что прямой доступ не давать.
1. "with execute as caller" на мою процедуру. Тогда на SP_EXECUTESQL тоже вызывается из под юзера. Т.е. что получается, надо давать права на нее. Так не получится.

2. "with execute as owner" на мою процедуру. SP_EXECUTESQL тоже отработает с правами owner'а. Ну тут проблем вроде бы нет.

Подскажите, если где то ошибаюсь.
23 июн 09, 13:19    [7332764]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить