2

I've got a query builder that's been built in house which is using a full text index in order to perform description searches.

The query is built and parametrized and I was wondering the best way to encode the form field from the website in order to pass search strings such as:

  1. Covered by
  2. "red" near "yellow"
  3. red" fish

Thanks

Davide Piras
  • 43,984
  • 10
  • 98
  • 147
RubbleFord
  • 7,456
  • 9
  • 50
  • 80
  • Do you mean you want to encode parameters values to avoid SQL injection and to support special chars at the same time? what type are the input parameters of your stored proc? – Davide Piras May 25 '11 at 07:52
  • It's not using a stored procedure it's a parametrized query, so the query looks like Select x from y where q = @param1. I want to be able to expose the power of the full text index to the users. So they can say give me everything that has "RED" near "Shirt" for example. – RubbleFord May 25 '11 at 08:01
  • The parameter type in this instance is string, sorry forgot to mention in the previous comment. – RubbleFord May 25 '11 at 08:11

1 Answers1

0

If you want to use full text search you should use where clause with other specific functions ( not just = or like ).

@param1 will still be a string (nvarchar eventually); see here:

Querying SQL Server Using Full-Text Search

for example, you query in this way (from MSDN):

USE AdventureWorks2008R2;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'performance'
SELECT Description 
FROM Production.ProductDescription 
WHERE CONTAINS(Description, @SearchWord);

about special chars and escaping them, just have a look here: SQL Server Full Text Search Escape Characters?

Community
  • 1
  • 1
Davide Piras
  • 43,984
  • 10
  • 98
  • 147
  • That's exactly what I'm doing, but passing the above examples doesn't work. – RubbleFord May 25 '11 at 08:49
  • Show your SQL, this: Select x from y where q = @param1 does not work, does not use full text at all... where is the CONTAINS? how do you execute the query? – Davide Piras May 25 '11 at 08:51
  • Sorry that wasn't a good example I've now solved it using a microsoft article. However the article to full text search characters has solved the issue. – RubbleFord May 25 '11 at 09:46