We have a web site with a search box. The search uses a fulltext index column. However, we just cant feed the text the user has input into this stored procedure.
CREATE PROCEDURE [dbo].[SearchPages] @Term varchar(200) AS BEGIN SELECT pc.SearchData, from PageContent pc where contains(pc.SearchData, @Term) END
Searches with space in them fails, also there is a pletora of sql functions we do not want to expose to the users like
NEAR((bike,control), 10, TRUE)
and the like or binary operators like AND or OR.
So we need to escape the term in some way.
One way that immediately comes in mind is to put an AND between every word. However, it doesnt feel like thats a good solution. It reminds me to much about 1998 style coding.
So is there any better suggestions?