How to Build a Secure MySQLi Prepared Statement with Variable LIKE Conditions?

Building a SELECT Query with Variable LIKE Conditions as a mysqli Prepared Statement

When working with user input, it's essential to prepare statements to prevent SQL injections and ensure data integrity. This becomes challenging when the number of LIKE conditions is variable.

Problem Overview

The code snippet attempts to solve this issue by dynamically creating the LIKE conditions based on user input. However, it fails to correctly format the parameters for the prepared statement.

Solution

The solution lies in placing the % wildcards around the parameters, not the placeholders. This ensures that the parameters are used to filter the query.

Detailed Explanation

1. Initialize Ingredients:

   a. Create an array of WHERE clause expressions using ORs.
   b. Determine the data types of the values (in this case, strings).
   c. Combine the data types and parameters into a single array.

2. Prepare Conditions:

   a. Convert user input into an array of unique values.
   b. For each value, create a LIKE expression.
   c. Update the parameters array to include data types and wildcarded values.

3. Prepare Query:

   a. Start with a basic SELECT * query.
   b. If there are conditions, add a WHERE clause with the dynamic expressions.

4. Bind Parameters and Execute:

   a. Bind the parameter array to the prepared statement using splat operator (...).
   b. Execute the statement and retrieve results if any.

Example Code:

$string = "Bill N_d Dave";

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

$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);
}

foreach ($result as $row) {
    echo "<div>{$row['name']}</div>\n";
}

Additional Notes:

• If no conditions are specified, execute the query without a WHERE clause.
• Consider using addcslashes() to escape wildcards in user input, if necessary.

The above is the detailed content of How to Build a Secure MySQLi Prepared Statement with Variable LIKE Conditions?. For more information, please follow other related articles on the PHP Chinese website!