M
matt
Hi,
I'm writing a visual basic application which searches a database of e-mail
messages, based on certain criteria. I'm using dynamic SQL and an exec
sp_executesql statement on the dynamic sql string. I'm using full text
indexing, and a contains clause to search certain columns, and I'm trying to
figure out the best way to validate the input passed from visual basic to my
sql query. SQL has certain "noise" words that are ignored, and generate
errors in my program. The requirement for the validation is...
1. search strings cannot start with (and, or, not)
2. single search terms must be surrounded by quotes, and cannot be a noise
word.
3. phrases(two or more words separated by spaces) must be surrounded by
quotes, and may contain noise words, but all words within the phrase must
not be noise words.
I found trying to write something to validate this is quite a headache, and
I was wondering if anyone had thoughts on an efficient way to do this. A
list of noise words can be found at:
http://beinecke.library.yale.edu/SQLIgnoredWords.html
The part of the dynamic sql search looks like this:
IF @SubjectSearch IS NOT NULL
select @sql = @sql + ' AND CONTAINS(M.Subject, @xSubjectSearch)'
Thanks in advance.
-Matt
I'm writing a visual basic application which searches a database of e-mail
messages, based on certain criteria. I'm using dynamic SQL and an exec
sp_executesql statement on the dynamic sql string. I'm using full text
indexing, and a contains clause to search certain columns, and I'm trying to
figure out the best way to validate the input passed from visual basic to my
sql query. SQL has certain "noise" words that are ignored, and generate
errors in my program. The requirement for the validation is...
1. search strings cannot start with (and, or, not)
2. single search terms must be surrounded by quotes, and cannot be a noise
word.
3. phrases(two or more words separated by spaces) must be surrounded by
quotes, and may contain noise words, but all words within the phrase must
not be noise words.
I found trying to write something to validate this is quite a headache, and
I was wondering if anyone had thoughts on an efficient way to do this. A
list of noise words can be found at:
http://beinecke.library.yale.edu/SQLIgnoredWords.html
The part of the dynamic sql search looks like this:
IF @SubjectSearch IS NOT NULL
select @sql = @sql + ' AND CONTAINS(M.Subject, @xSubjectSearch)'
Thanks in advance.
-Matt