Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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. And then 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. And then you build your own parser for this seach field, and convert the resulting "abstract search tree" to something SQL Server understands, and give it to SQL Server.

share|improve this answer
    
Uh, this question is tagged [sql-server]. –  Jon Seigel Jul 13 '13 at 15:18
    
@JonSeigel You're correct. Now I edited/fixed my answer. (I was searching for PostgreSQL and had only PostgreSQL in my mind) –  KajMagnus Jul 13 '13 at 15:27
    
But what I wanted was a totally stupid search function where you can not exclude any words or use any finesses. –  Anders Lindén Jul 13 '13 at 22:45
add comment

Sql Server has a FREETEXT() function which works great for these kinds of queries.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.