Sql-server – Fulltext search limiting users possibilities

full-text-searchsql servert-sql

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

So is there any better suggestions?

Best Answer

Here are three suggestions.

Suggestion 1. Use plainto_tsquery when you convert the user provided search string to PostgreSQL's tsquery, see the PostgreSQL docs. — I think that plainto_tsquery discards any special magical characters + AND, OR etcetera. (But I'm not completely sure. You'd better verify this so you don't do anything dangerous) Oops I didn't notice that this was about SQL Server not PostgreSQL.

Suggestion 2. Build a HTML form for advanced search queries, with fields like:

Include all these words: ...
Include any of these words: ...
Exclude all these ...

And so on. Next, construct the otherwise dangerous full text search expression yourself in SQL Server syntax.

Suggestion 3. You could provide a single search text field, and let e.g. "-someword" mean that "someword" should be excluded, and "+anotherword" mean that "anotherword" must be included. Then, you build your own parser for this search field, and convert the resulting "abstract search tree" to something SQL Server understands, and give it to SQL Server.

Related Solutions

Sql-server – SQL Server Fulltext search against big amount of search terms over some period of time

Execution plan XML would be useful here but as a shot in the dark:

Daft as it may sound, try pushing either the [id] or date predicate into the CONTAINSTABLE query. See SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned - Consider embedding filter conditions as keywords in the indexed text.
I don't remember where but I recall reading an article or blog post some time ago that flagged big OR full-text queries as problematic. IIRC the suggested hack/workaround was to issue a UNION query instead.

For your example, the UNION for 2) would be along the lines of:

INSERT
    @processed_rules
    (
    rule_id
    , story_id
    )
SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term one"')

UNION

SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term two"')

UNION

  SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term three"')

Sql-server – sql server full text search – fuzzy searching

You could try an implementation of one of the string distance functions. Here's a T-SQL implementation of the Levenshtein distance taken from here. That post does suggest there's a performance issue with the function and scalar functions generally can be bad for performance when used in queries, but worth a look. You could always try and convert it to a table function.

enter image description here

Best Answer

Related Solutions

Sql-server – SQL Server Fulltext search against big amount of search terms over some period of time

Sql-server – sql server full text search – fuzzy searching

Related Question