Dynamic SQL - Search Engine Query - Variable Number of Keywords

• chadmonahan

  Grasshopper

  Points: 13

  More actions

  September 30, 2008 at 9:39 pm

  #382900

  We are trying to update our classic asp search engine to protect it from SQL injection. We have a VB 6 function which builds a query dynamically by concatenating a query together based on the various search parameters. We have converted this to a stored procedure using dynamic sql for all parameters except for the keywords.

  The problem with keywords is that there are a variable number words supplied by the user and we want to search several columns for each keyword. Since we cannot create a separate parameter for each keyword, how can we build a safe query?

  Example

  [font="Courier New"]@CustomerId AS INT @Keywords AS NVARCHAR(MAX)

  @sql = 'SELECT event_name FROM calendar WHERE customer_id = @CustomerId '

  --(loop through each keyword passed in and concatenate)

  @sql = @sql + 'AND (event_name LIKE ''%' + @Keywords + '%'' OR event_details LIKE ''%' + @Keywords + '%'')'

  EXEC sp_executesql @sql N'@CustomerId INT, @CustomerId = @CustomerId[/font]

  What is the best way to handle this and maintaining protection from SQL injection?