How to Safely Use Prepared Statements with LIKE Queries in PHP?

Understanding prepared statements and LIKE queries

In PHP, when using prepared statements for LIKE queries, string parameters must be handled carefully to avoid potential errors. Prepared statements utilize placeholders (?) to prevent SQL injection while improving performance.

Case 1: Use '%{$var}%' to match username

The following code:

<code class="language-php">$sql = 'SELECT * FROM `users` WHERE username LIKE \'%{?}%\' ';</code>

throws an error because the placeholder ? is not properly defined in the prepared SQL statement.

Case 2: Use %{?}% to match username

The following code:

<code class="language-php">$sql = 'SELECT * FROM `users` WHERE username LIKE %{?}% ';</code>

will also fail because the syntax is invalid. The percent sign (%) cannot be used directly as a placeholder in prepared statements.

Solution: Use a concatenated LIKE variable

To resolve this issue, use the concatenated LIKE variable:

<code class="language-php">$likeVar = "%" . $yourParam . "%";
$stmt = $mysqli->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->bind_param("s", $likeVar);
$stmt->execute();</code>

In this example, $likeVar is constructed by combining a percent sign with user input $yourParam. The LIKE condition then uses the ? placeholder, which is bound to the concatenated string $likeVar during parameter binding.

This approach ensures that the percent sign is treated as a literal character rather than a syntax element in the prepared statement. It also allows case-insensitive searches by ensuring $likeVar contains the correct wildcard characters.

The above is the detailed content of How to Safely Use Prepared Statements with LIKE Queries in PHP?. For more information, please follow other related articles on the PHP Chinese website!