Dynamic SQL Search - Variable Keywords

Question

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

The problem with the keywords is that the user has variable numeric words, and we want to find several columns for each keyword. Since we cannot create a separate parameter for each keyword, how can we create a secure query?

Example:

@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

What is the best way to handle this and maintain protection against SQL injection?

Answer 1

, , SQL- . SQL, SQL-.

:

string sql  = "SELECT Name, Title FROM Staff WHERE UserName=@UserId";
using (SqlCommand cmd = new SqlCommand(sql))
{
  cmd.Parameters.Add("@UserId", SqlType.VarChar).Value = "smithj";

SQL , , . , , , TSQL, .

Answer 2

3 .

• . , - .

  SELECT * 
  FROM calendar c
     JOIN dbo.fnListToTable(@Keywords) k 
         ON c.keyword = k.keyword  
  

• N

  CREATE PROC spTest
  @Keyword1 varchar(100),
  @Keyword2 varchar(100),
  .... 
  

• escaping string TSQL .

Answer 3

• , , [a-zA-Z], - , , - . , , .

• sambo99 # 1, ( ) :

:

SELECT DISTINCT event_name
FROM calendar
INNER JOIN #keywords
    ON event_name LIKE '%' + #keywords.keyword + '%'
    OR event_description LIKE '%' + #keywords.keyword + '%'

• In fact, you can generate an SP with a large number of parameters instead of manually encoding (set the default value to "or NULL depending on your preferences when encoding your searches"). If you find that you need additional parameters, it would be easy to increase the number of generated parameters.

• You can move the search to a full-text index outside the database, such as Lucene, and then use the Lucene results to display the corresponding database rows.

Answer 4

You can try the following:

SELECT * FROM [tablename] WHERE LIKE % +keyword%