Проблемы, связанные с полнотекстовым поиском по SQL Server 2008

Я построил запрос T-SQL следующим образом:

DECLARE @search nvarchar(1000) = 'FORMSOF(INFLECTIONAL,hills) AND FORMSOF(INFLECTIONAL,print) AND FORMSOF(INFLECTIONAL,emergency)'

SELECT * FROM Tickets
WHERE ID IN (
                -- unioned subqueries using CONTAINSTABLE
                            ...
            )

GUI для этого поиска будет страницей aspx с одним текстовым полем, в котором пользователь может выполнить поиск.

Я планирую каким-то образом построить термин поиска как пример выше (@search).

У меня есть некоторые проблемы, но:

Является ли пример поисковым термином выше лучшего или единственного способа включить в себя перегибы всех слов в поиске?
Я должен отделить слова и построить поисковый запрос в С# или T-SQL. Я склоняюсь к С# для принятия решений/циклов/строительства, но я хочу ваше мнение.
Я ненавижу строить SQL динамически из-за риска инъекции. Как я могу защититься от этого?
Следует ли вместо этого использовать FREETEXTTABLE? Есть ли способ заставить FREETEXT искать ВСЕ слова вместо ЛЮБОГО?
В общем, как еще вы это сделаете?

Ответ 1

Недавно я использовал полнотекстовый поиск, поэтому я постараюсь ответить на некоторые из ваших вопросов.

• "Я ненавижу строить sql динамически из-за риска инъекции. Как я могу защититься от этого?"

Я использовал метод sanitize следующим образом:

static string SanitizeInput(string searchPhrase)
    {
        if (searchPhrase.Length > 200)
            searchPhrase = searchPhrase.Substring(0, 200);

        searchPhrase = searchPhrase.Replace(";", " ");
        searchPhrase = searchPhrase.Replace("'", " ");
        searchPhrase = searchPhrase.Replace("--", " ");
        searchPhrase = searchPhrase.Replace("/*", " ");
        searchPhrase = searchPhrase.Replace("*/", " ");
        searchPhrase = searchPhrase.Replace("xp_", " ");

        return searchPhrase;
    }

• Следует ли вместо этого использовать FREETEXTTABLE? Есть ли способ заставить FREETEXT искать ВСЕ слова вместо ЛЮБОГО?

Я использовал FREETEXTTABLE, но мне было нужно какое-либо из слов. Насколько я читал об этом (и я читал совсем немного), вы должны использовать CONTAINSTABLE для поиска ВСЕ слов или разных комбинаций. FREETEXTTABLE кажется более легким решением, но не тем, которое нужно выбрать, когда вы хотите более глубокие настройки.

Ответ 2

Дэн, мне нравится ваш метод SanitizeInput. Я реорганизовал его, чтобы сделать его более компактным и немного улучшить производительность.

 static string SanitizeInput(string searchPhrase, int maxLength)
        {
            Regex r = new Regex(@";|'|--|xp_|/\*|\*/", RegexOptions.Compiled);
            return r.Replace(searchPhrase.Substring(0, searchPhrase.Length > maxLength ? maxLength : searchPhrase.Length), " ");
        }

        static string SanitizeInput(string searchPhrase)
        {
            const int MAX_SEARCH_PHRASE_LENGTH = 200;
            return SanitizeInput(searchPhrase, MAX_SEARCH_PHRASE_LENGTH);
        }

Я согласен с тем, что FreeTextTable является слишком легким для решения.

Ответ 3

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

Я бы рекомендовал построить поисковый запрос в С#; передавая окончательный термин поиска в качестве параметра, как уже упоминалось.

Насколько я помню, FREETEXTTABLE использует разрывы слов, чтобы полностью разложить условия поиска на свои отдельные компоненты. Однако оператор FREETEXTTABLE автоматически разлагает слова на флективные эквиваленты, поэтому вам не придется создавать сложный оператор CONTAINSTABLE, если вы решите его использовать.

Вы можете INNER JOIN получить результаты нескольких запросов FREETEXTTABLE для получения эквивалентного результата AND.

Ответ 4

Все наши поисковые запросы находятся в столбцах базы данных с предопределенными действительными символами. Наш алгоритм поиска включает это с регулярным выражением, которое допускает только эти предопределенные символы. Из-за этого экранирование в строке поиска не требуется. Наше регулярное выражение уничтожает любые попытки инъекций в веб-коде (asp и aspx). Для стандартных комментариев пользователей мы используем экранирование, которое меняет все символы, которые могут быть использованы для вреда в SQL, ASP, ASPX и Javascript.
Сайт TransStar http://latranstar.tann.com/ использует расширенную форму Soundex для поиска имен улиц, адресов и городов в любой точке Южной Калифорнии. Soundex сам по себе исключает необходимость использования кода для инъекций, поскольку он работает только с альфа-символами.