How to Securely Build Dynamic LIKE Queries with MySQLi Prepared Statements?

Using Prepared Statements with Dynamic LIKE Conditions

To construct a SELECT query with a dynamic number of LIKE conditions using MySQLi prepared statements, it is essential to wrap the percentage signs (%) around the parameters, not the placeholders.

Here's a step-by-step guide to implement this in PHP:

1. Extract and Prepare Expressions and Data Types:
   Separate the user input into individual search terms using explode(). Initialize arrays for storing WHERE clause expressions ($conditions) and the combination of data types and values ($parameters).

$conditions = [];
$parameters = [''];

2. Construct Expressions and Parameters:
   Iterate over the search terms, creating a series of LIKE expressions and adding corresponding data types to $parameters.

foreach ($search_exploded as $value) {
    $conditions[] = "name LIKE ?";
    $parameters[0] .= 's';
    $parameters[] = "%{$value}%";
}

3. Prepare and Execute Query:
   Concatenate the $conditions into the WHERE clause. Prepare the statement with the query, bind the $parameters using the splat operator (...), and execute it.

$query = "SELECT * FROM info";
if ($conditions) {
    $stmt = $mysqli->prepare($query . ' WHERE ' . implode(' OR ', $conditions));
    $stmt->bind_param(...$parameters);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $result = $conn->query($query);
}

By following these steps, you can effectively execute queries with a dynamic number of LIKE conditions using MySQLi prepared statements, ensuring both flexibility and security.

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