Консолидировано: значения SQL Pass, разделенные запятыми, в SP для фильтрации

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

У меня есть таблица «Предмет», и у меня есть поисковая СП для нее. Я хочу иметь возможность искать несколько кодов предметов, например:

- Table structure : Item(Id INT, ItemCode nvarchar(20))
- Filter query format: SELECT * FROM Item WHERE ItemCode IN ('xx','yy','zz')

Я хочу сделать это динамически, используя хранимую процедуру. Я передам параметр @ItemCodes, значения которого будут разделены запятыми (','), и поиск будет выполнен, как указано выше.

Ну, я уже посетил много сообщений \ форумов, и вот несколько тем:

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

Также другие подходы, такие как XML и т. Д., Если они усложняют ситуацию, я не могу их использовать.

И, наконец, пожалуйста, никаких дополнительных трюков JOIN для повышения производительности. Я должен управлять не только сложностью, но и производительностью.

http://www.sommarskog.se/arrays-in-sql-2005.html Это потребует от меня «объявить» тип параметра при передаче его в SP, это искажает абстракцию (я не устанавливаю тип ни в одном из моих параметров, потому что каждый из к ним относятся в общем случае)

http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters. Это структурированный подход, но он увеличивает сложность, требует изменения уровня структуры БД и не является абстрактным, как указано выше.

http://madprops.org/blog/splitting-text-into-words-in-sql-revisited/ Что ж, похоже, это соответствует моим старым решениям. Вот что я делал в прошлом -

I created an SQL function : [GetTableFromValues] (returns a temp table populated each item (one per row) from the comma separated @ItemCodes)

И вот как я использую его в моем фильтре WHERE caluse в SP -

SELECT * FROM Item WHERE ItemCode in (SELECT * FROM[dbo].[GetTableFromValues](@ItemCodes))

Этот многоразовый, выглядит простым и коротким (конечно, сравнительно). Все, что я пропустил, или любой эксперт с лучшим решением (очевидно, «в рамках» ограничений, указанных выше).

Спасибо.


csv sql sql-server filtering
person Hemant Tank    schedule 02.11.2009    source источник
comment
Что ж, мне предлагали многие эксперты, а также многие сообщения на форумах, что использование динамического T-SQL непосредственно из приложения ... имеет те же преимущества, что и развертывание SP !!! Это переворачивает мою проблему вверх ногами, я могу обойтись без полноценной динамической генерации SQL и на самом деле творить чудеса из кода моего приложения ... звучит ли это справедливой идеей enuf?   -  person Hemant Tank    schedule 04.11.2009
comment
bloggingabout.net/ блоги / adelkhalil / архив / 01.06.2008 / ---------------------------------- ---------------- codingefficiency.com/2009/08/03/   -  person Hemant Tank    schedule 04.11.2009
comment
Я понимаю, что вы смотрите на это с точки зрения производительности, но еще одна веская причина сохранить построение динамических запросов в приложении состоит в том, что делать это в SP с TSQL некрасиво и сложно поддерживать. Если вы действительно хотели использовать SP, я думаю, что лучший подход - использовать UDF GetTableFromValues, поскольку это позволяет избежать написания динамического SQL в SP.   -  person JulianM    schedule 13.11.2009

Ответы (1)


arrow_upward
0
arrow_downward

Я думаю, что использование динамического T-SQL будет прагматичным в этом сценарии. Если вы будете осторожны с дизайном, динамический sql работает как шарм. Я использовал его в бесчисленных проектах, когда он мне подходил. С учетом сказанного позвольте мне рассмотреть две ваши основные проблемы - производительность и внедрение sql.

Что касается производительности, прочтите справочник T-SQL по параметризованному динамическому sql и sp_executesql (вместо sp_execute). Комбинация параметризованного sql и использования sp_executesql избавит вас от проблем с производительностью, гарантируя, что планы запросов будут повторно использоваться и sp_recompiles избежать! Я использовал динамический sql даже в контексте реального времени, и он отлично работает с этими двумя элементами. Для вашего удовлетворения вы можете запустить цикл из миллиона или около того вызовов sp с двумя оптимизациями и без них, а также использовать профилировщик sql для отслеживания событий sp_recompile.

Теперь по поводу SQL-инъекции. Это будет проблемой, если вы используете неправильный пользовательский виджет, например текстовое поле, чтобы пользователь мог вводить коды элементов. В этом сценарии возможно, что хакер может написать операторы select и попытаться извлечь информацию о вашей системе. Вы можете написать код, чтобы предотвратить это, но я думаю, что пойти по этому пути - ловушка. Вместо этого рассмотрите возможность использования соответствующего пользовательского виджета, такого как список (в зависимости от вашей клиентской платформы), который позволяет множественный выбор. В этом случае пользователь просто выберет из списка «представленных элементов», и ваш код сгенерирует строку, содержащую соответствующие коды элементов. Обычно вы не передаете пользовательский текст в динамический sql sp! Вы даже можете использовать удобные виджеты выбора на основе JQuery, но суть в том, что пользователь не может вводить неприемлемый текст, попадающий на ваш уровень данных.

Затем вам просто нужна простая хранимая процедура в базе данных, которая принимает параметр для кодов элементов (например, '' 'xyz' '' ',' '' abc '' '). Внутренне он должен использовать процедуру sp_executesql с параметризованным динамическим запросом.

Надеюсь, это поможет. -Табрез

person Tabrez    schedule 21.11.2011
comment
Я согласен, что T-SQL может значительно улучшить ситуацию, если использовать его с полной осторожностью. Однако, возвращаясь к моему сценарию, T-SQL всегда будет подвергаться угрозе со стороны SQL-Injection, и разработчик приложения должен убедиться, что у него есть безопасный метод ввода. Я надеюсь, что мой метод избавит разработчика от этих забот, а также обеспечит чистый и простой подход к использованию динамических значений, разделенных запятыми, в фильтре. - person Hemant Tank; 22.11.2011
comment
Как я уже упоминал, с помощью маршрута sp вы можете избежать внедрения sql, ЕСЛИ ВЫ КОНТРОЛИРУЕТЕ, как вводится интерфейсный ввод. Если разработчик приложения находится в другой группе и вы не можете контролировать сбор данных, лучше иметь простые проверки ключевых слов SQL, таких как SELECT, символ @ (системные переменные) и, возможно, круглые скобки (для проверки вызовов функций). Я думаю, что по большей части наличие параметризованной хранимой процедуры защитит вас от этих проблем, потому что параметры SQL закодированы в кавычки. Однако, если есть сомнения, вышеуказанные проверки можно поместить в функцию для проверки строки перед запуском sql. - person Tabrez; 23.11.2011
comment
Лучше иметь функцию, потому что она будет глобальной и полезной и в других SP. И ограничьте логику / управление / изменения одной функцией. Лучше для дальнейших улучшений. Спасибо. - person Hemant Tank; 24.11.2011