Concerns about SQL Server 2008 Full Text Search

Question

I have built a T-SQL query like this:

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
                            ...
            )

The GUI for this search will be an aspx page with a single textbox where the user can search.

I plan to somehow construct the search term to be like the example above (@search).

I have some concerns, though:

• Is the example search term above the best or only way to include the inflections of all words in the search?
• Should I separate the words and construct the search term in C# or T-SQL. I tend to lean toward C# for decisions/looping/construction, but I want your opinion.
• I hate building SQL dynamically because of the risk of injection. How can I guard against this?
• Should I use FREETEXTTABLE instead? Is there a way to make FREETEXT look for ALL words instead of ANY?
• In general, how else would you do this?

Answer 1

I recently used Full-Text Search, so I'll try to answer some of your questions.

• "I hate building sql dynamically because of the risk of injection. How can I guard against this?"

I used a sanitize method like this:

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;
    }

• Should I use FREETEXTTABLE instead? Is there a way to make FREETEXT look for ALL words instead of ANY?

I did use FREETEXTTABLE, but I needed any of the words. As much as I've read about it (and I've read quite a bit), you have to use CONTAINSTABLE to search for ALL words, or different combinations. FREETEXTTABLE seems to be the lighter solution, but not the one to pick when you want deeper customizations.

Answer 2

Dan, I like your SanitizeInput method. I refactored it to make it more compact and enhance performance a little.

 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);
        }

I agree that FreeTextTable is too lightweight of a solution.