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

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?

From stackoverflow

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

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

  michal zygula : This is exactly kind of answer I was expecting. Can You provide code sample or link...

• 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