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

In your example, you have the @search variable already defined. As a rule of thumb, you shouldn't include dynamically concatenated text into raw SQL, due to the risk of injection. However, you can of course set the value of @search in the calling command object from your application. This completely negates the risk of injection attacks.

I would recommend construction of the search term in C#; passing the final search term in as a parameter like already mentioned.

As far as I recall, FREETEXTTABLE uses word breakers to completely decompose the search terms into their individual components. However, the FREETEXTTABLE operator automatically decomposes words into inflectional equivalents also, so you won't have to construct a complex CONTAINSTABLE operator if you decide to use it.

You could INNER JOIN the results of multiple FREETEXTTABLE queries to produce an equivalent AND result.

Answer 3

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.