SQL CONTAINS clause parameter substitution problem

[Stargate4004]

Hi,

I'm in the process of parameterising the SQL SELECT statements in my
ASP.NET application so that I don't directly include user input in
the SQL (to protect against SQL Injection attacks). I've run into a
problem with a statement which has a CONTAINS clause, which seems to
result from parameters within quotes not being substitued. The
following examples illustrate the results when the user inputs a single
search string ("bottle") and two search strings ("bottle" and
"corkscrew").

With user input embedded in the SQL it all works as expected for both
single and multiple search strings:

SELECT * FROM CatalogueItems WHERE (CONTAINS (Description,
'"bottle"'))

This returns all records whose Description field contains the word
"bottle" (as expected). Note the single and double quotes around
the "bottle" search string. Without the single quotes I get an
"Incorrect syntax near 'bottle'" error. The double quotes seem
optional, but I included them as they are needed if multiple search
strings are specified (see below).

SELECT * FROM CatalogueItems WHERE (CONTAINS (Description,
'"bottle" AND "corkscrew"'))

This returns all records whose Description field contains the word
"bottle" and the word "corkscrew" (as expected). Note the
single and double quotes around the search strings. The double quotes
are needed because multiple search strings are specified (without them
I get an "Incorrect syntax near the keyword 'AND'" error).

My problems began when I modified the (C#) code to use an SqlCommand
Object with parameters instead of directly embedding the user input in
the SQL...

Searching using a single parameter works fine:

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add(new SqlParameter("@SearchWord1",
SqlDbType.Char, 6));
sqlCommand.Parameters["@SearchWord1"].Value = "bottle";

sqlCommand.CommandText = "SELECT * from CatalogueItems WHERE (CONTAINS
(Description, @SearchWord1))

This returns all records whose Description field contains the word
"bottle" (as expected). Note the absence of single quotes around
the "@SearchWord1" parameter. If the quotes are added, then I get
no results.

Searching with two parameters fails:

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add(new SqlParameter("@SearchWord1",
SqlDbType.Char, 6));
sqlCommand.Parameters["@SearchWord1"].Value = "bottle";

sqlCommand.Parameters.Add(new SqlParameter("@SearchWord2",
SqlDbType.Char, 9));
sqlCommand.Parameters["@SearchWord2"].Value = "corkscrew";

sqlCommand.CommandText = "SELECT * from CatalogueItems WHERE (CONTAINS
(Description, '"@SearchWord1" AND '"@SearchWord2"'))

This does not return any results. Omitting single and/or double quotes
from around the parameters causes varying types of failure:

CONTAINS (Description, @SearchWord1 and @SearchWord2)

Gives "Incorrect syntax near the keyword 'AND'" error.

CONTAINS (Description, '@SearchWord1 and @SearchWord2')

Gives no hits.

It looks like enclosing the parameters in quotes prevents their
substitution, but leaving the quotes out causes SQL syntax errors.

Can anyone tell me how to do this please?

 

[Stargate4004]

Further to my earlier post, I've discovered this workaround:

SELECT * from CatalogueItems WHERE (CONTAINS (Description,
@SearchWord1) AND CONTAINS (Description, @SearchWord2) )

No quotes, so the parameter substition behaves properly. It could
generate a very long SQL statement if my user enters many search
strings though, so if anyone out there has any better ideas then I'd
love to hear them.

Thanks.

 

[michael.lang]

You may want to think about making this a stored procedure. That way
the query gets cached and reused on subsequent calls.

Create a stored procedures that takes a string @SearchWords parameter.
In the procedure split the value on some delimeter (comma or pipe).
Then in the where clause use that list in a SQL IN statement. When you
call the stored procedure, you concatenate the individual strings using
the same delimeter and pass that single string in the @SearchWords
parameter.

Sorry I don't have the exact sql code on hand.

I have also seen it done the way you have shown in your second post.

Michael Lang
XQuiSoft LLC
http://www.xquisoft.com/

 

[Stargate4004]

Thanks for your input Michael. I'm new to SQL Server and haven't used
stored procedures yet. Looks like this is a reason for me to learn
about them!