Custom searches and LoadForCriteria

[psterritt]

I'm trying to get better search results for a customer who wants to continue to use the standard (not advanced) search. They want the search to return results where the search criteria are found in product name, description or SearchKeywords.

I've had some luck by modifying SearchPage.ascx.cs. I'm parsing the words out of the entered keywords, splitting them out by spaces, and building a query statement using 'LIKE' statements for each of the fields, so a search for 'valve cover' would generate

(Name LIKE '%valve%' and Name LIKE '%cover%') or (Description LIKE ''%valve%' and Description LIKE '%cover%')or (SearchKeywords LIKE ''%valve%' and SearchKeywords LIKE '%cover%')

The resulting string is passed to ProductDataSource.LoadForCriteria() and the List<Product> that results is used for the ProductList.DataSource that would normally be returned by ProductDataSource.NarrowSearch.

I assume an approach like this would have little risk of something like SQL injection attacks, since it breaks up the keywords, but are there any other possible security/attack vulnerabilites in this approach?

Are there any other drawbacks to this approach?

Thanks!

[mazhar]

You can write some custom SQL Injection detection regular expression and then before building criteria make sure entered text doesn't contain any SQL via validating through that regular expression.

[jmestep]

There is also StringHelper.SafeSqlString()
In the source code, here is what it does:
public static string SafeSqlString(string str)
{
if (str == null)
{
return string.Empty;
}

return str.Replace("'", "''"); // that is doubleqoute, singlequote doubleqoute, doublequote, two single quotes, double quote
}

[psterritt]

Thanks, Judy. Amazing how many fonts make it impossible to distinguish single quotes from doubles!