How Can I Safely Use MySQL's LIKE '%{$var}%' with Prepared Statements?

Safeguarding Your Database: Proper Use of LIKE '%{$var}%' in Prepared Statements

Building dynamic search features that respond to user input requires careful consideration of security. A common scenario involves searching for usernames containing a partial match, using MySQL's LIKE operator. However, directly incorporating user input into a LIKE clause within a prepared statement is prone to SQL injection vulnerabilities.

Incorrect approaches often involve attempting to embed placeholders within the LIKE wildcard characters, such as SELECT * FROM users WHERE username LIKE '%{?}%'. This is flawed because the placeholder syntax is not correctly interpreted by the database driver.

The key to secure implementation lies in constructing the LIKE expression before preparing the statement. This ensures the user input is properly sanitized and treated as data, not executable code. Here's the correct method:

<code class="language-php">$searchParam = "%" . $yourParam . "%"; // Prepend and append wildcards
$stmt = $mysqli->prepare("SELECT * FROM REGISTRY WHERE name LIKE ?");
$stmt->bind_param("s", $searchParam); // Bind the complete LIKE expression
$stmt->execute();</code>

In this improved example, the % wildcards are added to the user-supplied $yourParam to create the complete LIKE pattern stored in $searchParam. This complete pattern is then bound to the prepared statement's placeholder using bind_param with the string type ("s"). This prevents SQL injection while maintaining the dynamic search functionality. This approach guarantees both security and the efficiency offered by prepared statements.

The above is the detailed content of How Can I Safely Use MySQL's LIKE '%{$var}%' with Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!