Can Parameters Be Used with the LIKE Statement in SQL for Pattern Matching?

How to Use Parameters and the LIKE Statement in SQL

In SQL, the LIKE statement allows you to perform pattern matching within strings. However, using it without proper precautions can lead to SQL injection attacks. To prevent such attacks, parameterized queries can be employed.

Original Question

In an effort to limit SQL injection vulnerabilities, the following query was constructed using parameters:

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

However, executing this query in a program yields no results. Can parameters be used in this manner, or are they only applicable in situations like the following:

SELECT * FROM compliance_corner WHERE body LIKE '%<string>%'

Solution

Parameters can be used in LIKE statements as intended. The syntax in the original query is incorrect. Here is the correct way to use parameters in this scenario:

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

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

This query will correctly match strings that contain the search string within the body or title fields.

VB.NET Considerations

The provided solution is syntactically correct for VB.NET. However, the actual implementation may vary depending on the specific database provider and connection method used. Refer to the appropriate documentation for further guidance.

The above is the detailed content of Can Parameters Be Used with the LIKE Statement in SQL for Pattern Matching?. For more information, please follow other related articles on the PHP Chinese website!