How do you handle user input during full-text search on ms sql server?

Question

Hi all, I have a simple search mechanism on ms sql server that uses full-text "contains". I pass search value as a parameter to contains predicate - directly from user's input (via asp.net web site). The problem is that i get sql exception in variety of cases: - when user inputs two words, like 'simple case' - when user adds qoute to search value, like 'test"'

I have made test using query like below:

declare @val nvarchar(40)
set @val = N'test"' -- or 'simple case'

select x.* FROM xx as x
where CONTAINS(x.[name], @val)

Usually i get: "Syntax error near 'xxx' in the full-text search condition 'xxx'." The problem is that i would like to allow users to make advanced queries with '*', 'or' and 'and' conditions.

How do You handle user-input? Do You qoutename value?

Answer 1

By the errors you are getting i would say you are not using parameters for your queries. This is very dangerous and allows your users to perform sql injections.

Now, as for your question, i think the best option would be for you to set 2 text entry types, one for regular users, another for advanced users so you can know where to parse for advanced queries.

Answer 2

In our app we use our own simple query language for the user input: just operands (words or phrases with optional wildcards), operators (AND, OR, NOT) and parentheses.

We parse the user input, break it down into it's constituent parts and check the syntax etc.

If we're happy that the input is valid, we rebuild it into the equivalent CONTAINS condition and send that as a parameter to our search sproc. Because we're creating the condition ourselves we know that it's syntactically correct.

If the user input is invalid then we're able to return a meaningful error message to the user without needing to connect to the database at all.

Answer 3

I built an app years ago using this; what I did is the front-end gives you a text box to type your terms and it has a drop-down to select between a few options like so:

<option selected value="AND">All of the words entered</option>
<option value="OR">Any of the words entered</option>
<option value="ALL">The exact phrase entered</option>

So I don't want them typing ands or ors - I do that for them. I do let them quote a sub-phrase.

Below is some code that accomplishes this. The resulting phrase is sent as a parameter to a procedure; as the commenter above mentioned you really must do this to protect against SQL injection. Unfortunately this code is legacy ASP VBScript and isn't even well-written by that language's standards but maybe this gives you an idea.

 function formatContainsString (sCriteria, sANDorOR)
    dim sReturnBuf 'where we build our string
    dim sWordList 'sCriteria split
    dim lCurPos   'where we are at in sWordList
    dim bInnerQuotes ' an open quote was found
    if sCriteria = "" or sANDorOR = "" then
     formatContainsString = ""
     exit function
    end if

    ' If the passed parameter is 'ALL' then use the exact phrase typed by users
    if sANDorOR = "ALL"  then
     formatContainsString = "'"  & chr(34) & sCriteria  & chr(34) & "'"
     Exit Function
    End If

    sReturnBuf = "'"
    sWordList = split(sCriteria," ")
    for lCurPos = 0 to ubound(sWordList)
     if bInnerQuotes then 'want to pass this as a single phrase
      sReturnBuf = sReturnBuf & " " & sWordList(lCurPos)
      if right(sWordList(lCurPos),1) = chr(34) then
         sReturnBuf = left(sReturnBuf,len(sReturnBuf)-1) & "*" & chr(34)
       sReturnBuf = sReturnBuf & " " & sANDorOR & " "'phrase is over
       bInnerQuotes = False
      end if
     else  
      if left(sWordList(lCurPos),1) = chr(34) then 
       sReturnBuf = sReturnBuf & sWordList(lCurPos)
       bInnerQuotes = True
      else
       sReturnBuf = sReturnBuf & chr(34) & sWordList(lCurPos) & "*" & _ 
        chr(34) & " " & sANDorOR & " "'quote the word
      end if
     end if
    next
    'finally, remove the last AND or OR... and append the tick
    formatContainsString = left(sReturnBuf,len(sReturnBuf)-(len(sANDorOR)+1)) _ 
     & "'"

end function