How Can I Safely Use Parameters with SQL LIKE Statements to Prevent SQL Injection?

Using Parameters in SQL LIKE Statement

When creating a search function, it's imperative to employ parameters to safeguard against SQL injection threats. However, using parameters in a LIKE statement may pose challenges, as demonstrated by the following query:

SELECT * FROM compliance_corner WHERE (body LIKE '%@query%') OR (title LIKE '%@query%')

This query is susceptible to SQL injection because the parameters are not properly sanitized. To rectify this issue, the parameters must be properly defined and assigned using a value like:

Dim cmd as New SqlCommand(
 "SELECT * FROM compliance_corner"_
  + " WHERE (body LIKE @query )"_ 
  + " OR (title LIKE @query)")

cmd.Parameters.Add("@query", "%" +searchString +"%")

In this example, the parameter @query is defined using the value of searchString. This ensures that the user's input, searchString, is properly sanitized and the query is protected against SQL injection.

Additionally, it's worth noting that the query returns results when executed directly in SQL Server:

SELECT * FROM compliance_corner WHERE (body LIKE '%max%') OR (title LIKE '%max%')

This is because the query is executed without parameters, and the LIKE statement compares the string %max% directly with the contents of the body and title columns. However, when executed with parameters, the LIKE statement compares the parameter value, @query, with the columns' contents, which are properly sanitized, resulting in no matches being returned.

The above is the detailed content of How Can I Safely Use Parameters with SQL LIKE Statements to Prevent SQL Injection?. For more information, please follow other related articles on the PHP Chinese website!