How Can I Safely Use Parameters with the LIKE Statement in SQL to Prevent Injection Attacks?

Using Parameters with LIKE Statement in SQL Queries and Preventing SQL Injections

In database querying, using parameters is a crucial practice for preventing malicious SQL injection attacks. However, when working with the LIKE statement, certain syntax considerations arise.

Question: Is it possible to use parameters in a LIKE statement and if so, how?

Answer: Yes, it is possible to use parameters in a LIKE statement. However, it's important to handle the string concatenation appropriately.

Syntax for Parameterized LIKE Statement:

SELECT * FROM table_name WHERE (column_name LIKE @parameter)

Using Parameters in VB.NET:

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

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

Explanation:

• The LIKE statement compares the value of the body or title column to the given parameter @query.
• The @query parameter is declared and its value is constructed dynamically, including the "%" signs as wildcards.
• By using parameters, we can prevent SQL injection attacks by ensuring that the injected string is treated as a literal value and not executed as malicious code.

Example Query:

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

This query will retrieve all records where the body or title column contains the substring "max".

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